CODEWITHSUNDEEP
javaexceldateapache-poi
noai
noai

Reputation: 41

Date in Cell formatted as Date not recognized as Date

I am trying to use Apache's Java POI to add Dates to a spreadsheet. However, I am ending up with the dates in the date-formatted cells not being recognized as dates by the cell but as Strings. Yet, when I open the spreadsheet subsequently, nothing more than it takes is a click into the cell - i.e. editing it while not even making an effective change - and the date format is recognized automatically. How can I make my program conduct this very last step without me needing to interfere? Thank you very much, in advance!

    CreationHelper creationHelper = wb.getCreationHelper();
    XSSFCellStyle cellStyleDate = wb.createCellStyle();

    LocalDate start = LocalDate.of(2000, 1, 1);
    LocalDate end = LocalDate.of(2000, 12, 31);

    cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd.mm.yyyy"));
    int i = 1;
    for (LocalDate date = start; date.isBefore(end); date = date.plusDays(1)) {

        // wb.getSheetAt(0).createRow(i).createCell(0).setCellValue(date);
        wb.getSheetAt(0).createRow(i).createCell(0).setCellValue(date.toString());
        // wb.getSheetAt(0).createRow(i).createCell(0).setCellValue(date.getDayOfMonth() + "." + date.getMonthValue() + "." + date.getYear());

        wb.getSheetAt(0).getRow(i).getCell(0).setCellStyle(cellStyleDate);
        i++;
    }

Upvotes: 3

Views: 7343

Answers (2)

Axel Richter
Axel Richter

Reputation: 61915

As of it is documented there are two possible methods for setting a date value. setCellValue(java.util.Calendar value) and setCellValue(java.util.Date value). Using java.time.LocalDate is not supported until now.

So you must convert the LocalDate into java.util.Date before setting as cell value.

Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Date;
import java.time.LocalDate;
import java.time.ZoneId;

import java.awt.Desktop;

class LocalDateTest {

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("Sheet1");

   CreationHelper creationHelper = wb.getCreationHelper();
   CellStyle cellStyleDate = wb.createCellStyle();

   LocalDate start = LocalDate.of(2000, 1, 1);
   LocalDate end = LocalDate.of(2000, 12, 31);

   cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd.mm.yyyy"));
   int i = 1;
   for (LocalDate localdate = start; localdate.isBefore(end.plusDays(1)); localdate = localdate.plusDays(1)) {
    Date date = Date.from(localdate.atStartOfDay(ZoneId.systemDefault()).toInstant());
    wb.getSheetAt(0).createRow(i).createCell(0).setCellValue(date);
    wb.getSheetAt(0).getRow(i).getCell(0).setCellStyle(cellStyleDate);
    i++;
   }

   OutputStream out = new FileOutputStream("LocalDateTest.xlsx");
   wb.write(out);
   wb.close();

   System.out.println("Done");
   File outputfile = new File("LocalDateTest.xlsx");
   Desktop.getDesktop().open(outputfile);


  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

The above is true for Apache POI versions of 2016. The current apache poi 5.2.2 provides Cell.setCellValue(java.time.LocalDate value) as well as Cell.setCellValue(java.time.LocalDateTime value).

Upvotes: 7

Marc Dzaebel
Marc Dzaebel

Reputation: 435

setCellValue is now also defined for LocalDate and LocalDateTime

Upvotes: 5

Related Questions