C#导出Excel cvs数据

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 = @" " + " " + " " + " " + " " + " " + " " + " "; const string endExcelXML = " "; int rowCount = 0; int sheetCount = 1; excelDoc.Write(startExcelXML); excelDoc.Write(" "); for (int x = 0; x < source.Columns.Count; x++) { excelDoc.Write(" "); excelDoc.Write(source.Columns[x].ColumnName); excelDoc.Write(" "); } excelDoc.Write(" "); excelDoc.Write(""); 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(" "); excelDoc.Write(" "); excelDoc.Write(" "); //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(" " + " "); excelDoc.Write(XMLstring); excelDoc.Write(" "); 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(" " + " "); excelDoc.Write(XMLDatetoString); excelDoc.Write(" "); break; case "System.Boolean": excelDoc.Write(" " + " "); excelDoc.Write(x[y].ToString()); excelDoc.Write(" "); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": excelDoc.Write(" " + " "); excelDoc.Write(x[y].ToString()); excelDoc.Write(" "); break; case "System.Decimal": case "System.Double": excelDoc.Write(" " + " "); excelDoc.Write(x[y].ToString()); excelDoc.Write(" "); break; case "System.DBNull": excelDoc.Write(" " + " "); excelDoc.Write(""); excelDoc.Write(" "); break; default: throw (new Exception(rowType.ToString() + " not handled.")); } } excelDoc.Write(" "); } excelDoc.Write(""); } excelDoc.Write(" "); excelDoc.Write(" "); excelDoc.Write(endExcelXML); excelDoc.Close(); } } }