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;
}
}
}
