C#导出数据到Excel的代码
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Windows.Forms; using System.IO; namespace TopAPI { class ExcelCreate { public void CreateCsv(System.Data.DataTable dt, string strName) { //先打印标头 StringBuilder strColu=new StringBuilder(); StringBuilder strValue=new StringBuilder(); int i=0; try { StreamWriter sw = new StreamWriter(new FileStream(strName+".csv", FileMode.Create), Encoding.GetEncoding("GB2312")); for( i=0;i<=dt.Columns.Count-1;i++) { strColu.Append("/""+dt.Columns[i].ColumnName+"/""); strColu.Append(","); } strColu.Remove(strColu.Length-1,1);//移出掉最后一个,字符 sw.WriteLine(strColu); foreach(DataRow dr in dt.Rows) { strValue.Remove(0,strValue.Length);//移出 for(i=0;i<=dt.Columns.Count-1;i++) { strValue.Append("/""+dr[i].ToString().Replace("'","''").Replace (",",",")+"/""); strValue.Append(","); } strValue.Remove(strValue.Length-1,1);//移出掉最后一个,字符 sw.WriteLine(strValue); } sw.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } } public void CreateExcel(DataTable source, string fileName) { System.IO.StreamWriter excelDoc; excelDoc = new System.IO.StreamWriter(fileName); const string startExcelXML = @"<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> "; const string endExcelXML = "</Workbook>"; int rowCount = 0; int sheetCount = 1; excelDoc.Write(startExcelXML); excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">"); excelDoc.Write("<Table>"); excelDoc.Write("<Row>"); for (int x = 0; x < source.Columns.Count; x++) { excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">"); excelDoc.Write(source.Columns[x].ColumnName); excelDoc.Write("</Data></Cell>"); } excelDoc.Write("</Row>"); foreach (DataRow x in source.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.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 by //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(); } } }