Export DataTable to Excel using c# with Formatting Styles

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.

Reference: http://vijirajkumar.blogspot.com/2009/07/export-datatable-to-excel-using-c-with.html


Related Articles

Export Data to Excel in CSV format using .NET

Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

More articles: Export to Excel

Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: