DateTime value appear as ###### when column header is short

Aspose.Cells Product Family
1

Hi,

I have a DateTime field, that it’s header is “Created”.
I selected number format 22 for the value and when I open the Excel, all the values in the columns are displayed “######”.
I used the following code before I saved the workbook to a file:
worksheet.autoFitColumns();
worksheet.autoFitRows();

but I am still getting a column to narrow for the DateTime value.
Can you suggest a solution?
Thanks,
shirly

2

Hi,


Thanks for providing us some details.

Could you provide us template Excel file, we will check your issue soon.

Thank you.
3

Hi,

Thanks for the quick response.
I attached and example to the report I am creating.
Let me know if you need more info.
4

Hi,


Thanks for the sample file.

I could not see “######” for the DateTime columns, these are displayed fine. I think this is your expected file. Could you provide us your sample file that has DateTime values as “######”, you may also provide sample code (runnable) if you are creating the file from the scratch. We will try to test your scenario/ case using Worksheet.autoFitColumns() operation to reproduce the issue on our end. Also, kindly try our latest version v8.6.3.x, if you are not using it already.

Thank you.
5

hi Amjad,

Thanks for the quick reply.
when I am opening the sample file I sent you I see ‘#####’ in Created column.
6

Hi,


Well, I simply opened your sample file into Ms Excel 2007/2010, I could not spot any value shown as ‘#####’ for the Created column (cells) in the worksheet, see the screen shot attached here:
http://prntscr.com/9mr6yj

Thank you.
7

Hi Amjad,

Here is another file I created with 2 columns.
the ‘created’ is DateTime.
Here is the code I used to add the values for the cells:
Style style = cell.getStyle();
style.setNumber(22);
cell.setStyle(style);

LocalDateTime local = ((DateTimeValue) value).getLocalDateTime();
ZonedDateTime zdt = local.atZone(DateTimeUtils.utcZoneId);
Date output = Date.from(zdt.toInstant());
cell.setValue(output);

Could it be related that I am using office 2013?
I am currently working on cells version 8.4.1 
and we are in the middle of a process to purchase the latest version.
8

Hi,

Thanks for your posting and using Aspose.Cells.

You can either autofit the single column or all the columns using Worksheet.autoFitColumn() or Worksheet.autoFitColumns() method. But they are not working for your sample excel file. Please now set the column width manually using the Worksheet.getCells().setColumnWidth() method.

Please see the following sample code and its output excel file which I have attached for your reference.

Java

String filePath = “F:\Shak-Data-RW\Downloads\sample.xlsx”;

Workbook wb = new Workbook(filePath);

Worksheet ws = wb.getWorksheets().get(“Sheet1”);
ws.getCells().setColumnWidth(1, 20);

wb.save(filePath + “.out.xlsx”);