Placing picture at specific cell in excel using vb.net

Question

I have coded the following code to export data(including images) from datagridview to excel in vb.net Everything works fine except that pictures are not placing at their desired positions. I didn't find any method that belongs to Shapes.addPicture() method to specify the position by indexes. After the creation of file all the pictures are are by default at the starting of excel like a stack. Here is the code.

 Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        For k As Integer = 1 To DataGridView1.Columns.Count
            xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
        Next

        Dim count As Integer = 0
        For i = 0 To DataGridView1.RowCount - 1
            For j = 0 To DataGridView1.ColumnCount - 1

                Dim cj = DataGridView1(j, i).Value
                If (cj.GetType = GetType(System.Byte())) Then
                    Dim data As Byte() = DirectCast(cj, Byte())
                    Dim ms As New System.IO.MemoryStream(data)
                    Dim im As System.Drawing.Image = System.Drawing.Image.FromStream(ms)
                    Dim h As String = "c:\h" + count.ToString + ".jpg"
                    im.Save(h, Imaging.ImageFormat.Jpeg)
                    xlWorkSheet.Shapes.AddPicture(h, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, i + 2, j + 1, 100, 100)
                    count += 1
                Else
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                End If



            Next
        Next

        xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        Dim res As MsgBoxResult
        res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
        If (res = MsgBoxResult.Yes) Then
            Process.Start("d:\vbexcel.xlsx")
        End If

score 1 · Accepted Answer · answered Sep 22 '14 at 05:37

1

The left and top parameters of the AddPicture method specify the left and top position of the picture in points from the top-left corner of the sheet. You seem to be thinking that this will be in cells. You need to calculate the size of your cells in points and use those figures. Pictures in excel sit "over" the grid - not "in" it.

answered Sep 22 '14 at 05:37

Dale M

2,453
1
13
21

You are right. I just found in article http://stackoverflow.com/questions/9776611/insert-picture-into-excel-cell . But May you please guide me to how to get the left and top parameters by specific cell's indexes(row and column index) so that i can complete my task. – Sahil Manchanda Sep 22 '14 at 05:43
Get the cell you want it in as an `Excel.Range`. These have `top` and `left` properties. – Dale M Sep 22 '14 at 05:46
in the above code i have current cell like xlWorkSheet.Cells(i + 2, j + 1) now if i place a dot in the end then i get the methods of an object not the methods of cell... – Sahil Manchanda Sep 22 '14 at 05:55
You have to cast the object to an `Excel.Range` – Dale M Sep 22 '14 at 05:55
Dale M, after casting it works now i have to set the height and width to make look as if they are in cells. – Sahil Manchanda Sep 22 '14 at 06:03
Do you think the range might also have height and width properties? – Dale M Sep 22 '14 at 06:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61650/discussion-between-user2299040-and-dale-m). – Sahil Manchanda Sep 22 '14 at 06:15

Placing picture at specific cell in excel using vb.net

1 Answers1