Wednesday, August 30, 2017

office/Export DataTable object to

Download

Introduction

The sample applciation demonstrates that how you can export the datatable object in c# to excel format.

How to use

1. Download the sample project.

2. Open with your visual studio.

3. Build the project and Run.

Description

Let's say we have a datatable object that contains the emoloyees' details ( employee id, name and age). And we need to export this data to an excel file. For it firstly i create a datatable object of the employees' details at runtime. I am using DataGrodVoew cxontrol to show this data with the help of its DataSource property.

The sample application allows you to chosse the your excel file name and the option for inserting the column names in to excel file ot not.

We need to add the reference of the Microsoft.Office.Interop.excel library.

Code

Create the datatable and set datasource to datagridview.

The following code shows how to create the data table object at runtime.

Visual Basic
Edit|Remove
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load           DataGridView1.DataSource = GetDatatable()       End Sub       Private Function GetDatatable() As DataTable           Dim dt As New DataTable           dt.Columns.Add("id")           dt.Columns.Add("Name")              dt.Rows.Add()           dt.Rows(0)(0) = "1"           dt.Rows(0)(1) = "David"              dt.Rows.Add()           dt.Rows(1)(0) = "2"           dt.Rows(1)(1) = "Ram"              dt.Rows.Add()           dt.Rows(2)(0) = "3"           dt.Rows(2)(1) = "John"           Return dt       End Function

 Exmport to Excel
Visual Basic
Edit|Remove
    Private Sub ExportToExcel(ByVal dtTemp As DataTable, ByVal filepath As String)           Dim strFileName As String = filepath           If System.IO.File.Exists(strFileName) Then               If (MessageBox.Show("Do you want to replace from the existing file?""Export to Excel", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = System.Windows.Forms.DialogResult.Yes) Then                   System.IO.File.Delete(strFileName)               Else                   Return               End If                     End If           Dim _excel As New Excel.Application           Dim wBook As Excel.Workbook           Dim wSheet As 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           If CheckBox1.Checked Then               For Each dc In dt.Columns                   colIndex = colIndex + 1                   wSheet.Cells(1, colIndex) = dc.ColumnName               Next           End If           For Each dr In dt.Rows               rowIndex = rowIndex + 1               colIndex = 0               For Each dc In dt.Columns                   colIndex = colIndex + 1                   wSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)               Next           Next           wSheet.Columns.AutoFit()           wBook.SaveAs(strFileName)              ReleaseObject(wSheet)           wBook.Close(False)           ReleaseObject(wBook)           _excel.Quit()           ReleaseObject(_excel)           GC.Collect()              MessageBox.Show("File Export Successfully!")       End Sub       Private Sub ReleaseObject(ByVal o As Object)           Try               While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)               End While           Catch           Finally               o = Nothing           End Try       End Sub
 

 

Source Code Files

  • Form1.vb contains all code for creating data table at run time and export to excel.

More Information

When you works with any office component programming, some time Office application does not quit after automation: 

http://support.microsoft.com/kb/317109 

How ever i hope you will not face this problem with the above sample code.

No comments:

Post a Comment