Export Data from Database to Excel in WPF

This section will introduce a solution to export data from database to Excel for WPF. The whole solution is easily performed by this WPF Excel component Spire.XLS for WPF.

database to excel

In the task of database to Excel, first we need to connect with database by this class provided by Microsoft: System.Data.Oledb.OledbConnection. Here we use an MS access database. Then, OleDbCommand can help us specify a datatable from database in order to save the data in Dataset later. Finally fill the data into dataset table. After all the connection, we can see the key source code provided by Spire.XLS to export data columns from datatable to Excel: Spire.Xls.Worksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn).

There are four parameters passed.

  • dataTable: The first parameter is to export the data column;
  • columnHeaders: the second is to indicate whether to import field names;
  • firstRow, firstColumn: the third and fourth parameters are index of first row and first column.

Here we can download Spire.XLS for WPF. After installing it on system, and start our database to excel task as below code:

[C#]
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls");
System.Diagnostics.Process.Start("insertTableToExcel.xls");
[VB.NET]
//export datatable to excel
Dim book As New Workbook()
Dim sheet As Worksheet = book.Worksheets(0)
sheet.InsertDataTable(t, True, 1, 1)
book.SaveToFile("insertTableToExcel.xls")
System.Diagnostics.Process.Start("insertTableToExcel.xls")
End Sub
End Class
End Namespace