.NET Core导出带图片的Excel

本次实现技术为.NET Core 3.1

导出的Excel项目中，包含如下的一张图片：

将当前图片导出,代码如下：

 

控制器代码：

 /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<ApiResponse> ExportExcelAchemesDataList(string code, string type)
        {
            PagedList<dynamic> data = _eventRecordServices.GetQRCodeList(code, type, 1, 10000);
            if (data==null)
            {
                return ApiResponse.Fail("暂无数据");
            }
                    
            //导出excel
            IWorkbook workbook = new HSSFWorkbook();//创建Workbook
            workbook.CreateSheet("巡检二维码");//创建sheet
            string FileName = "巡检二维码.xls";
            string Paths = Path.GetFullPath("..");
            string path = "";
            foreach (var item in Paths.Split("\\"))
            {
                if (item == "PandaSewerage")
                {
                    break;
                }
                path += item + "\\";
            }
            string guid = Guid.NewGuid().ToString();
            string stringPath = path + $"BufFile\\OutFiles\\DownLoadFiles\\ExportExcel\\{guid}\\";
            string filePath = $"{stringPath}";
            if (!System.IO.Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            using (FileStream fs = System.IO.File.Create(filePath + FileName))
            {

                #region 考核列表
                ISheet sheet = workbook.GetSheetAt(0);//获取sheet
                IRow rowHeader = sheet.CreateRow(0);
                //创建头部
                rowHeader.CreateCell(0).SetCellValue("污水厂名称");
                rowHeader.CreateCell(1).SetCellValue("设备编码");
                rowHeader.CreateCell(2).SetCellValue("二维码名称");
                rowHeader.CreateCell(3).SetCellValue("二维码类型");
                rowHeader.CreateCell(4).SetCellValue("二维码");
                rowHeader.CreateCell(5).SetCellValue("编制人");
                rowHeader.CreateCell(6).SetCellValue("编制时间");
                //创建头部样式和列宽度
                ICellStyle titleStyle = workbook.CreateCellStyle();
                titleStyle.Alignment = HorizontalAlignment.Center; // 居中
                IFont titleFont = workbook.CreateFont();
                titleFont.IsBold = true;
                titleFont.FontHeightInPoints = 12;
                titleFont.Color = HSSFColor.Black.Index;//设置字体颜色
                titleStyle.SetFont(titleFont);
                sheet.GetRow(0).GetCell(0).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(1).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(2).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(3).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(4).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(5).CellStyle = titleStyle;
                sheet.GetRow(0).GetCell(6).CellStyle = titleStyle;
                short defHeight = sheet.DefaultRowHeight;


                sheet.SetColumnWidth(0, 5000);
                sheet.SetColumnWidth(1, 5000);
                sheet.SetColumnWidth(2, 5000);
                sheet.SetColumnWidth(3, 5000);
                sheet.SetColumnWidth(4, 8000);
                sheet.SetColumnWidth(5, 5000);
                sheet.SetColumnWidth(6, 6000);
                //创建内容样式
                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center; ;//垂直居中   
                IFont font = workbook.CreateFont();
                font.IsBold = false;
                font.FontHeightInPoints = 12;
                font.Color = HSSFColor.Black.Index;
                style.SetFont(font);
                //赋值
                int x = 1;
                for (int i = 0; i < data.list.Count; i++)
                {

                    IRow rowData = sheet.CreateRow(i + 1);
                   
                    rowData.CreateCell(0).SetCellValue(data.list[i].污水厂名称);
                    rowData.GetCell(0).CellStyle = style;

                    rowData.CreateCell(1).SetCellValue(data.list[i].关联编码);
                    rowData.GetCell(1).CellStyle = style;

                    rowData.CreateCell(2).SetCellValue(data.list[i].二维码名称);
                    rowData.GetCell(2).CellStyle = style;

                    rowData.CreateCell(3).SetCellValue(data.list[i].二维码类型);
                    rowData.GetCell(3).CellStyle = style;

                    string picurl = data.list[i].二维码;  //图片存储路径
                    if (ImageExists(picurl))
                    {
                        rowData.Height = 130 * 20;
                        AddPieChart(sheet, workbook, picurl, i + 1, 4);                    
                        //rowData.GetCell(4).CellStyle = style;
                    }
                    else
                    {
                        rowData.Height = defHeight;
                        //rowData.CreateCell(4).SetCellValue(data.list[i].二维码);
                        rowData.CreateCell(4).SetCellValue("未找到二维码");
                        rowData.GetCell(4).CellStyle = style;
                    }                       
                    rowData.CreateCell(5).SetCellValue(data.list[i].编制人);
                    rowData.GetCell(5).CellStyle = style;

                    rowData.CreateCell(6).SetCellValue(data.list[i].编制时间.ToString());
                    rowData.GetCell(6).CellStyle = style;
                }
                #endregion
                workbook.Write(fs);
                var relativePositioning = $"\\BufFile\\OutFiles\\DownLoadFiles\\ExportExcel\\{guid}\\" + FileName;
                return ApiResponse.OK(relativePositioning);

                //return Ok(new { code = 0, msg = "成功", data = relativePositioning });
                //return File(ms, "application/vnd.ms-excel", fileName);
            }


        }

关键代码导出图片

    private void AddPieChart(ISheet sheet, IWorkbook workbook, string fileurl, int row, int col)
        {
            try
            {
                string Paths = Path.GetFullPath("..");
                string path = "";
                foreach (var item in Paths.Split("\\"))
                {
                    if (item == "PandaSewerage")
                    {
                        break;
                    }
                    path += item + "\\";
                }
                string stringPath = path.TrimEnd('\\') + $"{fileurl.Replace("/", "\\")}";
                if (System.IO.File.Exists(stringPath))
                {
                    string FileName = stringPath;
                    byte[] bytes = System.IO.File.ReadAllBytes(FileName);
                    if (!string.IsNullOrEmpty(FileName))
                    {
                        int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 30, 30, col, row, col+1 , row+1 );
                        //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

                        HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                        // pict.Resize();这句话一定不要，这是用图片原始大小来显示
                    }
                }

            }
            catch (Exception ex)
            {

            }
        }

效果图：

 

代码简单说明：

显示图片核心代码为【HSSFClientAnchor】这个方法，具体说明如下：

dx1 dy1 起始单元格中的x,y坐标.

dx2 dy2 结束单元格中的x,y坐标

col1,row1 指定起始的单元格，下标从0开始

col2,row2 指定结束的单元格 ，下标从0开始