Page 1 of 1

Calc columns

PostPosted: Fri Jan 29, 2016 6:30 am
by cgalli
Hi everybody.
I'm developing a custom extension to extract data from third-party files, and represent it in spreadsheet documents.
The extension is developed entirely on Java, (Netbeans IDE 8.x, with AOO plug-in).

Sometimes, due to the data structure requiered, more than 1024 columns are needed.
So, I have tryed buiding calc again, modifying the defintions for the maximum number of rows and columns, in sc/inc/address.hxx (now, the sheets has 131072 rows and 8092 columns, both multiples of 16, so the total number of cells remains unchanged, to minimize the impact on performance).

Aparently, the calc application works ok, but the java bridge throws IndexOutOfBounds exception every time the getCellRangeByPosition is called from within the extension.

Does anybody knows what else is needed to make the extension could manage more than 1024 columns too?


Re: Calc columns

PostPosted: Fri Jan 29, 2016 8:25 am
by Zizi64
You can achieve tis task with
- a real database and the Base application
- MS Excel.
Or you can separate data by the import procedure onto more than one sheet, or into more than one file.

The Apache OpnOffice Calc and LibreOffice Calc have 1024 column limit now. (This limit was 256 in the past time.)

Re: Calc columns

PostPosted: Fri Jan 29, 2016 9:34 am
by RoryOF
Your reference to 8092 is surely a typo - ought it not be 8192? I suspect your answer lies in Java limitations; as you know the powers of 2 are "magic numbers" and form obvious boundary points for programmers. Array indexing in Java starts at 0, so might you be testing for 8192 when the value expected is 8191?

Re: Calc columns

PostPosted: Fri Jan 29, 2016 11:02 am
by cgalli
Thank you both for the answer.

RoryOF, the only changes I've made were the constants defining maximums number of columns and rows in the calc appli ation code itself (I haven' change anything on my Java code). So, why it works fine with 1024 columns and throws an exception with any other value of maximum column number?

As I see, this exception is thrown by native functions (e.g. getCellRangeByPosition), not by Java, when the max column or row index is exceeded (it's not the array's IndexOutOfBoundException).

It seems that the columns are limited somewhere else in AOO's code, and I can't find where...

Re: Calc columns

PostPosted: Fri Jan 29, 2016 11:10 am
by RoryOF
As far as I know, and confirmed by Zizi64's posting, the column limit on Calc (hardcoded) is 1024 columns. To alter this you would need to recompile Calc, but that would mean your extension would only work with your version of Calc. Instead, perhaps you could alter your code so that after 1024 columns, your code forced another sheet for the next 1024 columns. and so on until the limit of 256(?) sheets was reached.

As a spreadsheet user who gets confused when spreadsheets go over one A4/US Letter page, the thought of 1024 column spreadsheets using many sheets and umpteen rows causes me to have a dizzy spell. Perhaps I'll go and lie down!

Re: Calc columns

PostPosted: Fri Jan 29, 2016 11:28 am
by cgalli
Don't worry about trying to see those sheets printed...
The idea is represent the data contained in third-party-format files to make them human readable.
I thought before about splitting sheets in these cases, but our customer (final user) dislikes this option.
So, I've changed the focus and as I see, the best option is recompike calc, generating a version that allows more tha 1024 columns.

I'm aware that those spreadsheets will be readable only by that version of calc. It's ok.

Again, I won't try to print them and torture you... so, don't worry about it.


Re: Calc columns

PostPosted: Fri Jan 29, 2016 11:39 am
by RoryOF
Hints on OpenOffice Develpment, including building from source, are at
OpenOffice Development

The source code is linked off this page
OpenOffice Source code

I am aware that there can be many minor problems with compilation, so you will probably need to subscribe to the developer mailing list for help, linked off the following page. Be aware that it is a high traffic list, and many users are not patient with beginners. Fortunately, these are not the informed and helpful users.
OpenOffice Mailing lists

Re: Calc columns

PostPosted: Fri Jan 29, 2016 12:11 pm
by cgalli
Thanks for the references.

In fact, I did compile and build succesfully AOO on my system (using the Build Guide).
As I said in the first post, I have changed the constants defining maximums and rebuild calc.

It seems to be allright (calc works ok) except when getCellRangeByPosition is called from within my extension (that's when an IndexOutOfBounds exception is thrown).

Re: Calc columns

PostPosted: Fri Jan 29, 2016 1:53 pm
by RoryOF
I can only suggest that you insert a debugging line in your extension to record and display the value being questioned, either writing the result to a file or putting up a console message..

Other than that suggestion this is now beyond my knowledge.

Re: Calc columns

PostPosted: Tue Feb 02, 2016 2:33 pm
by cgalli

well, debugging my java extension, I found an issue that causes the errors.
So, there is nothing else to do but changing the constants defining maximums for columns and rows in main/sc/inc/address.hxx, as sugested in documentation and other posts.
Thanks for your help.

Re: Calc columns

PostPosted: Wed Apr 13, 2016 11:42 pm
by Lupp
Just an additional information: LibO is on the way to implement dynamic column containers. (See ... i?id=50916 )

Re: Calc columns

PostPosted: Thu Apr 14, 2016 12:03 am
by cgalli
It would be awesome!