Export DataTable To Excel in C#

Introduction

I would like to share a utility that can be used to export a DataTable to an Excel file using C#. There are different ways to export a DataTable to an Excel file. In this article, we will learn how to export a DataTable to Excel using Interop.

Code and Steps

We will learn the following in this article.

1. Creating Excel file using C#
2. Writing data to cells
3. Formatting data to cells
4. Working with Excel range

Step 1. Add Interop References

First, we need to add a reference for Microsoft.office.interop.Excel as in the following.

Step 2. Create a DataTable dynamically

Use the following code to add a DataTable with data. If you're new to ADO.NET and DataTable, read Mastering DataTable In C#

static DataTable GetTable()
{
    // Create a DataTable with four columns
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Sex", typeof(string));
    table.Columns.Add("CreatedDate", typeof(string));
    table.Columns.Add("City", typeof(string));

    // Add five DataRows
    table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now, "Noida");
    table.Rows.Add(50, "Nikhil Vats", "M", DateTime.Now, "Noida");
    table.Rows.Add(10, "Heena Sharma", "F", DateTime.Now, "Delhi");
    table.Rows.Add(21, "Nancy Sharma", "F", DateTime.Now, "Delhi");
    table.Rows.Add(100, "Avinash", "M", DateTime.Now, "Delhi");
    table.Rows.Add(25, "Devesh gupta", "M", DateTime.Now, "Delhi");
    table.Rows.Add(50, "Nikhil gupta", "M", DateTime.Now, "Noida");
    table.Rows.Add(10, "HS gupta", "F", DateTime.Now, "Delhi");
    table.Rows.Add(21, "VS gupta", "F", DateTime.Now, "Delhi");
    table.Rows.Add(100, "RJ gupta", "M", DateTime.Now, "Delhi");

    return table;
}

Step 3. The class file for generating Excel

We created a separate class file for generating the Excel (Excelutlity.cs).

Step 4. Creation of 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;

I have attached a sample code for more details.

Step 5. Initialization of Excel objects

// Start Excel and get 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 Worksheet.
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Test work sheet";

Step 6. Writing to Excel file

excelSheet.Cells[1, 1] = “Sample test data”;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

Step 7. Working with range and formatting Excel cells

// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

Step 8. Coloring cells

We will use the following function to format and color Excel cells:

public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
    range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
    range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
    
    if (IsFontbool == true)
    {
        range.Font.Bold = IsFontbool;
    }
}

Step 9. Build and run the application

We are binding a DataGrid at the load of a Form.

Output

After clicking on Export to Excel, we will have our Excel file as per the following screen. You need to modify the file path in the attached code.

Conclusion

The attached application can be used in projects for the reporting purposes.