CODEWITHSUNDEEP
javaexcelapache-poi
paramvir
paramvir

Reputation: 404

Restrict the Excel to not use OS default Date format in Java using apachePOI

Hi I am working on generating xls file using apachePOI in Java. I have column of date. Now the problem is that Excel picks the date format from default OS settings.

I have a problem that I want Excel to pick the dd-mm-yyyy always. But the systems where the OS setting is US, it picks mm-dd-yyyy. So a valid date like 17-2-2017, is rendered invalid in US systems because there is no 17th month.

So my question is can I force Excel to use the date format I want. In other words can I restrict the Excel to not use OS settings. If not possible, any other workaround is appreciated. Thanks.

Code: 

private static void doCreate() throws FileNotFoundException, ParseException {

        Workbook workbook;
        Row row;
        Sheet spreadsheet;

        workbook = new HSSFWorkbook();
        spreadsheet = workbook.createSheet("Order Details");

        dateCellStyle = workbook.createCellStyle();
//        LocaleUtil.setUserTimeZone(LocaleUtil.TIMEZONE_UTC);
//        // Locale.setDefault();
//        final String excelFormatPattern = DateFormatConverter.convert(Locale.JAPANESE, "dd MMMM, yyyy");
//        // final DataFormatter dataFormatter = new DataFormatter(Locale.ENGLISH);
        final short df = workbook.createDataFormat().getFormat("dd-mm-yyyy");
        dateCellStyle.setDataFormat(df);

        final String inputDate = "2017-10-24";

        row = spreadsheet.createRow(0);
        final Cell cell = row.createCell(0);

        final Date creationDate = inputCreationDateFormat.parse(inputDate);
        cell.setCellValue(outputCreationDateFormat.format(creationDate));
        cell.setCellStyle(dateCellStyle);

        final FileOutputStream out = new FileOutputStream(new File("Writesheet.xls"));
        try {
            workbook.write(out);
            workbook.close();
        } catch (final IOException e) {

        }
        System.out.println("Writesheet.xls written successfully");
    }
}

Upvotes: 0

Views: 313

Answers (1)

Andrew S
Andrew S

Reputation: 2756

The posted code is not complete but it appears the cell format is being set to dd-mm-yyyy.

However, the code takes the Date:

Date creationDate = inputCreationDateFormat.parse(inputDate);

and is converting it to some other type (String?) for the actual cell value.

cell.setCellValue(outputCreationDateFormat.format(creationDate));

Instead, just use the Date:

cell.setCellValue(creationDate);

so Excel can apply the format to a date value.

Here's an example with multiple formats:

public class XlsApp {
    public static void main(String[] args) throws IOException {
        XlsApp app = new XlsApp();
        app.doCreate();
    }

    private void doCreate() throws IOException {
        Workbook workbook = new HSSFWorkbook();

        CellStyle mmddyyyy = workbook.createCellStyle();
        mmddyyyy.setDataFormat(workbook.createDataFormat().getFormat("mm-dd-yyyy"));

        CellStyle ddmmyyyy = workbook.createCellStyle();
        ddmmyyyy.setDataFormat(workbook.createDataFormat().getFormat("dd-mm-yyyy"));

        Sheet sheet = workbook.createSheet();
        for (int r = 0; r < 10; r++) {
            Date date = new Date(System.currentTimeMillis() - ThreadLocalRandom.current().nextInt());

            Row row = sheet.createRow(r);

            Cell cell = row.createCell(0);
            cell.setCellStyle(mmddyyyy);
            cell.setCellValue(date);

            cell = row.createCell(1);
            cell.setCellStyle(ddmmyyyy);
            cell.setCellValue(date);
        }

        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);

        FileOutputStream out = new FileOutputStream(new File("c:\\temp\\test.xls"));
        workbook.write(out);
        workbook.close();
    }

}

Upvotes: 1

Related Questions