The following code snippet shows how we can search some text in your excel sheet. Continue reading “Search text in your excel file or sheet in VB.net”
Tag: Excel in vb.net
Find list of all spreadsheet names of any excel file in vb.net
This example shows that how to extract all spread sheet names of any excel file in vb.net.
Continue reading “Find list of all spreadsheet names of any excel file in vb.net”
Export datatable to excel in VB.Net
Problem
Some time in your application there is need to export the data table into Excel worksheet.
Example
In the following code snippet we pass datatable object into ‘DatatableToExcel‘ function that will create the excel file on given path. The following code is using the Microsoft.Office.Interop.Excel interope library of the Excel application so you need to add the reference of the this library in your project.
Private Sub DatatableToExcel(ByVal dtTemp As DataTable) Dim _excel As New Microsoft.Office.Interop.Excel.Application Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = _excel.Workbooks.Add() wSheet = wBook.ActiveSheet() Dim dt As System.Data.DataTable = dtTemp Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 0 For Each dc In dt.Columns colIndex = colIndex + 1 _excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0 For Each dc In dt.Columns colIndex = colIndex + 1 _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) Next Next wSheet.Columns.AutoFit() Dim strFileName As String = "C:\datatable.xlsx" If System.IO.File.Exists(strFileName) Then System.IO.File.Delete(strFileName) End If wBook.SaveAs(strFileName) wBook.Close() _excel.Quit() End Sub
In the above code we are reading the values of every item of every rows and put into the cell of the excel. With the same process you can also export the data of the datagridview control into the excel file.
About the library ‘Microsoft.Office.Interop.Excel’
Microsoft.Office.Interop.Excel.dll is the rich library that provides the Microsoft.Office.Interop.Excel namespace. The types and members of the Microsoft.Office.Interop.Excel namespace provide support for interoperability between the COM object model of Excel application and your .net application.
How to reference Microsoft.Office.Interop.Excel.dll
Before using the above code you will need to add the refernce of the this library, Do the following these steps:
1. On the Project menu, click Add Reference.
2. On the COM tab, locate Microsoft Excel Object Library, and then click Select.
3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click “Yes”.
Then you should be able to use this interope library.