Autofit or Autofitter options - do not have a min height specification

Aspose.Cells Product Family
1

Hi,

I tried this with aspose 20.6.

I am trying to autofit a excel sheet which has merged cells in one sheet and unmerged regular cells in another sheet.

I do specify fixed column widths and row heights.
column widths are depending upon the column - like in attached excel - “Name” column is 20

But, row height in both sheets is always 12 for each row.

Autofitting is happening, but its having some mixed behavior which I have some concerns on.

I use Tahoma size 8 throughout my excel.

PROBLEM 1:
If I autofit it (whether the value is bolded or unbolded -2 the row height comes to 10.5 always when the values can fit in one line - however at times even when it fits in one line, it increases the height slightly. I have attached a sample file. Please see the “Sample Data” sheet - row 8 - “Rhode Island Springs”).

PLEASE NOTE: my column widths you see are to remain fixed and so I fix it using the setColumnWidth methods of aspose - THEY ARE FIXED BY NEED . So, “Name” column in “Sample Data” sheet should be 20 only. It is not allowed to change based on project need.

PROBLEM 2:
For merged cells I use the following code:
AutoFitterOptions options = new AutoFitterOptions();

  •                options.setAutoFitMergedCells(true);*

  •                try *

  •                {*

  •                    worksheet.autoFitRows(options);*

  •                } *

  •                catch (Exception ex) *

  •                {*

  •                     getLogger().error("Autofit rows error : "+ex);*

  •                }*

If you see in attached sample file - sheet “Metadata” - Row 1 - containing value - “Sample Data” value - it gets 10.50 height - I expect this to be normal because when I use regular aspose autofit (not Autofitter) in “Sample Data” sheet I get 10.5.

But, the subsequent row “Row 2” - containing value “Sample Stream” gets row height 11.25. There are a merged cells on that entire row. So, it should not be an issue that it expanded slightly.
However,if I take to excel select in excel the entire content and autofit - the 11.25 becomes 10.5. This is what confuses me. Aspose gives it 11.25 for some rows and not all and when I autofit the excel in MS Excel, it further autofits. Its as if the autofit done in aspose is not done fully. Aspose does some autofitting but it can be further autofitted. Its as if like that.

So, my second question is that if I am autofitting on merged cells Row 1 gets 10.5 but Row 2 and subsequent rows are getting 11.25.

PLEASE NOTE: the individual columns are 3 units wide - you can see that in the sample excel. That should remain as is.

PROBLEM 3 (MAIN CONCERN):
I want to give a min height consistently. Meaning 10.5 when I autofit, I want that to be 12.
I want that the row height (in both sheets) should 12 always and if the text increases then it should expand only - but min height should be 12 always.
ANY WAY I CAN DO THIS?Aspose_Sample_Merged_cells.zip (7.7 KB)

2

@chetan.mishra,
We are looking into this issue and will share our feedback soon.

3

Hi Ahsan/Amjad,

Please refer to the sample code and the sample output. I had to separate it from my main application code but it has the same behavior.

aspose_autofitter.xlsx :

  • sheet “Metadata” - autofitter options applied still makes the row height as 12.75. You open that in microsoft excel and select all rows by clicking the left corner and then double clicking the first or second row (to autofit) - then you will see it will autofit further and then shrink it to 10.50. Same point as I highlighted above - Aspose autofits it but does not autofit fully. You can further autofit it.
  • sheet “Sample Data” - autofit applied makes the row height 10.50
    tahoma 8 is the default font and style applied across the workbook.

Not sure why the same bold row in both sheets gets a different row height when the default standardheight for “cells” in each sheet is set at 12.

Please see the attached code and output file.
aspose_autofitter.zip (8.9 KB)

My main concern is that - can I set a minimum height for autofit always? So, that it does not reduce to 10.5 based on Tahoma 8. That way, the issue I highlighted above will also solve my need. the base row height for anyone (with or without text) will be 12.0 and then it can increase based on the content.

1 Like
4

@chetan.mishra,
We have observed this issue and logged it in our database for further investigation and fix. You will be notified here once any update is ready to share.

This issue is logged as

CELLSJAVA-43237 - Improve autofitting and set min row height support
5

@chetan.mishra,
Please change default style before autofitting rows because the row style is automatic and it’s same as default style. When you autofit the first worksheet , the row style is Arial 10, so the autofit row height is 12.75 pt.

To verify this behavior, please add following line before calling worksheet.autoFitRows method in your sample code. As the default font is different, the default row height are different too like if the default font is Arial 10, the default row height is 12.75 points and if the default font is Tahoma 8 , the default row height is 10.5 points.

System.out.print(CSVWB.getDefaultStyle().getFont().getName());

6

Hi Ahsan,

I set the default style to Tahoma 8 at the start and it seems to work. However, my other ask still remains - can I specify a min height always before autofitting. We have max but we do not have a min. I hope that will be addressed as part of - “CELLSJAVA-43237”
Can you please tell as to when the fix for this might be expected.

Thanks
Chetan

7

@chetan.mishra,
We have noted this requirement and have created a separate ticket for analysis and implementation (if possible). We will write back here soon to share our feedback.

This ticket is logged as:
CELLSJAVA-43244-Provision to set minimum row height in AutoFitterOptions

8

@chetan.mishra,

Please call Cells.StandardHeight to set the default row height of the worksheet before autofitting rows, it should work for your needs.

9

HI Amjad,

You can please see the sample code I had attached. It does not work. you set the standardheight and then autofit, then it shrinks down below the standardheight set.

My requirement is that I need a min height fixed but still expect things to grow based on if there is more data but should not shrink.

Thanks and Regards
Chetan

10

@chetan.mishra,
We have tested the scenario and observed the behavior. These comments are logged with the ticket and we will provide our feedback after further analysis.