CODEWITHSUNDEEP
javaexcelspringfileapache-poi
Joe
Joe

Reputation: 551

How to read large size excel file after uploading

Before posting I searched but not get the solution.

I have a larger excel file may be .xls/xlsx of > 10 MB. When I am reading small excel file then it reads ok. But when its large it says out of memory/heap. Some says to increase heap size but I think Its not a good solution. I am uploading excel file and reading as:

    byte b[] = file.getBytes();
    InputStream ips = new ByteArrayInputStream(b);
    Workbook workbook = WorkbookFactory.create(ips);
    Sheet sheet = workbook.getSheetAt(0);
    // ============
    int i = 0;
    List<String> colName = new ArrayList<>();
    Map<Integer, Object> lhm = null;

    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext())
    {
        lhm = new LinkedHashMap<>();
        Row row = rowIterator.next();
        // For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext())
        {
            Cell cell = cellIterator.next();
            // Check the cell type and format accordingly
            switch (cell.getCellType())
            {
            case Cell.CELL_TYPE_NUMERIC:
                // System.out.print(cell.getNumericCellValue() + "--");
                if (DateUtil.isCellDateFormatted(cell))
                {
                    lhm.put(cell.getColumnIndex(), Utils.getDateStringFromString(cell.getDateCellValue().toString(), "yyyy-MM-dd"));

                } else
                {
                    lhm.put(cell.getColumnIndex(), String.valueOf(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_STRING:
                if (i == 0)
                {
                    colName.add(cell.getStringCellValue());
                } else
                {
                    // System.out.print(cell.getStringCellValue() +
                    // "==");
                    lhm.put(cell.getColumnIndex(), cell.getStringCellValue());

                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                // System.out.print(cell.getBooleanCellValue() + "--");
                lhm.put(cell.getColumnIndex(), String.valueOf(cell.getBooleanCellValue()));
                break;

            }

        }

This code not work for large excel file. What would be the solution for both xls/xlsx file. I am using apache POI API.

Upvotes: 1

Views: 2752

Answers (1)

centic
centic

Reputation: 15872

If the file can become really huge and may always exceed your available memory, you can take a look at the streaming-APIs in Apache POI, e.g. look at https://poi.apache.org/spreadsheet/how-to.html#event_api

It comes with an ready-to-run example.

For .xlsx/XSSF formatted files there is a similar way which provides the data in the Workbook in an even nicer way, see https://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

Upvotes: 1

Related Questions