This section will show you an easy solution to quickly export datatable from database to Excel via an Excel .NET component in C#, VB.NET.
Spire.XLS for .NET enables you to both export datatable to excel and import excel to datatable. This solution shows you two lines of key souce code for exporting data from datatable to Excel. One is XlsWorksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn) which is responsible for importing the data into worksheet. The other is Workbook.SaveToFile(string fileName) that is called to save the workbook to Excel file.
Here you can download Spire.XLS for .NET and start to perform the datatable to Excel task by below code.
Sample code:
[C#]
private void button1_Click(object sender, EventArgs e)
{
//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("insertTableToExcel.xls");
}
[VB.NET]
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
//connect database
Dim connection As OleDbConnection = New OleDbConnection
connection.ConnectionString = “Provider=””Microsoft.Jet.OLEDB.4.0””;
Data Source=""demo.mdb""; User Id=;Password="
Dim command As OleDbCommand = New OleDbCommand
command.CommandText = "select * from parts"
Dim dataSet As DataSet = New System.Data.DataSet
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(command.CommandText, connection)
dataAdapter.Fill(dataSet)
Dim t As DataTable = dataSet.Tables(0)
//export datatable to excel
Dim book As Workbook = New Workbook
Dim sheet As Worksheet = book.Worksheets(0)
sheet.InsertDataTable(t, True, 1, 1)
book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start("insertTableToExcel.xls")
End Sub
End Class
