vb.netexceldatagridview

Placing picture at specific cell in excel using vb.net


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

Solution

  • 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.