Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
445 views
in Technique[技术] by (71.8m points)

c# - Weird behavior when setting a row's height on EPPlus

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

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

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

Resulting on this:

Screenshot of the Excel File

Any help would be really appreciated.

Here is the excel file in question.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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:empemp.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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...