Accounting Format Combined with 6 Point Font Distorts Numbers in HTML - Regressive

Aspose.Cells Product Family
, ,
1

Hello,

Cells with a combination of the Accounting Format along with a small font (6 points or less) can cause the numeric values to become distorted when exported to HTML. This is regressive to Aspose Cells version 20.8 - which shows the values correctly when the workbook is exported to HTML.

This behavior can be seen in the latest Aspose Cells for Java version 21.3, the attached Arial_6pt_Font.xlsx file and the following Java code:

final String xlFile = [PATH] + "Arial_6pt_Font.xlsx";

Workbook wb = new Workbook(xlFile);

String html = xlFile.replace(".xlsx", "-v" + CellsHelper.getVersion() + ".html");
Files.deleteIfExists(Paths.get(html ));
wb.save(html );
System.out.println("Successfully created HTML file:  " + html);

Key Observations:

  1. Under Cells 20.3, the numeric values become distorted because of the 6 pt font size. Changing the font size to 7 pt or higher produces the expected results in HTML.
  2. Running the above code under Cells 21.3, should produce a file similar to the attached Arial_6pt_Font-v21.3.0.html file.
  3. Rendering the HTML output file in a Browser, shows the data values from “Table1” and “Table2” as distorted and different from how they appear in Excel.
  4. The 4 tables have the same data and all numeric values in the worksheet have the Accounting Number Format (as shown in the attached AccountingFormat.png screenshot). The only differences between the tables are:
    – Table1 has an Arial 6 pt Font, Word-wrap enabled.
    – Table2 has an Arial 6 pt Font and Word-wrap disabled.
    – Table3 has an Arial 7 pt Font and Word-wrap enabled (which displays correctly).
    – Table4 has an Arial 7 pt Font and Word-wrap disabled (which displays correctly).
  5. Running the same code under Aspose Cells for Java version 20.8, should produce a file similar to the attached Arial_6pt_Font-v20.8.0.html file.
  6. Rendering the HTML output file from Cells 20.8 in a Browser, displays all 4 tables correctly.

Environment Details:

  • Aspose Cells for Java 21.3 (for first run)
  • Aspose Cells for Java 20.8 (for second run)
  • Java version 1.8.0_211
  • Windows 10 OS (but also reproducible under Linux).

File description in the Arial_6pt_Font.zip (20.6 KB) attachment contains:

  • Arial_6pt_Font.xlsx: Excel workbook to be used by the code above.
  • AccountingFormat.png: Screen shot of the Accounting Number Format applied to the numeric values in the worksheet.
  • Arial_6pt_Font-v21.3.0.html: HTML output file produced from the code above under Aspose Cells 21.3.
  • Arial_6pt_Font-v20.8.0.html: HTML output file produced from the code above under Aspose Cells 20.8.

Thank you!

2

@oraspose,

Please notice, I am able to reproduce the issue as you mentioned using your sample code and template file. I found accounting format combined with 6 point Font distorts numbers in HTML file format. By the way, when I tried to convert your template file to HTML in MS Excel manually, it also distorts the numbers a bit, so latest versions work the same way. Anyways, I have logged a ticket with an id “CELLSJAVA-43428” for your issue. We will look into it soon.

Once we have an update on it or we figure it out, we will let you know.

3

Thank you Amjad.

We want to provide additional details related to this issue:

  1. After additional investigation, it appears that the font used is not really a factor.
  2. The crux of the issue seems to be a combination of the Accounting Number Format - along with the use of Word-wrap. When these formats are combined, the numeric values sometimes wrap around the cell when exported to HTML.

We agree that exporting the original workbook provided (Arial_6pt_Font.xlsx) as HTML via Excel produces the same output as Aspose Cells. However, there are other scenarios where the HTML conversion differs significantly.

If you use the updated workbook AccountingFormat.xlsx attached with in reply and use the same code above (after updating the file name) - you should see different results produced by Aspose Cells versus Excel’s save as HTML feature. Specifically, there are two new tables where:

  • The fonts used are Calibri 11 points and Times New Roman 11 points,
  • Both tables apply the word-wrap format to the numeric cells, and
  • Both tables apply a 1 point Right Indent to the numeric cells.

When converted to HTML by Cells (version 21.3), the numeric values wrap within the cells, But when exported to HTML by Excel, the same cells do not wrap (as expected).

File descriptions in the AccountingFormat.zip (15.8 KB) attachment:

  • AccountingFormat.xlsx: Same workbook as prior upload (Arial_6pt_Font.xlsx), but with two new tables.
  • AccountingFormat-v21.3.0.html: HTML file generated by Aspose Cells version 21.3 on our environment.
  • AccountingFormat-Excel.htm HTML file generated by Excel from the Workbook.

