Cells do not autofit as expected on merged cells

Aspose.Cells Product Family
1

Hi guys,

When I add a lot of text to some merged cells and then try to autofit the cells, the text is not totally visible.
This is the code I’m using:

final Workbook workbook = new Workbook(BASE_PATH + “TEST.xlsx”);
final Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getCells().get(0, 0).putValue(
“Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt” +
" ut labore et dolore magna aliqua. Augue interdum velit euismod in pellentesque. Interdum" +
" posuere lorem ipsum dolor. Viverra accumsan in nisl nisi scelerisque eu. Sit amet nulla" +
" facilisi morbi. Pretium aenean pharetra mag na ac placerat vestibulum. At quis risus sed" +
" vulputate odio. Vulputate sapien nec sagittis aliquam malesuada bibendum arcu. Tristique" +
" magna sit amet purus gravida quis blandit. Ullamcorper eget nulla facilisi etiam dignissim." +
" Id venenatis a condimentum vitae sapien pellentesque. Convallis aenean et tortor at risus." +
" Varius vel pharetra vel turpis. Diam sollicitudin tempor id eu nisl nunc. Arcu non odio euismod" +
" lacinia a t. Arcu bibendum at varius vel. Dignissim diam quis enim lobortis scelerisque. " +
“Diam ut venenatis tellus in metus. Vitae sapien pellentesque habitant morbi. Volutpat sed” +
" cras ornare arcu dui. Ut ornare lectus sit amet. Egestas egestas fringilla phasellus " +
“faucibus scelerisque. Aliquam sem et tortor consequat id porta nibh venenatis cras. " +
“Aliquam nulla facilisi cras fermentum odio. Morbi tincidunt ornare massa eget egestas purus” +
" viverra accumsan. Lect us quam id leo in vitae. Eu mi bibendum neque egestas congue quisque.” +
" Parturient montes nascetur ridiculus mus. Lacus laoreet non curabitur gravida arcu ac tortor." +
" Orci nulla pellentesque dignissim enim sit amet venenatis urna. Aliquam eleifend mi in nulla." +
" Sagittis vitae et leo duis ut diam quam. Nisl condimentum id venenatis a condimentum vitae sapien." +
" Cras ornare arcu dui vivamus arcu felis bibe ndum ut. Augue neque gravida in fermentum et" +
" sollicitudin ac. Sagittis nisl rhoncus mattis rhoncus. Viva mus arcu felis bibendum ut." +
" Dolor morbi non arcu risus quis varius quam quisque. Habitasse platea dictu mst quisque" +
" sagittis purus sit amet. Neque ornare aenean euismod elementum nisi quis eleifend. Dui" +
" sapien eget mi proin. Pretium lectus quam id leo in vitae turpis massa. Tristique" +
" senectus et netus et malesuada fames ac turpis egestas. Hendrerit dolor magna eget est " +
“lorem ipsum dolor sit amet. Quam pellentesque nec nam aliquam sem et tortor. Pellentesque” +
" elit eget gravida cum sociis.");
final AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.setAutoFitMergedCells(true);
options.setIgnoreHidden(true);
worksheet.autoFitRows(options);
workbook.save(BASE_PATH + “result.xlsx”);

What happens is that only the first row is autofitted to the text (increasing in height) and the other rows do not change.
I’m using Aspose cells Java 19.2 but I also tested this code using 20.7.

Is there a way to get the expected result?

Attached the TEST.xlsx and result.xlsx files.
Files.zip (16.8 KB)

Best regards,
Hugo Freixo

2

@Hugo_Freixo,

Please try our latest version/fix: Aspose.Cells for Java v20.8.3 (attached)
aspose-cells-20.8.3-java.zip (7.2 MB)

I have tested using Aspose.Cells for Java v20.8.3 and it works fine. See the output file which is generated using your sample code and template file.
files1.zip (9.0 KB)

3

Hi @Amjad_Sahi,

I tested using the Aspose cells 20.8.3 dependency and the result was similar to the file you sent.
There’re two things that still concern me:

  1. The function setAutoFitMergedCells(boolean) on the AutoFitterOptions class is marked as deprecated. What function should be used in this scenario?
  2. The resulting file has a lot of empty space on the first cells, which means the cells do not autofit to the text.

Best regards,
Hugo Freixo

4

@Hugo_Freixo,

  1. Yes, the function setAutoFitMergedCells(boolean) on the AutoFitterOptions class is marked as deprecated and will be removed (from the APIs list) after sometime. Please see/use the following sample code using the newer APIs instead:
    e.g
    Sample code:

    …
    // Set auto-fit for merged cells
    options.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE);
    options.setAutoFitWrappedTextType(AutoFitWrappedTextType.DEFAULT);
    options.setIgnoreHidden(true);
    worksheet.autoFitRows(options);
    …

  2. Yes, I noticed the leading empty space but atleast Aspose.Cells could auto-fit the whole long text in merged area. I tried in MS Excel manually and Ms Excel could not do it (you may confirm this).

5

Hi @Amjad_Sahi,

Another thing I notice is that in your most recent versions the class HTMLLoadOptions was removed.
What should I use now? How do I write this code with the new classes and methods?

final HTMLLoadOptions opts = new HTMLLoadOptions();
opts.setAutoFitColsAndRows(true);
opts.setDeleteRedundantSpaces(true);

Best regards,
Hugo Freixo

6

@Hugo_Freixo,
There is difference of small and capital letters so you can write it as:

final com.aspose.cells.HtmlLoadOptions opts = new com.aspose.cells.HtmlLoadOptions();
opts.setAutoFitColsAndRows(true);
opts.setDeleteRedundantSpaces(true);