Generate an Excel Spreadsheet from any DataTable (C#)

Quick-and-Dirty Spreadsheet Spread

If you just need the data from a DataTable (such as the result of calling a Stored Procedure or a SQL query) and don't need to gussy it up much, this code will generate a spreadsheet with that data.

First, as for any Excel Interop project, you need to add a reference to the Microsoft.Office.Interop.Excel assembly, and then add a couple of using clauses like so:

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

Then add the Excel objects you will need:

private Excel.Application _xlApp;
private Excel.Workbook _xlBook;
private Excel.Sheets _xlSheets;
private static Excel.Worksheet _xlSheet;

...and the DataTable:

private DataTable dtSPResults;

Then, add a method like this (the code that populates the DataTable is an exercise left to the reader):

private void GenerateAndSaveSpreadsheet()
{
    try
    {
        InitializeExcelObjects();
        AddColumnHeadingRow();
        AddDataRows();
        _xlSheet.Columns.AutoFit();
        WriteSheet();
    }
    finally
    {
        DeinitializeExcelObjects();
    }
}

...with these methods that it calls:

private void InitializeExcelObjects()
{
    _xlApp = new Excel.Application
    {
        SheetsInNewWorkbook = 1,
        StandardFont = "Calibri",
        StandardFontSize = 11
    };
    Thread.Sleep(2000); // if you need this line (I can't recall why I added it),
                        // add "using System.Threading;"

    _xlBook = _xlApp.Workbooks.Add(Type.Missing);

    _xlSheets = _xlBook.Worksheets;
    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];

    _xlSheet.Name = "BlaSheetName"; // You can replace the name
}

private void AddColumnHeadingRow()
{
    colCount = dtSPResults.Columns.Count;
    List<string> colNames = new List<string>();
    for (int i = 0; i < colCount; i++)
    {
        colNames.Add(dtSPResults.Columns[i].ToString());
    }

    var columnHeaderRowRange = _xlSheet.Range[
        _xlSheet.Cells[1, 1],
        _xlSheet.Cells[1, colCount]];
    columnHeaderRowRange.Interior.Color = Color.LightBlue;
    columnHeaderRowRange.RowHeight = 18;
    columnHeaderRowRange.Font.Bold = true;
    columnHeaderRowRange.Font.Size = 13;

    int rowToPop = 1;
    int currentColumn = 1;
    foreach (string s in colNames)
    {
        var colHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPop, currentColumn];
        colHeaderCell.Value2 = s;
        currentColumn++;
    }

    _xlSheet.Activate();
    _xlSheet.Application.ActiveWindow.SplitRow = 1;
    _xlSheet.Application.ActiveWindow.FreezePanes = true;
}

private void AddDataRows()
{
    int rowToPop = 2;
    foreach (DataRow row in dtSPResults.Rows)
    {
        for (int i = 1; i <= colCount; i++)
        {
            var genericCell = (Excel.Range)_xlSheet.Cells[rowToPop, i];
            var curVal = row[i-1].ToString();
            genericCell.Value2 = curVal;
        }
        rowToPop++;
    }
}

private void WriteSheet()
{
    Directory.CreateDirectory("BlaFolderName")); // Feel free to change this
    string filename = @"C:\BlaFolderName\Whatever.xlsx"); // Feel free to change this
    if (File.Exists(filename))
    {
        File.Delete(filename);
    }
    _xlBook.SaveAs(filename, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing,
                   Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing);
}

public void DeinitializeExcelObjects()
{
    Marshal.ReleaseComObject(_xlSheet);

    _xlBook.Close(false);
    Marshal.ReleaseComObject(_xlBook);

    _xlApp.DisplayAlerts = false;
    _xlApp.Quit();
    Marshal.ReleaseComObject(_xlApp);
    _xlApp = null;
}

That should generate an Excel spreadsheet from the data you feed it via the DataTable with a column header row that is frozen followed by all the raw data.