POI-XSSF: user defined data formats

Question

I am reading an excel , which seems to have user defined data formats. for example it has a data format : "yyyy"E , which just displays the date in yyyy format followed by the letter E.

Now, this format is not a part of the built in ,available formats.

So, it seems that when i try to set this as DataFormat of a CellStyle, it doesn't work.

First i read a workbook and then create the DataFormat from this workbook.

XSSFWorkbook wb = new XSSFWorkbook(excelToRead);
XSSFDataFormat df = wb.createDataFormat();

the df object now has the user defined data formats as well (checked by printing till 200). Now, I try to create a new cell in the same workbook, with a new style like this:

XSSFCell cellTemp = row.createCell(0);
XSSFCellStyle styleTemp = wb.createCellStyle();
styleTemp.setDataFormat(df.getFormat(cell.getCellStyle().getDataFormatString()));
cellTemp.setCellStyle(styleTemp);
cellTemp.setCellValue(cell.getStringCellValue());
System.out.print(formatter.formatCellValue(cellTemp)+" ");

But, the formatter does not give me the correct string value, instead it gives me the underlying Integer value of the date (which i guess is the default if the format is not recognized).

What is the correct way to use user defined formats in XSSF?

UPDATE: It seems that i am able to use other user defined formats like yyyy, 0.0% but not yyyy"E" or yyyy"A"

Answer 1

It is not really clear for me what exactly you are trying to achieve. But if the DataFormatter not properly formats, then one could also use a class derived from CellFormatter.

Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.format.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

import java.awt.Desktop;
import java.util.Date;

class CustomDateFormatTest {

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

   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("Sheet1");
   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);

   cell.setCellValue(new Date());

   DataFormat format = wb.createDataFormat();
   CellStyle cellstyle = wb.createCellStyle();
   cellstyle.setDataFormat(format.getFormat("yyyy\"E\""));

   cell.setCellStyle(cellstyle);

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

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

   InputStream inp = new FileInputStream("CustomDateFormatTest.xlsx");
   wb = WorkbookFactory.create(inp);

   sheet = wb.getSheetAt(0);
   row = sheet.getRow(0);
   cell = row.getCell(0);

   //This will not format properly
   DataFormatter dataformatter = new DataFormatter();
   System.out.println(dataformatter.formatCellValue(cell));

   //This will format properly
   String formatstring = cell.getCellStyle().getDataFormatString();
   System.out.println(formatstring);
   CellDateFormatter celldateformatter = new CellDateFormatter(formatstring);
   //For using CellDateFormatter we need to know that the cell value is a Date.
   System.out.println(celldateformatter.format(cell.getDateCellValue()));


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