NPOI导出多张图片到Excel

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

常用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(, * );
sheet1.SetColumnWidth(, * );
IRow rowHeader = sheet1.CreateRow();//创建表头行
rowHeader.CreateCell(, CellType.STRING).SetCellValue("生产单号");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("学/工号");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("手机号");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("单位");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("预约类型");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("车牌号");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("颜色");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("品牌");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("工作证");
// rowHeader.CreateCell(9, CellType.STRING).SetCellValue("身份证正面");
// rowHeader.CreateCell(10, CellType.STRING).SetCellValue("身份证反面");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("驾驶证正面");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("驾驶证反面");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("结婚证");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("状态");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("预约时间");
rowHeader.CreateCell(, CellType.STRING).SetCellValue("申请时间");
var res = db.Appointments.ToList();
if (res.Count > )
{
int rowline = ;//从第二行开始(索引从0开始)
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
for (int i = ; i < res.Count; i++)
{
IRow row = sheet1.CreateRow(rowline);
//设置行高 ,excel行高度每个像素点是1/20
row.Height = * ;
//填入生产单号
row.CreateCell(, CellType.STRING).SetCellValue(res[i].Name);
row.CreateCell(, CellType.STRING).SetCellValue(res[i].SchoolNumber);
row.CreateCell(, CellType.STRING).SetCellValue(res[i].Mobile);
row.CreateCell(, CellType.STRING).SetCellValue(res[i].School);
row.CreateCell(, CellType.STRING).SetCellValue(GetEnumTxt(res[i].AppointmentType));
row.CreateCell(, CellType.STRING).SetCellValue(res[i].CardNumber);
row.CreateCell(, CellType.STRING).SetCellValue(res[i].Color);
row.CreateCell(, CellType.STRING).SetCellValue(res[i].Brand);
//将图片文件读入一个字符串
setPic(workbook,patriarch, res[i].WrokImg, sheet1, rowline, );
// 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, );
setPic(workbook, patriarch, res[i].DriveCardImgBack, sheet1, rowline, );
setPic(workbook, patriarch, res[i].MarryCardImg, sheet1, rowline, );
row.CreateCell(, CellType.STRING).SetCellValue((GetEnumTxt(res[i].State)));
row.CreateCell(, CellType.STRING).SetCellValue(res[i].VerifyTime.ToString());
row.CreateCell(, 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(, , , , col, rowline, col+, rowline + );
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}