Export DataTable To Excel in C#

Devesh Omar
1y
1.1m
0
25

Article

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.

Creating Excel file using C#
Writing data to cells
Formatting data to cells
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.

Adding Refrences

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.

binding datagrid at load of Form