none
DataGridView Image Export to Excel RRS feed

  • Question

  • Hey all!

    I've been having problems resolving this issue for several days now and it has become very frustrating as i don't feel I've made any progression at all. I've tried searching for the solution on several forums, and decided to come here as a last option. 

    I have form1 which has the following; Datagridview, Picturebox1 & button1(Upload Image) Button2 (Process)

    The idea is that upon the user selecting a file from their computer which isn't stored into a database within the program but as a memorystream in the picturebox it should be shown in the datagridview then 'Button2' would process exported to excel.

    Here is my code so far;

    Imports System.Data.DataTable
    Imports System.IO
    Imports Microsoft.Office.Interop
    Imports System.Runtime.InteropServices
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '  Create Datagridview image column
            Dim dgvImageColumn As New DataGridViewImageColumn
            DataGridView1.Columns.Add(dgvImageColumn)
            dgvImageColumn.ImageLayout = DataGridViewImageCellLayout.Stretch
            Dim dgvTextColumn As New DataGridViewTextBoxColumn
            DataGridView1.Columns(0).Name = "Image"
    
    
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            DataGridView1.RowTemplate.Height = 120
            DataGridView1.AllowUserToAddRows = False
    
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim opf As New OpenFileDialog
            opf.Filter = "Choose Image(*.jpg;*.png;*.gif)|*.jpg;*.png;*.gif"
    
            If opf.ShowDialog = DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(opf.FileName)
            End If
            Try
                Dim ms As New MemoryStream
                PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
                Dim img As Byte()
                img = ms.ToArray()
                DataGridView1.Rows.Add(img)
            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString())
            End Try
        End Sub
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            End
        End Sub
    End Class
    

    Button2 (Process) is where i'm having the issue in terms of exporting this to an excel file. I previously used the following for another windows application but it's just for data strings.

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            'Save to excel with headers
            Dim ExcelApp As Object, ExcelBook As Object
            Dim ExcelSheet As Object
            Dim i As Integer
            Dim j As Integer
    
            'create object of excel
            ExcelApp = CreateObject("Excel.Application")
            ExcelBook = ExcelApp.WorkBooks.Add
            ExcelSheet = ExcelBook.WorkSheets(1)
    
            With ExcelSheet
                For Each column As DataGridViewColumn In DataGridView1.Columns
                    .cells(1, column.Index + 1) = column.HeaderText
                Next
                Dim excelRowIndex = 2
                For Each row As DataGridViewRow In DataGridView1.Rows
                    For j = 0 To DataGridView1.Columns.Count - 1
                        .cells(excelRowIndex, j + 1) = row.Cells(j).Value
                    Next
                    excelRowIndex += 1
                Next
                Dim formatRange As Excel.Range
                formatRange = ExcelSheet.Range("A1")
                formatRange.EntireRow.Font.Bold = True
                formatRange = ExcelSheet.Range("A1", "A1")
                formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue)
                formatRange.BorderAround(Excel.XlLineStyle.xlContinuous)
                formatRange = ExcelSheet.Range("a1", "A1")
                formatRange.EntireRow.BorderAround()
            End With
    
            ExcelApp.Visible = True
            '
            ExcelSheet = Nothing
            ExcelBook = Nothing
            ExcelApp = Nothing
            Application.Exit()
            End
        End Sub

    I have been suggested to use either ActiveSheet.Pictures.Insert or ActiveSheet.Shapes.AddPicture but haven't got a clue how to start this process.

    Many thanks

    Caf20012

    Tuesday, May 9, 2017 9:59 AM

All replies

  • Here is a working example for adding an image to a worksheet. After unzipping check project references as they may be missing if you have a different version of Excel installed.

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    
    Public Class Form1
        Public Sub OpenExcel(ByVal FileName As String, ByVal SheetName As String)
            If IO.File.Exists(FileName) Then
                Dim PicFile As String = IO.Path.Combine(Application.StartupPath, "Cheese.jpg")
                Dim Proceed As Boolean = False
    
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
                    Dim xlShapes = xlWorkSheet.Shapes
                    xlShapes.AddPicture(PicFile, Core.MsoTriState.msoCTrue, Core.MsoTriState.msoCTrue, 170, 50, 76, 68)
                    ReleaseComObject(xlShapes)
                    xlWorkSheet.SaveAs(FileName)
                Else
                    MessageBox.Show(SheetName & " not found.")
                End If
    
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
    
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If
        End Sub
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim FileName As String = IO.Path.Combine(Application.StartupPath, "MyFile.xlsx")
            OpenExcel(FileName, "Sheet2")
            MessageBox.Show("Done")
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, May 9, 2017 11:24 AM
    Moderator
  • Hi kareninstructor

    Thank you for the prompt response!

    Here is a link for my imagetodatagridview to excel

    https://1drv.ms/u/s!ArAq6tP9fO2jiEW91lOPentKUjxB

    The problem i have at the moment is as follows;

    With ExcelSheet
      For Each column As DataGridViewColumn In DataGridView1.Columns
        .cells(1, column.Index + 1) = column.HeaderText
      Next
      Dim excelRowIndex = 2
      Dim picX = 50
      Dim picY = 50
      For Each row As DataGridViewRow In DataGridView1.Rows
        For j = 0 To DataGridView1.Columns.Count - 1
          If (j = 0) Then
            .Shapes.AddPicture("D:\Test\TestImage.jpg", Microsoft.Office.Core.MsoTriState.msoFalse,
                               Microsoft.Office.Core.MsoTriState.msoCTrue, picX, picY, 200, 45)
            picX += 10
            picY += 10
          Else
            .cells(excelRowIndex, j + 1) = row.Cells(j).Value
          End If
        Next
        excelRowIndex += 1
      Next
      Dim formatRange As Excel.Range
      formatRange = ExcelSheet.Range("A1")
      formatRange.EntireRow.Font.Bold = True
      formatRange = ExcelSheet.Range("A1", "A1")
      formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue)
      formatRange.BorderAround(Excel.XlLineStyle.xlContinuous)
      formatRange = ExcelSheet.Range("a1", "A1")
      formatRange.EntireRow.BorderAround()
    End With
    i'm having to use .shapes.addpicture as i'm currently specifying a location but this needs to be overcome as i can't determine the users image files so the directory can differ. The user would need to select a different single picture each time they run as its a bespoke report . i was hoping to have this code Shapes.AddPicture(PictureBox1.Image, Microsoft.Office.Core.MsoTriState.msoFalse making this problem resolved but obviously it isn't accepted. or Shapes.AddPicture("",Microsoft.Office.Core.MsoTristate.Mso. as the location will depend on the users computer



    • Edited by caf20012 Tuesday, May 9, 2017 3:42 PM
    Tuesday, May 9, 2017 3:22 PM
  • Hi caf20012,

    Unfortunately I'm pretty sure there's no way to add the image without using a file name. According to the MSDN docs for Shapes, there does not appear to be any overload of AddPicture that takes anything other than a string to determine what image you want to insert.

    Is the goal here to add the image that you don't necessarily have on disk somewhere (or don't know where on disc it is)? If that's the case, you can get the location (path or URI) of the image with the ImageLocation property and pass that to AddPicture. You could also first save the image to disk using Image.Save() and the pass the new path to AddPicture.

    Hope it is helpful top you.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 12, 2017 6:49 AM
    Moderator