【NPOI】导出Excel带图片

1. 本地路径图片

//填写内容 
for (int i = 0; i < dt.Rows.Count; i++)
{  
    IRow row = sheet.CreateRow(i + 1 + 1);
    row.Height = 80 * 20; //设置excel行高，像素点是1/20
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        if (j == 4)
        {
            var dPath = AppDomain.CurrentDomain.BaseDirectory + dt.Rows[i][j].ToString().Replace(@"/", @"\");
            if (File.Exists(dPath)) //防止文件不存在时报错
            {
                byte[] picBytes = System.IO.File.ReadAllBytes(dPath);
                int picIndex = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.JPEG);
                XSSFDrawing patr = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 2, j + 1, i + 3);
                XSSFPicture picture = (XSSFPicture)patr.CreatePicture(anchor, picIndex);
            }
        }
        else
        {
            row.CreateCell(j, CellType.String).SetCellValue(dt.Rows[i][j].ToString());
        }
    }
}

2. 网络路径图片

/// <summary>
/// 向sheet插入图片
/// </summary> 
private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, string imgPath, int row, int col)
{
    Uri uri = new Uri(imgPath); //imgPath :网络图片地址    
    WebRequest webRequest = WebRequest.Create(uri);

    using (WebResponse webResponse = webRequest.GetResponse())
    {
        //防止发生报错：GDI+中发生一般性错误的解决办法
        Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流 
        Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 
        bitmap.Dispose();   //将原来的图片流释放，将图片文件进行解锁。 

        using (MemoryStream ms = new MemoryStream())
        {
            OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] bytes = ms.ToArray();
            int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);  //添加图片 
            XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row + 2, col + 1, row + 3);
            //图片位置，图片左上角为（col, row） 
            XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            //pict.Resize(); //用图片原始大小来显示   
        } 
    }
}

3. 导出Excel

#region 方案商品导出Excel
private static ICellStyle GetTitleCellStyleForScheme(IWorkbook wb)
{
    ICellStyle cellStyle = wb.CreateCellStyle();
    IFont font = wb.CreateFont();

    font.FontName = "微软雅黑";
    font.FontHeightInPoints = 15;
    font.Boldweight = short.MaxValue;
    cellStyle.SetFont(font);

    //水平对齐  
    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
    //垂直对齐  
    cellStyle.VerticalAlignment = VerticalAlignment.Center;
    //自动换行  
    cellStyle.WrapText = true;
    return cellStyle;
}

/// <summary>
/// 向sheet插入图片
/// </summary> 
private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, string imgPath, int row, int col)
{
    Uri uri = new Uri(imgPath); //imgPath :网络图片地址    
    WebRequest webRequest = WebRequest.Create(uri);

    using (WebResponse webResponse = webRequest.GetResponse())
    {
        Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流 
        Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 
        bitmap.Dispose();   //将原来的图片流释放，将图片文件进行解锁。 

        using (MemoryStream ms = new MemoryStream())
        {
            OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] bytes = ms.ToArray();
            int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);  //添加图片 
            XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row + 2, col + 1, row + 3);
            //图片位置，图片左上角为（col, row） 
            XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            //pict.Resize(); //用图片原始大小来显示   
        }
    }
}

