// Filename is the path to save the Excel file and file name
Public static void exporttoexcel (Dataset source, string filename)
{
System. Io. streamwriter exceldoc;
Exceldoc = new system. Io. streamwriter (filename );
Const string startexcelxml = "<XML version>/R/n <Workbook" +
"Xmlns =/" urn: Schemas-Microsoft-com: Office: spreadsheet/"/R/N" +
"Xmlns: O =/" urn: Schemas-Microsoft-com: Office/"/R/N" +
"Xmlns: x =/" urn: Schemas-Microsoft-com: Office: "+
"Excel/"/R/n xmlns: Ss =/"urn: Schemas-Microsoft-com:" +
"Office: spreadsheet/">/R/n <styles>/R/N "+
"<Style SS: Id =/" default/"SS: Name =/" normal/">/R/N" +
"<Alignment SS: vertical =/" bottom/"/>/R/n <borders/>" +
"/R/n <font/>/R/n <interior/>/R/n <numberformat/>" +
"/R/n <protection/>/R/n </style>/R/N" +
"<Style SS: Id =/" boldcolumn/">/R/n <font" +
"X: FAMILY =/" Swiss/"SS: bold =/" 1/"/>/R/n </style>/R/N" +
"<Style SS: Id =/" stringliteral/">/R/n <numberformat" +
"SS: format =/" @/"/>/R/n </style>/R/n <style" +
"SS: Id =/" decimal/">/R/n <numberformat" +
"SS: format =/" 0.0000/"/>/R/n </style>/R/N" +
"<Style SS: Id =/" integer/">/R/n <numberformat" +
"SS: format =/" 0/"/>/R/n </style>/R/n <style" +
"SS: Id =/" dateliteral/">/R/n <numberformat" +
"SS: format =/" mm/DD/YYYY; @/"/>/R/n </style>/R/N" +
"</Styles>/R/N ";
Const string endexcelxml = "</workbook> ";
Int rowcount = 0;
Int sheetcount = 1;
/*
<XML version>
<Workbook xmlns = "urn: Schemas-Microsoft-com: Office: spreadsheet"
Xmlns: O = "urn: Schemas-Microsoft-com: Office: office"
Xmlns: x = "urn: Schemas-Microsoft-com: Office: excel"
Xmlns: Ss = "urn: Schemas-Microsoft-com: Office: spreadsheet">
<Styles>
<Style SS: Id = "default" SS: Name = "normal">
<Alignment SS: vertical = "bottom"/>
<Borders/>
<Font/>
<Interior/>
<Numberformat/>
<Protection/>
</Style>
<Style SS: Id = "boldcolumn">
<Font X: FAMILY = "Swiss" SS: bold = "1"/>
</Style>
<Style SS: Id = "stringliteral">
<Numberformat SS: format = "@"/>
</Style>
<Style SS: Id = "decimal">
<Numberformat SS: format = "0.0000"/>
</Style>
<Style SS: Id = "integer">
<Numberformat SS: format = "0"/>
</Style>
<Style SS: Id = "dateliteral">
<Numberformat SS: format = "mm/DD/YYYY; @"/>
</Style>
</Styles>
<Worksheet SS: Name = "sheet1">
</Worksheet>
</Workbook>
*/
Exceldoc. Write (startexcelxml );
Exceldoc. Write ("<worksheet SS: Name =/" sheet "+ sheetcount +"/"> ");
Exceldoc. Write ("<Table> ");
Exceldoc. Write ("<row> ");
For (INT x = 0; x <source. Tables [0]. Columns. Count; X ++)
{
Exceldoc. Write ("<cell SS: styleid =/" boldcolumn/"> <data SS: TYPE =/" String/"> ");
Exceldoc. Write (source. Tables [0]. Columns [X]. columnname );
Exceldoc. Write ("</data> </cell> ");
}
Exceldoc. Write ("</row> ");
Foreach (datarow X in source. Tables [0]. Rows)
{
Rowcount ++;
// If the number of rows is> 64000 create a new page to continue output
If (rowcount = 64000)
{
Rowcount = 0;
Sheetcount ++;
Exceldoc. Write ("</table> ");
Exceldoc. Write ("</worksheet> ");
Exceldoc. Write ("<worksheet SS: Name =/" sheet "+ sheetcount +"/"> ");
Exceldoc. Write ("<Table> ");
}
Exceldoc. Write ("<row>"); // id = "+ rowcount +"
For (INT y = 0; y <source. Tables [0]. Columns. Count; y ++)
{
System. Type rowtype;
Rowtype = x [Y]. GetType ();
Switch (rowtype. tostring ())
{
Case "system. String ":
String xmlstring = x [Y]. tostring ();
Xmlstring = xmlstring. Trim ();
Xmlstring = xmlstring. Replace ("&","&");
Xmlstring = xmlstring. Replace (">", "> ");
Xmlstring = xmlstring. Replace ("<", "<");
Exceldoc. Write ("<cell SS: styleid =/" stringliteral/">" +
"<Data SS: TYPE =/" String/"> ");
Exceldoc. Write (xmlstring );
Exceldoc. Write ("</data> </cell> ");
Break;
Case "system. datetime ":
// Excel has a specific date format of YYYY-MM-DD followed
// The letter 't'then hh: mm: SSS. lll example 2005-01-31t24: 01: 21.000
// The following code puts the date stored in xmldate
// To the format above
Datetime xmldate = (datetime) x [y];
String xmldatetostring = ""; // Excel converted date
Xmldatetostring = xmldate. year. tostring () +
"-" +
(Xmldate. Month <10? "0" +
Xmldate. Month. tostring (): xmldate. Month. tostring () +
"-" +
(Xmldate. Day <10? "0" +
Xmldate. Day. tostring (): xmldate. Day. tostring () +
"T" +
(Xmldate. Hour <10? "0" +
Xmldate. Hour. tostring (): xmldate. Hour. tostring () +
":" +
(Xmldate. Minute <10? "0" +
Xmldate. Minute. tostring (): xmldate. Minute. tostring () +
":" +
(Xmldate. Second <10? "0" +
Xmldate. Second. tostring (): xmldate. Second. tostring () +
". 000 ";
Exceldoc. Write ("<cell SS: styleid =/" dateliteral/">" +
"<Data SS: TYPE =/" datetime/"> ");
Exceldoc. Write (xmldatetostring );
Exceldoc. Write ("</data> </cell> ");
Break;
Case "system. boolean ":
Exceldoc. Write ("<cell SS: styleid =/" stringliteral/">" +
"<Data SS: TYPE =/" String/"> ");
Exceldoc. Write (X [Y]. tostring ());
Exceldoc. Write ("</data> </cell> ");
Break;
Case "system. int16 ":
Case "system. int32 ":
Case "system. int64 ":
Case "system. Byte ":
Exceldoc. Write ("<cell SS: styleid =/" integer/">" +
"<Data SS: TYPE =/" number/"> ");
Exceldoc. Write (X [Y]. tostring ());
Exceldoc. Write ("</data> </cell> ");
Break;
Case "system. Decimal ":
Case "system. Double ":
Exceldoc. Write ("<cell SS: styleid =/" decimal/">" +
"<Data SS: TYPE =/" number/"> ");
Exceldoc. Write (X [Y]. tostring ());
Exceldoc. Write ("</data> </cell> ");
Break;
Case "system. dbnull ":
Exceldoc. Write ("<cell SS: styleid =/" stringliteral/">" +
"<Data SS: TYPE =/" String/"> ");
Exceldoc. Write ("");
Exceldoc. Write ("</data> </cell> ");
Break;
Default:
Throw (new exception (rowtype. tostring () + "not handled ."));
}
}
Exceldoc. Write ("</row> ");
}
Exceldoc. Write ("</table> ");
Exceldoc. Write ("</worksheet> ");
Exceldoc. Write (endexcelxml );
Exceldoc. Close ();
}