Re: setStringFormat doesn't always work as expected

[Nicholas Evans]

Dear Svante,

Thanks for your response.

In the end I solved my problem by directly manipulating the DOM with the
"lower classes" as you call them.  However, I have also attached a template
and a class that demonstrate the problem I was having with the Simple API
setFormatString method.

The problem seems to be that when there are user defined styles present,
the setFormatString method creates a number-style element in the
content.xml file, and not in the
styles.xml file.  This means that when LibreOffice follows the reference to
this style, it cannot find it.  This behaviour of LibreOffice conforms with
the odf spec (16.1):

*Styles and font face declarations are referenced by their style:name
attribute. A referenced style or font face declaration should be defined in
the same file as the reference, or in styles.xml. *

I have included some comments in the java file to try and clarify the
problem.

Best,
Nick


2014/1/15 Svante Schubert <svante.schub...@gmail.com>

> Hi Nicholas,
>
> would it possible to test the problem in the latest sources?
> In addition could you provide an issue and attach a test case. Test
> Driven Development, you know..
>
> For ease of you, just take an existing one, so one of us might debug
> into it, if the solution might not become apparent at once.
> In general I feel more responsible for the lower classes than for the
> Simple API that IBM once added.
>
> Thanks in advance,
> Svante
>
>
> Am 14.01.2014 16:59, schrieb Nicholas Evans:
> > Dear odf users,
> >
> > I am writing an application that makes use of the setFormatString
> function
> > from the ODF Simple API (the 0.7-incubating version that is available
> > through maven central).  My use case is the following:
> >
> > 1) I select a cell in the spreadsheet.
> > 2) I set the style of this cell using setCellStyleName
> > 3) I set a double value in the cell using setDoubleValue
> > 4) I set a format for the cell using setFormatString
> >
> > When I test run this sequence in a simple test project it works fine.
> > However, when I use the same sequence in a bigger spreadsheet, the cell
> is
> > not rendered as it should be.  When I open the generated spreadsheet in
> > LibreOffice and GoogleSpreadsheets, the cell is not formatted as I want,
> > although the double value is displayed.  However, when I open the
> > spreadsheet in Excel, the cell is formatted properly. If I save the file
> in
> > excel and reopen it in LibreOffice, the cell is formatted correctly.
> >
> > I have compared the ods xml files before and after saving it in Excel and
> > there are quite substantial differences in the way in the which the
> styles
> > are handled.
> >
> > Interestingly, the following sequence produces different results in my
> > small test project and my large project:
> >
> > 1) I select a cell in the spreadsheet.
> > 2) I set the style of this cell using setCellStyleName
> > 3) I set a double value in the cell using setDoubleValue
> > 4) I set a format for the cell using setStringFormat
> > 5) I get the cell style using getCellStyleName
> >
> > In the small project, the style I get in step 5 is different from the
> one I
> > set in step 2. It seems that somewhere a new style is generated that is a
> > child of the Default style and this is returned in step 5.  However, in
> the
> > larger project, step 5 returns the style I set in step 2.
> >
> > It seems like the API is working at least partially correctly, because
> > Excel can understand the generated spreadsheet.  However, it is rather
> > frustrating that LibreOffice can only understand the spreadsheet after it
> > has been through Excel.
> >
> > Has anyone experienced such a problem before?  Do you have any
> suggestions
> > for how I could get the setFormatString to work?
> >
> > Thanks for your help.
> >
> > Kind regards,
> >
> > Nick
> >
>
>

odf-template.ods
Description: application/vnd.oasis.opendocument.spreadsheet

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;

/**
 * This class demonstrates what looks like a bug in the implementation of setFormatString().  If you run the
 * code with the call to the setSomeCellStyles() method, the formatting of the chosen cell does not display
 * properly in LibreOffice.  If you comment out this method, the cell is displayed correctly.  In both cases,
 * getting the formatString returns the same result.
 * @author Nick
 *
 */

public class OdfTest {

	public static void main(String[] args) {
		
		try{
		 testSpreadsheet();
		}catch(Exception e){
			System.exit(-1);
		}
	}
	
	public static void testSpreadsheet() 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);
	    
	    setSomeCellStyles(table);
	    
	    //Try to format a random cell.
	    Row row = table.getRowByIndex(11);
	    Cell cell = row.getCellByIndex(11);
	    cell.setDoubleValue(200.0);
	    cell.setFormatString("EUR 0");
	    String cellFormat = cell.getFormatString();
	    
	    //Prints "EUR #0", but when the file is opened in LibreOffice, the cell is not displayed with format.
	    System.out.print(cellFormat);		
	    
	    spreadsheet.save(outputLocation);
	}

	public static void setSomeCellStyles(Table table) {
	
		//Set some styles for the cells.
	    for (int i = 0; i < 30; i++){
	    	Row row = table.getRowByIndex(i);
	    	for (int j = 0; j < 20; j++){
	    		Cell cell = row.getCellByIndex(j);
	    		if (i*j % 2 == 0){
	    			cell.setCellStyleName("evenStyle");
	    		}else{
	    			cell.setCellStyleName("oddStyle");
	    		}
	    	}
	    }
		
	}
	
}