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++){
Row row = currentRow;
writeRow(row);
currentRow = row.getNextRow();
}
spreadsheet.save(outputLocation);
}
}
