Data Import/Export (2)
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:
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"); }
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
Excel import or export is always a hot issue for developers. When you go across technical forums, these forums are glutted with solutions, which make developers hard to find a really useful one. This section will introduce an effective way to easily and quickly import data from Excel to datatable via a .NET Excel component in C#, VB.NET.
By using Spire.XLS for .NET, you only need two lines of core code to finish the import or export data from excel to datatable. One is to import an Excel file by the Load method: Workbook.LoadFromFile(string fileName); the other is to get the datasource displayed in datagridview by calling the method: DataTable Xlsworksheet.ExportDataTable().
View the effective screenshot of Excel to datatable task as below firstly:
Here comes to the steps:
- Download Spire.XLS for .NET (or Spire.Office for .NET) and install it on your system.
- Add Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".
- Check the main codes as below:
//Create a new workbook Workbook workbook = new Workbook(); //Load a file and imports its data workbook.LoadFromFile(@”..\FandH.xlsx”); //Initialize worksheet Worksheet sheet = workbook.Worksheets[0]; // get the data source that the grid is displaying data for this.dataGridView1.DataSource = sheet.ExportDataTable();
Dim workbook As Workbook = New Workbook workbook.LoadFromFile(..\ FandH.xlsx) Dim sheet As Worksheet = workbook.Worksheets(0) Me.dataGridView1.DataSource = sheet.ExportDataTable
Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. It supports C#, VB.NET, ASP.NET, ASP.NET MVC.