You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by peruzzo <m....@yahoo.com> on 2013/03/25 12:35:23 UTC
Apache POI works but it uses too RAM: is it a limit of your
library?
I am using Apache POI to create two methods in java like these:
http://www.mathworks.it/it/help/matlab/ref/xlswrite.html
http://www.mathworks.it/it/help/matlab/ref/xlsread.html
They work but I can not write bit excel files because your library write on
RAM before to write into files.
Can I improve my methods? I would like to write big matrices without using
too RAM.
*See you and thanks for support*
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
I wrote an example for you and I would like to ask you if there is a
different method to use your library because the following system uses too
ram and I must use the code *-Xms256m -Xmx3584m* but it makes instable the
operative system.
*Answer me please!*
/THE CODE:/
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
int a = 10;
int b = 10;
Double[][] d = new Double[a][b];
Double m = 1001d;
for (int i = 0; i < d.length; i++) {
for (int j = 0; j < d[0].length; j++) {
d[i][j] = m;
}
}
String nomefoglio = "z.xlsx";
String nomefile = "z.xlsx";
try {
FileInputStream file = new FileInputStream(new File(nomefile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheet(nomefoglio);
int rownum = 0;
int cellnum = 0;
for (int i = 0; i < a; i++) {
Row row = sheet.createRow(rownum++);
for (int j = 0; j < b; j++) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue((Double) d[i][j]);
}
cellnum = 0;
}
file.close();
FileOutputStream outFile = new FileOutputStream(new File(nomefile));
workbook.write(outFile);
outFile.close();
} catch (IOException e) {
e.printStackTrace();
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712444.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Thanks, I found a method on your web site but I don't understand like I can
use SXSSF and I am afraid to have to rewrite all my previously code (it has
many rows of code). For example, like can I use this following code? I don't
see any input array! Could you give to me an example of writing and
rewriting please?
public static void metodo() throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
// exceeding rows will be
// flushed to disk
Sheet sh = wb.createSheet();
for (int rownum = 0; rownum < 1000; rownum++) {
Row row = sh.createRow(rownum);
for (int cellnum = 0; cellnum < 10; cellnum++) {
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for (int rownum = 0; rownum < 900; rownum++) {
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for (int rownum = 900; rownum < 1000; rownum++) {
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712446.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Ok, thanks!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712454.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Your code is a very good work! Thank you very much!
Wawwwwww!!! Now I can write and read without problem!!!
Yessss!
Thanks!
My claps are very few for your program!
Yessss!
Thanks!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712561.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Like HTML, XML is a markup language that defines a set of rules. These rules
render a document both human and machine readable. Unlike HTML however, the
rules are defined almost completely by the designer of the document.
An XML document consists of, crudely, a series of elements. Typically, each
element has a start tag and a matching end tag, although an empty element
can simply consist of an empty element tag. The elements content will be
found between the tags and it will consist of other markup such as child
elements or character data.
Elements may have attributes which consist of a name/value pair. The name
and value will be separated by the equals sign (=) and the value will be
enclosed by speech marks (").
Knowing this, consider the following fragment of markup;
<row r="1" spans="1:1">
<c r="A1" t="s">
<v>30</v>
</c>
</row>
It represents a single row on an Excel worksheet. The row has an opening tag
with the name 'row' and a matching closing tag. It contains one child
element which defines the markup for a single cell. Note that the name of
the cell tag is 'c' and that there are two attributes defined within the
opening tag, r and t. The r attribute has the value A1 and this tells us
that the markup relates to cell A1 on the worksheet while the 't' attribute
has the value 's' and this actually tells us that the cell contains a
string. The c (cell) element is a child element of the row element and it
has child elements of its own. In this case, the c element has a child
element with the name 'v' and this element simply contains the value that
will appear in the cell.
A parser analyses the contents of a file, in this case, and converts it into
a form that can be further manipulated. In order to make the process as
flexible as possible, it is possible to create handlers that define how the
conversion should take place. Using a technique referred to as a callback,
the parser will reference methods defined within the handler to process the
contents of the file.
Referring to the event example, the SheetHandler class contains the methods
the parser will call as it reads the contents of the Excel workbook. When
the parser encounters a start element tag, it will call the startElement()
method and pass to it information such as the name of the tag, the
document's namespace and any attributes that were defined in the start
element tag. When the parser encounters an end element tag, it will call the
endElement() method of the handler and pass a similar collection of
parameters to it - with the exception of the list of attributes. If an
element contains character data, then the parser will call the handler's
characters() method and pass it it an arry containing the elements character
data along with the index of the first character in the array and the length
of the arrays contents.
Knowing this, it is easy to see how the fragment of XML above could be
parsed and the sequence of calls that are made to the handler;
1. A call will be made to the startElement() and while values will be passed
to all four parameters, we are only interetsted in the name and attributes
parameters values here. As the parser has found a row element, ''row" will
be passed to the name parameter and the 'r' and 'spans' attributes to the
'attributes' parameter. The code we write in the startElement() method will
need to react to this information and, in this case, we will create a new
ArrayList to hold the values recovered from the cells on the row.
2. Another call will be made to the startElement() method and, this time,
the value 'c' will be passed to the name parameter and the attributes r and
to to the attributes parameter. Again, we must write code to interpret and
react to this and the first thing to do is to check the type of the cell and
this is done, in part, because strings may be handled in a special way, they
may be written into the SharedStringsTable (more of that later).
3. Another call is made to the startElement() method and the value 'v' will
be passed to the name parameter, informing us that this element holds the
cells value. But we do not yet have access to that value, we must wait until
the parser has read it from the element and this will be accomplished by a
call to the characters() method. The parser will make this call and pass an
array - of type char - that simply contains the characters '3' and '0'.
4. A call will now be made to the endElement() method and the value 'v' will
be passed to the name parameter. By checking the value of the name
parameter, and because we know this elements parent is a cell - c - element,
that we have the cells value to hand. We must though make one further check
because the true contents of the cell could be held in the Shared Strings
Table if we are dealing with a cell of type 's'. If we are dealing with a
string then we use the integer value the cell held - int this case 30 - to
index into the Shared String Table and retrieve the actual value displayed
in the cell. Once we have the cells value, we can store that away into the
ArrayList.
5. A call is made to the endElement() method and the value 'c' is passed to
the name parameter. We do not need to react to the closing cell element tag
in this case.
6. A call is made to the endElement() method and the value 'row' is passed
to the name parameter and we could choose to react to this. When we
encounter an end element with the name of 'row', we know that we have
recovered the contents from every cell on a single row. Now would be the
ideal time to convert the ArrayList into an array of type Object and to
store that into the sheetData ArrayList.
To make life a little more straightforward for you, let's take the
SheetHandler class out of the ExampleEventUserModel class and make it into a
stand-alone class, a little like this;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import java.util.List;
import java.util.ArrayList;
class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private Object[][] sheetData = null;
private List<String> rowData = null;
private List<Object[]> tempSheetData = null;
/**
* Create an instance of the SheetHandler class using the following
* parameters
*
* @param sst An instance of the SharedStringsTable class that
* encapsulates the workbook's shared strings table.
*/
public SheetHandler(SharedStringsTable sst) {
this.sst = sst;
this.rowData = new ArrayList<String>();
this.tempSheetData = new ArrayList<Object[]>();
}
/**
* Called whenever the parser encounters the start tag of an element
* within the xml markup.
*
* @param uri An instance of the String class that will encapsulate the
* name of the Namespace. It will be empty if namespaces are not
* used.
* @param localName An instance of the String class that will
* encapsulatethe local name for the element.
* @param name An instance of the String class that will encapsulate the
* qualified name for the element.
* @param attributes An instance of the Attributes class that will
* encapsulate any of the element's attributes. Empty if there
* are none.
* @throws SAXException Thrown if a problem is encountered parsing the
* xml markup.
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// If the element marks the start of the markup for a cell...
if (name.equals("c")) {
// Figure out if the value is an index in the SST set a flag
// to indicate that the Shared Strings Table should be
// interrogated for the cells contents.
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
/**
* Called when the parser encounters the end element tag within the
* xml markup.
*
* @param uri An instance of the String class that will encapsulate the
* name of the Namespace. It will be empty if namespaces are not
* used.
* @param localName An instance of the String class that will
* encapsulatethe local name for the element.
* @param name An instance of the String class that will encapsulate the
* qualified name for the element.
* @throws SAXException Thrown if a problem is encountered parsing the
* xml markup.
*/
public void endElement(String uri, String localName, String name)
throws SAXException {
// This is the end of the row so convert the ArrayList into an array
of
// type Object
if(name.equals("row")) {
this.tempSheetData.add(this.rowData.toArray());
this.rowData = new ArrayList<String>();
}
else if(name.equals("v")) {
// This is the closing tag of a cells contents so handle that by
// getting the cells value from the SST if the cell held a
String
// and then store that value into the ArrayList
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new
XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// Add the cells contents to the row ArrayList.
this.rowData.add(lastContents);
}
}
/**
* Called to process the elements character data.
*
* @param ch An array of type character that contains the character data
* recovered from the element.
* @param start A primitive int whose value indicates the start position
* of the character data within the ch array.
* @param length A primitive int whose value indicates how many
* characters were read.
* @throws SAXException Thrown if a problem is encountered whilst
* parsing the xml markup.
*/
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
/**
* Called by the parser once processing of the document has been
completed,
* this method will simply create a multi-dimensional array of type
object
* which holds the contents of the sheet's cells. It will copy a
reference
* to that object into the sheetData variable thus making it available
to
* the class that created the handler.
*/
public void endDocument() {
this.sheetData = new Object[this.tempSheetData.size()][];
for(int i = 0; i < this.tempSheetData.size(); i++) {
this.sheetData[i] = this.tempSheetData.get(i);
}
}
/**
* Get the sheet's data.
*
* @return A multi-dimensional array of type Object whose elements
* encapsulate the contents of the sheet.
*/
public Object[][] getSheetData() {
return(this.sheetData);
}
}
Next, create another class that uses this handler to parse the xml markup
for a specific sheet in the workbook. It could look a little like this;
public class NewArrayExample {
/**
* Recover the contents the cells from a specific sheet from an Excel
workbook
* as elements in a multi-dimensional array.
*
* @param filename An instance of the String class that encapsulates the
name
* of and path to a valid Excel OOXML (.xlsx) file.
* @param sheetIndex A primitive int whose value indicates which sheet
in
* the workbook should be processed. Just as with XSSFWorkbook,
sheet
* indices begin at zero. This is to say that the first sheet in
the
* workbook will be at index 0, the second sheet at index 1, etc.
* @return A multi-dimensional array of type Object which should
encapsulate
* the contents of the cells found on the sheet. Each entry in
the
* array will itself by an array of type Object and each entry
in
* this array will be an Object that encapsulates the cells
value,
* as a String.
* @throws Exception
*/
public static Object[][] xlsxRead(String filename, int sheetIndex)
throws Exception {
String sheetName = null;
OPCPackage pkg = null;
XSSFReader reader = null;
SharedStringsTable sst = null;
XMLReader parser = null;
SheetHandler contentHandler = null;
InputStream sheet = null;
InputSource sheetSource = null;
pkg = OPCPackage.open(filename);
reader = new XSSFReader(pkg);
sst = reader.getSharedStringsTable();
contentHandler = new SheetHandler(sst);
parser = fetchSheetParser(contentHandler);
// The name of the sheet can be either 'rIDn' or 'rSheetn' where n
is the
// number of the sheet. Test, firstly to see if the sheets name is
// 'rIdn' here. If that fails....
sheetName = ("rId" + (sheetIndex + 1));
sheet = reader.getSheet(sheetName);
if(sheet != null) {
processSheet(sheet, parser);
}
else {
// ...then check to see if the sheets name is 'rSheetn'. If this
fails
// throw an exception. If it suceeds, process the sheet.
sheetName = ("rSheet" + (sheetIndex + 1));
if(sheet != null) {
processSheet(sheet, parser);
}
else {
throw new IllegalArgumentException("No sheet exists in the "
+ "workbook with the index specified: " +
sheetIndex);
}
}
// Get the sheets contents and return to the caller.
return(contentHandler.getSheetData());
}
/**
* Process (parse) the sheet recovered from the Excel workbook.
*
* @param sheet An instance of the InputStream class 'attached' to the
* sheet's xml markup and through which the parser can access it
* contents.
* @param parser An instance of the XMLReader class that encapsulates
the
* necessary logic to both parse the xml markup and interpret
what
* the parser finds (the SheetHandler).
* @throws IOException
* @throws SAXException
*/
private static void processSheet(InputStream sheet, XMLReader parser)
throws IOException, SAXException {
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
/**
* Get an XMLReader to handle parsing of the workbook's xml markup.
*
* @param contentHandler An instance of the SheetHandler class that
* encapsulates the logic to interpret the sheet's xml markup.
* @throws SAXException
*/
private static XMLReader fetchSheetParser(ContentHandler contentHandler)
throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser");
parser.setContentHandler(contentHandler);
return parser;
}
}
and it would be called like this;
Object[][] sheetData = NewArrayExample.xlsxRead("C:/temp/Book2.xlsx", 1);
Obviously, you would change the values appsed to the parameters of the
xlsxRead() method to suit your own needs. Remember that the sheet number is
zero based. Passing 0 will process the first sheet in the workbook, passing
1 will process the second sheet, etc.
This really is far more than you should expect from a list which has a very
specific purpose, to assist people in using the POI api. You really should
look into parsers, callbacks and the structure of the files markup yourself
- none of these are specific nor unique to POI. This is the last time I can
offer help to you I am afraid because writing so much code and explaining
all of these details is very time consuming.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712543.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Ok, thanks for the support, and I look forward to great enthusiasm. I always
dreamed to interagine with Excel using Java and I'm sure you'll find a
solution. I do not want to rush, take your time and I'm sorry if sometimes
I'm too nagging.
*Thanks for all*
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712540.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
This is not really the place to ask questions about parsers and callbacks but
I will try to explain. Just give me a little time to put something together.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712538.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
There are too many objects that are a part of your library that I do not know
how to manipulate. It 's really too complicated for me. I step whole days
before this class.
I tried to make this change but it does not work and /I'm not sure it's fair
to use twice ArrayList/. What you see below is not working:
package xls;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
public class ArrayExample {
private ArrayList<ArrayList<Object>> dataArray = null;
public ArrayExample(int rows, int cols) {
this.dataArray = new ArrayList<ArrayList<Object>>();
}
public void processOneSheet(String filename, String sheetname)
throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = r.getSheet(sheetname);
if (sheet2 != null) {
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
} else {
throw new IllegalArgumentException("No sheet exists in the "
+ "workbook with the name specified: " + sheetname);
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst, this.dataArray);
parser.setContentHandler(handler);
return parser;
}
public Object[][] getDataArray() {
return (this.dataArray);
}
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private Object[][] dataArray = null;
private CellReference cellRef = null;
private SheetHandler(SharedStringsTable sst, Object[][] dataArray) {
this.sst = sst;
this.dataArray = dataArray;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (name.equals("c")) {
cellRef = new CellReference(attributes.getValue("r"));
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
lastContents = "";
}
public void endElement(String uri, String localNamem, String name)
throws SAXException {
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
nextIsString = false;
}
if (name.equals("v")) {
dataArray[cellRef.getRow()][cellRef.getCol()] = lastContents;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) {
ArrayList<ArrayList<Object>> ae = null;
ArrayList riga = new ArrayList();
ArrayList<ArrayList<Object>> foglio = new
ArrayList<ArrayList<Object>>();
try {
ae = new ArrayList<ArrayList<Object>>();
ae.processOneSheet("file.xlsx", "rId1");
Object[][] sheetData = ae.getDataArray();
Object[] rowData = null;
Object cellData = null;
for (int i = 0; i < sheetData.length; i++) {
rowData = sheetData[i];
for (int j = 0; j < rowData.length; j++) {
System.out.print(i + ", " + j);
cellData = rowData[j];
if (cellData != null) {
System.out.println("\t" + cellData.toString());
riga.add(cellData);
} else {
System.out.println("\tEmpty cell.");
}
}
foglio.add(riga);
riga = new ArrayList();
}
} catch (Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
int c = 0;
Iterator<ArrayList<Object>> itr = foglio.iterator();
while (itr.hasNext()) {
ArrayList element = itr.next();
element.size();
if (element.size() > c) {
c = element.size();
}
}
int r = foglio.size();
Object[][] matrice = new Object[r][c];
int i = 0;
int j = 0;
Iterator<ArrayList<Object>> itr2 = foglio.iterator();
Iterator itr3 = null;
while (itr2.hasNext()) {
itr3 = itr2.next().iterator();
while (itr3.hasNext()) {
matrice[i][j] = itr3.next();
System.out.println("matrice[" + i + "][" + j + "] = "
+ matrice[i][j]);
j++;
}
j = 0;
i++;
}
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712533.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You already have all of the code you need to accomplish this. All you need to
do is modify the code that has already been posted to this thread so that
you can call methods like xlsread() and xlswrite(). I will try to help you
but do have a go yourself please.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712532.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
/My goal is simple, it is this:/
*Double[][] u = new Double[100000][100];
for (int i = 0; i < u.length; i++) {
for (int j = 0; j < u[0].length; j++) {
u[i][j] = (double) (i - j);
}
}
xlswrite_performance("file.xlsx", u);
Object[][] U = xlsread("file.xlsx", 0);*
/ArrayExample ~ xlsread ~ It is shortly, it is almost what I would get ... I
hope you can still lend a hand ... In Italy "lend a hand" is a popular
saying, is there also in English?/
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712527.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
/Thanks for your help. It is pleasant to meet person like you into the web. I
begin to understand how to use your tool for reading files.
I don't understand these methods and I would like to ask you if you can
semplify them:/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
}
public void endElement(String uri, String localNamem, String name)
throws SAXException {
}
public void characters(char[] ch, int start, int length)
throws SAXException {
}
/uri, localNamem, name are not set! If I remove these three methods the
programme doesn't work so I think that it is possible to rewrite these three
methods more clearly.
I am very happy because I can got my matrix with ArrayList and Iterator but
I would like to remove 100000 and 100, is it possible too?
See my code please:/
package xls;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
public class ArrayExample {
private Object[][] dataArray = null;
public ArrayExample(int rows, int cols) {
this.dataArray = new Object[rows][cols];
}
public void processOneSheet(String filename, String sheetname)
throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = r.getSheet(sheetname);
if (sheet2 != null) {
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
} else {
throw new IllegalArgumentException("No sheet exists in the "
+ "workbook with the name specified: " + sheetname);
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst, this.dataArray);
parser.setContentHandler(handler);
return parser;
}
public Object[][] getDataArray() {
return (this.dataArray);
}
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private Object[][] dataArray = null;
private CellReference cellRef = null;
private SheetHandler(SharedStringsTable sst, Object[][] dataArray) {
this.sst = sst;
this.dataArray = dataArray;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (name.equals("c")) {
cellRef = new CellReference(attributes.getValue("r"));
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
lastContents = "";
}
public void endElement(String uri, String localNamem, String name)
throws SAXException {
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
nextIsString = false;
}
if (name.equals("v")) {
dataArray[cellRef.getRow()][cellRef.getCol()] = lastContents;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) {
ArrayExample ae = null;
ArrayList riga = new ArrayList();
ArrayList<ArrayList<Object>> foglio = new
ArrayList<ArrayList<Object>>();
try {
ae = new ArrayExample(100000, 100);
ae.processOneSheet("FOGLI_XLS_VARI/_testdata.xlsx", "rId1");
Object[][] sheetData = ae.getDataArray();
Object[] rowData = null;
Object cellData = null;
for (int i = 0; i < sheetData.length; i++) {
rowData = sheetData[i];
for (int j = 0; j < rowData.length; j++) {
System.out.print(i + ", " + j);
cellData = rowData[j];
if (cellData != null) {
System.out.println("\t" + cellData.toString());
riga.add(cellData);
} else {
System.out.println("\tEmpty cell.");
}
}
foglio.add(riga);
riga = new ArrayList();
}
} catch (Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
int c = 0;
Iterator<ArrayList<Object>> itr = foglio.iterator();
while (itr.hasNext()) {
ArrayList element = itr.next();
element.size();
if (element.size() > c) {
c = element.size();
}
}
int r = foglio.size();
Object[][] matrice = new Object[r][c];
int i = 0;
int j = 0;
Iterator<ArrayList<Object>> itr2 = foglio.iterator();
Iterator itr3 = null;
while (itr2.hasNext()) {
itr3 = itr2.next().iterator();
while (itr3.hasNext()) {
matrice[i][j] = itr3.next();
System.out.println("matrice[" + i + "][" + j + "] = "
+ matrice[i][j]);
j++;
}
j = 0;
i++;
}
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712526.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You seem more comfortable using arrays so here is a different approach that
employs that data structure;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
/**
* A simple example that shows how to use the event model to read a single
sheet
* from an OOXML based Excel workbook and store the contents of each cell
into
* a multi-dimensional array of type Object. Note that it is important to
ensure
* that the array is sized appropriately as no checks are made in the code
to
* ensure that there is space in it for the contents of a cell.
*
* Currently, the class will place the contents of each cell into an element
of
* a multi-dimensional array in a manner that reflects that cells location
on
* the worksheet. A worksheet that looks like this;
*
* |-------|-------|-------|-------|-------|
* | One | | Two | | |
* |-------|-------|-------|-------|-------|
* | | Three | | Four | |
* |-------|-------|-------|-------|-------|
* | | | | | |
* |-------|-------|-------|-------|-------|
* | | | Six | | |
* |-------|-------|-------|-------|-------|
*
* will produce an array that looks like this;
*
* {{"One", null, "Two", null, null},
* {null, "Three", null, "Four", null},
* {null, null, null, null, null},
* {null, null, "Six", null, null}}
*
* it would be a trivial task to modify the code if this behaviour was not
* required.
*
* @author Mark Beardsley
* @version 1.00 8th April 2013
*/
public class ArrayExample {
private Object[][] dataArray = null;
/**
* Create an instance of the ArrayExample class using the following
* parameters;
*
* @param rows A primitive int whose value will indicate how many rows
to
* allow space for. Note that it is critical to allow space to
* accomodate all rows on the sheet.
* @param cols A primitive int whose value will indicate how many
columns
* to allow space for. Note that it is critical to allow enough
space
* to accomodate all cells on every row on the sheet.
*/
public ArrayExample(int rows, int cols) {
this.dataArray = new Object[rows][cols];
}
/**
* Call this method to process one sheet within a specific Excel
workbook.
*
* @param filename An instance of the String class encapsulating the
path to
* and name of an Excel workbook file. Note this file must be an
OOXML
* format file.
* @param sheetname An instance of the String class that encapsulates
the
* name of a single sheet within the workbook. Typically, sheets
will
* be named either 'rIdn' or 'rSheetn' where n is a whole number
* value indicating the sheets position in the workbook. Thus,
you
* would pass either "rId1" or "rSheet1" to process the first
sheet
* in the workbook.
* @throws Exception
*/
public void processOneSheet(String filename, String sheetname) throws
Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = r.getSheet(sheetname);
if(sheet2 != null) {
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
else {
throw new IllegalArgumentException("No sheet exists in the "
+ "workbook with the name specified: " + sheetname);
}
}
/**
* Get an XMLReader to handle parsing of the workbook's xml markup.
*
* @param sst An instance of the SharedStringsTable class that
encapsulates
* the xml markup that defines the workbook's shared strings
table.
* @return An instance of the XMLReader class that may be used to handle
* parsing of the .xlsx file's xml markup.
* @throws SAXException
*/
public XMLReader fetchSheetParser(SharedStringsTable sst) throws
SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst, this.dataArray);
parser.setContentHandler(handler);
return parser;
}
public Object[][] getDataArray() {
return(this.dataArray);
}
/**
* Handlers use a mechanism called a 'callback' to provide the necessary
* functionality to interpret the xml markup contained within the
various
* sheetn.xml (where n is an integer that identifies the sheet, e.g.
* sheet1.xml)files in the OOXML archive. In simple terms, the parser
* will call the startElement method when it encounters the start
element tag
* within the markup and the endElement method when it encounters the
end
* element tag of that. We must write code in these various methods that
* determines how the element's content - if there are any - will be
handled.
*/
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private Object[][] dataArray = null;
private CellReference cellRef = null;
/**
* Create an instance of the SheetHandler class using the following
* parameters
*
* @param sst An instance of the SharedStringsTable class that
* encapsulates the workbook's shared strings table.
* @param bookData A three dimensional array of type Object that
will
* be used to hold the data recovered from the worksheet.
*/
private SheetHandler(SharedStringsTable sst, Object[][] dataArray) {
this.sst = sst;
this.dataArray = dataArray;
}
/**
* Called whenever the parser encounters the start tag of an element
* within the xml markup.
*
* @param uri An instance of the String class that will encapsulate
the
* name of the Namespace. It will be empty if namespaces are
not
* used.
* @param localName An instance of the String class that will
* encapsulatethe local name for the element.
* @param name An instance of the String class that will encapsulate
the
* qualified name for the element.
* @param attributes An instance of the Attributes class that will
* encapsulate any of the element's attributes. Empty if
there
* are none.
* @throws SAXException Thrown if a problem is encountered parsing
the
* xml markup.
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (name.equals("c")) {
// Firstly, get the cells address and store into the cellRef
// variable.
cellRef = new CellReference(attributes.getValue("r"));
// Figure out if the value is an index in the SST set a flag
// to indicate that the Shared Strings Table should be
// interrogated for the cells contents.
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
/**
* Called when the parser encounters the end element tag within the
* xml markup.
*
* @param uri An instance of the String class that will encapsulate
the
* name of the Namespace. It will be empty if namespaces are
not
* used.
* @param localName An instance of the String class that will
* encapsulatethe local name for the element.
* @param name An instance of the String class that will encapsulate
the
* qualified name for the element.
* @throws SAXException Thrown if a problem is encountered parsing
the
* xml markup.
*/
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new
XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
if (name.equals("v")) {
// Store the contents of the cell into the array. Note how
// the CellReference object is derefernced to provide the
// column and row indices for the cell.
dataArray[cellRef.getRow()][cellRef.getCol()] =
lastContents;
}
}
/**
* Called to process the elements character data.
*
* @param ch An array of type character that contains the character
data
* recovered from the element.
* @param start A primitive int whose value indicates the start
position
* of the character data within the ch array.
* @param length A primitive int whose value indicates how many
* characters were read.
* @throws SAXException Thrown if a problem is encountered whilst
* parsing the xml markup.
*/
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) {
ArrayExample ae = null;
try {
ae = new ArrayExample(600, 10);
ae.processOneSheet("C:/temp/Book2.xlsx", "rId1");
Object[][] sheetData = ae.getDataArray();
Object[] rowData = null;
Object cellData = null;
for(int i = 0; i < sheetData.length; i++) {
rowData = sheetData[i];
for(int j = 0; j < rowData.length; j++) {
System.out.print(i + ", " + j);
cellData = rowData[j];
if(cellData != null) {
System.out.println("\t" + cellData.toString());
}
else {
System.out.println("\tEmpty cell.");
}
}
}
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712509.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
The ExampleEventUIserModel class contains a private, local, variable called
bookData. This is the List variable that will be used to hold all of the
data recovered from the workbook. All you need to do is to modify the
ExampleEventUserModel class by adding a single method like this;
public List<List> getBookData() {
return(this.bookData);
}
and then simply add a call to that method after you have processed all of
the sheets in the workbook;
package xls;
public class LeggiXLSX {
public static void main(String[] args) throws Exception {
ExampleEventUserModel howto = new ExampleEventUserModel();
howto.processAllSheets("file.xlsx");
List<List> cellContents = howto.getBookData();
}
}
that will return a List that holds the contents of all of the cells from
every sheet in the workbook.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712500.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
I understand your discourse but I can not find a method to take the ArrayList
that includes the ArrayList of cells.
package xls;
public class LeggiXLSX {
public static void main(String[] args) throws Exception {
ExampleEventUserModel howto = new ExampleEventUserModel();
howto.processAllSheets("file.xlsx");
}
}
howto is void and it has not return of ArrayList.
If I understand like to have ArrayList objects perhaps I can use the
following code:
ArrayList al = new ArrayList();
Object ia[] = new Object[al.size()];
ia = al.toArray(ia);
You send me to a big code but I can only interact on part following and I do
not understand how:
ExampleEventUserModel howto = new ExampleEventUserModel();
howto.processAllSheets("file.xlsx");
I have to find the unit of a sheet: *the cell*. When I find the cell I can
use "ArrayList" and "While" to read the sheet or sheets.
*I don't understand your help and what I must do...*
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712499.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
/this.rowData.add(lastContents); /
/this.raiwData/ is a variable that holds a reference to an ArrayList while
/lastContents/ is a String variable that is used to hold the contents of the
cell as it is parsed. Calling the /add()/ method on the ArrayList object
stores a reference to a specific String in an element of the ArrayList; it
stores the cells contents into the ArrayList.
There is nothing preventing you from using an arry and it would be a trivial
task to modify the code to do just this. The problem with using arrays is
that you need to specify the size of the array at the time you create it in
your code and what if you are wrong? If you do not allow enough space for
the contents of the workbook then you risk either taking a huge performance
hit while you resize the array or your code will throw exceptions when it
tries to write to an element that does not exist. Lists allow you to
sidestep this problem because they have the ability to resize themselves but
they are not a prefect solution. It may be the case that you wish to store
the contents of cell B3 into the third element of row two and the code I
wrote using Lists will not do this. But you could. There is a class that can
take the cell address and convert it into row and column co-ordinates. These
would allow you to store the cells contents into a very specific position in
an multi-dimensional array if you wished to do this.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712498.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
*Thanks for your help!* List is a good thing but I don't find into your code
"-" symbol. I find only this code lines:
System.out.println("New sheet:");
System.out.println("[" + cellIter.next() + "]");
I don't find the value of cell that I can put in a dynamic array as
recommended by you.
I am reading your code...
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712497.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
There is much code in this last class that I can not use and there is a
simple method without return (*it is only void type!*).
*(1)* How can I do to save the content of the file into an Object matrix?
*(2)* Why "howto.processOneSheet("filename.xlsx")" does not work?
Esample:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
*INPUT*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
import org.apache.poi.xssf.eventusermodel.examples.FromHowTo;
public class LeggiXLS_Prova {
public static void main(String[] args) throws Exception {
FromHowTo howto = new FromHowTo();
*howto.processOneSheet("filename.xlsx");* ///Doesn't work!!!/
howto.processAllSheets("filename.xlsx");
}
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
*OUTPUT*
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
Processing new sheet:
A1 - 2
B1 - 2
C1 - 2
A2 - 2
B2 - 2
C2 - 2
A3 - 2
B3 - 2
C3 - 2
Processing new sheet:
Processing new sheet:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712494.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
*I am sorry, I am a stupid.* /I was wrong by downloading the zip file, the
file is right at the bottom of the page! I am sorry!
I tryed class ExampleEventUserModel and it is very good!!! Waww!!!
Now I must change class ExampleEventUserModel to get xlsread(...) method.
Do you have some advices for me?
I post the start point, could you help me too?/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
package xls;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.examples.FromHowTo;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.Package;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
public class ExampleEventUserModel {
public void processOneSheet(String filename) throws Exception {
Package pkg = Package.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// Seems to either be rId# or rSheet#
InputStream sheet2 = r.getSheet("rId2");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processAllSheets(String filename) throws Exception {
Package pkg = Package.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while(sheets.hasNext()) {
System.out.println("Processing new sheet:\n");
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
System.out.println("");
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws
SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// Print the cell reference
System.out.print(attributes.getValue("r") + " - ");
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
// Output after we've seen the string contents
if(name.equals("v")) {
System.out.println(lastContents);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) throws Exception {
FromHowTo howto = new FromHowTo();
howto.processOneSheet("filename.xlsx");
howto.processAllSheets("filename.xlsx");
}
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712493.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
My problem is simplest what you think. I am writing two method:
xlswrite(...) and xlsread(...)
The first works well while the second no.
My exercise is this:
*1)* To build a simple matrix:
int columns = 100;
int rows = 100000;
Object[][] matrix = new Object[rows][columns];
for (int j = 0; j < rows; j++) {
for (int k = 0; k < columns; k++) {
matrix[j][k] = j - k;
}
}
*2)* To build 2 methods with these specifications
xlswrite("filename.xlsx",matrix);
Object[][] matrix2 = xlsread("filename.xlsx");
*3)* It must be worth the following mathematical relationship:
matrix = matrix2
I just created xlsread() and it works but it can not read filename.xlsx
because it is too big file.
I used your example code:
public class ExampleEventUserModel {...}
but it doesn't work and I can not find xercesImpl.jar!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712492.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You are faced with two choices really. The classes in the XSSF stream will
allow you to read .xlsx files with ease but the drawback is, as you have
found out, that it consumes memory. In order ot reduce the loading on the
machines memory, you can use the event driven approach but this is not a
trivial task and it will take a little effort on your part.
If you still want to use the event driven approach, then you will need to
download one of the archives from the URL I supplied. Save the file onto
your machine and then unzip it. When you have unzipped the archive you
should find that there are a few JAR files there, one of which is called
xercesImpl.jar. This is the archive that you will need to make available at
compile/run time in order to execute the example code.
I am not at all clear what the piece of code you posted means. What are the
variables j, k, C, R and RECORD referring to? Are you trying to say that you
want to recover a three dimensional array that contains the data recovered
from a Workbook? If so, does the format applied to the contents of the cells
matter to you or is the raw value sufficient?
If using POI seems like it might be too much work, take a look at Tika -
http://tika.apache.org/ - it is a project focused on developing techniques
to extract the contents of various documents. It uses POI to handle Office
documents and will read Excel workbooks. I have no experience with it and do
not know if the OOXML files will be processed with the Event or User Models
but it might be worth taking a look.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712488.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
I can not improve my code to read xlsx. I built a very beautiful method to
write but I can not do the same thing reading xlsx. Your advice is too
difficult to implement. Could you post a little example of code? I would
want to use your library into my programm but without a good method to read
I can not do that.
I must read at least one matrix with these dimensions:
* int j;
int k;
int C = 100;
int R = 100000;
Object[][] MATRICE = new Object[R][C];
for (j = 0; j < RECORD; j++) {
for (k = 0; k < C; k++) {
MATRICE[j][k] = j - k;
}
}*
I don't understand what I have to do because on Apache Xerces I don't find
jar file... A little help for me please... I am almost finished my work...
Mark Beardsley wrote
> You can download Xerces frome here - http://xerces.apache.org/mirrors.cgi
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712486.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You can download Xerces frome here - http://xerces.apache.org/mirrors.cgi
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712479.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Where I can download all classes for my programme?
I am using this file:
http://poi.apache.org/download.html
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712470.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Nick Burch <ap...@gagravarr.org>.
On Mon, 1 Apr 2013, peruzzo wrote:
> I tryed your code but it doesn't work and Eclipse tells me that
> "Package" is deprecated...
You probably want OPCPackage instead
> *ERROR*
>
> Exception in thread "main" org.xml.sax.SAXException: SAX2 driver class
> org.apache.xerces.parsers.SAXParser not found
> java.lang.ClassNotFoundException: org.apache.xerces.parsers.SAXParser
http://poi.apache.org/overview.html#components lists the dependencies of
the different parts of Apache POI
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
I tryed your code but it doesn't work and Eclipse tells me that "Package" is
deprecated...
*CODE*
package xls;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.examples.FromHowTo;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.Package;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
public class ExampleEventUserModel {
public void processOneSheet(String filename) throws Exception {
Package pkg = Package.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// Seems to either be rId# or rSheet#
InputStream sheet2 = r.getSheet("rId2");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processAllSheets(String filename) throws Exception {
Package pkg = Package.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while(sheets.hasNext()) {
System.out.println("Processing new sheet:\n");
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
System.out.println("");
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws
SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// Print the cell reference
System.out.print(attributes.getValue("r") + " - ");
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
// Output after we've seen the string contents
if(name.equals("v")) {
System.out.println(lastContents);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) throws Exception {
FromHowTo howto = new FromHowTo();
howto.processOneSheet("nomefile.xlsx");
howto.processAllSheets("nomefile.xlsx");
}
}
*ERROR*
Exception in thread "main" org.xml.sax.SAXException: SAX2 driver class
org.apache.xerces.parsers.SAXParser not found
java.lang.ClassNotFoundException: org.apache.xerces.parsers.SAXParser
at org.xml.sax.helpers.XMLReaderFactory.loadClass(Unknown Source)
at org.xml.sax.helpers.XMLReaderFactory.createXMLReader(Unknown Source)
at
org.apache.poi.xssf.eventusermodel.examples.FromHowTo.fetchSheetParser(FromHowTo.java:72)
at
org.apache.poi.xssf.eventusermodel.examples.FromHowTo.processOneSheet(FromHowTo.java:43)
at xls.ExampleEventUserModel.main(ExampleEventUserModel.java:118)
Caused by: java.lang.ClassNotFoundException:
org.apache.xerces.parsers.SAXParser
at java.net.URLClassLoader$1.run(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at org.xml.sax.helpers.NewInstance.newInstance(Unknown Source)
... 5 more
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712468.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
The SXSSF 'approach' can only be used for creating files I am afraid. If
memory is an issue and you do want to read large files, then you will have
to take a look at the event driven approach that is outlined here -
http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712465.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Thanks for your support. Is there a command (or an example) like SXSSF to
read big dimension file too?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712463.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
There is no need to apologise for your English. It is very good and we are
perfectly able to understand the question you are asking.
With regard to the 100 numeric literal in the SXSSF example, it is just a
suggestion and has no special meaning or significance at all. SXSSF was a
contribution to the project made by one of it's users and is very much a
'developers tool' so to speak. As a result, there are no methods to help
optimise the size of the row cache, you will have to devise and implement
something for yourself if you feel it is important.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712462.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
I see the following example but I don’t understand 100 value and I would like
to understand if there is a method to calculate this value to have always
best performance every time on each computer.
Could you help me in this last questions please?
Thanks for all and I am sorry for my English
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712457.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
As it currently stands, SXSSF is intended to support the creation of very
large workbooks. A patch has been submitted that makes it possible to edit
pre-existing cells but this has not yet been reviewed and applied, at least
as far as I know. If you need to open an existing workbook, modify one or
more of the cells it already contains and then write the results back to
file, you will have to use XSSF, at least for the foreseeable future.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712453.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Can I use font and style? How? Can you post an example for me please?
Thanks
These doesn't work:
...
*SXSSFWorkbook wb = new SXSSFWorkbook(a);*
*XSSFFont font = (XSSFFont) wb.createFont();*
*XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();*
*Sheet sh = wb.createSheet();*
for (int rownum = 0; rownum < righemassime; rownum++) {
Row row = sh.createRow(rownum);
for (int cellnum = 0; cellnum < colonne; cellnum++) {
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
*font.setItalic(true);*
*style.setFont(font);*
*cell.setCellStyle(style);*
}
}
...
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712452.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by peruzzo <m....@yahoo.com>.
Is it possible to add to Microsoft database values with SXSSF? Can I edit a
.xlsx file with these new Apache command? I ask this because Eclipse tell to
me that these code lines are wrong:
SXSSFWorkbook workbook = new SXSSFWorkbook(file);
SXSSFSheet sheet = workbook.getSheet(nomefoglio);
If it is possible to edit .xlsx file could you send to me a little example
please?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712451.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI works but it uses too RAM: is it a limit of your
library?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
XSSF does use a lot of memory to construct an in memory representation of the
Excel worksheet. This question has been asked a number of times on the list
and there is a solution available. If you are limited for memory, then may I
suggest that you take a look at the streaming api - SXSSF.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-works-but-it-uses-too-RAM-is-it-a-limit-of-your-library-tp5712425p5712445.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org