How do I set cell value to Date and apply default Excel date format

Question

I ve been using Apache POI for some time to read existing Excel 2003 files programmatically  Now I have a new requirement to create entire  xls files in-memory  still using Apache POI  and then write them to a file at the end  The only problem standing in my way is the handling of cells with dates   Consider the following code   Date myDate   new Date    HSSFCell myCell     code that assigns a cell from an HSSFSheet to  myCell  would go here    myCell setCellValue myDate     When I write the workbook containing this cell out to a file and open it with Excel  the cell is displayed as a number  Yes  I do realize that Excel stores its  dates  as the number of days since January 1 1900 and that is what the number in the cell represents   QUESTION  What API calls can I use in POI to tell it that I want a default date format applied to my date cell   Ideally I want the spreadsheet cell to be displayed with the same default date format that Excel would have assigned it if a user had manually opened the spreadsheet in Excel and typed in a cell value that Excel recognized as being a date

User · Answer

To know the format string used by Excel without having to guess it: create an excel file, write a date in cell A1 and format it as you want. Then run the following lines:

FileInputStream fileIn = new FileInputStream("test.xlsx");
Workbook workbook = WorkbookFactory.create(fileIn);
CellStyle cellStyle = workbook.getSheetAt(0).getRow(0).getCell(0).getCellStyle();
String styleString = cellStyle.getDataFormatString();
System.out.println(styleString);

Then copy-paste the resulting string, remove the backslashes (for example d/m/yy\ h\.mm;@ becomes d/m/yy h.mm;@) and use it in the http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells code:

CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy h.mm;@"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

User · Answer

To set to default Excel type Date  defaulted to OS level locale  -  i e  xlsx will look different when opened by a German or British person  and flagged with an asterisk if you choose it in Excel s cell format chooser  you should       CellStyle cellStyle   xssfWorkbook createCellStyle        cellStyle setDataFormat  short 14       cell setCellStyle cellStyle     I did it with xlsx and it worked fine

User · Answer

This example is for working with  xlsx file types  This example comes from a  jsp page used to create a  xslx spreadsheet   import org apache poi xssf usermodel      import needed  XSSFWorkbook  wb   new XSSFWorkbook         Create workbook XSSFSheet sheet   wb createSheet            Create spreadsheet in workbook XSSFRow row   sheet createRow rowIndex      Create the row in the spreadsheet     1  Create the date cell style XSSFCreationHelper createHelper   wb getCreationHelper    XSSFCellStyle cellStyle           wb createCellStyle    cellStyle setDataFormat  createHelper createDataFormat   getFormat  MMMM dd  yyyy         2  Apply the Date cell style to a cell    This example sets the first cell in the row using the date cell style cell   row createCell 0   cell setCellValue new Date     cell setCellStyle cellStyle

User · Answer

http   poi apache org spreadsheet quick-guide html CreateDateCells  CellStyle cellStyle   wb createCellStyle    CreationHelper createHelper   wb getCreationHelper    cellStyle setDataFormat      createHelper createDataFormat   getFormat  m d yy h mm     cell   row createCell 1   cell setCellValue new Date     cell setCellStyle cellStyle

User · Answer

I am writing my answer here because it may be helpful to other readers  who might have a slightly different requirement than the questioner here   I prepare an  xlsx template  all the cells which will be populated with dates  are already formatted as date cells  using Excel    I open the  xlsx template using Apache POI and then just write the date to the cell  and it works   In the example below  cell A1 is already formatted from within Excel with the format   -409 mmm yyyy  and the Java code is used only to populate the cell   FileInputStream inputStream   new FileInputStream new File  Path to  xlsx template     Workbook wb   new XSSFWorkbook inputStream   Date date1 new Date    Sheet xlsMainTable    Sheet  wb getSheetAt 0   Row myRow  CellUtil getRow 0  xlsMainTable   CellUtil getCell myRow  0  setCellValue date1     WHen the Excel is opened  the date is formatted correctly

User · Answer

This code sample can be used to change date format  Here I want to change from yyyy-MM-dd to dd-MM-yyyy  Here pos is position of column   import org apache poi ss usermodel Cell  import org apache poi ss usermodel CellStyle  import org apache poi ss usermodel CreationHelper  import org apache poi ss usermodel Row  import org apache poi xssf usermodel XSSFCellStyle  import org apache poi xssf usermodel XSSFColor  import org apache poi xssf usermodel XSSFFont  import org apache poi xssf usermodel XSSFSheet  import org apache poi xssf usermodel XSSFWorkbook   class Test   public static void main  String   args     String input  D   somefolder  somefile xlsx   String output  D   somefolder  someoutfile xlsx  FileInputStream file   new FileInputStream new File input    XSSFWorkbook workbook   new XSSFWorkbook file   XSSFSheet sheet   workbook getSheetAt 0   Iterator lt Row gt  iterator   sheet iterator    Cell cell   null  Row row null  row iterator next    int pos 5     5th column is date  while iterator hasNext          row iterator next         cell row getCell pos-1         CellStyle cellStyle   wb createCellStyle        XSSFCellStyle cellStyle    XSSFCellStyle cell getCellStyle        CreationHelper createHelper   wb getCreationHelper        cellStyle setDataFormat          createHelper createDataFormat   getFormat  dd-MM-yyyy         SimpleDateFormat sdf   new SimpleDateFormat  yyyy-MM-dd        Date d null      try           d  sdf parse cell getStringCellValue           catch  ParseException e               TODO Auto-generated catch block         d null          e printStackTrace            continue            cell setCellValue d       cell setCellStyle cellStyle         file close    FileOutputStream outFile  new FileOutputStream new File output    workbook write outFile   workbook close    outFile close

[java] How do I set cell value to Date and apply default Excel date format?

Examples related to java

Examples related to date

Examples related to apache-poi