使用 npoi 无法在 Excel 中显示图像

Question

下面的代码使用 npoi 创建 Excel 文档，在 Open Office calc 中显示图像，但在 Excel 中不显示图像。

如果我在 calc 中打开文档并保存文档，然后在 Excel 中打开文档，我就可以在 Excel 中看到图像。

这是代码。

  public static byte[] CreateExcel(CampaignViewModel viewModel, string fileName)
    {

        byte[] output;

        using (FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(@"\Data\templates\NPOITemplate.xls"), FileMode.Open, FileAccess.ReadWrite))
        {

            var templateWorkbook = new HSSFWorkbook(fs, true);
            var sheet = templateWorkbook.GetSheet("Sheet1");
            var patriarch = sheet.CreateDrawingPatriarch();
            var leftFieldHeaders = CsvHelper.GetMatrixAllLeftFields();
            var productHeaders = CsvHelper.GetMatrixProducts(viewModel.ProductCampaigns);
            var totalCols = leftFieldHeaders.Count + productHeaders.Count;
            var colWidth = 5000;
            for (int i = 0; i < totalCols; i++)
            {
                sheet.SetColumnWidth(i, colWidth);
            }

            var imageRow = sheet.CreateRow(0);
            imageRow.Height = 2000;
            var imageCellCount = 0;
            foreach (var header in leftFieldHeaders)
            {
                imageRow.CreateCell(imageCellCount).SetCellValue("");
                imageCellCount++;
            }

            foreach (var product in viewModel.ProductCampaigns)
            {
                try
                {
                    var anchor = new HSSFClientAnchor(0, 0, 0, 0, imageCellCount, 0, imageCellCount, 0);
                    anchor.AnchorType = 2;
                    var path = HttpContext.Current.Server.MapPath(product.Product.ImageThumbUrl);
                    var picture = patriarch.CreatePicture(anchor, LoadImage(@path, templateWorkbook));

                    picture.Resize();
                    picture.LineStyle = HSSFPicture.LINESTYLE_SOLID;
                }
                catch (Exception)
                {

                }
                imageCellCount++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                templateWorkbook.Write(ms);
                output = ms.ToArray();
            }
        }
        return output;
    }

    public static int LoadImage(string path, HSSFWorkbook wb)
    {
        try
        {
            var file = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
            var buffer = new byte[file.Length];
            file.Read(buffer, 0, (int)file.Length);
            return wb.AddPicture(buffer, PictureType.JPEG);
        }
        catch (Exception)
        {
            return 0;
        }

    }

Answer 1

我已经以迂回的方式解决了上述问题。事实证明，我实际上并不需要使用模板，只需从头开始创建 xls。这向文件中添加了更多元数据，我怀疑这是问题所在

public static byte[] CreateExcel2(CampaignViewModel viewModel, ICollection<DeliveryPoint> deliveryPoints, string fileName)
{
    FileContentResult fileContentResult;
    byte[] output;
    var matrixCampaignLines = viewModel.MatrixCampaignLines;

    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
    dsi.Company = "NPOI Team";
    hssfworkbook.DocumentSummaryInformation = dsi;
    ////create a entry of SummaryInformation
    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
    si.Subject = "NPOI SDK Example";
    hssfworkbook.SummaryInformation = si;
    var sheet = hssfworkbook.CreateSheet("Sheet1");

    var patriarch = sheet.CreateDrawingPatriarch();
    var leftFieldHeaders = (viewModel.Campaign.EnableNameOnCampaign) ? CsvHelper.GetMatrixAllLeftFields() : CsvHelper.GetMatrixAllLeftFieldsWithoutName();
    var productHeaders = CsvHelper.GetMatrixProducts(viewModel.ProductCampaigns);
    var totalCols = leftFieldHeaders.Count + productHeaders.Count;
    var colWidth = 5000;
    for (int i = 0; i < totalCols; i++)
    {
        sheet.SetColumnWidth(i, colWidth);
    }

    var imageRow = sheet.CreateRow(0);
    imageRow.Height = 2000;
    var imageCellCount = 0;
    foreach (var header in leftFieldHeaders)
    {
        imageRow.CreateCell(imageCellCount).SetCellValue("");
        imageCellCount++;
    }

    foreach (var product in viewModel.ProductCampaigns)
    {
        try
        {
            var anchor = new HSSFClientAnchor(0, 0, 0, 0, imageCellCount, 0, imageCellCount, 0);
            anchor.AnchorType = 2;
            var path = HttpContext.Current.Server.MapPath(product.Product.ImageThumbUrl);
            var picture = patriarch.CreatePicture(anchor, LoadImage(@path, hssfworkbook));

            picture.Resize();//Comment this line if your code crashes.

            picture.LineStyle = HSSFPicture.LINESTYLE_SOLID; might not
        }
        catch (Exception)
        {

        }
        imageCellCount++;
    }

    using (MemoryStream ms = new MemoryStream())
    {
        hssfworkbook.Write(ms);
        output = ms.ToArray();
    }

    return output;
}

public static int LoadImage(string path, HSSFWorkbook wb)
{
    try
    {
        var file = new FileStream(path, FileMode.Open, FileAccess.Read);
        var buffer = new byte[file.Length];
        file.Read(buffer, 0, (int)file.Length);
        return wb.AddPicture(buffer, PictureType.JPEG);
    }
    catch (Exception)
    {
        return 0;
    }
}

Answer 2

我现在就明白了。

我正在使用

try
            {

                ISheet sheet = templateWorkbook.GetSheet(sheetName);

                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                //create the anchor
                HSSFClientAnchor anchor;
                anchor = new HSSFClientAnchor(0, 0, 255, 255, 
                            start.Col, start.Row, end.Col, end.Row);
                anchor.AnchorType = 2;
                patriarch.CreatePicture(anchor, 
                                            LoadImage(imagePath, templateWorkbook));
            }
            catch (IOException ioe)
            {

            }

LoadImage() 方法，该方法从服务器返回路径。

用这个吧。它运行良好。