最近项目中,根据用户要求,需要批量导出Excel(系统里每天产生很多单子,以前都是一个一个的导出的,最近用户要求输入日期,导出该天所有单子)。
原来是使用NPOI操作Excel。自以为是的以为批量导出不就是把该天所有单子都查询出来,写个循环一个一个导出就OK了。
可是写好之后运行时才发现,导出第一个Excel后,页面就没有反应了,貌似是输出流已关闭,可是不关闭时测试后还是不行。
就开始换个思路了,我不导出多个Excel,我就只导出一个,而每个单子分别导入一个Sheet里,就是说导出到同一个Excel里的多个工作簿Sheet里。
由于我用的Excel模块,所有首先需要根据单子的数量来创建相应数量的Sheet,而且每个Sheet的格式须和第一模版Sheet的格式一样。
经过多次撞墙,代码也终于出来了:
复制Sheet
当然首先要引入命名空间:
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
string templetfilepath = @"E:\000\template.xls";//模版Excelstring tpath = @"E:\000\out.xls";//中介Excel,以它为中介来导出,避免直接使用模块Excel而改变模块的格式FileInfo ff = new FileInfo(tpath);if (ff.Exists){ff.Delete();}FileStream fs = File.Create(tpath);HSSFWorkbook x1 = new HSSFWorkbook();x1.Write(fs);fs.Close();FileStream fileRead = new FileStream(templetfilepath, FileMode.Open, FileAccess.Read);HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);HSSFSheet CPS = hssfworkbook.GetSheet("Sheet0") as HSSFSheet;for (int i = 0; i <= dty1.Rows.Count - 1; i++)//dty1是我用SQL查询出来当天单子集合{CPS.CopyTo(book2, "Sheet" + i.ToString(), true, true);using (FileStream fileSave = new FileStream(tpath, FileMode.Open, FileAccess.Write)){book2.Write(fileSave);}}
模版:
新生成的:
表示按模块Excel中Sheet0的样式复制创建的多个Sheet的新Excel。
当然如果你需要在通过复制在原Excel里创建Sheet的话也是可以的,就把上面的中介Excel操作那部门不要,把CopyTo函数那里修改一下就可以了。
删除Excel中的Sheet:
string templetfilepath = @"E:\000\template.xls";//string tpath = @"E:\000\out.xls";string tpath = @"E:\000\out.xls";FileInfo ff = new FileInfo(tpath);if (ff.Exists){ff.Delete();}FileStream fs = File.Create(tpath);HSSFWorkbook x1 = new HSSFWorkbook();x1.Write(fs);fs.Close();FileStream fileRead = new FileStream(templetfilepath, FileMode.Open, FileAccess.Read);HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);int sheetnumber = book2.Workbook.NumSheets - 1;for (int x = 0; x < sheetnumber; x++)//{book2.RemoveSheetAt(1);//如果你要保留第一个Sheet,这里参数就设置为1,如果你要全部都删除这里设为0(表示每次都删除第一个Sheet)}using (FileStream fileSave = new FileStream(tpath, FileMode.Open, FileAccess.Write)){book2.Write(fileSave);}
至于循环把集合导入到Excel的多个Sheet里,我也懒得描述了,思路知道了,代码都不难,我就直接帖代码了:
<span style="font-size:14px;">int j = 0;foreach (DataRow dr in dty1.Rows){SqlHelp sqlx1 = new SqlHelp();string strfax1 = "select * from tOrderProductInformation where SerialNumber='" + dr["SerialNumber"].ToString() + "' and Record='" + dr["Record"].ToString() + "' and bEffective='True'";DataTable dtx1 = sqlx1.GetDataTable(strfax1);sqlx1.SqlClose();// ToExcel(hssfworkbook,j, dr["OrderID"].ToString(), dr["SerialNumber"].ToString(), templetfilepath, dtx1, dr["CustomCode"].ToString());string OrderID = dr["OrderID"].ToString();string sns = dr["SerialNumber"].ToString();DataTable dt = dtx1;string CustomCode = dr["CustomCode"].ToString();string loginid = Session["LoginID"].ToString();Authority ac = new Authority();int tp = Convert.ToInt32(Session["Power"].ToString());bool ap = ac.JudgeAuthority(loginid, "a1213");//价格/客户名称查看权限bool an = ac.JudgeAuthority(loginid, "a1215");//交换机型号查看权限bool ar = ac.JudgeHeadAuthority(loginid, CustomCode);bool at = ac.JudgeViewAuthority(loginid, CustomCode);bool az = ac.JudgeAuthority(loginid, "a1216");//无价格订单查询HSSFSheet ws = (HSSFSheet)book2.GetSheet("Sheet" + j.ToString());//添加或修改WorkSheet里的数据 #region//给Excel单元格赋值SqlHelp sqlf = new SqlHelp();string strfacf = "select * from tOrderReview where OrderID='" + OrderID + "' and aEffective='True'";//string strfacf = "select * from tOrderReview a where not exists(select 1 from tOrderReview where SerialNumber=a.SerialNumber and Record>a.Record) and SerialNumber='" + lb_serialnum.Text.ToString() + "'";SqlDataReader drf = sqlf.ExecuteReader(strfacf);drf.Read();if (drf.HasRows){ws.GetRow(5).GetCell(15).SetCellValue(sns);ws.GetRow(6).GetCell(2).SetCellValue(drf["CustomCode"].ToString());ws.GetRow(6).GetCell(4).SetCellValue(drf["CustomName"].ToString());ws.GetRow(6).GetCell(10).SetCellValue(drf["OrderSource"].ToString());ws.GetRow(6).GetCell(15).SetCellValue(drf["OrderGrade"].ToString());ws.GetRow(7).GetCell(2).SetCellValue(drf["SubmitDate"].ToString());ws.GetRow(7).GetCell(4).SetCellValue(drf["DeliveryDate"].ToString());ws.GetRow(7).GetCell(7).SetCellValue(drf["OrderSort"].ToString());ws.GetRow(7).GetCell(16).SetCellValue(drf["CustomPO"].ToString());ws.GetRow(15).GetCell(1).SetCellValue(drf["PruchasingOpinion"].ToString());ws.GetRow(16).GetCell(3).SetCellValue(drf["PurchasingName"].ToString());ws.GetRow(15).GetCell(4).SetCellValue(drf["PDOpinion"].ToString());ws.GetRow(16).GetCell(6).SetCellValue(drf["PDName"].ToString());ws.GetRow(15).GetCell(9).SetCellValue(drf["QualityOpinion"].ToString());ws.GetRow(16).GetCell(11).SetCellValue(drf["QualityName"].ToString());ws.GetRow(15).GetCell(13).SetCellValue(drf["FinanceOpinion"].ToString());ws.GetRow(16).GetCell(15).SetCellValue(drf["FinanceName"].ToString());ws.GetRow(17).GetCell(3).SetCellValue(drf["ApplicantName"].ToString());ws.GetRow(17).GetCell(6).SetCellValue(drf["MarketName"].ToString());ws.GetRow(17).GetCell(10).SetCellValue(drf["AddApprovalName"].ToString());ws.GetRow(17).GetCell(15).SetCellValue(drf["LeaderName"].ToString());ws.GetRow(18).GetCell(2).SetCellValue(drf["CommerceOpinion"].ToString());ws.GetRow(18).GetCell(4).SetCellValue(drf["MarketOpinion"].ToString());ws.GetRow(18).GetCell(8).SetCellValue(drf["AddOpinion"].ToString());ws.GetRow(18).GetCell(13).SetCellValue(drf["LeaderOpinion"].ToString());}drf.Dispose();sqlf.SqlClose();IRow mySourceStyleRow = ws.GetRow(10);//获取源格式行if (dt.Rows.Count > 3){MyInsertRow(ws, 12, dt.Rows.Count - 3, mySourceStyleRow);//模版中已有3行,现在须调用函数插入DataTable行数-3行}if (dt.Rows.Count > 0){for (int n = 0; n < dt.Rows.Count; n++){ws.GetRow(n + 10).GetCell(0).SetCellValue(n + 1);ws.GetRow(n + 10).GetCell(1).SetCellValue(dt.Rows[n]["ProductCode"].ToString());ws.GetRow(n + 10).GetCell(2).SetCellValue(dt.Rows[n]["ProductModel"].ToString());ws.GetRow(n + 10).GetCell(3).SetCellValue(dt.Rows[n]["CustomVersion"].ToString());ws.GetRow(n + 10).GetCell(4).SetCellValue(dt.Rows[n]["Amount"].ToString());if (!ap)//不满足【价格、客户名称查看权限】{if (ar || at)//但是满足该客户的下单、查看权限{ws.GetRow(n + 10).GetCell(5).SetCellValue(dt.Rows[n]["ProductPrice"].ToString());}else{ws.GetRow(n + 10).GetCell(5).SetCellValue("*");}}else{ws.GetRow(n + 10).GetCell(5).SetCellValue(dt.Rows[n]["ProductPrice"].ToString());}if (az){ws.GetRow(n + 10).GetCell(5).SetCellValue("*");}ws.GetRow(n + 10).GetCell(6).SetCellValue(dt.Rows[n]["TechnicalIndicator"].ToString());ws.GetRow(n + 10).GetCell(7).SetCellValue(dt.Rows[n]["Label"].ToString());ws.GetRow(n + 10).GetCell(8).SetCellValue(dt.Rows[n]["ProductShell"].ToString());ws.GetRow(n + 10).GetCell(9).SetCellValue(dt.Rows[n]["ProductPackage"].ToString());ws.GetRow(n + 10).GetCell(10).SetCellValue(dt.Rows[n]["Soft"].ToString());ws.GetRow(n + 10).GetCell(11).SetCellValue(dt.Rows[n]["CodeType"].ToString());ws.GetRow(n + 10).GetCell(12).SetCellValue(dt.Rows[n]["VendorName"].ToString());ws.GetRow(n + 10).GetCell(13).SetCellValue(dt.Rows[n]["PN"].ToString());ws.GetRow(n + 10).GetCell(14).SetCellValue(dt.Rows[n]["SN"].ToString());ws.GetRow(n + 10).GetCell(15).SetCellValue(dt.Rows[n]["TestReport"].ToString());ws.GetRow(n + 10).GetCell(16).SetCellValue(dt.Rows[n]["ReviewTabel"].ToString());ws.GetRow(n + 10).GetCell(17).SetCellValue(dt.Rows[n]["Remarks"].ToString());if (an || ar){ws.GetRow(n + 10).GetCell(18).SetCellValue(dt.Rows[n]["SwitchType"].ToString());}else{ws.GetRow(n + 10).GetCell(18).SetCellValue("");}}}#endregionws.ForceFormulaRecalculation = true;j++;}using (FileStream filess = File.OpenWrite(tpath)){book2.Write(filess);}//filess.Close(); System.IO.FileInfo filet = new System.IO.FileInfo(tpath);Response.Clear();Response.Charset = "GB2312";Response.ContentEncoding = System.Text.Encoding.UTF8;// 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(SearchText + ".xls"));// 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", filet.Length.ToString());// 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel";// 把文件流发送到客户端 Response.WriteFile(filet.FullName);// 停止页面的执行 Response.End();</span>