Issues with custom formats

Hello,


I have 2 issues when create cells with custom format.

1. Create cell with Date (let’s say Feb 01, 2012) and format “dd.MM.yyyy” and save it to ODS format. In OpenOffice Calc this cell formatted as “01022012” instead of “01.02.2012”. The problem exists with dot separator only, other work fine.

2. Excel doesn’t show format of cells (Format Cells -> Number) when save workbook with custom formats to XLSX/XLS formats.

There is code snippet to reproduce both problems:

private static void setCell(Cell cell, Object value, String format) {
if (format != null && format.length() > 0) {
Style cellStyle = cell.getStyle();
cellStyle.setCustom(format);
cell.setStyle(cellStyle);
}
cell.setValue(value);
}
public static void main(String[] args) {
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);

long time = new GregorianCalendar().getTime().getTime();
java.sql.Time timeSql = new java.sql.Time(time);
java.sql.Date dateSql = new java.sql.Date(time);
java.util.Date dateUtil = new java.util.Date(time);

Cells cells = sheet.getCells();

// Header
setCell(cells.get(“A1”), “Name”, null);
cells.merge(0, 1, 1, 2);
setCell(cells.get(“B1”), “Integer”, null);
cells.merge(0, 3, 1, 3);
setCell(cells.get(“D1”), “DateTime”, null);
cells.merge(0, 6, 1, 3);
setCell(cells.get(“G1”), “Double”, null);
cells.merge(0, 9, 1, 3);
setCell(cells.get(“J1”), “Date”, null);
cells.merge(0, 12, 1, 3);
setCell(cells.get(“M1”), “Time”, null);

// Row 1
setCell(cells.get(“A2”), “Row 1”, null);
cells.merge(1, 1, 1, 2);
setCell(cells.get(“B2”), 1, null);
cells.merge(1, 3, 1, 3);
setCell(cells.get(“D2”), dateUtil, “yyyy-MM-dd hh:mm:ss”);
cells.merge(1, 6, 1, 3);
setCell(cells.get(“G2”), Math.PI, ““MSRP $”#.00”);
cells.merge(1, 9, 1, 3);
setCell(cells.get(“J2”), dateSql, “dd.MM.yyyy”);
cells.merge(1, 12, 1, 3);
setCell(cells.get(“M2”), timeSql, “hh:mm:ss”);
try {
workbook.save(“cellstest.ods”, FileFormatType.ODS);
workbook.save(“cellstest.xlsx”, FileFormatType.XLSX);
} catch (Exception e) {
e.printStackTrace();
}

Regards, Jeff

Hi Jeff,


Could you try to set the appropriate local settings. The date format may depend on locale settings. Could you change your computer’s locale accordingly, then, you may see the date shown in Excel appropriately.
Also, kindly try to set locale for your workbook in your code accordingly, e.g

WorkbookSettings setting = workbook.getWorkbookSettings();
setting.setLocale(…);


Let us know if it works fine.

Thank you.

Hello Amjad,


It doesn’t work of course. As I wrote in the previous post the problem exists in ODS format only with “.” separator. Aspose.Cells for Java doesn’t create style properly in the styles.xml file.

It creates

<number:date-style style:name=“N179”>
<number:day number:style=“long” number:decimal-places=“0” />
<number:month number:style=“long” number:decimal-places=“0” />
<number:year number:style=“long” />
</number:date-style>

instead of

<number:date-style style:name=“N179”>
<number:day number:style=“long” number:decimal-places=“0” />
number:text.</number:text>
<number:month number:style=“long” number:decimal-places=“0” />
number:text.</number:text>
<number:year number:style=“long” />
</number:date-style>

Btw, what about second issue?

Thanks, Jeff

Hi Jeff,


Thanks for providing further details.

We have logged a ticket with an id: CELLSJAVA-40127. We will look into your both issue to figure them out soon.

We will get back to you soon.

Thank you.

Hi,

Please download: Aspose.Cells for Java v7.1.0.4

We have fixed the issue of saving number format “dd.MM.yyyy” for ODS file. For the issue of showing number format in excel for the generated xlsx/xls, it is the behavior of ms excel. If cells are merged and the merged cells do not have same style, the number format cannot be displayed. To make it be shown, please unmerge the cells, or make all cells in the merged area have the same style, such as change your code as:

Java


Style cellStyle = cell.getStyle();

cellStyle.setCustom(format);

cell.setStyle(cellStyle);

for(int i=1; i<3; i++)

cell.getWorksheet().getCells().get(cell.getRow(), cell.getColumn()+i).setStyle(cellStyle);


Hello,

Thank you, I will try this new version.

Btw, I found better way to set correct custom number format for merged cells:

if (cell.isMerged()) {
Range range = cell.getMergedRange();
StyleFlag flag = new StyleFlag();
flag.setAll(false);
flag.setNumberFormat(true);
range.applyStyle(cellStyle, flag);
}

Regards, Jeff

Hello,


Export to ODS works fine with new version but now we have strange issues with saving XLSX documents. The problem looks very similar to this one.

Regards, Jeff

Hi,

Please download the latest Major version:

Aspose.Cells for Java 7.1.0



Once you have downloaded it, then update the apsose.cells jar with the latest Minor version:
Aspose.Cells
for Java v7.1.0.4



In this fix we have fixed the similar issue of this one. If you still get the same issue, please give us your template file and code to reproduce the issue.

The issues you have found earlier (filed as CELLSJAVA-40126) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.