You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mahmood Naderan <nt...@yahoo.com> on 2017/05/03 15:40:38 UTC
POI memory usage for 4MB exce file is high
Hi
I used the Apache POI to read an excel file. The file has 16000 rows and 50 columns.
Each cell is read as a string and they are put into a 2D array
(e.g. a row is a string array with size 50 and 16000 rows are put in another array).
the code looks like
try (FileInputStream fIP = new FileInputStream(selectedFile)) {
XSSFWorkbook wb = new XSSFWorkbook(fIP);
XSSFSheet ws = wb.getSheetAt(0);
maxRows = ws.getLastRowNum() + 1;
maxColumns = ws.getRow(0).getLastCellNum();
theRows = new OneRow[maxRows];
Iterator< Row > rowIt = ws.iterator();
XSSFRow row;
int i = 0;
long chs = 0;
while ( rowIt.hasNext() ) {
row = (XSSFRow) rowIt.next();
Iterator< Cell > cellIt = row.cellIterator();
String [] str = new String[maxColumns];
int j = 0;
long ch = 0;
while ( cellIt.hasNext() ) {
Cell cell = cellIt.next();
str[ j ] = cell.getStringCellValue();
ch += str[ j ].length(); // sum one row's length
System.out.print( str[ j ].length() + " " );
++j;
}
System.out.print( "\n ->" + ch + "\n" );
chs += ch; // sum total characters length
theRows[ i ] = new OneRow( maxColumns );
theRows[ i ].add( str );
++i;
}
System.out.print( "\n ==>" + chs + "\n" );
}
the program also counts the length of each cell, ch and the size of all
rows are updated with chs += ch. Then the total number of characters are chs.
The value is 3,230,798 (3M chars) but the used heap is about 1GB. That means a
character is consuming 330 bytes!! (more or less).
Using the profilers, I see that needs a lot of heap and I have to increase
the heap size to 2048. Also the cpu time is devoted to GC which is undesirable.
I have to say that XSSFSheet ws = wb.getSheetAt(0); is the most time and
memory consuming part. In order to investigate that, I manually count the data
structure sizes with ch and chs.
Please see the profiler at https://i.stack.imgur.com/ZfLHj.jpg
In the memory chart and from left, the first peak is where it finishes
loading the excel file.
Regards,
Mahmood
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI memory usage for 4MB exce file is high
Posted by Mahmood Naderan <nt...@yahoo.com.INVALID>.
Thanks. I am new to that and have not used Maven yet. I have downloaded the jar file
https://github.com/monitorjbl/excel-streaming-reader/releases/download/1.1.0/xlsx-streamer-1.1.0.jar
and put that in the project folder. It is said to add
<dependencies>
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>1.0.1</version>
</dependency>
</dependencies>
to the pom.xml. I see one pom.xml in the project folder which looks like
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.scu</groupId>
<artifactId>testmaven</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
</project>
where should I add those lines? in the properties tag?
Regards,
Mahmood
On Wednesday, May 3, 2017 8:34 PM, Andreas Reichel <an...@manticore-projects.com> wrote:
Mahmood,
that was discussed many times already: XSSFWorkbook is a memory hog as it holds the whole information in a DOM tree with a lot of strings. (Does Java 8 String Deduplication help on that, when you have enough cores?)
For cases like yours reading from a SXSSFWorkbook/stream will be more suitable. You also might have a look at: https://github.com/monitorjbl/excel-streaming-reader eventually.
Best regards
Andreas
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI memory usage for 4MB exce file is high
Posted by Andreas Reichel <an...@manticore-projects.com>.
Mahmood,
that was discussed many times already: XSSFWorkbook is a memory hog as it holds the whole information in a DOM tree with
a lot of strings. (Does Java 8 String Deduplication help on that, when you have enough cores?)
For cases like yours reading from a SXSSFWorkbook/stream will be more suitable. You also might have a look at: https://g
ithub.com/monitorjbl/excel-streaming-reader eventually.
Best regards
Andreas
On Wed, 2017-05-03 at 15:40 +0000, Mahmood Naderan wrote:
> Hi
>
> I used the Apache POI to read an excel file. The file has 16000 rows and 50 columns.
>
>
> Each cell is read as a string and they are put into a 2D array
>
>
> (e.g. a row is a string array with size 50 and 16000 rows are put in another array).
>
>
> the code looks like
>
>
>
> try (FileInputStream fIP = new FileInputStream(selectedFile)) {
> XSSFWorkbook wb = new XSSFWorkbook(fIP);
> XSSFSheet ws = wb.getSheetAt(0);
> maxRows = ws.getLastRowNum() + 1;
> maxColumns = ws.getRow(0).getLastCellNum();
> theRows = new OneRow[maxRows];
> Iterator< Row > rowIt = ws.iterator();
> XSSFRow row;
> int i = 0;
> long chs = 0;
> while ( rowIt.hasNext() ) {
> row = (XSSFRow) rowIt.next();
> Iterator< Cell > cellIt = row.cellIterator();
> String [] str = new String[maxColumns];
> int j = 0;
> long ch = 0;
> while ( cellIt.hasNext() ) {
> Cell cell = cellIt.next();
> str[ j ] = cell.getStringCellValue();
> ch += str[ j ].length(); // sum one row's length
> System.out.print( str[ j ].length() + " " );
> ++j;
> }
> System.out.print( "\n ->" + ch + "\n" );
> chs += ch; // sum total characters length
> theRows[ i ] = new OneRow( maxColumns );
> theRows[ i ].add( str );
> ++i;
> }
> System.out.print( "\n ==>" + chs + "\n" );
> }
>
>
>
> the program also counts the length of each cell, ch and the size of all
>
>
> rows are updated with chs += ch. Then the total number of characters are chs.
>
>
> The value is 3,230,798 (3M chars) but the used heap is about 1GB. That means a
>
>
> character is consuming 330 bytes!! (more or less).
>
>
>
> Using the profilers, I see that needs a lot of heap and I have to increase
>
>
> the heap size to 2048. Also the cpu time is devoted to GC which is undesirable.
>
>
>
> I have to say that XSSFSheet ws = wb.getSheetAt(0); is the most time and
>
>
> memory consuming part. In order to investigate that, I manually count the data
>
>
> structure sizes with ch and chs.
>
>
>
>
> Please see the profiler at https://i.stack.imgur.com/ZfLHj.jpg
>
> In the memory chart and from left, the first peak is where it finishes
>
>
> loading the excel file.
>
>
>
> Regards,
> Mahmood
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>