今天遇到个项目，需要把dataset（多Datatable的）数据导入到一个excel中不同的sheet中，在网上找了好久，最后通过自己修改，总结了两个方法，记录下来备份。
方法一：
通过操作Microsoft.Office.Interop.Excel。
这个方法的优点是写法简单，缺点也是很明显的效率太低，导出速度太慢。
/// <summary> /// 导出数据到多个sheet /// </summary> /// <param name="ds"></param> /// <param name="sheetName"></param> public static void ToExcelSheet(DataSet ds,string fileName) { //int testnum = ds.Tables.Count-1;
SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog();
fileName = saveDialog.FileName; if (fileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application appExcel; appExcel = new Microsoft.Office.Interop.Excel.Application(); if (appExcel == null) { MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); return; }
Microsoft.Office.Interop.Excel.Workbook workbookData; Microsoft.Office.Interop.Excel.Worksheet worksheetData; Range range; workbookData = appExcel.Workbooks.Add(Missing.Value);
appExcel.DisplayAlerts = false;//不显示警告 //xlApp.Visible = true;//excel是否可见 // //for (int i = workbookData.Worksheets.Count; i > 0; i--) //{ // Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(i); // oWorksheet.Select(); // oWorksheet.Delete(); //}
for(int k=0;k<ds.Tables.Count;k++) { worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); // testnum--; if(ds.Tables[k]!=null) { worksheetData.Name = ds.Tables[k].TableName; //写入标题 for (int i = 0; i < ds.Tables[k].Columns.Count; i++) { worksheetData.Cells[1, i + 1] = ds.Tables[k].Columns[i].ColumnName; range = (Range)worksheetData.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; range.NumberFormatLocal = "@";//文本格式 // range.EntireColumn.AutoFit();//自动调整列宽 // range.WrapText = true; //文本自动换行 range.ColumnWidth = 15;
} //写入数值
for (int r = 0; r < ds.Tables[k].Rows.Count; r++) { for (int i = 0; i < ds.Tables[k].Columns.Count; i++) {
worksheetData.Cells[r + 2, i + 1] = ds.Tables[k].Rows[r][i]; //Range myrange = worksheetData.get_Range(worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]);
//myrange.NumberFormatLocal = "@";//文本格式 myrange.EntireColumn.AutoFit();//自动调整列宽 myrange.WrapText = true; //文本自动换行 //myrange.ColumnWidth = 15;
} // rowRead++;
System.Windows.Forms.Application.DoEvents();
} } worksheetData.Columns.EntireColumn.AutoFit(); workbookData.Saved = true;
} workbookData.SaveAs(fileName); workbookData.Close();
appExcel.Quit(); DialogResult btn = MessageBox.Show(null, "文件“" + fileName + "”保存成功。是否现在打开？", "信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (btn == DialogResult.Yes) { ApplicationClass excelApp = new ApplicationClass(); Workbook workbook2 = excelApp.Workbooks.Open(fileName); excelApp.Visible = true;
} // appExcel.Quit();
GC.Collect(); }
方法二：
根据所有的数据都是数据流，最后决定操作数据流，来拼一个excel，通过xml格式来拼了一个能使用excel打开的数据流，
这个方法的优点是速度快，缺点是编写复杂
方法如下：
/// <summary> /// 执行导出 /// </summary> /// <param name="ds">要导出的DataSet</param> /// <param name="strExcelFileName">要导出的文件名</param> public static void ExportToExcel(DataSet source, string fileName) { SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog();
fileName = saveDialog.FileName; if (fileName.IndexOf(":") < 0) return; //被点了取消
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: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; excelDoc.Write(startExcelXML); foreach (System.Data.DataTable dt in source.Tables) { excelDoc.Write("<Worksheet ss:Name=/"" + dt.TableName + "/">"); excelDoc.Write("<Table>"); excelDoc.Write("<Row>"); for (int x = 0; x < dt.Columns.Count; x++) { excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">"); excelDoc.Write(dt.Columns[x].ColumnName); excelDoc.Write("</Data></Cell>"); } excelDoc.Write("</Row>"); foreach (DataRow x in dt.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 < dt.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();
DialogResult btn = MessageBox.Show(null, "文件“" + fileName + "”保存成功。是否现在打开？", "信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (btn == DialogResult.Yes) { ApplicationClass excelApp = new ApplicationClass(); Workbook workbook2 = excelApp.Workbooks.Open(fileName); excelApp.Visible = true;
} }
读取数据流的方式是最快的，但也是很难操作的，我对数据流的操作很不熟练，需要加强
