-
Jul 26th, 2019, 04:36 AM
#1
Thread Starter
New Member
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
-
Jul 27th, 2019, 04:39 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|