Thank you once again!

4

@oraspose,

Thanks for sharing your findings.

We have recorded your providing details with sample files with your issue into our database. It may help us to evaluate and resolve the issue. We will try to figure your issue soon.

Once we have new updates or fix, we will you know here.

5

@oraspose,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

6

Thank you for the update Amjad.

Could you tell us if you think the fix will go into the the Aspose Cells for Java version 21.4 ?
We noticed that Aspose.Cells for .NET 21.4 has been released and assume that the Java version will shortly follow.

7

@oraspose,
Yes, this fix is part of our regular release Aspose.Cells for Java 21.4.

8

The issues you have found earlier (filed as CELLSJAVA-43428) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

9

Thank you for providing the fix. We took a look at the HTML output generated under Aspose Cells for Java version 21.4, but it is not what we were expecting.

The HTML produced under Aspose Cells for Java version 21.3, matches the Worksheet better than the HTML generated under version 21.4. The main purpose of the Accounting number format is to add spaces between the currency symbol “$” and the first digit - such that the currency symbol appears left-aligned, while the digits are right-aligned. The HTML under version 21.4 no longer produces accomplishes this (see attached file AccountingFormat-v21.4.0.html).

In essence, we raised this issue with the hopes that the algorithm that generates the HTML for cells containing the Accounting number format would be more precise.

For example, under Cells version 21.3, the HTML pertaining to cell C5 from the attached Workbook AccountingFormat.xlsx is:

<td class='x31' align='right' x:num="4.57378924E8" style='text-align:right;'>&nbsp;$
<span style='mso-spacerun:yes;'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</span>457,378,924&nbsp;</td>

Note that there are 18 &nbsp; characters between the ‘$’ symbol and the first digit ‘4’. However, because the cell has word-wrap turned on (in the Workbook), the numeric value wraps within the <td> (which we do not want).

One solution would be to reduce the number of &nbsp when a number format that uses the codes “* ” is detected. Of course, this needs to take into account the current font size and the width of all the characters within the cell.

Continuing with the example from cell C5, if we reduce the number of &nbsp' characters from 18 to 10, the word-wrap does not affect the row height. Under this solution, the new HTML would be:

<td class='x31' align='right' x:num="4.57378924E8" style='text-align:right;'>&nbsp;$
<span style='mso-spacerun:yes;'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>
 457,378,924&nbsp;</td>

Another solution would be to use an <span> tag with an “inline-block” style and a calculated width, using the following pattern:

<td ...>&nbsp;$<span style="width:[WIDTH]pt; display:inline-block;">&nbsp;</span>[NUMBER]&nbsp;</td>

Where:

  • [WIDTH] is the calculated width for the inline-block that determines the space between the “$” symbol and the first digit.
  • [NUMBER] is the formatted number to be displayed on the HTML page.

For example, using this alternate solution, the HTML for cell C5 would be:

 <td class='x31' align='right' x:num="4.57378924E8" style='text-align:right;'>&nbsp;$
 <span style="width:20pt; display:inline-block;">&nbsp;</span>457,378,924&nbsp;</td>

File description in the FixedAccountingFormats.zip (12.3 KB) attachment contains:

  • AccountingFormat-v21.4.0.html: HTML file generated from Aspose Cells for Java version 21.4 without any edits.
  • AccountingFormat-v21.3.8-fixed-space.html: HTML file generated from Aspose Cells for Java version 21.3.8 - which is manually edited with the 1st solution.
  • AccountingFormat-v21.4.0-fixed-span.html: HTML file generated from Aspose Cells for Java version 21.4.0 - which is manually edited with the 2nd solution.

We realize CELLSJAVA-43428 is now marked as “Resolved”, but please consider revisiting this issue by bringing back an enhanced solution that builds on the HTML output from Cells version 21.3.

Thank you!

10

@oraspose,
We have noted your concerns and will share our feedback after detailed analysis.

11

@oraspose,

Sorry for the delay.

To evaluate your issue accurately and for better organization, we logged a separate ticket with an id “CELLSJAVA-43844” for your issue/requirements. We will evaluate your demand regarding Accounting numbers formatting. We will also analyze your suggested/proposed solutions and algorithms in details.

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

12

@oraspose

Please try the latest fix v21.10.3.
aspose-cells-21.10.3-java.zip (7.4 MB)

We reduce the number of &nbsp from 18 to 17. The output html looks similar as opening the source file in Microsoft Excel. Attached a screenshot of the output html: Screenshot_in_html.png (48.1 KB)

Let us know your feedback.

13

The issues you have found earlier (filed as CELLSJAVA-43844) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi