在EPPlus中自动调整合并单元格的行高 [英] Autofit Row Height of Merged Cell in EPPlus

问题描述

我使用的是EPPlus和C＃，并尝试自动调整行的高度/使其适合行高，以适应用文本换行显示合并单元格所有内容所需的高度。但是，无论我尝试什么，文本始终会被截断。由于我要在各种工作表上使用各种文本大小来重复此过程，因此，我不想对行高进行硬编码（除非强制为行设置最小高度）。如果可能的话，我想在EPPlus / C＃中执行此操作。

将单元格A2：E2合并并且WrapText = true：

带有文本截断的单元格

这是所需单元格高度的外观

，除非我读错了它，否则似乎无法直接回答我的问题。

解决方案

以下是可重用方法中的解决方案。传入文本值，单元格使用的字体，合并的列的总和，然后返回行高。

方法的使用

  eppWorksheet.Row（2）.Height = MeasureTextHeight（cell.Value，cell.Style.Font，[输入列宽AE的总和]））; 
  

可重用方法

  public double MeasureTextHeight（字符串文本，ExcelFont字体，双倍宽度）
 {
 if（text.IsNullOrEmpty（））返回0.0; 
 var位图= _位图?? （_bitmap =新的Bitmap（1，1））; 
 var graphics = _graphics ?? （_graphics = Graphics.FromImage（bitmap））; 
 
 var pixelWidth = Convert.ToInt32（width * 7）; //每个excel列宽
 7个像素var fontSize = font.Size * 1.01f; 
 var drawingFont =新的Font（font.Name，fontSize）; 
 var size = graphics.MeasureString（text，drawingFont，pixelWidth，new StringFormat {FormatFlags = StringFormatFlags.MeasureTrailingSpaces}）; 
 
 // 72 DPI和每英寸96点。 Excel高度，以点为单位，每个Excel要求的最大值为409。 
 return Math.Min（Convert.ToDouble（size.Height）* 72/96，409）; 
} 
  

I'm using EPPlus and C# and trying to autosize/autofit the height of a row to accommodate the height needed to show all of the contents of a merged cell with text wrapping. However no matter what I try the text always truncates. Since I'm repeating this process with various text sizes on various worksheets, I don't want to hard code the row height (except to enforce a minimum height for the row). If possible I'd like to do this within EPPlus/C#.

With the cells A2:E2 merged and WrapText = true:

Cell with Text Truncated

Here's what it should look like with desired Cell Height

Here's my relevant and short C# code

Int32 intToCol;
intToCol = 5;
eppWorksheet.Cells[2, 1, 2, intToCol].Merge = true;
eppWorksheet.Cells[2, 1].Style.WrapText = true; 
//Check if at the minimum height. If not, resize the row
if (eppWorksheet.Row(2).Height < 35.25)
{
    eppWorksheet.Row(2).Height = 35.25;
}

I've looked at Autofit rows in EPPlus and it didn't seem to directly answer my question unless I'm reading it wrong.

解决方案

Here is the solution in a reusable method. Pass in the text value, font used for the cell, summed width of the columns merged, and receive back the row height. Set the row height with the result.

Use of Method

eppWorksheet.Row(2).Height = MeasureTextHeight(cell.Value, cell.Style.Font, [enter the SUM of column widths A-E]);

Reuseable Method

    public double MeasureTextHeight(string text, ExcelFont font, double width)
    {
        if (text.IsNullOrEmpty()) return 0.0;
        var bitmap = _bitmap ?? (_bitmap = new Bitmap(1, 1));
        var graphics = _graphics ?? (_graphics = Graphics.FromImage(bitmap));

        var pixelWidth = Convert.ToInt32(width * 7);  //7 pixels per excel column width
        var fontSize = font.Size * 1.01f;
        var drawingFont = new Font(font.Name, fontSize);
        var size = graphics.MeasureString(text, drawingFont, pixelWidth, new StringFormat { FormatFlags = StringFormatFlags.MeasureTrailingSpaces });

        //72 DPI and 96 points per inch.  Excel height in points with max of 409 per Excel requirements.
        return Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409);
    }

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