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.