Export DataTable to Excel can be very easy through Spire.DataExport (or Spire.Office). Furthermore, Spire.DataExport enables user to Export data to Excel through DataGridView. Through DataGridView users can preview and modify data information before exporting.
Download Spire.DataExport (or Spire.Office) with .NET Framework together. Only 2 Simple steps you can finish the whole datatable to Excel exporting process.
Step 1: Load Data Information
In this step, Spire.DataExport will help you load Data information from your datatable. After you put in your data source and SQL command, you can preview and modify data information in DataGridView area.
private void btnLoad_Click(object sender, EventArgs e)
{
using(OleDbConnection oleDbConnection = new OleDbConnection())
{
oleDbConnection.ConnectionString = this.textBox1.Text;
OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.CommandText = this.textBox2.Text;
oleDbCommand.Connection = oleDbConnection;
using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
{
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
Effect Screenshot
Step 2: Set Export into Excel
Spire.DataExport allows user to export data into most popular file formats including MS Excel, MS Word, HTML, PDF, XML, CSV, DBF, DIF, etc. Now, in this step you should give an order to Export Data into Excel file format. Spire.DataExport will create a new MS Excel Worksheet for storing Data which exported out. You can rename the excel file in this step either.
private void btnRun_Click(object sender, EventArgs e)
{
Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;
worksheet1.StartDataCol = ((System.Byte)(0));
cellExport.Sheets.Add(worksheet1);
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport.SaveToFile("20110223.xls");
}
Effect Screenshot
