多个Dataset导出到一个Excel的多个Sheet中

///作者：taotie ///时间：2010-7-22 /// <summary> ///导出Excel文件 /// </summary> /// <param name="dv">用于导出的DataSET[数组]</param> /// <param name="tmpExpDir">导出的文件夹路径，例如d:/</param> /// <param name="refFileName">文件名，例如test.xls</param> /// <param name="sheetName">Sheet的名称，如果导出多个Sheet[数租]</param> /// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param> /// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param> public bool WebExportToExcel_1(DataSet[] dv, string tmpExpDir, string refFileName, string[] sheetName, int sheetSize, bool setBorderLine) { int RowsToDivideSheet = sheetSize;//计算Sheet行数 int sheetCount = dv.Length; GC.Collect();// 回收其他的垃圾 Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //申明循环中要使用的变量 int dvRowStart=0; int dvRowEnd; int rowIndex = 0; int colIndex = 0; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { rowIndex = 1; colIndex = 1; //设置初始化的行和列 //计算起始行 dvRowStart = 1; //sheetIndex * RowsToDivideSheet; //计算结束行 dvRowEnd = RowsToDivideSheet; //dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dv[sheetIndex].Tables[0].Rows.Count) { dvRowEnd = dv[sheetIndex].Tables[0].Rows.Count + 1; } //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName // xSt.Name = null; xSt.Name = sheetName[sheetIndex].ToString(); //if (sheetCount > 1) //{ xSt.Name += "1"; } //取得标题 foreach (DataColumn col in dv[sheetIndex].Tables[0].Columns) { //设置标题格式 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; //填值，并进行下一列 excel.Cells[rowIndex, colIndex++] = col.ColumnName; } //取得DATASET表格中数据 //int drvIndex; //for (drvIndex = dvRowStart; drvIndex <= dvRowEnd-1; drvIndex++) //{ // //新起一行，当前单元格移至行首 // rowIndex++; // colIndex = 1; // for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++) // { // string aa = dv[sheetIndex].Tables[0].Rows[0][i-1].ToString(); // excel.Cells[rowIndex, colIndex] = "'" + aa + ""; // colIndex++; // } //} //以下代码就是经过修正后的。上面注释的代码有问题。 foreach (DataRow dr in dv[sheetIndex].Tables[0].Rows) { //新起一行，当前单元格移至行首 rowIndex++; colIndex = 1; for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++) { string aa = dr[i - 1].ToString(); excel.Cells[rowIndex, colIndex] = "'" + aa + ""; colIndex++; } } Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); if (setBorderLine) { allDataWithTitleRange.Borders.LineStyle = 1; } }//Sheet循环结束 string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); return true; }