概述
导出excel文件,而且文件带有复杂的表头格式并有二维码信息。前端界面是easyui,后端是.net(用ashx方式),使用了NPOI和Qrcode,本示例还增加了页面参数传值,实现的思路很简单:
- 查出我们期望的数据集DataTable以下简称dt
- 独立绘制表头单元格,填充概要信息和表头样式
- 将sheet页填充数据的单元格进行整理,按需合并单元格,设置样式
- 填充数据
希望实现的效果如下图所示
前端页面
//打印的js代码
function Print() {
var tbxBoxno = $("#tbxBoxno").textbox('getValue'); //页面传值参数:箱单号
var tbxDBno = $("#tbxDBno").textbox('getValue'); //页面传值参数:批次号
var filename = "../../Controller/InvoiceManage/InvoiceImport.ashx?Action=ExportDetails&Boxno=" + encodeURIComponent(tbxBoxno) + "&DBno=" + encodeURIComponent(tbxDBno);
location.href = filename;
}
//打印按钮html代码
<a id="btnPrinter" href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-printer'" onclick="Print()">打印</a>
后端页面(导出excel的完整代码)
//InvoiceImport.ashx文件
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
//获取请求的动作
string action = context.Request["Action"];
if (string.IsNullOrEmpty(action)) return;
//页面参数
long projectId = Int64.Parse(Encrypt.Base64Decrypt(CookiesHelper.GetCookie_l("MC_pId"))); //通过cookie方式页面传值参数:项目ID
string domainName = Encrypt.Base64Decrypt(CookiesHelper.GetCookie_l("MC_doNa")); 通过cookie方式页面传值参数:域登录名
InvoiceImport Invoice = new InvoiceImport(); //封装好的业务类,查询dt用
DataTable json_data_details = new DataTable();
string json_stream_details = string.Empty;
string json_stream_format_details = string.Empty;
switch (action)
{
case "ExportDetails":// 打印箱单
#region 数据整理
string BoxnoImp = HttpUtility.UrlDecode(context.Request["Boxno"].ToString()); //对接收的字符串进行URL解码;
string DBnoImp = HttpUtility.UrlDecode(context.Request["DBno"].ToString());
string PartNumberImp = "";
DataTable dtImp = Invoice.GetDetailwithDesc(BoxnoImp, DBnoImp, PartNumberImp, projectId); //dtImp就是我们的dt
//概要数据
long ImpProjectId = projectId;
string ImpPONo = dtImp.Rows[0]["合同号"].ToString(); //合同号
string ImpPOName = dtImp.Rows[0]["合同名称"].ToString(); //合同号
string ImpDiscipline = dtImp.Rows[0]["专业"].ToString(); //专业名称
string ImpProductPlace = dtImp.Rows[0]["原产地"].ToString(); //原产地
string ImpInvoiceDate = dtImp.Rows[0]["登记时间"].ToString(); //箱单登记时间
string ImpPackageNo = dtImp.Rows[0]["采购包号"].ToString(); ; //采购包编号
string ImpBoxNo = dtImp.Rows[0]["箱号"].ToString(); //箱号
string ImpBacthNo = dtImp.Rows[0]["物流批次号"].ToString(); //物流批次号
string ImpVendorName = dtImp.Rows[0]["企业简称"].ToString();
string ImpSheetName = "Detailed Packing List 详细装箱清单";
WEC.Material.Utilitys.Report imp = new WEC.Material.Utilitys.Report();
string ImpProjName = imp.GetProjectName(ImpProjectId.ToString(), WEC.Material.Utilitys.Report.Language.Chinese); //自己写的方法查询项目全称
#endregion
#region 报表输出
int rowOfTitle = 5; //表头概要信息的行数
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
//表头字体
IFont fontInTitle = hssfworkbook.CreateFont();
fontInTitle.Color = HSSFColor.BLACK.index;
fontInTitle.FontHeightInPoints = 11;
fontInTitle.Boldweight = (short)FontBoldWeight.BOLD;
fontInTitle.FontName = "Times New Roman";
//数据字体
IFont fontInData = hssfworkbook.CreateFont();
fontInData.Color = HSSFColor.BLACK.index;
fontInData.FontHeightInPoints = 10;
fontInData.Boldweight = 10;
fontInData.FontName = "Times New Roman";
//表头cell样式
ICellStyle cellStyleOfTitle = hssfworkbook.CreateCellStyle();
cellStyleOfTitle.IsLocked = true;
cellStyleOfTitle.SetFont(fontInTitle);
cellStyleOfTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyleOfTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
cellStyleOfTitle.WrapText = true;
cellStyleOfTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyleOfTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyleOfTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyleOfTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
//数据cell样式(未锁定)
ICellStyle unlocked = hssfworkbook.CreateCellStyle();
unlocked.IsLocked = false;
unlocked.SetFont(fontInData);
unlocked.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
unlocked.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
unlocked.WrapText = true;
unlocked.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
unlocked.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
unlocked.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
unlocked.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
//数据cell样式(锁定)
ICellStyle locked = hssfworkbook.CreateCellStyle();
locked.IsLocked = true;
locked.SetFont(fontInData);
locked.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
locked.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
locked.WrapText = true;
locked.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
locked.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
locked.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
locked.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
//sheet页名称
ISheet sheet1 = hssfworkbook.CreateSheet("Detailed Packing List");
sheet1.DefaultColumnWidth = 2;
#region 表头初始化
//概要信息前三行
IRow[] rowsOfTitle = new IRow[rowOfTitle];
for (int i = 0; i < rowOfTitle; i++)
{
rowsOfTitle[i] = sheet1.CreateRow(i);
IRow row = rowsOfTitle[i];
if (i < 3)
{
row.HeightInPoints = 23;
}
else
{
row.HeightInPoints = 16;
}
}
//画公司图标
string imageUrl = System.Web.HttpContext.Current.Server.MapPath("~/Content/logo/logo_doc.jpg");
byte[] bytes = System.IO.File.ReadAllBytes(imageUrl);
int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
//dx1: 图片左边相对excel格的位置(x偏移) 范围值为: 0~1023; 即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1: 图片上方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。
//dx2: 图片右边相对excel格的位置(x偏移) 范围值为: 0~1023; 原理同上。
//dy2: 图片下方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。
//col1和row1: 图片左上角的位置,以excel单元格为参考,比喻这两个值为(1, 1),那么图片左上角的位置就是excel表(1, 1)单元格的右下角的点(A, 1)右下角的点。
//col2和row2: 图片右下角的位置,以excel单元格为参考,比喻这两个值为(2, 2),那么图片右下角的位置就是excel表(2, 2)单元格的右下角的点(B, 2)右下角的点。
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//公司名称单元格合并
ICell cellOfCompanyName = rowsOfTitle[0].CreateCell(0);
//cellOfCompanyName.SetCellValue(ImpLongName);
CellRangeAddress region = new CellRangeAddress(0, 2, 0, 9);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//项目名称
ICell cellOfProjectName = rowsOfTitle[0].CreateCell(10);
cellOfProjectName.SetCellValue(ImpProjName);
region = new CellRangeAddress(0, 0, 10, 29);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//合同名称
ICell cellOfPurchaseName = rowsOfTitle[1].CreateCell(10);
cellOfPurchaseName.SetCellValue(ImpPOName);
region = new CellRangeAddress(1, 1, 10, 29);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//表格名称
ICell cellOfPurchase = rowsOfTitle[2].CreateCell(10);
cellOfPurchase.SetCellValue(ImpSheetName);
region = new CellRangeAddress(2, 2, 10, 29);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//专业
ICell cellOfEnquiry = rowsOfTitle[0].CreateCell(30);
cellOfEnquiry.SetCellValue("专业");
region = new CellRangeAddress(0, 0, 30, 33);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//供应商
ICell cellOfPurchaseBag = rowsOfTitle[1].CreateCell(30);
cellOfPurchaseBag.SetCellValue("供应商");
region = new CellRangeAddress(1, 1, 30, 33);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//原产地
ICell cellOfSpecial = rowsOfTitle[2].CreateCell(30);
cellOfSpecial.SetCellValue("原产地");
region = new CellRangeAddress(2, 2, 30, 33);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//专业
ICell cellOfDisc = rowsOfTitle[0].CreateCell(34);
cellOfDisc.SetCellValue(ImpDiscipline);
region = new CellRangeAddress(0, 0, 34, 40);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//供应商
ICell cellOfVendor = rowsOfTitle[1].CreateCell(34);
cellOfVendor.SetCellValue(ImpVendorName);
region = new CellRangeAddress(1, 1, 34, 40);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//原产地
ICell cellOfProductPlace = rowsOfTitle[2].CreateCell(34);
cellOfProductPlace.SetCellValue(ImpProductPlace);
region = new CellRangeAddress(2, 2, 34, 40);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//合同号
ICell cellOfPO = rowsOfTitle[0].CreateCell(41);
cellOfPO.SetCellValue("合同号");
region = new CellRangeAddress(0, 0, 41, 44);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//箱号
ICell cellOfBox = rowsOfTitle[1].CreateCell(41);
cellOfBox.SetCellValue("箱号");
region = new CellRangeAddress(1, 1, 41, 44);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//物流批次号
ICell cellOfBatch = rowsOfTitle[2].CreateCell(41);
cellOfBatch.SetCellValue("物流批次号");
region = new CellRangeAddress(2, 2, 41, 44);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//合同号
ICell cellOfPOValue = rowsOfTitle[0].CreateCell(45);
cellOfPOValue.SetCellValue(ImpPONo);
region = new CellRangeAddress(0, 0, 45, 55);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//箱号
ICell cellOfBoxValue = rowsOfTitle[1].CreateCell(45);
cellOfBoxValue.SetCellValue(ImpBoxNo);
region = new CellRangeAddress(1, 1, 45, 55);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//物流批次号
ICell cellOfBatchValue = rowsOfTitle[2].CreateCell(45);
cellOfBatchValue.SetCellValue(ImpBacthNo);
region = new CellRangeAddress(2, 2, 45, 55);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
//放置二维码单元格
ICell cellOfQRValue = rowsOfTitle[2].CreateCell(56);
//cellOfQRValue.SetCellValue(ImpBacthNo);
region = new CellRangeAddress(0, 2, 56, 59);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
#endregion
#region 二维码处理
//画二维码
MemoryStream msqr = new MemoryStream();
ErrorCorrectionLevel Ecl = ErrorCorrectionLevel.M; //误差校正水平
//二维码需要加密,再简单的加密也比明文裸奔强
//我的二维码是扫描之后自动跳转到管理系统的一个确认页面,大家在这里可以自由发挥
string projectIdEcode = DES.Encode(projectId.ToString());
string domainNameEcode = DES.Encode(domainName.ToString());
string ImpBoxNoEcode = DES.Encode(ImpBoxNo.ToString());
string ImpBacthNoEcode = DES.Encode(ImpBacthNo.ToString());
string url = "https://你的二级域名.你的公司域名.com:8088/Invoice/Index?projectID="+ projectIdEcode + "&ITCode="+ domainNameEcode + "&boxNo="+ ImpBoxNoEcode + "&shippingNo="+ ImpBacthNoEcode;
string Content = url; //待编码内容
QuietZoneModules QuietZones = QuietZoneModules.Zero; //空白区域
int ModuleSize = 12;//大小
var encoder = new QrEncoder(Ecl);
QrCode qr;
if (encoder.TryEncode(Content, out qr))//对内容进行编码,并保存生成的矩阵
{
GraphicsRenderer render = new GraphicsRenderer(new FixedModuleSize(ModuleSize, QuietZones));
render.WriteToStream(qr.Matrix, ImageFormat.Png, msqr);
}
byte[] bytesQR = StreamToBytes(msqr);
int pictureIdxQR = hssfworkbook.AddPicture(bytesQR, PictureType.PNG);
HSSFPatriarch patriarchQR = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchorQR = new HSSFClientAnchor(450, 30, 590, 220, 56, 0, 59, 2);
//把图片插到相应的位置,这个坐标要有耐心慢慢调整
HSSFPicture pictQR = (HSSFPicture)patriarchQR.CreatePicture(anchorQR, pictureIdxQR);
#endregion
#region 字段初始化
//字段样式:"序号", "箱号", "合同号", "材料编码", "位号", "主项号", "材料名称", "材料描述", "数量", "单位", "备注"
//2, 7, 7, 4, 4, 4, 5, 15, 3, 3, 6
//这串数字表示每各数据列占据几个固定单位列,每个固定单位宽度的列宽度为3
ICell cellOfNUMInTitle = rowsOfTitle[3].CreateCell(0);
cellOfNUMInTitle.SetCellValue("序号\nSN.");
region = new CellRangeAddress(3, 4, 0, 1);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfBoxInTitle = rowsOfTitle[3].CreateCell(2);
cellOfBoxInTitle.SetCellValue("箱号\nPackage No.");
region = new CellRangeAddress(3, 4, 2, 8);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfPurchaseIDInTitle = rowsOfTitle[3].CreateCell(9);
cellOfPurchaseIDInTitle.SetCellValue("合同号\nPurchase No.");
region = new CellRangeAddress(3, 4, 9, 15);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMaterialNameInTitle = rowsOfTitle[3].CreateCell(16);
cellOfMaterialNameInTitle.SetCellValue("材料编码\n Part No.");
region = new CellRangeAddress(3, 4, 16, 19);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMaterialCodeInTitle = rowsOfTitle[3].CreateCell(20);
cellOfMaterialCodeInTitle.SetCellValue("位号\n Tag No.");
region = new CellRangeAddress(3, 4, 20, 23);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfDesignInTitle = rowsOfTitle[3].CreateCell(24);
cellOfDesignInTitle.SetCellValue("主项号\n Area");
region = new CellRangeAddress(3, 4, 24, 27);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMainItemNoInTitle = rowsOfTitle[3].CreateCell(28);
cellOfMainItemNoInTitle.SetCellValue("材料名称\n Name");
region = new CellRangeAddress(3, 4, 28, 32);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMainItemDescInTitle = rowsOfTitle[3].CreateCell(33);
cellOfMainItemDescInTitle.SetCellValue("材料描述\n Description");
region = new CellRangeAddress(3, 4, 33, 47);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMainItemNumInTitle = rowsOfTitle[3].CreateCell(48);
cellOfMainItemNumInTitle.SetCellValue("数量\nQty.");
region = new CellRangeAddress(3, 4, 48, 50);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfMainItemNuitInTitle = rowsOfTitle[3].CreateCell(51);
cellOfMainItemNuitInTitle.SetCellValue("单位\nUnit");
region = new CellRangeAddress(3, 4, 51, 53);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
ICell cellOfRemarkInTitle = rowsOfTitle[3].CreateCell(54);
cellOfRemarkInTitle.SetCellValue("备注\nRemark");
region = new CellRangeAddress(3, 4, 54, 59);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, cellStyleOfTitle);
#endregion
#region 填充材料数据
int dataCount = dtImp.Rows.Count;
IRow[] rowsOfData = new IRow[dataCount];
for (int i = 0; i < dataCount; i++)
{
int currentRowNum = i + 5;
rowsOfData[i] = sheet1.CreateRow(currentRowNum);
IRow currentRow = rowsOfData[i];
currentRow.HeightInPoints = 28;
ICell cellInData = currentRow.CreateCell(0);
cellInData.SetCellValue(dtImp.Rows[i]["序号"].ToString());//序号
region = new CellRangeAddress(currentRowNum, currentRowNum, 0, 1);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(2);
cellInData.SetCellValue(dtImp.Rows[i]["箱号"].ToString());//箱号
region = new CellRangeAddress(currentRowNum, currentRowNum, 2, 8);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(9);
cellInData.SetCellValue(dtImp.Rows[i]["合同号"].ToString());//合同号
region = new CellRangeAddress(currentRowNum, currentRowNum, 9, 15);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(16);
cellInData.SetCellValue(dtImp.Rows[i]["材料编码"].ToString());//材料编码
region = new CellRangeAddress(currentRowNum, currentRowNum, 16, 19);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(20);
cellInData.SetCellValue(dtImp.Rows[i]["位号"].ToString());//位号
region = new CellRangeAddress(currentRowNum, currentRowNum, 20, 23);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(24);
cellInData.SetCellValue(dtImp.Rows[i]["主项号"].ToString());//主项号
region = new CellRangeAddress(currentRowNum, currentRowNum, 24, 27);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(28);
cellInData.SetCellValue(dtImp.Rows[i]["材料名称"].ToString());//材料名称
region = new CellRangeAddress(currentRowNum, currentRowNum, 28, 32);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(33);
cellInData.SetCellValue(dtImp.Rows[i]["材料描述"].ToString());//描述
region = new CellRangeAddress(currentRowNum, currentRowNum, 33, 47);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(48);
cellInData.SetCellValue(WEC.Material.Utilitys.Util.turnStringToDouble(dtImp.Rows[i]["发货量"].ToString()));//数量
region = new CellRangeAddress(currentRowNum, currentRowNum, 48, 50);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(51);
cellInData.SetCellValue(dtImp.Rows[i]["计量单位"].ToString());//单位
region = new CellRangeAddress(currentRowNum, currentRowNum, 51, 53);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
cellInData = currentRow.CreateCell(54);
cellInData.SetCellValue(dtImp.Rows[i]["补充说明"].ToString());//备注
region = new CellRangeAddress(currentRowNum, currentRowNum, 54, 59);
sheet1.AddMergedRegion(region);
MakeRegionAndSetStyle(sheet1, region, locked);
}
#endregion
sheet1.ProtectSheet("password"); //设置文档保护密码
#region 打印设置
sheet1.SetMargin(MarginType.TopMargin, 2.1);
sheet1.SetMargin(MarginType.BottomMargin, 1.5);
sheet1.SetMargin(MarginType.RightMargin, 1.5);
sheet1.SetMargin(MarginType.LeftMargin, 1.0);
sheet1.PrintSetup.NoColor = true;
sheet1.PrintSetup.Landscape = true;
sheet1.PrintSetup.PaperSize = (short)PaperSize.A4;
sheet1.FitToPage = true;
sheet1.PrintSetup.FitHeight = 2;
sheet1.PrintSetup.FitWidth = 3;
sheet1.IsPrintGridlines = true;
#endregion
//设置文件名
string str_Impname = "箱单_" + ImpBoxNo + "_" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
#endregion
#region 输出
string str_rptname = string.Empty;
//解决中文文件名乱码只在IE中有效
//filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
if (context.Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
//当客户端使用IE时,对其进行编码;
//使用 ToHexString 代替传统的 UrlEncode();
str_rptname = WEC.Material.Utilitys.CommonHelper.ToHexString(str_Impname);
}
if (context.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
{
//为了向客户端输出空格,需要在当客户端使用 Firefox 时特殊处理
context.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + str_Impname + "\"");
}
else
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + str_Impname);
string ImpExtension = Path.GetExtension(str_Impname);
context.Response.ContentType = CommonHelper.GetMimeType(ImpExtension);
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.Clear();
MemoryStream Excelfile = new MemoryStream();
hssfworkbook.Write(Excelfile);
Excelfile.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
#endregion
break;
}
}
两个private方法
///设置范围的样式
private static void MakeRegionAndSetStyle(ISheet sheet, CellRangeAddress region, ICellStyle style)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
}
/// 将 Stream 转成 byte[]
private byte[] StreamToBytes(Stream stream)
{
byte[] bytes = new byte[stream.Length];
// 设置当前流的位置为流的开始
stream.Seek(0, SeekOrigin.Begin);
stream.Read(bytes, 0, bytes.Length);
return bytes;
}