The following code will parse through a spreadsheet and display data from the desired numbers of rows and columns in a Swing JEditorPane.
The main() method comes last, so it may help to read the code from the bottom up. First, we get a reference to the current document with
(a standard OO.o idiom), then we use that to get a reference to the XSpreadsheetDocument. (I should caution that there is no error-checking code here. It's assumed that a spreadsheet document is already open and frontmost in OpenOffice.) Once you've got the XSpreadsheetDocument, you can peel through its Sheets. With a Sheet, you can do:
to get cell data for the cell at column k, row i. If the spreadsheet is a converted .csv doc, the cells will all have text data, and (here's the weird part) to get the data you have to do:
since getValue() will only work if the cell contains a double-precision floating point number.
To test this macro, I looked online for a sizable .csv file that I could play with. I found a free/open movie database at George Tech's website. It's a comma-delimited file with data for 1742 movies ranging from 1925 to the early 1990s. It's by no means a definitive movie listing (apparently it represents one of the school's collections) but it suits my purposes just fine.
When I run the macro, it takes less than a second to display all of the spreadsheet's cell data (for all 1742 x 8 cells) in a JEditorPane window:
Ideas for further exploration:
- Style the data in some interesting way
- Save the data as HTML
- Push the data into a Sling repository (blog coming)