AutoFitRows and StandardHeight

Aspose.Cells Product Family
,
1

I’m using Cells 21.2 to create a spreadsheet with many merged cells. In the example, I have a merged B2-B4 and placed a value of “Row 1\nRow 2\nRow 3\nRow 4” in the B2. A second merge of C8-C12 has a value of “Test”. I have set StandardHeight = 5. When AutoFitRows is called, B2 expands to 3 rows of 14.25, but the 4 rows of text do not fit. Row 8 expands to 14.25 and leaves Row 9-12 at 5.

The expected result is for Rows 2-4 to expand so that “Row 1\nRow 2\nRow 3\nRow 4” is visible, and Rows 8-12 should remain at StandardHeight.

I have tried several combinations of AutoFitterOptions without success. If AutoFit can’t do this, is there a call to see if wrapped text exceeds the height of the cell?

Code and Excel results attached.

Please advise. Thanks.

    static void Main(string[] args)
        {
            string dataDir = "C:\\AsposeTest\\";

            // Instantiate a Workbook object that represents Excel file.
            Workbook wb = new Workbook();

            // When you create a new workbook, a default "Sheet1" is added to the workbook.
            Worksheet sheet = wb.Worksheets[0];

            sheet.Cells.StandardHeight = 5;

            string cellData;

            cellData = (string)"Row 1\nRow 2\nRow 3\nRow 4";

            Cell cell = sheet.Cells[1, 1];

            sheet.Cells.Merge(1, 1, 3, 1);

            Style style = cell.GetStyle();
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
            style.Borders[BorderType.TopBorder].Color = Color.Black;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
            style.Borders[BorderType.BottomBorder].Color = Color.Black;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
            style.Borders[BorderType.LeftBorder].Color = Color.Red;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
            style.Borders[BorderType.RightBorder].Color = Color.Black;
            style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center;
            style.IsTextWrapped = true;
            // Apply the border styles to the cell

            if (cell.IsMerged == true)
            { cell.GetMergedRange().SetStyle(style); }
            else { cell.SetStyle(style); }


            cell.HtmlString = cellData;

            sheet.Cells.Merge(7, 2, 5, 1);
            sheet.Cells[7, 2].PutValue("Test");

            AutoFitterOptions options = new AutoFitterOptions();
            //options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
            options.AutoFitMergedCellsType = AutoFitMergedCellsType.None;
            //options.AutoFitWrappedTextType = AutoFitWrappedTextType.Paragraph;

            sheet.AutoFitRows(options);

            // Save the Excel file.
            wb.Save(dataDir + "AutoFitRow_Test.xlsx", SaveFormat.Xlsx);

        }

AsposeTest.zip (14.1 KB)

2

@bstuba,
We are working on this issue however in the meanwhile you may please create this scenario in MS Excel manually and try to achieve the same result using MS Excel only. Once the desired output is created, share the exact steps performed in MS Excel along with the output file for our analysis.

3

Column B example
I am unable to use Auto Row Height in the Col B example to achieve the desired result. Unless there is a call to determine if a cell’s contents exceed the cell’s boundaries, I would have to place data in a cell and then determine the row height, and use that as a basis for the height of the resulting merge.

Column C example

  1. Ctrl-A - Row height = 5
  2. Enter “Test” in C8
  3. Merge C8-C12

Rows 8-12 remain @ 5.

4

@bstuba,
We need to analyze this issue more therefore we have logged it in our database. We will write back here once any update is ready for sharing.

This issue is logged as:
CELLSNET-47876 - Autofit rows and standard height not working properly for merged cells

5

@bstuba,

We evaluated your issue further. We can fix the issue of B2-B4, but we cannot remain 5 for the row 8 because row height must match the default font “Arial 10”. And, we can consider whether changing standard height as 12.75 when calling Worksheet.AutoFitRows().

6

@bstuba
Please try the latest fix 21.2.5 with the following codes:
AutoFitterOptions options = new AutoFitterOptions();
// options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
options.AutoFitWrappedTextType = AutoFitWrappedTextType.Paragraph;
sheet.AutoFitRows(options);
Aspose.Cells21.2.5 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.2.5 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.2.5 For .NetStandard20.Zip (5.5 MB)

7

The issues you have found earlier (filed as CELLSNET-47876) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao