Calc columns

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Calc columns

Post 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?

Thanks.
OpenOffice 4.1.2 on Fedora 22
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc columns

Post 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.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calc columns

Post 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?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Re: Calc columns

Post 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...
OpenOffice 4.1.2 on Fedora 22
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calc columns

Post 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!
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Re: Calc columns

Post 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.

Regards.
OpenOffice 4.1.2 on Fedora 22
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calc columns

Post 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
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Re: Calc columns

Post 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).
OpenOffice 4.1.2 on Fedora 22
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calc columns

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Re: Calc columns

Post by cgalli »

RoryOF,

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.
OpenOffice 4.1.2 on Fedora 22
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc columns

Post by Lupp »

Just an additional information: LibO is on the way to implement dynamic column containers. (See https://bugs.documentfoundation.org/sho ... i?id=50916 )
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
cgalli
Posts: 9
Joined: Mon Jul 28, 2014 11:38 pm

Re: Calc columns

Post by cgalli »

It would be awesome!
OpenOffice 4.1.2 on Fedora 22
Post Reply