Skip to main content

Exporting DataTable to Excel in C# Using Interop

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


Comments

Post a Comment

Contact Form

Name

Email *

Message *

Popular posts from this blog

Binding CheckBoxList in ASP.Net MVC

Blogspot Introduction I would like to share how to bind the checkbox list in MVC. I will use a Model (a class file) to define various attributes for checkboxes. For a basic understanding of MVC kindly use the following link: ASP.NET MVC Overview The following is the procedure. 1. Creating Model We created a "SubjectModel" class under the Models folder and defined the following two properties: Subject: to display text Selected: to display check/uncheck 2. Creating the controller We created a Controller "BindingCheckBoxController" under the controllers folder We created an Action having the name = "DisplaycheckBoxes" We created a list of Subjects (Subject model class) Returning a list of subjects to the View. public  ActionResult DisplayCheckboxes() {     List<SubjectModel> listsubject =  new  List<SubjectModel>();     listsubject.Add( new  SubjectModel( "Physics" , true )); ...

Indexes In SQL SERVER in Hindi Part 1