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(Patient) consists of details about the Employees. (Column names:Dosage, Drug,Patient, Date)

We may want to export Patient 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, BackColor as Green and ForeColor as White.


static DataTable GetTable()
{
DataTable table = new DataTable(); // New data table.
table.Columns.Add("Dosage", typeof(int)); // Add five columns.
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(15, "Abilify", "xxx", DateTime.Now); // Add five data rows.
table.Rows.Add(40, "Accupril", "yyy", DateTime.Now);
table.Rows.Add(40, "Accutane", "zzz", DateTime.Now);
table.Rows.Add(20, "Aciphex", "zyy", DateTime.Now);
table.Rows.Add(45, "Actos", "xxxy", DateTime.Now);
return table; // Return reference.
}


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
StreamWriter fs = new StreamWriter(myFile, false);

fs.WriteLine("<? xml version=\"1.0\"?>");
fs.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
fs.WriteLine("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");

// Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"1\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#254117\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column information
fs.WriteLine(" <ss:Style ss:ID=\"2\">");
fs.WriteLine(" <ss:Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"3\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#736AFF\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
fs.WriteLine(" </ss:Styles>");


// Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=\"Sheet1\">");
fs.WriteLine(" <ss:Table>");

fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{

fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"1\">" + "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", dc.ColumnName ));
}

fs.WriteLine(" </ss:Row>");


object cellText = null;

// Write contents for each cell
foreach (DataRow dr in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{
cellText = dr[dc];
// Check for null cell and change it to empty to avoid error
if (cellText == null ) cellText = "";
fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"2\">" +
"<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", cellText));
colIndex = colIndex + 1;
}
fs.WriteLine(" </ss:Row>");
}

fs.WriteLine(" <ss:Row>");
fs.WriteLine(" </ss:Row>");


// Close up the document
fs.WriteLine(" </ss:Table>");
fs.WriteLine("</ss:Worksheet>");
fs.WriteLine("</ss:Workbook>");
fs.Close();

}

Example

exportDataTableToExcel (GetTable(),"C:\\PatientDetails.xls");

will write the content of data table with Formatting Styles.