Am Donnerstag, den 05.07.2007, 09:49 -0300 schrieb Denis Camargo:
> 2) I am having a terrible delay in writing the data on the
> spreadsheet. My macro has to write around (20-30k rows * 9 cols) of
> information (shading some blocks of rows with related info), and takes
> around 2 hours to do so.
>
> I can't put a sample of the code here right now, but basically i loop
> through the items, with a nested loop for each row of data for that
> item, finishing with the shading, like:
>
> for( Pack p : packages) {
>
> lastRow = rowCount;
> rowCount = 0;
> for( Row r : p) {
>
> xSpreadsheet.getCell(x, y).setFormula( r.getData1() );
> ...
> xSpreadsheet.getCell(x, y).setFormula(r.getData9() );
> rowCount++;
> }
> range = xSpreadsheet.getCellRange( rows from lastRow to rowCount,
> cols 1 to 9)
> range.shade( isShade() );
> }
> The code above doesn't work I know that. The real program
> works, it just is taking too long to finish.
>
> Is there a way to speedwrite the data? All the data to write to the
> spreadsheet is available before I begin to write, so if there is a way
> to write to big chunks of cells at once, that is not a problem.
Try getting the model interface (quuery for an XModel) of the
SpreadSheet or SpreadsheetDocument service and use the methods
"lockControllers" and "unlockControllers" at it. This will save a lot of
screen updates and time costly formatting.
> 3 - On the same massive cell writing subject, how can I access the
> menu command "Insert -> Spreadsheet from file..." via macro? Because I
> already created a standalone program that writes a tab-separated text
> file with the data, and then I manually load the file. I lose all the
> formatting the old macro did, but the whole 30k rows process goes from
> 2 hours to less than 1 minute. Maybe this is an alternative to problem
> 2.
Two hours? Ouch ...
You could try using a simple file format keeping what you need. In the
past (approx. 10 years ago ;) I did something similar using .SLK (SYLK -
Symbolic Link Format) or .DIF (Data Interchange Format), those are
formats for transfering spreadsheet data with formatting. OO.o knows
both, you've got to try yourself which is easier to write from java.
HTH,
Marc
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
