在 EPPlus 上设置行高时的奇怪行为 [英] Weird behavior when setting a row's height on EPPlus

问题描述

我正在 MVC-5 C# 应用程序下使用 EEPlus 构建 Excel 文件.一切都按计划进行，直到我在一行上设置了高度(这样图像才能适合).

I am building an Excel file with EEPlus under MVC-5 C# application. Everything goes as planned until I set a height on a row (so an image can fit).

我加载图像并在第 20 列设置高度，如下所示:

I load de images and set the height on column 20, like so:

Image cfPhoto = null;
Bitmap cfBm = null;
ExcelPicture pictureCf = null;
var photoInitialColumn = 0;
i++;
completedFormPhotos.ForEach(delegate(CompletedFormPhoto cfP)
{
    cfPhoto = Image.FromFile(HostingEnvironment.MapPath("~/Content/Images/FormPhotos/" + cfP.Id + ".jpg"));
    cfBm = new Bitmap(cfPhoto, new Size(215, 170));
    pictureCf = worksheet.Drawings.AddPicture(cfP.Id.ToString(), cfBm);
    pictureCf.SetPosition(i+1, 0, photoInitialColumn, 10);
    worksheet.Cells[_alpha[photoInitialColumn] + (i + 3) + ':' + _alpha[photoInitialColumn + 1] + (i + 3)].Merge = true;
    worksheet.Cells[_alpha[photoInitialColumn] + (i + 3) + ':' + _alpha[photoInitialColumn + 1] + (i + 3)].Value = cfP.comment;
    worksheet.Cells[_alpha[photoInitialColumn] + (i + 3) + ':' + _alpha[photoInitialColumn + 1] + (i + 3)].Style.WrapText = true;
    photoInitialColumn += 2;
    //HERE I SET THE HEIGHT. At this point, i == 18 
    worksheet.Row(i+2).Height = 180;
});

但是，我在 Excel 文件(A1 单元格)的顶部有一个公司徽标，它也会调整大小(在高度上).定义如下:

But, I have a company logo at the top of the Excel file (A1 cell) which gets resized as well (on height). That is defined like this:

Image _keyLogo = Image.FromFile(HostingEnvironment.MapPath("~/Content/Images/key_logo.png"));
var pictureLogo = worksheet.Drawings.AddPicture("Logo Key Quimica", _keyLogo);
pictureLogo.SetPosition(0, 0, 0, 0);

由此产生:

任何帮助将不胜感激.

这里是有问题的 excel 文件.

Here is the excel file in question.

推荐答案

归结为图片标志的 EditAs 设置.默认情况下，它将设置为 OneCell 但将其设置为 TwoCell 我相信会解决您的问题.关于它的文档(查看 EPP 4.0.1)相当神秘，但它基本上说这个设置将告诉绘图保持其原始行/列位置和大小.不过，这些名称似乎有点违反直觉.

It comes down to the EditAs setting of the picture logo. By default it will be set to OneCell but setting it to TwoCell I believe will solve your problem. The documentation on it (looking at EPP 4.0.1) is rather cryptic but it basically says this setting will tell the drawing to maintain its original row/column position and size. The names seem a bit counter intuitive though.

我不得不猜测你的代码是什么样子(如果我猜错了，请告诉我)，我能够重现你遇到的问题，然后使用 EditAs 设置解决:

I had to guess what your code looks like (let me know if I got it wrong) and I was able to reproduce the problem you were having and then solve with the EditAs setting:

[TestMethod]
public void Image_Stretch_Test()
{
    //http://stackoverflow.com/questions/27873762/weird-behavior-when-setting-a-rows-height-on-epplus
    var existingFile = new FileInfo(@"c:	emp	emp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.Add("newsheet");

        var _keyLogo = Image.FromFile("C:/Users/Ernie/Desktop/key_logo.png");
        var pictureLogo = worksheet.Drawings.AddPicture("Logo Key Quimica", _keyLogo);
        pictureLogo.SetPosition(0, 0, 0, 0);
        pictureLogo.EditAs = eEditAs.TwoCell;  //REMOVE THIS TO SHOW THE STRETCH PROBLEM

        var cfPhoto = Image.FromFile("C:/Users/Ernie/Desktop/Main_Pic.png");
        var cfBm = new Bitmap(cfPhoto, new Size(215, 170));

        var pictureCf = worksheet.Drawings.AddPicture("Main_Pic", cfBm);
        pictureCf.SetPosition(10, 0, 0, 0);

        worksheet.Row(11).Height = 280;

        package.Save();
    }
}

修复它的另一件事是在调整行大小后添加徽标，但不确定这是否适合您尝试执行的操作.

The other thing that fix it was add the logo AFTER the row resize but not sure if that is practical to what you are trying to do.

这篇关于在 EPPlus 上设置行高时的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！