Multithreading in Apache POI Workbook

Question

I want to write into HSSFWorkBook or XSSFWorkBook in Multhreading environment. Each thread will do the modification in same or different sheet.

try {
    String filePath="C:/Test.xlsx";
    FileInputStream fileInputStream = new FileInputStream(filePath);
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    FileOutputStream fos = new FileOutputStream(filePath);
    workbook.write(fos);
    fos.close();
    fileInputStream.close();
} catch(Exception e) {
    e.printStackTrace();
    System.out.println(e.getMessage());
}

While testing this code in LoadTest with MultiThreading in SOAPUI. I got the exception in line:

Workbook workbook = new XSSFWorkbook(fileInputStream);

The exception is as follows:

org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]

Solution 1

Be aware: Apache POI explicitly does not support multi-threading access to the same workbook-object! This is because there are structures that are handled on a workbook-level, e.g. Styles, Comments, ...

You will run into obscure errors and corrupted documents if you try to do this naively.

The only guarantee that it makes is that separate workbooks in different threads will work fine, i.e. there is no thread-unsafe global state kept anywhere.

The only way that should work would be to synchronize every access to the workbook via a synchronized block:

synchronized (workbook) {
    ... access the sheet and the contents
}

Read-only access might work, but again Apache POI does not make guarantees that concurrent read-access to the same workbook will work.

Update: There is now a corresponding FAQ entry stating this as well.

Solution 2

You create a XSSFWorkbook by reading the file C:/Test.xlsx ; the constructor called is XSSFWorkbook(InputStream), which constructs a OPCPackage. This C:/Test.xlsx file has to be valid, i.e. not corrupted, not empty.

If you want to create a new workbook, you should not read an empty file, but rather use the appropriate constructor.

Regarding multi-threading, I haven't tested it but there are constraints on what the different threads can do (e.g. one per XSSheet). Look at this mail archive.

Solution 3

There is the solution... I create rows into an array... and I use the array in the threads, Tu debes evitar use create rows into a thread because this method is monitored by thread safe

Sorry, I dont speak english... I am trying too hard.

static int count=0;

  

    
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Products");
    sheet.createFreezePane(0, 1); // this will freeze first five rows
    
    int rowCount = 0;
    
    rowCount = getExcelTittle(sucsCompetencia,sucsProveedor, sucsPuntoVenta, sheet, rowCount);  


    // trucazo para multithread
    List<Row> r = new ArrayList<Row>();
    //List<Drawing> h = new ArrayList<Drawing>();
    
    //cell creation
    for ( int j = 0 ; j < ps.size() + 500 ; j++) {
        r.add(sheet.createRow(j));
        //h.add(sheet.createDrawingPatriarch());

    }
    // cells creation .. its a good idea do it 
    Map<String,Cell> cellMap = new HashMap<String,Cell>();
    for (int j = 0 ; j < r.size() ; j++) {
        for ( int  i = 0 ; i < 9 + sucsCompetencia.size() + sucsProveedor.size() + sucsPuntoVenta.size() ; i++) {
            cellMap.put( j + "-" + i, r.get(j).createCell(i));
        }


    }       
    
    Stream<Producto> arrStream = ps.parallelStream();
    arrStream.forEach(p->
                        {   
                            count++;
        int contadorPropio = count;
        
        if (contadorPropio % 1000 == 0)  log.info("* Procesando Generacion Excel " + contadorPropio + " de " + ps.size());
        
       // if (rowCount == 1000 ) break;
        

        
        int columnCount = 0;
        
        
        {
            //IMPORTANTEEEEEEEE
            Cell cell = cellMap.get(contadorPropio + "-" + columnCount++); 
            cell.setCellValue(p.getIdProducto());
        }     

Comments

[rameshvanka]

please refer my blog: rameshvanka.blogspot.com/2020/03/…