Time format hh:mm:ss not triggered automatically in Java

Aspose.Cells Product Family
1

Hi,

In excel, the format hh:mm:ss is already set, but when I render the data using aspose APIs, this format doesn’t get applied to the data. I still get the data in its original format which is hh:mm:ss.000. When I edit that data manually in excel, then that format gets applied. Where is the problem? Is there any way to trigger it?

2

Hi Neha,


Thank you for contacting Aspose support.

As you have mentioned that you need to manually edit the cell/format to see the correct format in Excel, that mean the problem is in the input spreadsheet. Please note, Aspose.Cells APIs tend to render the contents (in PDF & image formats) as they are being displayed in Print Preview of Excel application.

That said, it would be appropriate that you should share your input spreadsheet, output PDF and the locale of the machine where you are converting the spreadsheet to PDF format with Aspose.Cells for Java APIs.
3

Hi,

I am never converting excel sheet to PDF. The problem comes in excel only. Let me again explain -
I am programmatically setting the format pattern with this line in my spreadsheet for some specific columns

Style style = col.getStyle();
style.setCustom(“hh:mm:ss”);
col.applyStyle(style, flag);

Then I am rendering the data (which is in hh:mm:ss.000 format) into these columns using

designer.setDataSource(col_name, dataArray);

But, the above format(hh:mm:ss) does not get applied to these columns.
Is there any way we can trigger this after rendering the data, because I can see in excel that the format hh:mm:ss is set for those columns.

Thanks,
Neha

4

Hi Neha,


Thank you for sharing more details.

I have tried the complete scenario while using the following piece of code against the latest version of Aspose.Cells for Java 16.10.2, however, I am not able to see the said problem (please check attached resultant spreadsheet). I suggest you to please give a try to the latest version (attached) on your side as well. In case the problem persists, please share a standalone executable sample application for further investigation.

Java

WorkbookDesigner report = new WorkbookDesigner();
Worksheet w = report.getWorkbook().getWorksheets().get(0);
w.getCells().get(“A1”).putValue("&=$VariableArray");
Style style = w.getCells().getColumns().get(0).getStyle();
style.setCustom(“hh:mm:ss”);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
w.getCells().getColumns().get(0).applyStyle(style, flag);
report.setDataSource(“VariableArray”, new String[] { “11:12:00”, “11:59:00”, “1:12:00”, “1:1:22”, “00:00:00” });
report.process(false);
report.getWorkbook().save(dir + “output.xlsx”);
5

Hi,

Can you try with this datasource -
report.setDataSource(“VariableArray”, new String[] { “11:12:00.800”, “11:59:00.342”, “1:12:00.100”, “1:1:22.600”, “00:00:00.200” });

And tell me if milliseconds does not appear in excel? I want to make sure that it works before upgrading to the newest version.

Thanks,
Neha

6

Hi,


Thanks for providing further details.

Could you try to use “numeric” parameter when defining Smart Markers in the template file, it would work fine for your needs. I have tested your scenario/ case with the following updated code and it works fine:
e.g
Sample code:

WorkbookDesigner report = new WorkbookDesigner();

Worksheet w = report.getWorkbook().getWorksheets().get(0);

w.getCells().get(“A1”).putValue("&=$VariableArray(numeric)");

Style style = w.getCells().getColumns().get(0).getStyle();

style.setCustom(“hh:mm:ss”);

StyleFlag flag = new StyleFlag();

flag.setNumberFormat(true);

w.getCells().getColumns().get(0).applyStyle(style, flag);

report.setDataSource(“VariableArray”, new String[] { “11:12:00.800”, “11:59:00.342”, “1:12:00.100”, “1:1:22.600”, “00:00:00.200” });

report.process(false);

report.getWorkbook().save(“out1.xlsx”);
Hope, this helps a bit.

Thank you.
7

Hi,

It doesn’t work for me. Is there any other solution?

Thanks,
Neha Singh

8

Hi Neha,


The solution shared by Amjad in previous response works correctly against the latest version of Aspose.Cells for Java 16.10.6 (attached). You can review the resultant spreadsheet before giving the latest version a try on your end.