You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Avik Sengupta <av...@itellix.com> on 2003/09/04 18:20:12 UTC
Request for feedback : More Shared Formula thoughts
To do toFormulaString on shared formulas, one needs to reference once
cell from another. So for eg, from cell A10, one might need to reference
the CellValueRecord for cell A1 in the same sheet.
A. Now the interface to randomly access a cell is in the usermodel.
However, there are two problems.. 1) there are no upreferences, ie, from
an HSSFCell object, i have no reference to its row or sheet, which are
needed to access another cell.. also 2) from the usermodel of course
there is no access to the underlying strucutres.
B. In the low level ojbects in the 'model' package, there is no way to
access the record for a particular cell .. you can only iterate thru the
records one at a time.
Once you get a reference to the records somehow, the rest of it is easy.
Any thoughts on this folks? I cant think of a cool/quick/magic solution.
Anyone?
Regards
-
Avik
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Wed, Sep 10, 2003 at 02:16:49PM +0530, Naveen N Rao wrote:
> > In my spare time I have been working on a new approach to the whole
> > issue that builds upon the RandomAccessFile capabilities of POIFS2. The
> > INDEX and DBCELL records are used to locate arbitrary cells in the sheet
> > and cells are read lazily by seeking to the proper location and copying
> > only the necessary records. There is an adjustable cache of recently
> > read records which helps to keep the seeking to a minimum. The end
> > result is that you can process arbitrarily large sheets using a small
> > and essentially constant amount of memory. Naturally by controlling
> > memory use the speed is greatly improved as well.
>
> This definitely is the best way to go for Random access. But otherwise, I
> am not sure if this is the way to go for the usual method. What I think is
> that in Chris's method, we'll need to recognise and (possibly) reconstruct
> record objects or parts thereof before they can be used. Instead, if we
> can get pointers to already existing in-memory record objects, much work
> is saved. But, hey! I am just guessing and waiting for further
> comments...
In my method part of the advantage is that there aren't any "already
existing in-memory record objects". When reading in a workbook only the
barest minimum of info is read, to keep memory use to a minimum. Of
course this means that when you actually want a cell value you have to
locate it (preferably using INDEX/DBCELL) and read the underlying record
(unless it is cached), but the total amount of "work" (i.e. reading
records) is much less.
If you are going to need to read every single cell in a sheet, for
instance, it may be faster to use the existing method of slurping in all
records. But even in this unusual case I'm not sure it would be, because
there are a lot of useless records intermingled with all the relevant
ones.
I'll try to get some real numbers soon...spare time is lacking...
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Avik Sengupta <av...@apache.org>.
>This definetely is the easiest fix - I just implemented it ;-)
Cool! I would be happy with such a solution, provided the performance
hit is not felt in non-relevant cases, ie, we are able to quickly
isolate a Shared Formula case from a non shared formula case, and do the
complicated processing only in the former case. In that case, we will be
better off that where we are now.
Why dont you send your code over to the list, and we can get some
feedback from people.
Once again, great!
>I was thinking about this and I just realised that currently, such a
> container for records is already being maintained - it is the 'cells'
> field in Sheet. The cells field is of type ValueRecordsAggregate and it
> maintains *all the records* for all cells (or so I think - I hope so
> atleast :-) in the form of a TreeMap.
Actually, i was looking at ValueRecordsAggregate..tho i still am not
sure that it contains all records.. i *think* it does.. Now to get it
out of the tree, do this... create a new FormulaRecord, and sets its row
and col to what you want to query on, and use that as the parameter in
the map.get() field... you'll get the correct record for the cell you
want.. it works coz the comparator/comparable for all
CellValueRecordInterface classes are supposed to work on the basis of
row/col only.. this is why things are stored in a map! you might need a
new method in ValueRecords Aggregate.. make it as
"get(CellValueRecordInterface c)" .. Make sense?
-
Avik
On Wed, 2003-09-10 at 14:16, Naveen N Rao wrote:
> More thoughts:
> > > 1. The easiest way (quickest one, i guess, without requiring lots of
> code
> > > changes) makes use of the "cells" field in Model.Sheet. This field
> holds
> > > only records relating to the cells (iterating throgh this is better
> than
> > > iterating through all records). We can introduce a new method in
> > > Model.Sheet that returns the shared formula record for a given cell
> > > position. This is easy to do - we just need to iterate through the
> records
> > > in 'cells'. Since HSSFCell has a reference to its containing sheet, we
> can
> > > use this API to obtain the required shared formula record from within
> > > HSSFCell. A small change in HSSFCell.getCellFormula() and we have
> solved
> > > this problem!!!
>
> This definetely is the easiest fix - I just implemented it ;-)
>
> > > 2. The permanent fix (which addresses issues that may arise in the
> future
> > > wherein there is a need to be able to access all records relating to a
>
> > > particular cell) is to use indexing as Chris has pointed out. However,
> the
> > > solution that Chris has pointed out - to use DBCell and INDEX records
> > > might just be getting pointers to the stream, but not references to
> > > in-memory records. So, I would suggest constructing a 2D array-like
> > > container full of Lists. Each List contains all records relating to a
> > > single cell. This way, we could support a new API in Model.Sheet that
> > > returns a List of all records relating to a cell.
> >
> > One problem is that such a 2D array will use even more memory. A sheet
> > can contain (256 ^ 3) cells and even the bookmaking info becomes
> > unwieldy long before that point.
>
> I was thinking about this and I just realised that currently, such a
> container for records is already being maintained - it is the 'cells'
> field in Sheet. The cells field is of type ValueRecordsAggregate and it
> maintains *all the records* for all cells (or so I think - I hope so
> atleast :-) in the form of a TreeMap. *If* this is the case, we can change
> the internal working (implementation) of ValueRecordsAggregate to use a
> HashMap. Then, we will be able to map from a cell (a new class maybe, that
> has a row and a column field - essentially representing a cell) to a List
> again that has all records for this cell. This way, we'll be able to do a
> get(cell) to get a list of all records for this cell.
>
> > In my spare time I have been working on a new approach to the whole
> > issue that builds upon the RandomAccessFile capabilities of POIFS2. The
> > INDEX and DBCELL records are used to locate arbitrary cells in the sheet
> > and cells are read lazily by seeking to the proper location and copying
> > only the necessary records. There is an adjustable cache of recently
> > read records which helps to keep the seeking to a minimum. The end
> > result is that you can process arbitrarily large sheets using a small
> > and essentially constant amount of memory. Naturally by controlling
> > memory use the speed is greatly improved as well.
>
> This definitely is the best way to go for Random access. But otherwise, I
> am not sure if this is the way to go for the usual method. What I think is
> that in Chris's method, we'll need to recognise and (possibly) reconstruct
> record objects or parts thereof before they can be used. Instead, if we
> can get pointers to already existing in-memory record objects, much work
> is saved. But, hey! I am just guessing and waiting for further
> comments...
>
> > > 3. Another way is to restructure the user model so that it resembles a
>
> > > tree structure - introduce new fields in HSSFCell that points back to
> the
> > > containing HSSFSheet and also new field in HSSFSheet that contains
> > > references to all HSSFCells, and so on. This is in fact the way a user
>
> > > would visualize the spreadsheet; so, this will be more intuitive,
> However,
> > > this also requires quite a lot of work since the references need to be
>
> > > kept valid at all times.
>
> This, I believe, is the best thing to do because, from an end-user
> perspective a tree structure seems natural. So, we can have a workbook
> consisting of sheets, sheets containing rows/cells and so on (with
> backpointers) - irrespective of the underlying model. But, I am not sure
> if this really addressed this particular issue since we'll still need
> access to the underlying records...
>
>
> Regards,
> Naveen
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Naveen N Rao <na...@in.ibm.com>.
More thoughts:
> > 1. The easiest way (quickest one, i guess, without requiring lots of
code
> > changes) makes use of the "cells" field in Model.Sheet. This field
holds
> > only records relating to the cells (iterating throgh this is better
than
> > iterating through all records). We can introduce a new method in
> > Model.Sheet that returns the shared formula record for a given cell
> > position. This is easy to do - we just need to iterate through the
records
> > in 'cells'. Since HSSFCell has a reference to its containing sheet, we
can
> > use this API to obtain the required shared formula record from within
> > HSSFCell. A small change in HSSFCell.getCellFormula() and we have
solved
> > this problem!!!
This definetely is the easiest fix - I just implemented it ;-)
> > 2. The permanent fix (which addresses issues that may arise in the
future
> > wherein there is a need to be able to access all records relating to a
> > particular cell) is to use indexing as Chris has pointed out. However,
the
> > solution that Chris has pointed out - to use DBCell and INDEX records
> > might just be getting pointers to the stream, but not references to
> > in-memory records. So, I would suggest constructing a 2D array-like
> > container full of Lists. Each List contains all records relating to a
> > single cell. This way, we could support a new API in Model.Sheet that
> > returns a List of all records relating to a cell.
>
> One problem is that such a 2D array will use even more memory. A sheet
> can contain (256 ^ 3) cells and even the bookmaking info becomes
> unwieldy long before that point.
I was thinking about this and I just realised that currently, such a
container for records is already being maintained - it is the 'cells'
field in Sheet. The cells field is of type ValueRecordsAggregate and it
maintains *all the records* for all cells (or so I think - I hope so
atleast :-) in the form of a TreeMap. *If* this is the case, we can change
the internal working (implementation) of ValueRecordsAggregate to use a
HashMap. Then, we will be able to map from a cell (a new class maybe, that
has a row and a column field - essentially representing a cell) to a List
again that has all records for this cell. This way, we'll be able to do a
get(cell) to get a list of all records for this cell.
> In my spare time I have been working on a new approach to the whole
> issue that builds upon the RandomAccessFile capabilities of POIFS2. The
> INDEX and DBCELL records are used to locate arbitrary cells in the sheet
> and cells are read lazily by seeking to the proper location and copying
> only the necessary records. There is an adjustable cache of recently
> read records which helps to keep the seeking to a minimum. The end
> result is that you can process arbitrarily large sheets using a small
> and essentially constant amount of memory. Naturally by controlling
> memory use the speed is greatly improved as well.
This definitely is the best way to go for Random access. But otherwise, I
am not sure if this is the way to go for the usual method. What I think is
that in Chris's method, we'll need to recognise and (possibly) reconstruct
record objects or parts thereof before they can be used. Instead, if we
can get pointers to already existing in-memory record objects, much work
is saved. But, hey! I am just guessing and waiting for further
comments...
> > 3. Another way is to restructure the user model so that it resembles a
> > tree structure - introduce new fields in HSSFCell that points back to
the
> > containing HSSFSheet and also new field in HSSFSheet that contains
> > references to all HSSFCells, and so on. This is in fact the way a user
> > would visualize the spreadsheet; so, this will be more intuitive,
However,
> > this also requires quite a lot of work since the references need to be
> > kept valid at all times.
This, I believe, is the best thing to do because, from an end-user
perspective a tree structure seems natural. So, we can have a workbook
consisting of sheets, sheets containing rows/cells and so on (with
backpointers) - irrespective of the underlying model. But, I am not sure
if this really addressed this particular issue since we'll still need
access to the underlying records...
Regards,
Naveen
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Tue, Sep 09, 2003 at 10:51:34PM +1000, Glen Stampoultzis wrote:
> At 09:54 AM 9/09/2003, you wrote:
> >In my spare time I have been working on a new approach to the whole
> >issue that builds upon the RandomAccessFile capabilities of POIFS2. The
> >INDEX and DBCELL records are used to locate arbitrary cells in the sheet
> >and cells are read lazily by seeking to the proper location and copying
> >only the necessary records. There is an adjustable cache of recently
> >read records which helps to keep the seeking to a minimum. The end
> >result is that you can process arbitrarily large sheets using a small
> >and essentially constant amount of memory. Naturally by controlling
> >memory use the speed is greatly improved as well.
>
> Do you have some performance numbers?
I found some time to finish my "technology demo". The testcase was
reading in a 160K excel spreadsheet and summing an entire column. The
POI code I used was:
private static double sumcolumn2(String filename, int column)
throws IOException
{
short c = (short)column;
FileInputStream fis = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(fis));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
double total = 0;
for (int r = sheet.getFirstRowNum(), max = sheet.getLastRowNum(); r <= max; r++) {
HSSFRow row = sheet.getRow(r);
if (row != null) {
HSSFCell cell = row.getCell(c);
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
total += cell.getNumericCellValue();
}
}
}
fis.close();
return total;
}
Running this loop 100 times on my machine took 15 seconds, and the
rewritten version only 800ms, about a 20x speedup. It used POIFS2 with a
FileChannel (NIO) source and used the INDEX and DBCELL records to lazily
read in cell data as I described above.
Maybe for POI 7.0...
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Tue, Sep 09, 2003 at 10:51:34PM +1000, Glen Stampoultzis wrote:
> Do you have some performance numbers?
I'll try to get some soon, the code is a bit broken at the moment.
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Glen Stampoultzis <gs...@iinet.net.au>.
At 09:54 AM 9/09/2003, you wrote:
>On Mon, Sep 08, 2003 at 04:48:40PM +0530, Naveen N Rao wrote:
> > 2. The permanent fix (which addresses issues that may arise in the future
> > wherein there is a need to be able to access all records relating to a
> > particular cell) is to use indexing as Chris has pointed out. However, the
> > solution that Chris has pointed out - to use DBCell and INDEX records
> > might just be getting pointers to the stream, but not references to
> > in-memory records. So, I would suggest constructing a 2D array-like
> > container full of Lists. Each List contains all records relating to a
> > single cell. This way, we could support a new API in Model.Sheet that
> > returns a List of all records relating to a cell.
>
>One problem is that such a 2D array will use even more memory. A sheet
>can contain (256 ^ 3) cells and even the bookmaking info becomes
>unwieldy long before that point.
>
>In my spare time I have been working on a new approach to the whole
>issue that builds upon the RandomAccessFile capabilities of POIFS2. The
>INDEX and DBCELL records are used to locate arbitrary cells in the sheet
>and cells are read lazily by seeking to the proper location and copying
>only the necessary records. There is an adjustable cache of recently
>read records which helps to keep the seeking to a minimum. The end
>result is that you can process arbitrarily large sheets using a small
>and essentially constant amount of memory. Naturally by controlling
>memory use the speed is greatly improved as well.
>
>But this is such a change from the current codebase that I don't really
>know if it is worth pursuing...
>
>Chris
Do you have some performance numbers?
Regards,
Glen
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Mon, Sep 08, 2003 at 04:48:40PM +0530, Naveen N Rao wrote:
> 2. The permanent fix (which addresses issues that may arise in the future
> wherein there is a need to be able to access all records relating to a
> particular cell) is to use indexing as Chris has pointed out. However, the
> solution that Chris has pointed out - to use DBCell and INDEX records
> might just be getting pointers to the stream, but not references to
> in-memory records. So, I would suggest constructing a 2D array-like
> container full of Lists. Each List contains all records relating to a
> single cell. This way, we could support a new API in Model.Sheet that
> returns a List of all records relating to a cell.
One problem is that such a 2D array will use even more memory. A sheet
can contain (256 ^ 3) cells and even the bookmaking info becomes
unwieldy long before that point.
In my spare time I have been working on a new approach to the whole
issue that builds upon the RandomAccessFile capabilities of POIFS2. The
INDEX and DBCELL records are used to locate arbitrary cells in the sheet
and cells are read lazily by seeking to the proper location and copying
only the necessary records. There is an adjustable cache of recently
read records which helps to keep the seeking to a minimum. The end
result is that you can process arbitrarily large sheets using a small
and essentially constant amount of memory. Naturally by controlling
memory use the speed is greatly improved as well.
But this is such a change from the current codebase that I don't really
know if it is worth pursuing...
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Naveen N Rao <na...@in.ibm.com>.
I have been working on the shared formula problem and I wanted to share my
thoughts on how this problem can be addresed. There are really two things
that need to be addressed. The first one and the main one is finding a
reference to the shared formula record. The second part is to refactor the
formula.
As far as the first part is concerned, there are three ways in which this
problem can be addressed:
1. The easiest way (quickest one, i guess, without requiring lots of code
changes) makes use of the "cells" field in Model.Sheet. This field holds
only records relating to the cells (iterating throgh this is better than
iterating through all records). We can introduce a new method in
Model.Sheet that returns the shared formula record for a given cell
position. This is easy to do - we just need to iterate through the records
in 'cells'. Since HSSFCell has a reference to its containing sheet, we can
use this API to obtain the required shared formula record from within
HSSFCell. A small change in HSSFCell.getCellFormula() and we have solved
this problem!!!
2. The permanent fix (which addresses issues that may arise in the future
wherein there is a need to be able to access all records relating to a
particular cell) is to use indexing as Chris has pointed out. However, the
solution that Chris has pointed out - to use DBCell and INDEX records
might just be getting pointers to the stream, but not references to
in-memory records. So, I would suggest constructing a 2D array-like
container full of Lists. Each List contains all records relating to a
single cell. This way, we could support a new API in Model.Sheet that
returns a List of all records relating to a cell.
3. Another way is to restructure the user model so that it resembles a
tree structure - introduce new fields in HSSFCell that points back to the
containing HSSFSheet and also new field in HSSFSheet that contains
references to all HSSFCells, and so on. This is in fact the way a user
would visualize the spreadsheet; so, this will be more intuitive, However,
this also requires quite a lot of work since the references need to be
kept valid at all times.
As for refactoring the formula, we need a new method in
SharedFormulaRecord that accepts a cell position and returns a refactored
formula. However, as I just found out, refactoring is damn easy - you only
need to replace RefNPtgs with ReferencePtgs in the shared formula ;-)
NOTE: Before anything can be done, we'll need to change the implementation
of ExpPtg and SharedFormulaRecord classes slightly to reflect the
underlying record structure more appropriately - this should be easy. We
also need a new class RefNPtg.
Regards,
Naveen
Avik Sengupta <av...@apache.org>
09/05/2003 08:40 PM
Please respond to "POI Developers List"
To: POI Developers List <po...@jakarta.apache.org>
cc:
Subject: Re: Request for feedback : More Shared Formula
thoughts
>Thinking about it more, the current code probably has every cell record
> in memory, so I'm not sure why it couldn't easily support a random
> access API.
You yeah, of course it would.. all the information it there in memory..
However, since indexed access is currently allowed only from the high
level api, doing it in the low level api implies a lot of sequential
iteration .... so while it is certainly possible, my question is more
about
a) doing it with current api
and most important b) getting it done without doing too much work!!!
On Thu, 2003-09-04 at 22:18, Chris Nokleberg wrote:
> On Thu, Sep 04, 2003 at 09:44:30AM -0700, Chris Nokleberg wrote:
> > You could implement low-level support for random-access using the
INDEX
> > and DBCELL records. I've been playing around with this a bit in
> > conjunction with a RandomAccessFile-based POIFS stream.
>
> Thinking about it more, the current code probably has every cell record
> in memory, so I'm not sure why it couldn't easily support a random
> access API.
>
> Chris
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-dev-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Avik Sengupta <av...@apache.org>.
>Thinking about it more, the current code probably has every cell record
> in memory, so I'm not sure why it couldn't easily support a random
> access API.
You yeah, of course it would.. all the information it there in memory..
However, since indexed access is currently allowed only from the high
level api, doing it in the low level api implies a lot of sequential
iteration .... so while it is certainly possible, my question is more
about
a) doing it with current api
and most important b) getting it done without doing too much work!!!
On Thu, 2003-09-04 at 22:18, Chris Nokleberg wrote:
> On Thu, Sep 04, 2003 at 09:44:30AM -0700, Chris Nokleberg wrote:
> > You could implement low-level support for random-access using the INDEX
> > and DBCELL records. I've been playing around with this a bit in
> > conjunction with a RandomAccessFile-based POIFS stream.
>
> Thinking about it more, the current code probably has every cell record
> in memory, so I'm not sure why it couldn't easily support a random
> access API.
>
> Chris
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-dev-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Thu, Sep 04, 2003 at 09:44:30AM -0700, Chris Nokleberg wrote:
> You could implement low-level support for random-access using the INDEX
> and DBCELL records. I've been playing around with this a bit in
> conjunction with a RandomAccessFile-based POIFS stream.
Thinking about it more, the current code probably has every cell record
in memory, so I'm not sure why it couldn't easily support a random
access API.
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
Re: Request for feedback : More Shared Formula thoughts
Posted by Chris Nokleberg <ch...@sixlegs.com>.
On Thu, Sep 04, 2003 at 09:50:12PM +0530, Avik Sengupta wrote:
> B. In the low level ojbects in the 'model' package, there is no way to
> access the record for a particular cell .. you can only iterate thru the
> records one at a time.
>
> Once you get a reference to the records somehow, the rest of it is easy.
>
> Any thoughts on this folks? I cant think of a cool/quick/magic solution.
> Anyone?
You could implement low-level support for random-access using the INDEX
and DBCELL records. I've been playing around with this a bit in
conjunction with a RandomAccessFile-based POIFS stream.
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org