Exporting DataTable to Excel in C# Using Interop

Devesh Omar
10y
498.1k
0
6

Article

Introduction

I would like to share a utility that can be used to export a datatable to Excel file using C#.

We have various approaches to perform this activity.

In ASP.NET we can do this by changing the Content type to xls but in C# .Net we might have various other approaches.

Through this article we will learn how to export a data table to Excel using Interop.

Code and Steps

Here in this application we will use a Sample datatable and then learn how to export data to an Excel file.

We will learn the following things in this article:

Creating Excel file using C#
Writing data to cells
Formatting data to cells
Working with Excel range

1. Adding References

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

2. Adding sample data table to the code

Use the following code to add the sample data table:

static DataTable GetTable()

{

// Here we 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));

// Here we 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;

}

3. Class file for generating Excel

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

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 sample code for more details.

5. Initialization of Excel objects

// Start Excel and get Application object.

excel = new Microsoft.Office.Interop.Excel.Application();

// for making Excel visible

excel.Visible = false;

excel.DisplayAlerts = false;

// Creation a new Workbook

excelworkBook = excel.Workbooks.Add(Type.Missing);

// Workk sheet

excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;

excelSheet.Name = "Test work sheet";

6. Writing to Excel file

excelSheet.Cells[1, 1] = “Sample test data”;

excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

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;

8. Coloring the 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;

}

9. Running the application

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

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