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