Create an Excel spreadsheet from Datatable

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