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

        }

每一张表只能有一个HSSFPatriarch对象，如果把它的创建放到了setPic方法中，那么一行只会出现一张图片，最后的图片会消掉之前的图片。也不能放到for循环里。所以放在最上面。再界面上方一个a标签即可下载：

<a class="btn btn-primary" href="@Url.Action("ExportAppsImg","Appointment")">导出</a>

效果图：

参考博客：

http://blog.csdn.net/pan_junbiao/article/details/39717443 -- NPOI使用手册

http://www.cnblogs.com/wei325/p/4748324.html

NPOI导出多张图片到Excel的相关教程结束。