Introduction
Create Excel spreadsheets from Datatable without looping through each row
Background
A number of solutions that export Datatables to Excel involve looping through each row and column, which works for a very small dataset, but is extremely slow for larger ones.
This uses DataGridView
and the ClipBoard
to speed up the process
Using the code
You need to add a reference to the Microsoft.Office.Interop.Excel
assembly, and then add a using
clause:
using Excel = Microsoft.Office.Interop.Excel;
Next, you would add a method as shown below:
private void SendToExcel(DataTable dataTable, bool formatted)
{
System.Windows.Forms.Cursor.Current = Cursors.WaitCursor;
Excel.Application wapp = default(Excel.Application);
Excel.Worksheet wsheet = default(Excel.Worksheet);
Excel.Workbook wbook = default(Excel.Workbook);
wapp = new Excel.Application();
wapp.Visible = false;
wbook = wapp.Workbooks.Add();
wsheet = wbook.ActiveSheet;
DataGridView tempGrid = new DataGridView();
this.Controls.Add(tempGrid);
tempGrid.DataSource = dataTable;
tempGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
tempGrid.MultiSelect = true;
tempGrid.SelectAll();
DataObject dataObj = tempGrid.GetClipboardContent();
Clipboard.SetDataObject(dataObj);
wsheet.Range["A1"].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll);
wapp.Visible = true;
if (formatted == true)
{
Excel.Range last = wsheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = wsheet.get_Range("A1", last);
range.AutoFormat(Format: Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, Number: true, Font: true, Alignment: true, Border: true, Pattern: true, Width: true);
}
wsheet.Range["A1"].EntireColumn.Delete();
wapp.Visible = true;
System.Windows.Forms.Cursor.Current = Cursors.Default;
Clipboard.Clear();
this.Controls.Remove(tempGrid);
}
This Section creates the Excel sheet:
Excel.Application wapp = default(Excel.Application);
Excel.Worksheet wsheet = default(Excel.Worksheet);
Excel.Workbook wbook = default(Excel.Workbook);
wapp = new Excel.Application();
wapp.Visible = false;
wbook = wapp.Workbooks.Add();
wsheet = wbook.ActiveSheet;
Next it copies the DataTable to the Clipboard via a temporary DataGridView, as you cannot copy a Datatable directly:
DataGridView tempGrid = new DataGridView();
this.Controls.Add(tempGrid);
tempGrid.DataSource = dataTable;
tempGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
tempGrid.MultiSelect = true;
tempGrid.SelectAll();
DataObject dataObj = tempGrid.GetClipboardContent();
Clipboard.SetDataObject(dataObj);
Finally it pastes and optionally formats the Excel Sheet using predefined Excel formats
wsheet.Range["A1"].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll);
wapp.Visible = true;
if (formatted == true)
{
Excel.Range last = wsheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = wsheet.get_Range("A1", last);
range.AutoFormat(Format: Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, Number: true, Font: true, Alignment: true, Border: true, Pattern: true, Width: true);
}
wsheet.Range["A1"].EntireColumn.Delete();
wapp.Visible = true;
System.Windows.Forms.Cursor.Current = Cursors.Default;
Clipboard.Clear();
this.Controls.Remove(tempGrid);
History
July 12, 2016 - Initial Post