Using Apache POI we can write dates in various formats in an excel cell using CellStyle. Lets see ways of writing various date formats in excel using POI.
Lets follow the below steps
1. Create a workbook object
2. Create a XSSFSheet object
3. Now create a CreationHelper object (This is needed to style the date object)
4. Now create row/cells to write the date
5. Create CellStyle and apply the CreationHelper object to format the date in your formats.
example
|
1 |
style.setDataFormat(creationHelper.createDataFormat().getFormat("dd-mm-yyyy")); |
Please Note that, if we do not provide any date formatter while writing date into cells, the date will be written as simple string or number. (Please see the example below)
Now lets see an example. We will write date in 3 formats
1st Cell – Write Date without any formatter
2nd Cell – Write Date using a CellStyle and formatter – format dd-mm-yyyy
3rd Cell – Write Date using another CellStyle formatter – format mm/dd/yyyy hh:mm:ss
Please note : It is important to create a new cell style from the workbook otherwise you can end up modifying the existing style and this will affect all the other cells that use that style.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
package com.kscodes.sampleproject; import java.io.File; import java.io.FileOutputStream; import java.util.Date; 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.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcelUsingPOI { public static void main(String[] args) { // Create instance of the class and call the writeExcelFile() WriteExcelUsingPOI writeExcelUsingPOI = new WriteExcelUsingPOI(); writeExcelUsingPOI .writeExcelFile("C:\\kscodes\\ExcelDateFormatTest.xlsx"); } public void writeExcelFile(String fileName) { XSSFWorkbook workbook = null; FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(new File(fileName)); // Create a Workbook workbook = new XSSFWorkbook(); // Create an Empty Sheet XSSFSheet sheet = workbook.createSheet("Date Formats"); CreationHelper creationHelper = workbook.getCreationHelper(); Row row = sheet.createRow(1); // Create Cell and set value as new Date() Cell cell = row.createCell((short) 1); cell.setCellValue(new Date()); // Create Another Cell and set todays date in dd-mmm-yyyy format cell = row.createCell((short) 2); cell.setCellValue(new Date()); CellStyle style1 = workbook.createCellStyle(); style1.setDataFormat(creationHelper.createDataFormat().getFormat( "dd-mm-yyyy")); cell.setCellStyle(style1); // Create Another Cell and set todays date in mm/dd/yyyy hh:mm:ss // format. Note that we have created a new CellStyle for this cell. // This is not modify the existing CellStyle that was created for // previous cell cell = row.createCell((short) 3); cell.setCellValue(new Date()); CellStyle style2 = workbook.createCellStyle(); style2.setDataFormat(creationHelper.createDataFormat().getFormat( "mm/dd/yyyy hh:mm:ss")); cell.setCellStyle(style2); workbook.write(fileOutputStream); System.out.println("Excel File created and written !!!!"); } catch (Exception e) { System.out.println("An Exception occured while writing Excel" + e); } finally { try { if (fileOutputStream != null) { fileOutputStream.close(); } if (workbook != null) { workbook.close(); } } catch (Exception e) { } } } } |
Output
As you can see the results
1st Cell – Date was converted to Number and then written.
2nd Cell – Date was converted to the desired format.
3rd Cell – Date was converted to the desired format.
