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 have many lines.
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
I'm using the POI and searching I found about SXSSF, but I could not reproduce by 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();
}
}