PS: Corrected unnecessary object creation in the last method.
2014-01-31 Nicholas Evans <[email protected]>:
> Dear ODF users,
>
> For a project I am working on, I am using the ODF toolkit to create
> spreadsheets that can become rather large (>10 000 rows). I have noticed
> that as the spreadsheet gets larger, writing the rows becomes very slow. I
> have put together a class containing 4 different ways of writing 10 000
> rows of 10 columns to a spreadsheet. The fastest method (using
> getRowByIndex and then getCellByIndex) takes 70 seconds. The methods that
> use getRowList and getNextRow are much slower, taking about 170 seconds
> each. The method using the Iterator<Row> seems to freeze for large inputs,
> and doesn't behave as expected for small inputs.
>
> I would really like to improve this performance. I think this could be
> done by manipulating the DOM directly. However, it would great if there
> was a way of using the Simple API that I have overlooked that could help me.
>
> Does anyone have experience with improving the performance of the ODF
> toolkit in the context of writing rows to an ods spreadsheet?
>
> Regards,
> Nick
>
package nick.tests;
import org.odftoolkit.simple.SpreadsheetDocument;
import org.odftoolkit.simple.table.Row;
import org.odftoolkit.simple.table.Table;
import org.odftoolkit.simple.table.Cell;
import java.util.Iterator;
import java.util.List;
public class OdfPerformanceTest {
public static void main(String[] args) {
try{
Long startTime = System.currentTimeMillis();
System.out.println("Processing started at " + startTime);
//takes 25 seconds.
writeWithIndexGetter();
//Behaves strangely for small number of row and freezes for large numbers.
writeWithIterator();
//takes 184 seconds.
writeWithList();
//takes 171 seconds.
writeWithNextRow();
Long endTime = System.currentTimeMillis();
Long duration = endTime - startTime;
System.out.println("Duration: " + duration);
Long durationInSeconds = duration / 1000;
System.out.println("Print took: " + durationInSeconds + " seconds.");
}catch(Exception e){
System.out.print(e.getLocalizedMessage());
System.exit(-1);
}
}
public static void writeWithIndexGetter() throws Exception {
//The template must have oddStyle and evenStyle pre-defined.
String templateLocation = "/home/Nick/Documents/test/odf-template.ods";
String outputLocation = "/home/Nick/Documents/test/odf-test.ods";
SpreadsheetDocument spreadsheet = SpreadsheetDocument.loadDocument(templateLocation);
Table table = spreadsheet.getSheetByIndex(0);
System.out.println("Writing rows.");
for (int i = 0; i < 10000; i++){
Row row = table.getRowByIndex(i);
writeRow(row);
}
spreadsheet.save(outputLocation);
}
public static void writeRow(Row row) {
for (int i = 0; i < 10; i++){
Cell cell = row.getCellByIndex(i);
cell.setStringValue("test");
cell.setCellStyleName("evenStyle");
}
}
public static void writeWithIterator() throws Exception {
//The template must have oddStyle and evenStyle pre-defined.
String templateLocation = "/home/Nick/Documents/test/odf-template.ods";
String outputLocation = "/home/Nick/Documents/test/odf-test-iterator.ods";
SpreadsheetDocument spreadsheet = SpreadsheetDocument.loadDocument(templateLocation);
Table table = spreadsheet.getSheetByIndex(0);
System.out.println("Appending rows.");
table.appendRows(10000);
System.out.println("Writing rows.");
Iterator<Row> rowIterator = table.getRowIterator();
for(int i = 0; i < 10000; i++){
System.out.println("Getting row " + i);
Row row = rowIterator.next();
System.out.println("Printing row " + i);
writeRow(row);
}
spreadsheet.save(outputLocation);
}
public static void writeWithList() throws Exception {
//The template must have oddStyle and evenStyle pre-defined.
String templateLocation = "/home/Nick/Documents/test/odf-template.ods";
String outputLocation = "/home/Nick/Documents/test/odf-test-list.ods";
SpreadsheetDocument spreadsheet = SpreadsheetDocument.loadDocument(templateLocation);
Table table = spreadsheet.getSheetByIndex(0);
System.out.println("Appending rows.");
table.appendRows(10000);
System.out.println("Writing rows.");
List<Row> rowList = table.getRowList();
for(Row row: rowList){
writeRow(row);
}
spreadsheet.save(outputLocation);
}
public static void writeWithNextRow() throws Exception {
//The template must have oddStyle and evenStyle pre-defined.
String templateLocation = "/home/Nick/Documents/test/odf-template.ods";
String outputLocation = "/home/Nick/Documents/test/odf-test-nextRow.ods";
SpreadsheetDocument spreadsheet = SpreadsheetDocument.loadDocument(templateLocation);
Table table = spreadsheet.getSheetByIndex(0);
System.out.println("Appending rows.");
table.appendRows(10000);
System.out.println("Writing rows.");
Row startRow = table.getRowByIndex(0);
Row currentRow = startRow;
for(int i = 0; i < 10000; i++){
writeRow(currentRow);
currentRow = currentRow.getNextRow();
}
spreadsheet.save(outputLocation);
}
}
