Export DataTable to Excel using c# with Formatting Styles

Posted by VIJI Friday, July 3, 2009

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.

4 Responses to Export DataTable to Excel using c# with Formatting Styles

  1. Anonymous Says:
  2. I am having Microsoft office 2007.When i try to run you code.It is opening in the excel sheet in xml formatting only.Any Suggestions?

     
  3. Anonymous Says:
  4. i have also same problem.

     
  5. Roy badger Says:
  6. You can do this task with Aspose.PDF for Java library also and this lib allows you to import/export data from many different data sources. Click here to view the code for importing data from datatable into excel.

     
  7. lingmaaki Says:
  8. Check here all about...C# Excel Formatting

    Ling

     

Post a Comment