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"
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 ();
}