Export DataTable To Excel in C#

Add Interop References

The first step in the process is adding a reference to Microsoft.Office.Interop.Excel in your C# project to enable interaction with Excel.

Create a DataTable Dynamically

Use the following code to create and populate a DataTable with sample data.

static DataTable CreateSampleTable()
{
    // Create a DataTable with five columns
    DataTable table = new DataTable();
    table.Columns.Add("ProductID", typeof(int));
    table.Columns.Add("ProductName", typeof(string));
    table.Columns.Add("Category", typeof(string));
    table.Columns.Add("Price", typeof(decimal));
    table.Columns.Add("StockQuantity", typeof(int));

    // Add sample rows of product data
    table.Rows.Add(101, "Laptop", "Electronics", 750.50, 30);
    table.Rows.Add(102, "Smartphone", "Electronics", 350.75, 50);
    table.Rows.Add(103, "Desk Chair", "Furniture", 120.30, 15);
    table.Rows.Add(104, "Coffee Maker", "Appliances", 45.99, 80);
    table.Rows.Add(105, "Blender", "Appliances", 90.99, 20);
    table.Rows.Add(106, "Monitor", "Electronics", 180.00, 40);
    table.Rows.Add(107, "Keyboard", "Electronics", 25.49, 70);
    table.Rows.Add(108, "Sofa", "Furniture", 600.00, 10);
    table.Rows.Add(109, "Table Lamp", "Furniture", 40.00, 60);
    table.Rows.Add(110, "Headphones", "Electronics", 60.00, 100);

    return table;
}

Create a Class for Generating Excel Files

We created a separate class file named ExcelExporter.cs to manage the process of exporting sales data to Excel.

Declare Excel Objects

Define the following variables:

Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellRange;

Initialize Excel Objects

// Start Excel and get the Application object.
excel = new Microsoft.Office.Interop.Excel.Application();

// Make Excel invisible and disable alerts.
excel.Visible = false;
excel.DisplayAlerts = false;

// Create a new Workbook.
excelWorkBook = excel.Workbooks.Add(Type.Missing);

// Create a new Worksheet.
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.ActiveSheet;
excelSheet.Name = "Sales Report";

Write Data to Excel File

excelSheet.Cells[1, 1] = "Product Name";
excelSheet.Cells[1, 2] = "Units Sold";
excelSheet.Cells[1, 3] = "Price per Unit";
excelSheet.Cells[1, 4] = "Total Sales";

excelSheet.Cells[2, 1] = "Laptop";
excelSheet.Cells[2, 2] = 50;
excelSheet.Cells[2, 3] = 1200.00;
excelSheet.Cells[2, 4] = 50 * 1200.00;

excelSheet.Cells[3, 1] = "Smartphone";
excelSheet.Cells[3, 2] = 75;
excelSheet.Cells[3, 3] = 800.00;
excelSheet.Cells[3, 4] = 75 * 800.00;

excelSheet.Cells[4, 1] = "Tablet";
excelSheet.Cells[4, 2] = 30;
excelSheet.Cells[4, 3] = 500.00;
excelSheet.Cells[4, 4] = 30 * 500.00;

Work with Ranges and Format Cells

// Resize columns automatically.
excelCellRange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[4, 4]];
excelCellRange.EntireColumn.AutoFit();

// Add borders to the range.
Microsoft.Office.Interop.Excel.Borders border = excelCellRange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

Apply Formatting and Color to Cells

We use the following method to format and color the cells:

public void FormatExcelCells(Microsoft.Office.Interop.Excel.Range range, string htmlColorCode, System.Drawing.Color fontColor, bool isBold)
{
    range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(htmlColorCode);
    range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);

    if (isBold)
    {
        range.Font.Bold = isBold;
    }
}

Here's how to write data from the CreateSampleTable method into an Excel file:

using Microsoft.Office.Interop.Excel;
using System;
using System.Data;

class ExcelExporter
{
    public static void ExportToExcel(DataTable dataTable)
    {
        // Step 1: Declare Excel objects
        Application excel = new Application();
        Workbook excelWorkBook = excel.Workbooks.Add(Type.Missing);
        Worksheet excelSheet = (Worksheet)excelWorkBook.ActiveSheet;
        excelSheet.Name = "Sample Data";

        // Step 2: Write data to Excel file
        // Write headers
        for (int col = 0; col < dataTable.Columns.Count; col++)
        {
            excelSheet.Cells[1, col + 1] = dataTable.Columns[col].ColumnName;
        }

        // Write data rows
        for (int row = 0; row < dataTable.Rows.Count; row++)
        {
            for (int col = 0; col < dataTable.Columns.Count; col++)
            {
                excelSheet.Cells[row + 2, col + 1] = dataTable.Rows[row][col].ToString();
            }
        }

        // Step 3: Format Excel cells
        Range excelCellRange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]];
        excelCellRange.EntireColumn.AutoFit();

        // Add borders
        Borders border = excelCellRange.Borders;
        border.LineStyle = XlLineStyle.xlContinuous;
        border.Weight = 2d;

        // Step 4: Make Excel visible and save
        excel.Visible = true;
        excel.DisplayAlerts = false;

        // Optional: Save to a file
        // string filePath = @"C:\yourfile.xlsx";
        // excelWorkBook.SaveAs(filePath);
    }

    static void Main()
    {
        // Create a sample table
        DataTable sampleTable = CreateSampleTable();

        // Export the table to Excel
        ExportToExcel(sampleTable);
    }
}

This example will write the contents of the CreateSampleTable method to an Excel file.