NPOI 导出Excel 多文件打包下载
protected void Button1_Click(object sender, EventArgs e) { DataTable dt = new DataTable("客户档案"); ArrayList arrColumns = GetXlsColumns(); DataTable dtMemberUser = DAL.Member_User.GetList(); for (int i = 0; i < arrColumns.Count; i++) { dt.Columns.Add(arrColumns[i].ToString()); } foreach (DataRow dr in dtMemberUser.Rows) { DataRow row = dt.NewRow(); Model.Sell_Order modelSellOrder = DAL.Sell_Order.GetModel(Convert.ToInt32(dr["UserID"])); if (modelSellOrder != null) { row["客户编码"] = ""; row["客户名称"] = dr["UserName"].ToString() != "" ? dr["UserName"] : dr["NickName"]; row["联系电话"] = dr["Tel"]; row["手机"] = dr["Mobile"]; row["旺旺ID"] = ""; row["收货人"] = modelSellOrder.Recipients; row["客户分类"] = ""; row["省市"] = string.Format("{0} {1}", dr["Province"], dr["City"]); row["MSN"] = ""; row["skype"] = ""; row["支付宝账号"] = ""; row["淘宝地址"] = ""; row["易趣地址"] = ""; row["拍拍地址"] = ""; row["传真"] = ""; row["电子邮件"] = dr["Email"]; row["邮编"] = modelSellOrder.Zip; row["收货人地址"] = string.Format("{0} {1} {2} {3}", modelSellOrder.Province, modelSellOrder.City, modelSellOrder.Area, modelSellOrder.Street); row["店名"] = ""; row["经营地址"] = ""; row["发展时间"] = dr["CreateTime"]; row["到期日期"] = ""; row["生日"] = dr["BirthDay"]; row["备注"] = ""; row["邮件内容"] = dr["Email"]; row["积分"] = dr["UsablePoint"]; row["性别"] = dr["Sex"]; row["证件类型"] = ""; row["证件号码"] = ""; row["店铺名称"] = "官网"; row["视力矫正要求"] = ""; row["屈光度状态"] = ""; row["自定义属性3"] = ""; row["自定义属性4"] = ""; row["自定义属性5"] = ""; row["自定义属性6"] = ""; row["自定义属性7"] = ""; row["自定义属性8"] = ""; row["自定义属性9"] = ""; dt.Rows.Add(row); } } //GridView1.DataSource = dt; //GridView1.DataBind(); string xlsHeaderText = string.Format("{0} (导入时删除此行)", "客户档案"); //Common.ExcelHelper.Export(dt, null, "客户档案(未下单).xls"); int recordCount = dt.Rows.Count; ; int pageSize = 10000; int pageCount = (recordCount % pageSize) == 0 ? recordCount / pageSize : (recordCount / pageSize) + 1; MemoryStream st = new MemoryStream(); using (ZipFile zip = ZipFile.Create(st)) { zip.BeginUpdate(); for (int pageIndex = 1; pageIndex <= pageCount; pageIndex++) { DataTable dtPage = Common.Utility.GetPage(pageIndex, pageSize, out recordCount, dt); MemoryStream ms = Common.ExcelHelper.GetExcelStream(dtPage, xlsHeaderText); StreamDataSource sds = new StreamDataSource(ms); zip.Add(sds, string.Format("客户档案({0}).xls", pageIndex)); } zip.CommitUpdate(); } string filename = "客户档案.zip"; Response.ContentType = "application/zip"; if (Request.UserAgent.ToLower().IndexOf("msie") > -1) { filename = HttpUtility.UrlPathEncode(filename); } if (Request.UserAgent.ToLower().IndexOf("firefox") > -1) { Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", filename)); } else { Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); } Response.BinaryWrite(st.GetBuffer()); Response.Flush(); Response.End(); }
protected static void SetSheetDropdownList(HSSFSheet excelSheet) { for (int i = 0; i < ListColumnsName.Count; i++) { string[] s = null; switch (i) { case 5: s = new string[] { "Y", "N" }; break; case 7: s = new string[] { "Y", "N" }; break; case 8: s = new string[] { "1", "2", "3", "4", "4*" }; break; case 9: s = new string[] { "In", "Out" }; break; case 10: s = new string[] { "1", "2", "3", "4", "4*" }; break; case 11: s = new string[] { "In", "Out" }; break; case 12: s = new string[]{"6","20","7","21","6.1","201.1","20.1","207","10","195","15","198","24", "204","27","32","209","35.2","22","23","208","8.1","203","22.1","208.1"}; break; case 13: s = new string[] { "On-going agreement", "Single Occurrence", "No Restatement Possible", "Pending" }; break; case 16: s = new string[] { "Y", "N" }; break; default: break; } InsertList(excelSheet, s, 65535, i); } } /// <summary> /// 插入数据行 /// </summary> protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook) { int rowCount = 0; int sheetCount = 1; HSSFSheet newsheet = null; //行数 int daSourceCount = dtSource.Rows.Count; //循环数据源导出数据集 newsheet = excelWorkbook.CreateSheet("Report Table"); CreateHeader(newsheet, excelWorkbook); SetSheetDropdownList(newsheet); foreach (DataRow dr in dtSource.Rows) { rowCount++; //超出65535条数据 创建新的工作簿 if (rowCount % 65535 == 0) { sheetCount++; newsheet = excelWorkbook.CreateSheet("Report Table" + sheetCount); CreateHeader(newsheet, excelWorkbook); SetSheetDropdownList(newsheet); rowCount = 1; } HSSFRow newRow = newsheet.CreateRow(rowCount); InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, rowCount); } } /// <summary> /// sheet1的插入 /// </summary> /// <param name="dtSource"></param> /// <param name="drSource"></param> /// <param name="currentExcelRow"></param> /// <param name="excelSheet"></param> /// <param name="excelWorkBook"></param> protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook, int rowCount) { for (int i = 0; i < drSource.ItemArray.Length; i++) { string cellValue = string.Empty; HSSFCell cell = currentExcelRow.CreateCell(i); if (!(drSource.ItemArray[i] is DBNull)) { cellValue = drSource.ItemArray[i].ToString(); } cell.SetCellValue(cellValue); } } protected static void InsertList(HSSFSheet excelSheet, string[] s, int rowCount, int cellIndex) { if (s == null) { return; } CellRangeAddressList regions = new CellRangeAddressList(1, rowCount, cellIndex, cellIndex); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(s); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); excelSheet.AddValidationData(dataValidate); } /// <summary> /// 设置边框绿色底色字体加粗 /// </summary> /// <param name="excelWorkBook"></param> /// <returns></returns> protected static HSSFCellStyle setborderGreen(HSSFWorkbook excelWorkBook) { HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle(); HSSFFont font = excelWorkBook.CreateFont(); font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD; style.SetFont(font); style.FillForegroundColor = HSSFColor.GREEN.LIGHT_GREEN.index; style.FillPattern = HSSFCellStyle.BORDER_THIN; style.BorderBottom = HSSFCellStyle.BORDER_THIN; style.BorderLeft = HSSFCellStyle.BORDER_THIN; style.BorderRight = HSSFCellStyle.BORDER_THIN; style.BorderTop = HSSFCellStyle.BORDER_THIN; return style; } /// <summary> /// 设置边框灰色底色字体加粗 /// </summary> /// <param name="excelWorkBook"></param> /// <returns></returns> protected static HSSFCellStyle setborderViolet(HSSFWorkbook excelWorkBook) { HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle(); HSSFFont font = excelWorkBook.CreateFont(); font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD; style.SetFont(font); style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; style.FillPattern = HSSFCellStyle.BORDER_THIN; style.BorderBottom = HSSFCellStyle.BORDER_THIN; style.BorderLeft = HSSFCellStyle.BORDER_THIN; style.BorderRight = HSSFCellStyle.BORDER_THIN; style.BorderTop = HSSFCellStyle.BORDER_THIN; return style; } }}
[解决办法]
数据多了可以导出多个Sheet