且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

在EPPlus中自动调整合并单元格的行高

更新时间:2023-12-04 13:14:34

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



方法的使用

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