CODEWITHSUNDEEP
javaapache-poi
Mohan
Mohan

Reputation: 263

Setting date format to excel cell with apache POI is not working

I am using below code to set date value to excel cell but its showing double value in excel cell. Requirement is to see date value when we open excel.

row = sheet.createRow(rowNum++);
cell = row.createCell(0);

XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();       
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("mm/dd/yyyy"));
Date loginDate = formatStringToDate(entry.getKey(),"yyyy-MM-dd");
cell.setCellValue(loginDate);
cell.setCellStyle(cellStyle);

When I open and see the cell format it is showing as General as shown below enter image description here

If I change the format to date in excel then it is showing Date value in cell but I want the cell to show Date value by default

Upvotes: 1

Views: 3738

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

At first you need to know how Excel does managing it's cell styles. It does this on workbook level so that not each cell has it's own cell style. Instead there are as much different cell styles as needed and the cells are using one of those cell styles.

A new created cell has the default cell style and if using Cell.getCellStyle on this new created cell, you will get this default cell style. So your code tries to make the default cell style a date formatted cell style. This is not the way to go.

So at first on workbook level we are creating as much cell styles as needed. For example one date formatted cell style and one currency formatted cell style.

Then we are creating the sheet and the cells and are putting the data into the cells. If the cell needs a special cell style, then we are using one of the cell styles we have previous created.

Example:

import java.io.FileOutputStream;

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

import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Map;
import java.util.TreeMap;
import java.util.List;
import java.util.Arrays;

public class CreateExcelNumberFormats {

 public static void main(String[] args) throws Exception {

  Map<Date, List<Object>> data = new TreeMap<Date, List<Object>>();

  data.put(new GregorianCalendar(2017, 9, 29, 6, 0).getTime(), Arrays.asList("user 1", 1234.56));
  data.put(new GregorianCalendar(2017, 9, 30, 6, 0).getTime(), Arrays.asList("user 2", 789.12));
  data.put(new GregorianCalendar(2017, 9, 31, 6, 0).getTime(), Arrays.asList("user 3", 131415.16));
  data.put(new GregorianCalendar(2017, 9, 29, 15, 45).getTime(), Arrays.asList("user 4", 1234567.89));
  data.put(new GregorianCalendar(2017, 9, 30, 9, 45).getTime(), Arrays.asList("user 5", 123.45));

  Workbook wb = new XSSFWorkbook();
  CreationHelper creationHelper = wb.getCreationHelper();

  //on workbook level we are creating as much cell styles as needed:
  CellStyle datestyle = wb.createCellStyle();
  datestyle.setDataFormat(creationHelper.createDataFormat().getFormat("mm/dd/yyyy"));
  CellStyle currencystyle = wb.createCellStyle();
  currencystyle.setDataFormat(creationHelper.createDataFormat().getFormat("$#,##0.00"));

  //now we are creating the sheet and the cells and are putting the data into the cells
  Sheet sheet = wb.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Date");
  cell = row.createCell(1);
  cell.setCellValue("Logged in User");
  cell = row.createCell(2);
  cell.setCellValue("Amount");

  int rowNum = 1;

  for (Map.Entry<Date, List<Object>> entry : data.entrySet()) {

   row = sheet.createRow(rowNum++);
   cell = row.createCell(0);
   Date loginDate = entry.getKey();
   cell.setCellValue(loginDate);
   //if the cell needs a special cell style, then we are using one of the ones we have previous created
   cell.setCellStyle(datestyle);

   List<Object> userdatas = entry.getValue();

   int cellNum = 1;
   for (Object userdata : userdatas) {
    cell = row.createCell(cellNum);
    if (cellNum == 1) {
     cell.setCellValue((String)userdata);
    } else if (cellNum == 2) {
     cell.setCellValue((Double)userdata);
     //if the cell needs a special cell style, then we are using one of the ones we have previous created
     cell.setCellStyle(currencystyle);   
    }
    cellNum++;
   }

  }

  wb.write(new FileOutputStream("CreateExcelNumberFormats.xlsx"));
  wb.close();

 }

}

Upvotes: 5

Related Questions