SXSSF can't create Font and DateFormat

Question

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);

Errors incompatible types;found I can't make it run, so actually I don't get any runtime error — mbianchidev, Jan 10 '18 at 14:26
Every line in which I perform a workbook.createDataFormat() function — mbianchidev, Jan 10 '18 at 14:29
for `XSSFFont font = workbook.createFont();` use the `Font` interface instead of `XSSFFont`. maybe it works — XtremeBaumer, Jan 10 '18 at 14:31
Ok I figured it out. It was very dumb from me, I just added (XSSFFont) type cast to workbook.createFont() function. same for data — mbianchidev, Jan 10 '18 at 14:51

score 0 · Accepted Answer · answered Jan 10 '18 at 15:42

0

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)

answered Jan 10 '18 at 15:42

XtremeBaumer

6,275
3
19
65

I'm implementing this, as compile error I already get: cannot resolve symbol: method setBold (boolean)in interface org.apache.poi.ss.usermodel.Font at line 236 (236:10) I wonder how can this be possbile, it requires a short Also: setUnderline(byte) in org.apache.poi.ss.usermodel.Font cannot be applied to (org.apache.poi.ss.usermodel.FontUnderline) at line 241 (241:12) – mbianchidev Jan 10 '18 at 15:57
OK! It works. Thank you so much for the effort @XtremeBaumer, I managed to cast my sheets (I previously used XSSF Sheet instead of SXSSF Sheet, that was root of the problem). – mbianchidev Jan 10 '18 at 16:33
I ran into the same error, namely that setting a large SXSSF sheet's cells to a date format didn't work. I am creating the dateCellFormat during initialization (i.e. outside the creation loop) and am then assigning it to all relevant date cells. But that doesn't work for me. When I open the sheet in Excel all date cells are displayed as integers, :-( – mmo Mar 07 '23 at 10:50
@mmo make a separate question – XtremeBaumer Mar 07 '23 at 11:11
@XtremeBaumer I did already: https://stackoverflow.com/questions/75662033/apache-poi-sxssfworkbook-date-style-is-not-applied-not-working – mmo Mar 07 '23 at 20:08

SXSSF can't create Font and DateFormat

1 Answers1