export DataTable To Excel(C)

 

 

 

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.