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();
}
}
Have you tested the solution of the other question? http://answall.com/a/27272/28595
– user28595
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!
– utluiz
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.
– Felipe Brandão
Possible duplicate of Problem reading file . xlsx too big in Java
– Fagner Fonseca
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.
– Isac