//Excel导出方法 public void ExportToExcel() { //模板文件路径 string SaveDicPath = Server.MapPath("~/TemplateFiles/"); string SaveFilePath = SaveDicPath + "userfile.xls"; //导出文件路径 string TempPath = Server.MapPath("~/TempFiles/"); if (!Directory.Exists(TempPath)) { Directory.CreateDirectory(TempPath); } //excel文件名 string FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; string newFileName = TempPath + FileName; //文件存在则删除,避免复制发生错误 if (File.Exists(newFileName)) { File.Delete(newFileName); } //将模板文件复制新的excel文件,用来下载 File.Copy(SaveFilePath, newFileName); //初始化对象 Application app = null; Workbook MyBook = null; Worksheet mySheet = null; app = new Application(); MyBook = app.Workbooks.Open(newFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); mySheet = (Worksheet)MyBook.Worksheets[1]; System.Data.DataTable dt = (System.Data.DataTable)Session["datatable"]; //向EXCEL循环插入值 for (int i = 0; i < dt.Rows.Count; i++) { mySheet.Cells[2 + i, 1] = dt.Rows[i][1].ToString().Trim(); //部门 mySheet.Cells[2 + i, 2] = dt.Rows[i][3].ToString().Trim(); //工号 mySheet.Cells[2 + i, 3] = dt.Rows[i][4].ToString().Trim(); //姓名 mySheet.Cells[2 + i, 4] = ""; } //保存工作簿 MyBook.Save(); MyBook.Saved = true; if (mySheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet); mySheet = null; } if (MyBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook); MyBook = null; } //关闭对象 app.Workbooks.Close(); app.Quit(); if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); //垃圾回收 //跳转到相应的excel文件,进行下载 Response.Redirect("..//TempFiles//" + FileName); } |
|
来自: 悟静 > 《.net和asp.net》