套用模板输出Excel，并对数据进行分页

套用模板输出Excel，并对数据进行分页

2009-08-26 17:01

引用：通过Com 引用 Microsoft Excel 5.0 对象程序库，引用后 bin 文件夹中会出现 Interop.Excel.dll ，Microsoft.Vbe.Interop.dll ， Office.dll 三个文件。 //=====================ExcelHelper（套用模板输出Excel，并对数据进行分页）============== using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnostics; using cfg = System.Configuration; using Excel; namespace ExcelHelper { /// <summary> /// 功能说明：套用模板输出Excel，并对数据进行分页 /// </summary> public class ExcelHelper { protected string templetFile = null; protected string outputFile = null; protected object missing = Missing.Value; /// <summary> /// 构造函数，需指定模板文件和输出文件完整路径 /// </summary> /// <param name="templetFilePath"> Excel模板文件路径 </param> /// <param name="outputFilePath"> 输出Excel文件路径 </param> public ExcelHelper(string templetFilePath, string outputFilePath) { if (templetFilePath == null) throw new Exception(" Excel模板文件路径不能为空！ "); if (outputFilePath == null) throw new Exception(" 输出Excel文件路径不能为空！ "); if (!File.Exists(templetFilePath)) throw new Exception(" 指定路径的Excel模板文件不存在！ "); this.templetFile = templetFilePath; this.outputFile = outputFilePath; } /**/ /// <summary> /// 将DataTable数据写入Excel文件（套用模板并分页） /// </summary> /// <param name="dt"> DataTable </param> /// <param name="rows"> 每个WorkSheet写入多少行数据 </param> /// <param name="top"> Excel中行索引 </param> /// <param name="left"> Excel中列索引 </param> /// <param name="sheetPrefixName"> WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2” </param> public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName) { int rowCount = dt.Rows.Count; // 源DataTable行数 int colCount = dt.Columns.Count; // 源DataTable列数 int sheetCount = this.GetSheetCount(rowCount, rows); // WorkSheet个数 DateTime beforeTime; DateTime afterTime; if (sheetPrefixName == null || sheetPrefixName.Trim() == "") sheetPrefixName = " Sheet "; // 创建一个Application对象并使其可见 beforeTime = DateTime.Now; Excel.Application app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; // 打开模板文件，得到WorkBook对象 （网上都是13个参数，现在变成14个参数了） Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); // 得到WorkSheet对象 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); // 复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; // 记录起始行索引 int endRow = i * rows; // 记录结束行索引 // 若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; // 获取要写入数据的WorkSheet对象，并重命名 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); sheet.Name = sheetPrefixName + " - " + i.ToString(); // 将dt中的数据写入WorkSheet for (int j = 0; j < endRow - startRow; j++) { for (int k = 0; k < colCount; k++) { sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString(); } } ====================写文本框数据（有错误注释掉了）============= //Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes(" txtAuthor "); //Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes(" txtDate "); //Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes(" txtVersion "); //txtAuthor.Text = " KLY.NET的Blog "; //txtDate.Text = DateTime.Now.ToShortDateString(); //txtVersion.Text = " 1.0.0.0 "; sheet.Cells[rows + 1, left + 1] = "总计：10000"; sheet.Cells[rows + 1, left + 5] = "编码：10000"; } // 输出Excel文件并退出 try { //（网上是11个参数，现在是12个参数） workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); workBook.Close(null, null, null); app.Workbooks.Close(); app.Application.Quit(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); workSheet = null; workBook = null; app = null; GC.Collect(); } catch (Exception e) { throw e; } finally { Process[] myProcesses; DateTime startTime; myProcesses = Process.GetProcessesByName(" Excel "); // 得不到Excel进程ID，暂时只能判断进程启动时间 foreach (Process myProcess in myProcesses) { startTime = myProcess.StartTime; if (startTime > beforeTime && startTime < afterTime) { myProcess.Kill(); } } } } /**/ /// <summary> /// 获取WorkSheet数量 /// </summary> /// <param name="rowCount"> 记录总行数 </param> /// <param name="rows"> 每WorkSheet行数 </param> private int GetSheetCount(int rowCount, int rows) { int n = rowCount % rows; // 余数 if (n == 0) return rowCount / rows; else return Convert.ToInt32(rowCount / rows) + 1; } } } 使用示例： public partial class _Default : System.Web.UI.Page { ExcelHelper.ExcelHelper excel =null; protected void Page_Load(object sender, EventArgs e) { excel = new ExcelHelper.ExcelHelper(Server.MapPath("tempt/BJStock.xls"), Server.MapPath("ExcelFile/test.xls")); } protected void Button1_Click(object sender, EventArgs e) { excel.DataTableToExcel(DbHelperSQL.DbHelperSQL.GetDataTable("select * from excel"), 2, 1, 1, "Sheet"); Response.Redirect("ExcelFile/test.xls"); } }