在EPPlus中自动调整行 [英] Autofit rows in EPPlus

问题描述

我找不到使用EPPlus Excel库自动调整行高的方法。使用Excel Interop时，可以执行 sheet.Rows.AutoFit（）。我正在使用ILSpy查看界面，但到目前为止，我没有发现任何有用的信息。有什么解决方法，还是我错过了什么？

I cannot find a way to autofit row height using the EPPlus Excel library. When I used Excel Interop, I could do sheet.Rows.AutoFit(). I'm looking around the interface using ILSpy, but so far I didn't find anything useful. Is there some workaround, or am I missing something?

更新：第4行给我带来了问题：

UPDATE: Row 4 is giving me problems:

我尝试做sheet.Row（4）.CustomHeight = false，但是没有用...
我也尝试在加载单元格内容之前和之后设置属性，并且仍然相同。确实会将行高调整为其他值，但这不是一个好方法。也许EPPlus在测量琴弦高度时遇到问题？我知道当我弄乱了一些我需要测量字符串的旧版GDI +代码时，确实有很多问题……

I tried doing sheet.Row(4).CustomHeight = false, but it didn't work... I also tried setting the property before and after loading the cell content, and it's still the same. It does adjust the row height to a different value, but it's not a good one. Maybe EPPlus has issues with measuring string height? I know I did have lots of problems with that when I messed with some legacy GDI+ code in which I needed to measure strings...

推荐答案

实际上，如果查看行对象的属性 CustomHeight ，您会发现默认情况下将其设置为false。这意味着Excel在打开时将（应该）自动设置行的高度。如果要停止，则可以将其设置为 false 或手动设置行高，这将自动将其设置为 false 。

Actually, if you look at the property CustomHeight for the row object you will see that it is set to false by default. Which means that Excel will (should) automatically set the height of the row when opened. If you wanted to stop that you would either set it to false or set the row height manually which will automatically set it to false.

唯一的缺点是，如果您依靠该行的自动拟合功能，那么在构建时不知道该高度将是多少。自Excel以来的EPPlus，将在首次打开文件时决定。有点像，如果您使用 AutoFitColumn 函数，您将不知道列宽。

The only wrinkle is that you if you rely on the autofit for the row then you cannot know what that height is going to be at the time you build in EPPlus since Excel will decide that when it first opens the file. Kind of like you cannot know what the column width will be if you use the AutoFitColumn function.

属性的逻辑：

[TestMethod]
public void Row_Height_Test()
{
    //http://stackoverflow.com/questions/31496172/autofit-rows-in-epplus
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (int)),
        new DataColumn("Col2", typeof (int)),
        new DataColumn("Col3", typeof (int))
    });


    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        datatable.Rows.Add(row);
    }

    var existingFile2 = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package = new ExcelPackage(existingFile2))
    {
        //Add the data
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells.LoadFromDataTable(datatable, true);

        //CustomHeight is set to false by default on all rows and giving such a large font 
        //will cause it to autosize to a bigger height by Excel when first opened
        ws.Row(1).Style.Font.Size = 20;

        //Setting the height manually will automatically set CustomHeight to true
        //preventing excel from automatically setting the height
        ws.Row(2).Height = 30;
        ws.Row(2).Style.Font.Size = 20;

        //row 1 height will be around 26 when opened in Excel (but cannot know that now), 
        //row 2 height will be 30 as set above, 
        //row 3 height will be the DefaultHeight (usually 15) of the worksheet since it can fit the default font
        Console.WriteLine("{{{0} : {1}}}", ws.Row(1).Height, ws.Row(1).CustomHeight);
        Console.WriteLine("{{{0} : {1}}}", ws.Row(2).Height, ws.Row(2).CustomHeight);
        Console.WriteLine("{{{0} : {1}}}", ws.Row(3).Height, ws.Row(3).CustomHeight);

        //Save the file
        package.Save();
    }
}

这是控制台日志输出：

{15 : False}
{30 : True}
{15 : False}

这篇关于在EPPlus中自动调整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！