Export to Excel with image.

Export to Excel with Image in VB.NET

This article clearly shows how to export image to excel file in VB.NET. First we look the full code then Description for each and ever statement.

[code]
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
AddImageIntoExcel()
End Sub
Dim Img As Bitmap = Image.FromFile("c:\test1.gif")
'place u r image file name with path in above double quoted area
Private Sub AddImageIntoExcel()
'Create an Excel App
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
excelApp.Visible = False
'The Excel doc paths
Dim oMissing As Object = System.Reflection.Missing.Value
Dim destFile As String = "C:\test_image.xlsx"
Dim excelFile As String = "C:\Book1.xlsx"
'Open the worksheet file
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
excelBook = excelApp.Workbooks.Open(excelFile)
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
excelSheet = CType(excelBook.Sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim imgCell As Object = "G1"
Dim range As Microsoft.Office.Interop.Excel.Range
range = excelSheet.Range(imgCell)
range.Select()
Dim newImg As Bitmap = New Bitmap(80, 40)
Me.BackgroundImage = newImg
Using g As Graphics = Graphics.FromImage(newImg)
g.DrawImage(Img, New Rectangle(0, 0, newImg.Width, newImg.Height), _
0, 0, Img.Width, Img.Height, GraphicsUnit.Pixel)
End Using
Clipboard.SetDataObject(newImg)
excelSheet.Paste()
excelSheet.SaveAs(destFile)
'Quit
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
MessageBox.Show("image inserted!")
End Sub

End Class
[/code]

Image

Before going to draw the image in excel file we must load it in our application. we can achieve this by using the following statement.
[code]
Dim Img As Bitmap = Image.FromFile("c:\test1.gif")
[/code]
Or in other words we can say The above statement loads the image in img variable.

create the excel application by using below statement.
[code]
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
[/code]
create the excel book by the help of below statement
[code]
Dim destFile As String = "C:\test_image.xlsx"
Dim excelFile As String = "C:\Book1.xlsx"
'Open the worksheet file
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
excelBook = excelApp.Workbooks.Open(excelFile)
[/code]

Set the image displaying area by giving values to imgCell ( In our example its value is G1 so I will starts displaying at G1)

[code]
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
excelSheet = CType(excelBook.Sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim imgCell As Object = "G1"
Dim range As Microsoft.Office.Interop.Excel.Range
range = excelSheet.Range(imgCell)
range.Select()
[/code]

Below is the key statement g.DrawImage function will draw the image in uppropriate place
[code]
Using g As Graphics = Graphics.FromImage(newImg)
g.DrawImage(Img, New Rectangle(0, 0, newImg.Width, newImg.Height), _
0, 0, Img.Width, Img.Height, GraphicsUnit.Pixel)
End Using
[/code]




Then the image will be copied into clipboard and the same will be paste in excel file the file will be saved in destination file path Finally the excel application will be quit. By using following statements.

[code]
Clipboard.SetDataObject(newImg)
excelSheet.Paste()
excelSheet.SaveAs(destFile)
'Quit
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
MessageBox.Show("image inserted!")
[/code]