CODEWITHSUNDEEP
javaexcelapache-poi
vijayk
vijayk

Reputation: 2753

How to read big .xls files in java?

10 to read xls files in java.
But When I am going to read big .xls files like above 20 mb that time it gives me error.
Following my code is running properly for small .xls file but gives java heap error for big .xls file. Java code-

public static void main(String[] args) throws IOException {
        ArrayList<ArrayList<String>> Temp = new ArrayList<ArrayList<String>>();
        ArrayList<String> Temp1 = new ArrayList<String>();
        int row = 0;
        String fname = "D:/Vijay/xls/vijay/bookTest.xls";
        try {
            InputStream fis = new FileInputStream(fname);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheetAt(0);
            FormulaEvaluator formulaEval = workbook.getCreationHelper().createFormulaEvaluator();
            int rowEnd = sheet.getLastRowNum();
            int rowStart = sheet.getFirstRowNum();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row r = sheet.getRow(rowNum);
                int lastColumn = r.getLastCellNum();

                int cols = 0;
                Temp1 = new ArrayList<String>();
                for (int cn = 0; cn < lastColumn; cn++) {
                    String cellvalue = "";
                    Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                    if (c == null) {
                        cellvalue = "";
                    } else {
                        if (r.getCell(cn).getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            cellvalue = r.getCell(cn).getStringCellValue();
                        } else if (r.getCell(cn).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            if (HSSFDateUtil.isCellDateFormatted(r.getCell(cn))) {
                                DateFormat formatter = new SimpleDateFormat(
                                        "E MMM dd HH:mm:ss Z yyyy");
                                Date date = (Date) formatter.parse(r
                                        .getCell(cn).getDateCellValue()
                                        .toString());
                                Calendar cal = Calendar.getInstance();
                                cal.setTime(date);
                                cellvalue = cal.get(Calendar.DATE) + "/"
                                        + (cal.get(Calendar.MONTH) + 1) + "/"
                                        + cal.get(Calendar.YEAR);
                            } else {
                                r.getCell(cn).setCellType(
                                        r.getCell(cn).CELL_TYPE_STRING);
                                cellvalue = ""
                                        + r.getCell(cn).getStringCellValue();
                            }
                        } else if (r.getCell(cn).getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                            cellvalue = ""
                                    + r.getCell(cn).getBooleanCellValue();
                        } else if (r.getCell(cn).getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                            cellvalue = ""
                                    + formulaEval.evaluate(r.getCell(cn))
                                            .formatAsString();
                        }

                    }
                    Temp1.add(cols, cellvalue);
                    cols++;
                }
                if (Temp1.size() > 0) {
                    Temp.add(row, Temp1);
                    row++;
                }
            }
             for (ArrayList al : Temp) {
             System.out.println("Contents of temp " + al);
             }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

Error-

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at java.util.LinkedHashMap.createEntry(Unknown Source)
    at java.util.LinkedHashMap.addEntry(Unknown Source)
    at java.util.HashMap.put(Unknown Source)
    at sun.util.resources.OpenListResourceBundle.loadLookup(Unknown Source)
    at sun.util.resources.OpenListResourceBundle.loadLookupTablesIfNecessary(Unknown Source)
    at sun.util.resources.OpenListResourceBundle.handleGetObject(Unknown Source)
    at sun.util.resources.TimeZoneNamesBundle.handleGetObject(Unknown Source)
    at java.util.ResourceBundle.getObject(Unknown Source)
    at java.util.ResourceBundle.getObject(Unknown Source)
    at java.util.ResourceBundle.getStringArray(Unknown Source)
    at sun.util.TimeZoneNameUtility.retrieveDisplayNames(Unknown Source)
    at sun.util.TimeZoneNameUtility.retrieveDisplayNames(Unknown Source)
    at java.util.TimeZone.getDisplayNames(Unknown Source)
    at java.util.TimeZone.getDisplayName(Unknown Source)
    at java.util.Date.toString(Unknown Source)
    at com.test.arrayList.ValidateXls.main(ValidateXls.java:69)

Please help me to solve this problem or please suggest me another way to read big .xls file in java.
thanks in advance.

Upvotes: 0

Views: 551

Answers (1)

m.hassaballah
m.hassaballah

Reputation: 250

I think the first thing you need to try is to increase the java default heap space. for example: -Xms256m -Xmx512m -XX:PermSize=64M -XX:MaxPermSize=1000M

also you need to change the loading of file to be like this ( WorkbookFactory.create(new File("MyExcel.xls"))) according to poi documentation see this link

http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

Files vs InputStreams

When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx XSSFWorkbook, the Workbook can be loaded from either a File or an InputStream. Using a File object allows for lower memory consumption, while an InputStream requires more memory as it has to buffer the whole file.

If using WorkbookFactory, it's very easy to use one or the other:

// Use a file Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

// Use an InputStream, needs more memory Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

and if you still face the same exception try to use

XSSF and SAX (Event API)

http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

Upvotes: 4

Related Questions