Why did writing simple .xlsx book very slow in my case?

Question

I really need a hand here. I have very, very odd code that even writing only 2 cells takes almost 35 seconds. (Twice time took on devices like smartphones).

1. Notes : There is Nothing wrong with these 2 methods during the computation. They're quite fast. No need to debug these

// Require apache commong text 1.8

static Double compareStrings(String stringA, String stringB) {===>to compute distance
    

• static int getStartingRowToWriteBacklog(String pathDoc, int sheet, String titleCategory) throws IOException { ====> to compute at which row I start to write the cell

2. .xls files: https://www.file.io/download/lx2Zq2N1lTDX or here https://ufile.io/uzy4wvxz

My problem arises on this method/lines:

public static void writeAndCloseALLStream() throws IOException {
        /*
         * System.out.println ("excelInputFile"+excelInputFile);
         * System.out.println ("excelOutputFile"+excelOuputFile);
         */
        
        
        fis.close();
        //workbook.write(fos);

        SXSSFWorkbook book = new SXSSFWorkbook(workbook,100,true); 
         book.setCompressTempFiles(true);
        SXSSFSheet sheetSXSS= book.getSheetAt(0);
         book.write(fos);
         workbook.close();
         book.dispose();
         book.close();
        fos.close();
    }

that took 35~51 seconds to finish . I am desperately need a solution for this. Being researced for almost 1 week ended up with no solution.

full codes:

import java.util.ArrayList;
import java.util.concurrent.TimeUnit;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.text.similarity.JaroWinklerDistance;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelUtility {
    public static int globalSelectedIndex = 0;

    public static FileInputStream fis;
    public static XSSFWorkbook workbook;
    public static XSSFSheet sheet;
    public static FileOutputStream fos;

    public static String excelInputFile;
    public static String excelOuputFile;

    public static String excelInputFileForBacklog;
    public static String excelOutputFileForBacklog;
    public static XSSFSheet sheetBacklogForm;

    public static int banyakSheet;
    public static ArrayList<String> daftarSheet;

    // Jangan lupa set variable "excelOuputFile" sebelum constructInputStream di
    // invoke
    public static void constructInputStream() throws IOException {
        // ZipSecureFile.setMinInflateRatio(0.009);
        daftarSheet = new ArrayList<>();
        fis = new FileInputStream(new File(excelInputFile));

        workbook = new XSSFWorkbook(fis);
        sheet = workbook.getSheetAt(globalSelectedIndex);
        
        System.out.println("excelInputFile = " + excelInputFile);

    }

    // Jangan lupa set variable "excelOuputFile" sebelum constructInputStream di
    // invoke
    public static void constructInputStreamForBacklogForm(int sheetInd) throws IOException {
        if (fis!=null) {
            fis.close();
        }
        fis = new FileInputStream(new File(excelInputFileForBacklog));
        workbook = new XSSFWorkbook(fis);
        sheetBacklogForm = workbook.getSheetAt(sheetInd);

        System.out.println("excelInputFileForBacklog = " + excelInputFileForBacklog);
        System.out.println("sheetnya = " + workbook.getSheetName(sheetInd));

    }

    public static void constructOutputStream(String excelOuputFiles) throws FileNotFoundException {
        WriteExcelUtility.excelOuputFile = excelOuputFiles;
        // wajib nama lain
        fos = new FileOutputStream(new File(excelOuputFile));
        System.out.println("excelOutputFile" + excelOuputFile);

    }

    public static void constructOutputStreamForBacklogForm(String excelOuputFilesForBaglog)
            throws FileNotFoundException {
        // WriteExcelUtility.excelOuputFile = excelOuputFiles;
        // wajib nama lain
        fos = new FileOutputStream(new File(excelOuputFilesForBaglog));
        System.out.println("excelOuputFilesForBaglog = " + excelOuputFilesForBaglog);

    }

    public static void getNumberofsheetAndTheirName() throws IOException {
        // Log.d ("WriteExcelUtility.getNumberofsheetAndTheirName
        // :","excelInputFile = "+excelInputFile);
        // System.out.println ("excelOutputFile"+excelOuputFile);

        banyakSheet = workbook.getNumberOfSheets();
        System.out.println("Jumlah sheet = " + banyakSheet);
        for (int i = 0; i < banyakSheet; i++) {
            /* XSSFSheet sheetName = workbook.getSheetAt(i); */
            String namaSheet = workbook.getSheetName(i).toString();
            if (namaSheet.contains("Sheet")) {
                // do nothing
            } else {
                System.out.println(namaSheet);
                daftarSheet.add(namaSheet);
            }

        }
        fis.close();

    }

    // Cuma untuk update dan set cell value. Penulisan final di method
    // writeAndCloseALLStream()
    public static void updateDataPM(int sheetN, int row, int sell, String value)
            throws IOException, InvalidFormatException {
        System.out.println("excelInputFile" + excelInputFile);
        System.out.println("excelOutputFile" + excelOuputFile);

        XSSFRow row1 = sheet.getRow(row);
        if (row1 == null) {
            row1 = sheet.createRow(row);
        }
        XSSFCell cell1 = row1.getCell(sell);
        if (cell1 == null) {
            cell1 = row1.createCell(sell);
        }
        cell1.setCellValue(value);

    }

    // Cuma untuk update dan set cell value. Penulisan final di method
    // writeAndCloseALLStream()
    public static void updateDataLogback(int sheetN, int row, int sell, String value)
            throws IOException, InvalidFormatException {
        System.out.println("excelInputFileForBacklog =" + excelInputFileForBacklog);
        System.out.println("excelOutputFileForBacklog =" + excelOutputFileForBacklog);

        XSSFRow row1 = sheetBacklogForm.getRow(row);
        if (row1 == null) {
            row1 = sheetBacklogForm.createRow(row);
        }
        XSSFCell cell1 = row1.getCell(sell);
        if (cell1 == null) {
            cell1 = row1.createCell(sell);
        }
        cell1.setCellValue(value);

    }

    public static void writeAndCloseALLStream() throws IOException {
        /*
         * System.out.println ("excelInputFile"+excelInputFile);
         * System.out.println ("excelOutputFile"+excelOuputFile);
         */
        
        
        fis.close();
        //workbook.write(fos);

        SXSSFWorkbook book = new SXSSFWorkbook(workbook,100,true); 
         book.setCompressTempFiles(true);
        SXSSFSheet sheetSXSS= book.getSheetAt(0);
        
        
        
 
         book.write(fos);
         workbook.close();
         book.dispose();
         book.close();
        fos.close();
    }

    // Require apache commong text 1.8
    public static Double compareStrings(String stringA, String stringB) {
        return new JaroWinklerDistance().apply(stringA, stringB);
        // return StringUtils.Jar(stringA, stringB);
    }

    // for backlog doc
    // Method ini mereturnkan starting row dimana kita akan tulisi
    // titleCategory = misalkan ENGINE COMPARTMENT & ACCESSORIES, DRIVE TRAIN,
    // etc
    public static int getStartingRowToWriteBacklog(String pathDoc, int sheet, String titleCategory) throws IOException {

        XSSFSheet mySheet = workbook.getSheetAt(sheet);
        XSSFCell checked = null;
        int startingRowInACategory = 0;

        /**
         * =================================================================
         * cari di (kategori mis. Engine compartment, drive train, etc) mana
         * kita akan mulai loop (untuk start penulisan data ke row)
         * ===================================================================
         */
        for (int i = 0; i < 100; i++) {
            try {
                // 2 == column "inspection backlog" yg berisi misalkan ENGINE
                // COMPARTMENT & ACCESSORIES, DRIVE TRAIN, etc
                checked = mySheet.getRow(i).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                // kalau nilai cell "founded" tidak blank
                // if percentage similarity is high, i.e, 1.00 == SIMILAR
                if (WriteExcelUtility.compareStrings(titleCategory, checked.toString()) > (double) 0.39) {
                    System.out.println(" checked =" + checked.toString().trim());
                    startingRowInACategory = i;
                    System.out.println("Loop ended at baris =" + i);
                    break; // break loop
                }
            }
            // kalau nilai cell "founded" blank
            catch (NullPointerException npe) {
                // kalau nilai cell "founded" blank
                System.out.println(" checked =" + checked.toString().trim());
                
            }

        } // end loop

        int finalRowToWrite = 0;// ini nomor baris yang akan mulai ditulisi
    
        for (int i = (startingRowInACategory + 1); i < 100; i++) {

            if (mySheet.getRow(i).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).toString()
                    .equalsIgnoreCase("")) {
                finalRowToWrite = i;
                //System.out.println("finalRowToWrite = " + finalRowToWrite);
                break; // break loop
            }
        }
        
        fis.close();

        return finalRowToWrite; // end reading close input stream
    }

    public static void main(String[] args) throws IOException, Exception {
         String tempTask = "nangla";
         long start = System.nanoTime();

        ZipSecureFile.setMinInflateRatio(0.0);
        long end = System.nanoTime();
        long duration = end - start;
        duration = TimeUnit.SECONDS.convert(duration, TimeUnit.NANOSECONDS);
        System.out.println("Durasinya Zipsecure = "+duration+" seconds");
        
        long start2 = System.nanoTime();
        WriteExcelUtility.excelInputFileForBacklog = "D:\\DIR.xlsx";
        WriteExcelUtility.constructInputStreamForBacklogForm(0);
        long end2 = System.nanoTime();
        long duration2 = end2 - start2;
        duration = TimeUnit.SECONDS.convert(duration2, TimeUnit.NANOSECONDS);
        System.out.println("Durasinya constructInputStreamForBacklogForm = "+duration+" seconds");
        
        
        int rowtowrite = WriteExcelUtility.getStartingRowToWriteBacklog(WriteExcelUtility.excelInputFileForBacklog, 0,
                "Engine compartment");
        System.out.println("Mulai penulisan pada row = "+ rowtowrite);
        WriteExcelUtility.updateDataLogback(3, rowtowrite, 2, tempTask); 
         String fileNameBacklogtosavetointernal = "D:\\DIR1.xlsx";
         WriteExcelUtility.constructOutputStream(fileNameBacklogtosavetointernal);
        
         long start3 = System.nanoTime();
         WriteExcelUtility.writeAndCloseALLStream();
         long end3 = System.nanoTime();
            long duration3 = end3 - start3;
            //duration = TimeUnit.SECONDS.convert(duration3, TimeUnit.NANOSECONDS);
            System.out.println("Duration writeAndCloseALLStream = "+(Double.valueOf (duration3/ 1000000))/1000+" s");
        
    }

}

The ouput :

Durasinya Zipsecure = 0 seconds
excelInputFileForBacklog = D:\DIR.xlsx
sheetnya = Backlog 14 0416
Durasinya constructInputStreamForBacklogForm = 2 seconds
 checked =ENGINE COMPARTMENT & ACCESSORIES
Loop ended at baris =14
Mulai penulisan pada row = 33
excelInputFileForBacklog =D:\DIR.xlsx
excelOutputFileForBacklog =null
excelOutputFileD:\DIR1.xlsx
Duration writeAndCloseALLStream = 40.024 s

Answer 1

After struggling for about a week. Modifying so many stuff and almost losing hope to finish this software on time. I just realized that this doc itself is the source of the problems. It was originally created by some ERP SAP softwares. During the creation of the doc I believe the SAP app generated the xmls with some codes that aren't really compatible with the POI. Or, they are compatible but causing POI takes a long time to adapt to some constraints. Hence, the slow generation. To tackle this, I get no choice but to copy the data to a new workbook to process any data on the workbook. But then I have to copy-paste all the data on 25 sheets of the workbook. But that's fine. Afterall, my workbook generation time tooks only 0.337 seconds (drops from 40 seconds) on the new workbook.