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:
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
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
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:
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.
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.
i need this code
ReplyDelete