Column width wrongly calculated and serialized [OP-8221]

Aspose.Cells Product Family
1

Hello,
I noticed two particular behaviors that I would think wrong.

  1. Calculation of column widths changes according to the screen dpi: for example on my 96 dpi screen the StandardWidth and GetColumnWidth(0) gives 8.42… but when switching to 120dpi it gives 8.3333… for the same file.

  2. When executing a line of code that under the hood creates a Column instance, the Width of that column will be serialized into the saved workbook, but with a Math.Floor to 2 digits applied before. This obviously makes the widths different between saves and moreover they are saved inside the document according to the dpi. They should not be saved at all.

For now this bug is not prioritized, but in case we can rise it later.

I will attach an example. It must be run on different screen dpi to see the different effects.

Seems that using CellsHelper.DPI = 96; solves partially the problem (but I really don’t know what will happen when you develop a template on a 120dpi screen and try to manipulate it on a 96dpi screen with Aspose.Cells).

ConsoleApp1.zip (9.1 KB)

2

@andreaghirardello,

Thanks for the sample code and template file.

Well, it is not an issue with the APIs. If the DPI is changed, the default column’s width would be changed too in MS Excel. Aspose.Cells works as MS Excel does. We suggest you to specify the DPI at the start of your program (even if you are working in 120 dpi display settings) when reading the columns’ widths or other attributes:
e.g
Sample code:

.......
CellsHelper.DPI = 96;

Hope, this helps a bit.

3

I tested it with Excel 365 here but the result is different from the behavior of Aspose.Cells

The same document attached to the application here opened in Excel gives a default column width of 8.43 on 96dpi and 8.11 on 120dpi. In Aspose.Cells it gives 8.4285714285714288 on 96dpi (rounded 8.43 like Excel) but 8.3333333333333339 (rounded to 8.33) that is different from Excel.

So it behaves like Excel in the mean that it changes the default width, but the calculation behind is still wrong.

Moreover the point 2 is totally wrong, because Excel does not serialize column widths until you modify them. Instead Apose.Cells serializes them as soon as you directly call Worksheet.Cells.Columns[] or a pieace of code of Aspose calls it indirectly (like Cells.ApplyColumnStyle).

If we look also at the particular case when Excel serializes this information inside the Workbook they are recalculated according to the DPI (so for example, I tried to set a column width of 9 at 96dpi, and when checking it at 120dpi it was 8.89).

So I can accept that point 1 does not hold completely (but in this case we have point 3 that the sizes should be calculated exactly like Excel even for custom widths), but point 2 is still true.

The suggestion to use CellHelper=96 alleviates partially point 1, but does nothing for point 2 unfortunately and point 2 gets worse as soon as you begin to copy ranges between workbooks.

4

@andreaghirardello,

I did test the scenario/ case and you are right, MS Excel gives 8.11 where as Aspose.Cells gives 8.33… in 120 dpi settings. We will evaluate this issue and check the issue regarding column width’s serialization. We will get back to you soon.

5

@andreaghirardello,

Well, Aspose.Cells works good in the environments whose dpi settings (OS) is 96. For the environments where dpi setting is not 96, there will surely be some differences when comparing with Microsoft Excel. I am afraid, we have no other option but ask you to change/use the enviroments’ dpi settings to 96 (if possible).

And, regarding the issue, i.e.,“The width of the columns are serialized into the saved workbook by Aspose.Cells APIs”, I have logged a ticket with an id “CELLSNET-46498” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

6

@andreaghirardello,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46498”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Also note, if there is no change for a Column instance, we will remove this instance when exporting the file.

7

@andreaghirardello,

Please try our latest version/fix: Aspose.Cells for .NET v18.11.7:

Aspose.Cells18.11.7 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells18.11.7 For .Net4.0.Zip (4.7 MB)
Aspose.Cells18.11.7 For .NetStandard20.Zip (3.8 MB)

Your issue should be fixed in it.

Let us know your feedback.