Apache POI: How to use addIgnoredErrors(...) functionality in SXSSFSheet

Question

XSSFSheet (Javadoc XSSFSheet) has a method addIgnoredErrors(...). SXSSFSheet (Javadoc SXSSFSheet) hasn't got this method.

How can I ignore errors in a SXSSFSheet based sheet?

I can't use XSSFSheet, because I have 105,000 Rows and XSSFSheet will blow up the memory.

I put a text field in the table that may consist of digits only. Shitty Excel will show up an Warning Number stored as text for this cells. Even if I set the CellType to STRING and format as Text "@", no matter in which order.

Minimal runnable Demo:

public void run() {
    try {
        final SXSSFWorkbook workbook = new SXSSFWorkbook(-1);

        final DataFormat dataFormat = workbook.createDataFormat();

        CellStyle styleDef;
        styleDef = workbook.createCellStyle();
        styleDef.setDataFormat(dataFormat.getFormat("@"));

        final SXSSFSheet sheet = workbook.createSheet();
        final SXSSFRow row = sheet.createRow(0);

        final SXSSFCell cell = row.createCell(0);
        final String text = "123";
        cell.setCellType(CellType.STRING);
        cell.setCellValue(text);
        cell.setCellStyle(styleDef);

        workbook.write(new FileOutputStream("test.xlsx"));
        workbook.close();
        // Hint from Axel Richter, to make it a full working example
        workbook.dispose();

    } catch (final Exception e) {
        e.printStackTrace();
    }
}

user_3915431 · Answer

The SXSSFSheet has a field XSSFSheet _sh internally. So we could using reflection to get this and use it.

The following example puts a

in the /xl/worksheets/sheet1.xml and so the Excelwill ignore the error numberStoredAsText for the range A1:A100.

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.streaming.*;

import java.lang.reflect.Field;

public class CreateExcelSXSSFWorkbookNumberAsText {

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

  SXSSFWorkbook workbook = new SXSSFWorkbook();

  DataFormat dataFormat = workbook.createDataFormat();

  CellStyle styleDef;
  styleDef = workbook.createCellStyle();
  styleDef.setDataFormat(dataFormat.getFormat("@"));
  //styleDef.setQuotePrefixed(true);

  SXSSFSheet sheet = workbook.createSheet();

  Field _sh = SXSSFSheet.class.getDeclaredField("_sh");
  _sh.setAccessible(true); 
  XSSFSheet xssfsheet = (XSSFSheet)_sh.get(sheet); 
  xssfsheet.addIgnoredErrors(new CellRangeAddress(0, 99, 0, 0), IgnoredErrorType.NUMBER_STORED_AS_TEXT);     

  for (int r = 0; r < 100; r++) {
   SXSSFRow row = sheet.createRow(r);
   SXSSFCell cell = row.createCell(0);
   String text = "" + new java.util.Random().nextInt();
   cell.setCellValue(text);
   cell.setCellStyle(styleDef);
  }

  workbook.write(new FileOutputStream("test.xlsx"));
  workbook.close();
  workbook.dispose();
 }
}

Btw.: You should use SXSSFWorkbook.dispose() to get rid of the temporary files.

Apache POI: How to use addIgnoredErrors(...) functionality in SXSSFSheet

1 Answers