常用 NPOI 导出数据到 excel,但没有试过如何导出图片。NPOI 最大的特点就是不依赖于 Excel 组件,服务端不需要安装 Excel。在单元格中插入图片主要是用 HSSFClientAnchor 对象。他有 8 个参数。
- HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
前面四个表示在单元格中两个点的位置,后面四个表示是哪个单元格。先看代码。
- public FileResult ExportAppsImg()
- {
- using (var db=new PortalDb())
- {
- HSSFWorkbook workbook = new HSSFWorkbook();
- //创建一个sheet
- ISheet sheet1 = workbook.CreateSheet("sheet1");
- // 设置列宽,excel列宽每个像素是1/256
- sheet1.SetColumnWidth(0, 18 * 256);
- sheet1.SetColumnWidth(1, 18 * 256);
- IRow rowHeader = sheet1.CreateRow(0);//创建表头行
- rowHeader.CreateCell(0, CellType.STRING).SetCellValue("生产单号");
- rowHeader.CreateCell(1, CellType.STRING).SetCellValue("学/工号");
- rowHeader.CreateCell(2, CellType.STRING).SetCellValue("手机号");
- rowHeader.CreateCell(3, CellType.STRING).SetCellValue("单位");
- rowHeader.CreateCell(4, CellType.STRING).SetCellValue("预约类型");
- rowHeader.CreateCell(5, CellType.STRING).SetCellValue("车牌号");
- rowHeader.CreateCell(6, CellType.STRING).SetCellValue("颜色");
- rowHeader.CreateCell(7, CellType.STRING).SetCellValue("品牌");
- rowHeader.CreateCell(8, CellType.STRING).SetCellValue("工作证");
- // rowHeader.CreateCell(9, CellType.STRING).SetCellValue("身份证正面");
- // rowHeader.CreateCell(10, CellType.STRING).SetCellValue("身份证反面");
- rowHeader.CreateCell(9, CellType.STRING).SetCellValue("驾驶证正面");
- rowHeader.CreateCell(10, CellType.STRING).SetCellValue("驾驶证反面");
- rowHeader.CreateCell(11, CellType.STRING).SetCellValue("结婚证");
- rowHeader.CreateCell(12, CellType.STRING).SetCellValue("状态");
- rowHeader.CreateCell(13, CellType.STRING).SetCellValue("预约时间");
- rowHeader.CreateCell(14, CellType.STRING).SetCellValue("申请时间");
- var res = db.Appointments.ToList();
- if (res.Count > 0)
- {
- int rowline = 1;//从第二行开始(索引从0开始)
- HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
- for (int i = 0; i < res.Count; i++)
- {
- IRow row = sheet1.CreateRow(rowline);
- //设置行高 ,excel行高度每个像素点是1/20
- row.Height = 80 * 20;
- //填入生产单号
- row.CreateCell(0, CellType.STRING).SetCellValue(res[i].Name);
- row.CreateCell(1, CellType.STRING).SetCellValue(res[i].SchoolNumber);
- row.CreateCell(2, CellType.STRING).SetCellValue(res[i].Mobile);
- row.CreateCell(3, CellType.STRING).SetCellValue(res[i].School);
- row.CreateCell(4, CellType.STRING).SetCellValue(GetEnumTxt(res[i].AppointmentType));
- row.CreateCell(5, CellType.STRING).SetCellValue(res[i].CardNumber);
- row.CreateCell(6, CellType.STRING).SetCellValue(res[i].Color);
- row.CreateCell(7, CellType.STRING).SetCellValue(res[i].Brand);
- //将图片文件读入一个字符串
- setPic(workbook,patriarch, res[i].WrokImg, sheet1, rowline, 8);
- // setPic(workbook, patriarch, res[i].IDCardImg, sheet1, rowline, 9);
- //setPic(workbook, patriarch, res[i].IDCardImgBack, sheet1, rowline, 10);
- setPic(workbook, patriarch, res[i].DriveCardImg, sheet1, rowline, 9);
- setPic(workbook, patriarch, res[i].DriveCardImgBack, sheet1, rowline, 10);
- setPic(workbook, patriarch, res[i].MarryCardImg, sheet1, rowline, 11);
- row.CreateCell(12, CellType.STRING).SetCellValue((GetEnumTxt(res[i].State)));
- row.CreateCell(13, CellType.STRING).SetCellValue(res[i].VerifyTime.ToString());
- row.CreateCell(14, CellType.STRING).SetCellValue(res[i].CreateTime.ToString());
- rowline++;
- }
- }
- var path = Server.MapPath("/Content/Excel/预约申请表.xls");
- //把文件保存到d:\aaa.xls,注意扩展名是.xls不要写成.xlsx
- using (Stream stream = System.IO.File.OpenWrite(path))
- {
- workbook.Write(stream);
- }
- return File(path, "application/vnd.ms-excel","预约申请表.xls");
- }
- }
- //获取枚举类型的Display特性的name值
- public string GetEnumTxt(Enum eEnum)
- {
- var enumType = eEnum.GetType();
- var field = enumType.GetField(eEnum.ToString());
- var display = field.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault() as DisplayAttribute;
- return display != null ? display.Name : eEnum.ToString();
- }
- private void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch,string path, ISheet sheet, int rowline, int col)
- {
- if(string.IsNullOrEmpty(path))return;
- byte[] bytes = System.IO.File.ReadAllBytes(Server.MapPath(path));
- int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
- // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
- HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, rowline, col+1, rowline + 1);
- //把图片插到相应的位置
- HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
- }
每一张表只能有一个 HSSFPatriarch 对象,如果把它的创建放到了 setPic 方法中,那么一行只会出现一张图片,最后的图片会消掉之前的图片。也不能放到 for 循环里。所以放在最上面。再界面上方一个 a 标签即可下载:
- <a class="btn btn-primary" href="@Url.Action(" ExportAppsImg ","Appointment
- ")">
- 导出
- </a>
效果图:
来源: http://www.bubuko.com/infodetail-1976446.html