This following c# Function is used to export DataTable to Excel With Formatting(Font Bold, Font Size etc)
For example, let us assume that a data table(Employee) consists of details about the Employees. (Column names:Emp Name, Emp ID, DOJ)
We may want to export Employee DataTable to excel sheet. We may want to make the Font of the Heading Row of the excel sheet to Bold so that the Column names will be clearly identified.
This function handles that style formatting of excel sheet.
I have used XML tags to write the content of the DataTable so that we can apply formatting
such as Font Size, Bold etc.
For Header Columns, I have used Font Bold.
private void exportDataTableToExcel(DataTable dt, string filePath)
{
// Excel file Path
string myFile = filePath ;
System.Data.DataRow dr = default(System.Data.DataRow);
int colIndex = 0;
int rowIndex = 0;
// Open the file and write the headers
IO.StreamWriter fs = new IO.StreamWriter(myFile, false);
fs.WriteLine("");
fs.WriteLine("");
fs.WriteLine("");
// Create the styles for the worksheet
fs.WriteLine(" ");
// Style for the column headers
fs.WriteLine(" ");
fs.WriteLine(" ");
fs.WriteLine(" ");
fs.WriteLine(" ");
fs.WriteLine(" ");
// Style for the column information
fs.WriteLine(" ");
fs.WriteLine(" ");
fs.WriteLine(" ");
fs.WriteLine(" ");
// Write the worksheet contents
fs.WriteLine("");
fs.WriteLine(" ");
fs.WriteLine(" ");
colIndex = 0;
//Write the column names
foreach (var dc in dt.Columns) {
colIndex = colIndex + 1;
fs.WriteLine(string.Format(" " + "{0}", dc.ColumnName));
}
fs.WriteLine(" ");
// Write contents for each cell
string cellText = null;
foreach (var dr in dt.Rows) {
rowIndex = rowIndex + 1;
colIndex = 0;
foreach (var dc in dt.Columns) {
colIndex = colIndex + 1;
cellText = dr(dc.ColumnName);
// Check for null cell and change it to empty to avoid error
if (cellText == Constants.vbNullString) cellText = "";
fs.WriteLine(string.Format(" " + "{0}", cellText.ToString));
}
}
// Close up the document
fs.WriteLine(" ");
fs.WriteLine("");
fs.WriteLine("");
fs.Close();
}
Example
exportDataTableToExcel (dt, "C:\Employee.xls")
will write the content of data table with Formatting Styles.
For more details, visit http://vijirajkumar.blogspot.com/2009/07/export-datatable-to-excel-using-c-with.html
Read articles related to Export to Excel
|
No responses found. Be the first to respond and make money from revenue sharing program.
|