Excel with more than 300 thousand lines

Asked

Viewed 510 times

1

Good evening, I have a problem with Excel.

The spreadsheet has more than 300 thousand lines and when I run the application with 30 thousand lines I have no problem.

The error only happens with files that has many lines.

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

I am using the POI and searching found on SXSSF, but I could not play opening a spreadsheet.

package br.com.felipebrandao.prjExcel.util;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.model.ConvertAnchor;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import br.com.felipebrandao.prjExcel.jdbc.ConnectionFactory;

public class LendoColunasNumLoopPorLinhas {
    private String path;
    private static Connection connection;

    public static void main(String[] args) throws SQLException {
        connection = new ConnectionFactory().getConnection();
        System.out.println("Conexão aberta!");

        Integer linha = 0;
        try {
            File fi = new File("E:\\Projeto\\20102016.xlsx");
            XSSFWorkbook wb = new XSSFWorkbook(fi);
            XSSFSheet s = wb.getSheetAt(0);
            for (Row rowFor : s) {
                if (rowFor.getRowNum() != 0) {
                    //String sql = "insert into lancamentos (companyCode, account, assignment, documentHeaderText, documentDate, postingDate) values(?,?,?,?,?,?)";
                    //PreparedStatement stmt = connection.prepareStatement(sql);
                    System.out.println(rowFor.getCell(0).getStringCellValue());//companyCode
                    System.out.println(rowFor.getCell(1).getStringCellValue());//account
                    System.out.println(rowFor.getCell(2).getStringCellValue());//assignment
                    System.out.println(rowFor.getCell(3).getStringCellValue());//documentHeaderText
                    System.out.println(rowFor.getCell(4).getStringCellValue());//documentDate -date
                    System.out.println(rowFor.getCell(5).getStringCellValue());//postingDate -date
                    //stmt.execute();
                    //stmt.close();
                    linha++;
                    System.out.println(linha);
                }
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(linha);
        connection.close();
    }
}
  • 2

    Have you tested the solution of the other question? http://answall.com/a/27272/28595

  • Hello, Felipe. Welcome to [pt.so]! POI uses a lot of memory because it carries virtually the entire file in memory. As mentioned in the other linked question, perhaps an increase in the available memory for the JVM will solve the problem. You also need to check if your program keeps objects in memory unnecessarily. If you don’t know how to analyze this, post the full code on your question and I can help. Also explain in what context this code performs, whether it is an isolated program or an application that does more things, because in this case you may need to change the solution. Hug!

  • Luiz, I put all the code, my idea is to import this spreadsheet to a database, do the processing of the values that comes from it and pass to the bank.

  • 1
  • I think the problem is the way you are using Excel. It should not be forgotten that Excel was not designed to be a database and to have so much information. There are many databases, including Access, that allow you to work with 300,000 records without blinking.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.