vb6 ado how to add picture images to excel cell?

[xiaoyao]

Private Sub WriteImageToExcel(ByVal ExcelFilePath As String, ByVal ImageFilePath As String)
    Dim conn As New ADODB.Connection
    Dim binaryData() As Byte
    Dim sql As String
    Dim rs As New ADODB.Recordset

    ' ???Excel??
    conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended properties='Excel 12.0 Xml;Hdr=yes;IMEX=1';data source=" & ExcelFilePath

    ' ?????????
    binaryData = ReadBinaryFile(ImageFilePath)

    ' ????Excel????????????????????
    ' ???????????Excel????
    sql = "INSERT INTO [HiddenSheet$](ImageData) VALUES (?)"
    rs.Open sql, conn, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs.Fields("ImageData").AppendChunk binaryData
    rs.Update
    rs.Close
    conn.Close

I think this code is unable to display the picture.

[xiaoyao]

how to read pictures from excel sheet?
cell text is =DISPIMG("ID_74EDB220BEA24FB6A112C69353D9E16B",1)

load xml file? split all image by xml?

[wqweto]

You cannot use ADO to embed pictures in Excel sheets.

Not using ADO it's possible using copy/paste with HTML format.

Other option not using ADO is to use a raw BIFF12 writer like this sample: https://github.com/wqweto/Biff12Writ...mple/Form1.frm

cheers,
</wqw>

[xiaoyao]

can add images to xlsx files, and set cell str by ado?

i unzip a excel file:test.xlsx,for download images,xml files:

\xl\media\image1.png image2.png image3.png


unzip file by shell.application:

Duration: 866.4118 milliseconds [decompression 1]
Duration: 248.3394 milliseconds [decompression 2]
Duration: 395.8231 milliseconds [Extract image]
Duration: 489.3050 milliseconds [Extract image]

unzip by zlibwapi:

Duration: 2.9302 milliseconds [decompression 1]
Duration: 1.5231 milliseconds [decompression 2]
Duration: 1.5084 milliseconds [Extract image]
Duration: 1.7378 milliseconds [Extract image]

maybe can add files to this zip folder?

xl\cellimages.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<etc:cellImages xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData"><etc:cellImage><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="3" name="ID_40FEBD6A9E1C43FCA051741E6632D6F3"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId1"/><a:srcRect/><a:stretch><a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr><a:xfrm><a:off x="4253230" y="4576445"/><a:ext cx="4536440" cy="1504950"/></a:xfrm><a:prstGeom prst="rect"><a:avLst/></a:prstGeom></xdr:spPr></xdr:pic></etc:cellImage><etc:cellImage><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="5" name="ID_56A20461B3994F828D5574D4EFDD3FF9"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId1"/><a:srcRect/><a:stretch><a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr><a:xfrm><a:off x="4040505" y="6954520"/><a:ext cx="5080000" cy="1504950"/></a:xfrm><a:prstGeom prst="rect"><a:avLst/></a:prstGeom></xdr:spPr></xdr:pic></etc:cellImage><etc:cellImage><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="8" name="ID_74EDB220BEA24FB6A112C69353D9E16B"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId2" r:link="rId3"/><a:stretch><a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr><a:xfrm><a:off x="3467100" y="4165600"/><a:ext cx="1898650" cy="2311400"/></a:xfrm><a:prstGeom prst="rect"><a:avLst/></a:prstGeom><a:noFill/><a:ln><a:noFill/></a:ln></xdr:spPr></xdr:pic></etc:cellImage></etc:cellImages>

xl\_rels\cellimages.xml.rels

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="NULL" TargetMode="External"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="media/image3.png"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="media/image1.png"/></Relationships>