Export Datatable to Excel from Database

  • Demo
  • C# source
  • VB.Net source
This demo shows you how to import the data from datatable to spreadsheet.
No Matter How Big or Small Your Project is,
Any technical question related to our product, contact us at support@e-iceblue.com.
Any question related to the purchase of product, contact us at sales@e-iceblue.com.
If you don't find the demo you want, please contact support@e-iceblue.com for the free customized demo just for you.
using System.Data.OleDb;
using System.Data;
using Spire.Xls;
namespace DemoOnlineCode
{
    class ExportDatatabletoExcelfromDatabase
    {
        public void demoExportDatatabletoExcel(string dataBasePath, string tableName,string resultName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            DataTable dataTable = ExportDataTable(dataBasePath, tableName);
            sheet.InsertDataTable(dataTable, true, 1, 1);
            sheet.Name = tableName;
            book.SaveToFile(resultName,ExcelVersion.Version2010);
 
        }

        private DataTable ExportDataTable(string dataBasePath, string tableName)
        {
            OleDbConnection connection = new OleDbConnection();
         
            connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=" + dataBasePath + ";User Id=;Password=";
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from " + tableName;
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
            dataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            return dataTable;
        }
    }
}

Imports System.Data.OleDb
Imports System.Data
Imports Spire.XLS
Namespace DemoOnlineCode
    Class ExportDatatabletoExcelfromDatabase
        Public Sub demoExportDatatabletoExcel(dataBasePath As String, tableName As String, resultName As String)
            Dim book As New Workbook()
            Dim sheet As Worksheet = book.Worksheets(0)
            Dim dataTable As DataTable = ExportDataTable(dataBasePath, tableName)
            sheet.InsertDataTable(dataTable, True, 1, 1)
            sheet.Name = tableName
            book.SaveToFile(resultName, ExcelVersion.Version2010)

        End Sub

        Private Function ExportDataTable(dataBasePath As String, tableName As String) As DataTable
            Dim connection As New OleDbConnection()

            connection.ConnectionString = "Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=" & dataBasePath & ";User Id=;Password="
            Dim command As New OleDbCommand()
            command.CommandText = "select * from " & tableName
            Dim dataSet As DataSet = New System.Data.DataSet()
            Dim dataAdapter As New OleDbDataAdapter(command.CommandText, connection)
            dataAdapter.Fill(dataSet)
            Dim dataTable As DataTable = dataSet.Tables(0)
            Return dataTable
        End Function
    End Class
End Namespace