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.
 

  1.  Private Sub DatatableToExcel(ByVal dtTemp As DataTable)
  2.         Dim _excel As New Microsoft.Office.Interop.Excel.Application
  3.         Dim wBook As Microsoft.Office.Interop.Excel.Workbook
  4.         Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
  5.  
  6.         wBook = _excel.Workbooks.Add()
  7.         wSheet = wBook.ActiveSheet()
  8.  
  9.         Dim dt As System.Data.DataTable = dtTemp
  10.         Dim dc As System.Data.DataColumn
  11.         Dim dr As System.Data.DataRow
  12.         Dim colIndex As Integer = 0
  13.         Dim rowIndex As Integer = 0
  14.  
  15.         For Each dc In dt.Columns
  16.             colIndex = colIndex + 1
  17.             _excel.Cells(1, colIndex) = dc.ColumnName
  18.         Next
  19.  
  20.         For Each dr In dt.Rows
  21.             rowIndex = rowIndex + 1
  22.             colIndex = 0
  23.             For Each dc In dt.Columns
  24.                 colIndex = colIndex + 1
  25.                 _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
  26.             Next
  27.         Next
  28.  
  29.         wSheet.Columns.AutoFit()
  30.         Dim strFileName As String = "C:\datatable.xlsx"
  31.         If System.IO.File.Exists(strFileName) Then
  32.             System.IO.File.Delete(strFileName)
  33.         End If
  34.  
  35.         wBook.SaveAs(strFileName)
  36.         wBook.Close()
  37.         _excel.Quit()
  38.     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.

  • Micah Amoah

    Thank you for your article post. Awesome.

  • Nelson

    can you please send me the complete code set

    • hscoder

      hello nelson

      This is the complete code.. You need just add the excel interop dll and then you can use the above code. However i have just finished the small application for how to export datatable into excel. Today i will publish this application on MSDN samples. I will ping you just after published it with the URL.

      Thanks

    • Hirendra Sisodiya
  • Sneha

    thank u so much ..u saved my day :)

  • http://www.authorcode.com/ Hirendra Sisodiya

    I have just update the content on this article. Please review it and let me know about what other things are also needed to make more descriptive this article.

    thanks

  • Ajit Jena

    I have two excelsheets Sheet1 and Sheet2 both in the same excel workbook. I need to copy data from column A, column B and the total column A of sheet2 same work book. I have this code as below in VBA Excel macro which copies the data to sheet2.

    Sub VBA()
    Sheets("Sheet1").Select
    Range("A2:B4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
    End Sub

    But in VB.Net I am using following code as below and I am unable to get the last coulumn from sheet1 to sheet2.

    Private Sub Button1_Click
    Dim xlApp As New Excel.Application
    Dim xlwb As Excel.Workbook
    Dim xlsheet, xlsheet2 As Excel.Worksheet
    Dim lCol As Long = 0
    Dim xlsourceRange, xlDestRange As Excel.Range
    with xlApp
    .visible = True
    xlwb = .workbooks.open("D:test.xlsx")
    xlsheet = xlwb.Sheets("Sheet1")
    xlsheet2 = xlwb.Sheets("Sheet2")
    xlSourceRange = xlSheet.Range("A2:D2000")
    xlDestRange = xlsheet2.Range("A2")
    xlSourceRange.Copy(xlDestRange)
    End with
    End Sub
    End Class

    problem area is migration from VBA to VB.Net. To copy the total which is in column I to sheet2 because data is not fixed in sheet1 it is incremental towards the right. I mean in present senario I have six customer from column c to H and the total is in column I. But if there is an addition of two more customer the Total column moves from column I to column K. How can I copy the total to sheet2, total is always at the end column.
    Any help in this is really appriciated
    link to the file.stackoverflow.com/questions/17802919/how-to-find-a-last-column-in-excel-and-copy-in-vb-net

    • http://www.authorcode.com/ Hirendra Sisodiya

      hi..
      try the following code
      use the pastespecial() method to paste the entire range:

      Dim xlApp As New Microsoft.Office.Interop.Excel.Application
      Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
      Dim xlsheet, xlsheet2 As Microsoft.Office.Interop.Excel.Worksheet
      Dim lCol As Long = 0
      Dim xlsourceRange, xlDestRange As Microsoft.Office.Interop.Excel.Range
      With xlApp
      .visible = True
      xlwb = .workbooks.open(“D:test.xlsx”)
      xlsheet = xlwb.Sheets(“Sheet1″)
      xlsheet2 = xlwb.Sheets(“Sheet2″)
      xlsourceRange = xlsheet.Range(“A2:D2000″)
      xlDestRange = xlsheet2.Range(“A2″)
      ‘xlsourceRange.Copy(xlDestRange)
      End With
      xlsourceRange.Copy()
      xlsheet2.Range(“A2:D2000″).PasteSpecial(Excel.XlPasteType.xlPasteValues, _
      Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)

  • sanjaychitti

    what if we have many rows? Please tell me the method for bulk export from datatable to excel sheet without using ssis packages.

  • Ali Haider

    Thanks a lot, works brilliantly, so simple and effective.