Hello,
Some cells of the Numeric Format can cause the numeric values to be printed as series of hashtags (######) when exported to HTML. This is regressive to Aspose Cells version 21.3 - 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 23.7, the attached LocaleFormatting_NumberFormats.xlsx file and the following Java code:
final String xlFile = [PATH] + “LocaleFormatting_NumberFormats.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:
Cells in columns C and D have width 14.4
Cells in column F have width 13.7
All numbers use the same default font “Times New Roman” 12pt
Environment Details:
Aspose Cells for Java 23.7.2 (for first run)
Aspose Cells for Java 21.3 (for second run)
Java version 1.8.0_371b11
Windows 10 OS (but also reproducible under Linux).
File description in the LocaleFormatting_NumberFormats.zip attachment contains:
LocaleFormatting_NumberFormats.xlsx: Excel workbook to be used by the code above.
LocaleFormatting_NumberFormats-v_23.7.2.html: HTML output file produced from the code above under Aspose Cells 23.7.2.
LocaleFormatting_NumberFormats-v_21.3.0.html: HTML output file produced from the code above under Aspose Cells 21.3.
Thank you!
LocaleFormatting_NumericFormat.7z (13.3 KB)
@oraspose,
After an initial testing, I am able to reproduce the issue as you mentioned by converting your template Excel file to HTML. I found contents of some cells are not fully displayed within the width of the column when rendered to HTML. By the way, when I open your Excel file into MS Excel manually I also found the values for formula cells (C25, D25 and F25) are shown as “##########”. Anyways, we have to evaluate your issue in details.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45538
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Hi Amjad,
Thanks for looking into this.
What version of Excel are you opening attached file with?
Mine is MS Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20698) 32-bit.
And generated HTML was produced properly under Cells version 21.3.0.
Thank you,
Yan
@oraspose
image.png (4.5 KB)
All display value of C25, D25 and F25 are “###…”
The width of column F is 86 px in my machine. Please check the width of F in your machine.
Mine is MS Excel for Microsoft 365 version 2306 (Build 16529.20182)
The column F width is 13.7 points on my machine. See attached screenshot:
Excel_columnF_width.png (205.0 KB)
.
@oraspose,
Thanks for the screenshot.
We have logged it with your existing ticket “CELLSJAVA-45538” into our database. We will look into it soon.
It seems the issue is more widespread.
Please take a look at the attached archive containing:
- Input Excel workbook (used last worksheet “DateFormats” to reproduce the issue);
- Screenshot of the difference between input and produced HTML output
- Generated html (used Aspose.Cells build 23.7.0 )
LocaleFormatting_DateFormats.7z (243.9 KB)
@oraspose
Can you share us your OS and Excel language setting ?
Html displays same as Excel in my machine.
@oraspose
The unit of 13.7 is characters,not points.
The width of column will change if default font or local display setting is changed.
As the default font is fixed, is your local dispaly setting 100%?
OS Name: Microsoft Windows 10 Pro version 10.0.19045 Build 19045
MS Office: Version 2208 Build 16.0.15601.20698 32-bit
Office and MS Excel language setting - “English (United States)”
Local display setting - Scale and layout of 150% (Recommended).
Note that if I simply open attached Excel template and save as Web page (*.htm, *.html), html created properly.
Hope this helps.
@oraspose,
Thanks for providing details about OS, MS Office and MS Excel language settings of your environment. We will evaluate and get back to you.
Is there a way to revert the HTML output generation to the way it was under version 21.3 (and a few versions after that - up to 21.10 or so) ?
@oraspose,
Please spare us a little time to thoroughly evaluate your issue (logged as “CELLSJAVA-45538”) based on the details you have provided. Once we have an update, we will let you know.
@oraspose
We have made some optimizations for the 150% display ratio in next release 23.8,
Please try the code after 23.8 is released：
CellsHelper.setDPI(144);
Workbook workbook = new Workbook(dir + "CELLSJAVA45538.xlsx");
workbook.save(dir + "dest.html");
Thank you for the quick turnaround, will try this solution once 23.8 is released.
@oraspose,
You are welcome. We will keep you posted and notify you once the new version is published.
We ran the unit-tests using the same Excel template against new library Cells 23.8 and the issue still persists. Content of the “Total” cell still shows with hashtags.
Attached is produced HTML output.
LocaleFormatting_NumberFormats-v23.8.0.html.zip (3.1 KB)
@oraspose,
Thank you for providing the HTML output through the latest version. Could you please share the display settings (100%, 150%, or other) of the system where you performed the unit tests? Additionally, could you share the sample code that you used to generate the file? We will review it promptly.
Attached is the sample code that produced the output.
LocaleFormatting.zip (1.4 KB)
The display setting was set to 100% on Windows 10 OS.
Same output produced on Linux 7 system where the same test was running.
Please let me know if you need anything else.
Thanks,
Yan
@oraspose,
As suggested earlier, please try to set DPI value to “144” for better results. Also, set the DPI settings at the start in code and before using any APIs from Aspose.Cells:
e.g.
Sample code:
final String PATH = "f:\\files\\";
final String xlFile = PATH + "LocaleFormatting_NumberFormats.xlsx";
final String NAMED_RANGE = "NumberFormats!Data_Table"; // $B$2:$F$25
CellsHelper.setDPI(144);
//applyLicense("./Aspose.Cells.lic");
Workbook wb = new Workbook(xlFile);
String htmlFile = xlFile.replace(".xlsx", "-v" + CellsHelper.getVersion() + ".html");
// Initialize HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat.HTML);
// Try block to check for exceptions
try {
// Specify the HTML Saving Options
// IMPORTANT: Set the encoding to UTF8 so that non-ASCII characters in the range
// are generated properly.
options.setEncoding(Encoding.getUTF8());
// Note use HtmlCrossType.CROSS since DEFAULT can produce unwanted results in
// some scenarios
options.setHtmlCrossStringType(HtmlCrossType.CROSS);
options.setPresentationPreference(true); // supposedly creates a 'more beautiful presentation'
options.setExportHiddenWorksheet(false);
options.setExportActiveWorksheetOnly(true);
options.setExportImagesAsBase64(true); // avoids the temp folder
options.setCreateDirectory(false);
options.setExpImageToTempDir(false);
options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
// The following options reduce the size of the generated HTML (without
// impacting quality)
options.setExportFormula(false);
options.setExcludeUnusedStyles(true);
options.setExportComments(false);
options.setExportDocumentProperties(false);
options.setExportWorksheetProperties(false);
options.setExportBogusRowData(false);
options.setExportFrameScriptsAndProperties(false);
wb.save(htmlFile, options);
System.out.println("Successfully created HTML file: " + htmlFile);
} catch (IOException e) {
// Print and display the exceptions
System.out.println(e);
}
Please find attached the output HTML file for your reference.
LocaleFormatting_NumberFormats-v23.8.0.zip (3.1 KB)