Results 1 to 2 of 2

Thread: exporting datagridview with images to excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    1

    exporting datagridview with images to excel

    'i tried to export datagridview with images to excel. it works, but the position of images are at the same cell, please fix my code


    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

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

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

    Dim cj = datagridview(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 = "d:\h" + count.ToString + ".jpg"
    im.Save(h, Imaging.ImageFormat.Png)
    xlWorkSheet.Shapes.AddPicture(h, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, i + 1, j + 1, 100, 100)
    count += 1

    Else
    xlWorkSheet.Cells(i + 2, j + 1) = datagridview(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 Sub

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: exporting datagridview with images to excel

    First of all: use code tags! I made some changes to your code:

    Code:
    Imports Microsoft.Office.Core
    Imports Microsoft.Office.Interop
    Imports Microsoft.VisualBasic
    Imports System.Diagnostics
    Imports System.Drawing
    Imports System.IO
    Imports System.Reflection
    Imports System.Windows.Forms
    
    Public Class Form1
       Public WithEvents DataGridViewO As New DataGridView
    
       Public Sub Form_Click() Handles MyBase.Click
          Dim xlApp As New Excel.Application
          Dim misValue As Object = Missing.Value
          Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add(misValue)
          Dim xlWorkSheet As Excel.Worksheet = DirectCast(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)
    
          For k As Integer = 1 To DataGridViewO.Columns.Count
             xlWorkSheet.Cells(1, k) = DataGridViewO.Columns(k - 1).HeaderText
          Next k
    
          Dim count As Integer = 0
          For i As Integer = 0 To DataGridViewO.RowCount - 1
             For j As Integer = 0 To DataGridViewO.ColumnCount - 1
                Dim cj As Object = DataGridViewO(j, i).Value
                If (cj.GetType = GetType(Byte())) Then
                   Dim data As Byte() = DirectCast(cj, Byte())
                   Dim ms As New MemoryStream(data)
                   Dim im As Image = Image.FromStream(MS)
                   Dim h As String = "d:\h" + count.ToString + ".jpg"
                   im.Save(h, Imaging.ImageFormat.Png)
                   xlWorkSheet.Shapes.AddPicture(h, MsoTriState.msoTrue, MsoTriState.msoTrue, i + 1, j + 1, 100, 100)
                   count += 1
                Else
                   xlWorkSheet.Cells(i + 2, j + 1) = DataGridViewO(j, i).Value.ToString()
                End If
             Next j
          Next i
    
          xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
          xlWorkBook.Close()
          xlApp.Quit()
    
          If (MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then Process.Start("d:\vbexcel.xlsx")
       End Sub
    End Class
    A few of the changes I made:
    1. I added Imports statements so you don't need to specify the full path of a class. Look it up on MSDN.
    2. I enabled Option Strict. Even though this can make your code a bit longer it can also prevent nasty bugs.
    3. Shortened a few bits. The ability to initialize variables and to have "As" clauses in "For" statements is very useful!

    I am not sure what your code is trying to do. If you really want to people to help you you need to provide a better explanation of what you're trying to do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width