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