SXSSF can't create Font and DateFormat

Question

In a SXSSF Workbook:

How can I set font?

How can I set celltype to date?

Context

I need to write an excel .xlsx file with a huge amount of rows and columns (400.000 rows, 50 fields each) so I'm using apache poi, SXSSF workbook. I created a test file with this amount of dummy text and it works but I want cells to have a custom font: so if text is somehow representing time cell type should be set to "date" (allowing me to filter rows by date easily).

I wrote a function which allows me to create a cell, check and modify its text type (currency, string, data) based on textType value that I pass to the function. Text type is correctly recognized as I examine data. Currency and text are correctly modified but when I tried to set Font and Data type it doesn't work at all.

(I already have a working function (XSSF) which creates an excel with custom font and different data types included "date", it just can't generate large excel files due to GC overhead, that's why I had to switch to SXSSF)

Error

I get this kind of compile error.

Errors incompatible types;found : org.apache.poi.ss.usermodel.Font,required: org.apache.poi.xssf.usermodel.XSSFFont at line 235 (235:40) incompatible types;found : org.apache.poi.ss.usermodel.DataFormat,required: org.apache.poi.xssf.usermodel.XSSFDataFormat at line 271 (271:54) incompatible types;found : org.apache.poi.ss.usermodel.DataFormat,required: org.apache.poi.xssf.usermodel.XSSFDataFormat at line 308 (308:54) incompatible types;found : org.apache.poi.ss.usermodel.DataFormat,required: org.apache.poi.xssf.usermodel.XSSFDataFormat at line 335 (335:54) incompatible types;found : org.apache.poi.ss.usermodel.DataFormat,required: org.apache.poi.xssf.usermodel.XSSFDataFormat at line 375 (375:66)

Since SXSSFFont doesn't exists, I tried to fix it by creating a Font object and then using it instead of XSSFFont.

Same for DataFormat.

But I still find types issues here and there in code.

XSSFFont font = (XSSFFont) workbook.createFont();
XSSFDataFormat df = (XSSFFont) workbook.createDataFormat();

Also tried code above but it doesnt work, I get

ClassCastException error

Code

            //[...]
            SXSSFWorkbook workbook = new SXSSFWorkbook(100);
            Sheet sheet;
            Row row;
            Cell cell;
            //[...]
            XSSFFont font = workbook.createFont();
            font.setBold(fontBold);
            font.setItalic(fontItalic);
            font.setFontHeightInPoints( (short) fontSize);
            if (!fontUnderline) {
              font.setUnderline(FontUnderline.NONE);
            }
            else {
              font.setUnderline(FontUnderline.SINGLE);
            }
            font.setColor(fontColor);

            CellStyle style = workbook.createCellStyle();

            style.setFont(font);


            //[...]

            if (textType == 'T') {

               XSSFDataFormat df = workbook.createDataFormat();
               style.setDataFormat(df.getFormat("d-mmm-yyyy hh:mi:ss"));
               Calendar c = Calendar.getInstance();
               c.set(year, month, day, hour, minute,second);
               cell.setCellValue(c.getTime());
            }

            //in the end I set cell value and style

           cell.setCellStyle(style);
           cell.setCellValue(text);

Solution

Here I created a short example using a SXSSFWorkbook and filling 1 cell with a date and applying the proper format as well as a font.

SXSSFWorkbook s = new SXSSFWorkbook();
Font font = s.createFont();
font.setBold(true);
font.setItalic(true);
short dateStyle = s.createDataFormat().getFormat("mm/dd/yy;@");
CellStyle dateCellFormat = s.createCellStyle();
dateCellFormat.setDataFormat(dateStyle);
dateCellFormat.setFont(font);
SXSSFSheet sheet = s.createSheet("Test");
Row r = sheet.createRow(0);
Cell c = r.createCell(0);
c.setCellValue(new Date());
c.setCellStyle(dateCellFormat);
s.write(Files.newOutputStream(Paths.get("D:/test.xlsx"), StandardOpenOption.CREATE_NEW));
s.close();

You will have to make some adjustments, but I hope you get the idea (use the appropriate Interface's)