KEMBAR78
Export Datatable To Excel in VB | PDF | Component Object Model | Microsoft Excel
Scribd
Upload
252 views2 pages

Export Datatable To Excel in VB

This document discusses how to export a data table to an Excel worksheet in VB.Net. It provides an example code snippet that passes a data table object to a "DatatableToExcel" function, which then creates an Excel file at a given path. The code uses the Microsoft.Office.Interop.Excel library to interact with Excel, looping through the data table rows and columns to output the values to individual cells. Information is also provided on how to reference the Microsoft.Office.Interop.Excel library in a .Net project.

Uploaded by

alek kuningan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
252 views2 pages

Export Datatable To Excel in VB

This document discusses how to export a data table to an Excel worksheet in VB.Net. It provides an example code snippet that passes a data table object to a "DatatableToExcel" function, which then creates an Excel file at a given path. The code uses the Microsoft.Office.Interop.Excel library to interact with Excel, looping through the data table rows and columns to output the values to individual cells. Information is also provided on how to reference the Microsoft.Office.Interop.Excel library in a .Net project.

Uploaded by

alek kuningan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

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.

Sumber : http://www.authorcode.com/export-datatable-to-excel-in-vb-net/

You might also like