/// <summary>
/// 导出Excel
/// </summary>
/// <param name="tbScheme"></param>
/// <param name="tbGoods"></param>
/// <param name="sheetName"></param>
/// <param name="exportType">0：基础数据表；1：报价工具</param>
/// <param name="SchemeBuyWay">4：集采；其他为代发</param>
/// <returns></returns>
public static byte[] ExportExcelForSchemeGoods(DataTable tbScheme, DataTable tbGoods, string sheetName, string exportType, string SchemeBuyWay)
{
    byte[] ret = null;
    string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

    XSSFWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet(sheetName);
    IRow rowHead = sheet.CreateRow(0);  //首行为方案信息
    rowHead.Height = 50 * 20;
    string strSchemeInfo = string.Empty;
    if (tbScheme != null && tbScheme.Rows.Count > 0)
    {
        strSchemeInfo += "方案编号：" + tbScheme.Rows[0]["SchemeCode"] + "；方案名称：" + tbScheme.Rows[0]["SchemeName"] + "；单人预算：" + tbScheme.Rows[0]["SingleBudget"] + "；采购人数：" + tbScheme.Rows[0]["BuyNumber"] + "；方案状态：" + tbScheme.Rows[0]["ApprovalStatusName"] + "；创建人：" + tbScheme.Rows[0]["Creator"] + "；方案有效期：" + tbScheme.Rows[0]["SchemeAgeingTime"];
    }

    //合并单元格显示方案信息
    if (tbGoods != null && tbGoods.Rows.Count > 0)
    {
        for (int i = 0; i < tbGoods.Columns.Count; i++)
        {
            ICell cell = rowHead.CreateCell(i, CellType.String);
            cell.CellStyle = GetTitleCellStyleForScheme(workbook);
            cell.SetCellValue(strSchemeInfo);
        }

        NPOI.SS.Util.CellRangeAddress range = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tbGoods.Columns.Count - 1);
        sheet.AddMergedRegion(range);
        rowHead = sheet.CreateRow(1);
        //填写表头 
        for (int i = 0; i < tbGoods.Columns.Count; i++)
        {
            if (exportType == "0")    //基础数据
            {
                if (tbGoods.Columns[i].ColumnName.ToString() == "商品图片")
                {
                    sheet.SetColumnHidden(i, true);
                    continue;
                }
            }
            if (SchemeBuyWay != "4")  //代发不展示单人采购量
            {
                if (tbGoods.Columns[i].ColumnName.ToString() == "单人份采购量/总采购量")
                {
                    sheet.SetColumnHidden(i, true);
                    continue;
                }
            }
            ICell cell = rowHead.CreateCell(i, CellType.String);
            cell.CellStyle = getHeadCellStyle(workbook);
            cell.SetCellValue(tbGoods.Columns[i].ColumnName.ToString());
        }
        //填写内容 
        for (int i = 0; i < tbGoods.Rows.Count; i++)
        {
            IRow row = sheet.CreateRow(i + 1 + 1);
            for (int j = 0; j < tbGoods.Columns.Count; j++)
            {
                if (exportType == "1")  //报价工具：带图片
                {
                    if (j == 1)
                    {
                        var imgPath = tbGoods.Rows[i][j].ToString();
                        if (imgPath != "")
                        {
                            AddCellPicture(sheet, workbook, imgPath, i, j);
                        }
                    }
                }
                else
                {
                    // 跳过图片列
                    if (j == 1)
                    {
                        sheet.SetColumnHidden(j, true);
                        continue;
                    }
                }

                if (SchemeBuyWay != "4")    //代发不展示单人采购量
                {
                    if (j == 8)
                    {
                        sheet.SetColumnHidden(j, true);
                        continue;
                    }
                } 

                ICell cell = row.CreateCell(j, CellType.String);
                cell.CellStyle = getTextCellStyle(workbook);
                cell.SetCellValue(tbGoods.Rows[i][j].ToString());

                row.Height = 100 * 20;  //设置默单元格行高
            }
        }

        for (int i = 0; i <= tbGoods.Columns.Count; i++)
        {
            sheet.AutoSizeColumn(i);    //列宽自适应，只对英文和数字有效
            sheet.SetColumnWidth(1, 30 * 256);  //设置图片列大小 
        }
    }


    using (var ms = new MemoryStream())
    {
        HttpResponse httpResponse = HttpContext.Current.Response;
        httpResponse.Clear();
        httpResponse.Buffer = true;
        httpResponse.Charset = Encoding.UTF8.BodyName;
        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
        httpResponse.ContentEncoding = Encoding.UTF8;
        httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
        workbook.Write(httpResponse.OutputStream);
        httpResponse.End();

        ret = ms.ToArray();
        ms.Close();
        ms.Dispose();
    }

    GC.Collect();
    return ret;
}
#endregion