Auto-fit rows having merged cells and hidden columns in the worksheet

Aspose.Cells Product Family
1

I have two columns merged in row. Column on the left is hidden. When I use AutoFitRows not all text is visible.

image.png (17.1 KB)

2

@AdamMada,

Thanks for the screenshot.

Could you try to auto-fit rows using AutoFitterOptions with respect to merged cells, see the sample code for your reference:
e.g
Sample code:

.........
 AutoFitterOptions options = new AutoFitterOptions();
            options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
            worksheet.AutoFitRows(options);
........

If you still find the issue, kindly do provide your template Excel file and sample code that you using, we will check it soon.

3

Thanks for the code, but I still find the issue. Below You find my sample code, thanks.

AsposeTest AutoFitRows.zip (604.9 KB)

4

@AdamMada,

Thanks for the template file and sample code.

I checked your file and actually it has all the data in the B2 cell (which is hidden), this cell is merged with C2 cell. C2 cell is empty. Now you may even perform the task (auto-fit row(s)) in Ms Excel manually and you will see it does not do anything either. In short, even MS Excel cannot do it properly. The cell should be visible and you need to perform the autofit operation on it. Anyways, to cope with it you got to manually extend the height of the cell to see all the data. Please note this is not an issue with the APIs as this task cannot be performed until you make the data cell(B2) visible. See the following sample code that works fine (it is a kind of manual way but I could not find any better way to cope with it in MS Excel either):
e.g
Sample code:

string filePath = "E:\\test2\\AsposeTest AutoFitRows\\SmartMarkerTest\\template.xlsx";
            Workbook workbook = new Workbook(filePath);
            var worksheet = workbook.Worksheets[0];
           
            AutoFitterOptions options = new AutoFitterOptions();
            options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
            worksheet.AutoFitRows(options);

            worksheet.Cells.SetRowHeight(1, 88);

            workbook.Save("e:\\test2\\out1.xlsx", SaveFormat.Xlsx);

Hope, this helps a bit.

5

Thank You for solution but I think I’ve found better. Instead of hide columns I set columns width to 0,05, and then invoke AutoFitRows and after that set columns hidden. You must also turn off “word wrap” for all cells with text in column which has width changed.

6

@AdamMada,
Good to know that your issue is sorted out. Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.