Frozen rows information is lost when exporting to xls

[paha]

Hi, sorry if the question was asked already on the group, couldn't find any post about it.
I have the following problem: I want to convert the ods file with a frozen header row (stays on top while scrolling).
When doing this via Calc itself (save as Excel 97/2000/XP), everything works fine - the final xls file has this header row locked.
But...when exporting via oo api like in offsite example

Code: Select all

protected void storeDocComponent(XComponent xDoc, String storeUrl) throws java.lang.Exception { 
     XStorable xStorable = (XStorable)UnoRuntime.queryInterface(XStorable.class, xDoc);
     PropertyValue[] storeProps = new PropertyValue[1];
     storeProps[0] = new PropertyValue();
     storeProps[0].Name = "FilterName";
     storeProps[0].Value = "MS Excel 97"; 
     xStorable.storeAsURL(storeUrl, storeProps); 
 }

the row "lock" information is lost, and the row in the resulting document is not frozen anymore. Can somebody reproduce the problem?
I've tested it on win7 x64 with 3.2.1 api.
Is it a bug or some known OO API limitation?

P.S. i'm attaching the sample of source ods and resulting xls if they of any use

[FJCC]

This Basic code produces an xls file where the frozen row is still in place.

Code: Select all

Dim propval(0) as New com.sun.star.beans.PropertyValue
propval(0).Name = "FilterName"
propval(0).Value = "MS Excel 97"
oDoc = ThisComponent
fileURL = convertToURL("C:\Test_Frozen.xls") 
oDoc.storeToURL(fileURL, propval)

I am also using OOo 3.2.1 and can't account for the difference

[paha]

This Basic code produces an xls file where the frozen row is still in place

correct me if I'm wrong but the basic code works inside of OO itself, while Java accesses it from outside via api which talks to oo daemon/service. so that the results are not necessarily the same. i don't say "they must be different", but they "may be different".

[FJCC]

I don't know enough about how these things work to say much about the differences in how Java and OOoBasic interact with OOo documents. OOoBasic certainly uses the API, but the interfaces do not have to be explicitly queried as in Java or C++. Here is another bit of code in Python that also preserves the frozen row.

Code: Select all

import uno
from com.sun.star.beans import PropertyValue

def saver():
  propval = PropertyValue()
  propval.Name = 'FilterName'
  propval.Value = 'MS Excel 97'
  oDoc = XSCRIPTCONTEXT.getDocument()
  fileURL = 'file:///C:/python_frozen.xls'
  oDoc.storeAsURL(fileURL, (propval,))

[paha]

i think it would be a challenge to deliberately convert the file via api in the way, that everything (styles, data) is preserved and "freeze" isn't. i don't believe there is some property for such "special" case. but here we go, the resulting file is in attachment.

[FJCC]

As a last attempt to replicate the problem I wrote the following bit of JavaScript. The Excel file it creates preserves the frozen row.

Code: Select all

// Excel save in JavaScript
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.frame.XStorable);
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.lang.XServiceInfo);

importClass(Packages.com.sun.star.script.provider.XScriptProviderFactory); 
importClass(Packages.com.sun.star.script.provider.XScriptProvider);

//get the document from the scripting context
oDoc = XSCRIPTCONTEXT.getDocument();
storeProps = new Array(1);
storeProps[0] = new PropertyValue();
storeProps[0].Name = "FilterName";
storeProps[0].Value = "MS Excel 97";
storeURL = "file:///C:/javascriptexcel.xls";
//get the Xstorable interface
xStoreDoc = UnoRuntime.queryInterface(XStorable,oDoc);
xStoreDoc.storeAsURL(storeURL, storeProps);

Some of the imported classes are needed for the use of xray, in case you are wondering why I bothered with them.

[paha]

As a last attempt to replicate the problem I wrote the following bit of JavaScript. The Excel file it creates preserves the frozen row

thanks for your effort. i will try to reproduce the problem with the simplest code possible in java.

[paha]

Here we go again:

I tested the behavior on the ConnectionAwareClient from OO First Steps guide. I slightly changed the code - there is no need for command line arguments, the uno-url is hardcoded in main() and oo daemon is started automatically. The path to source and destination files is defined in private static constants. When the user presses "New calc" button, the source file is opened and saved to destination file. if everything goes right, there is a "connected" message in label.
when i run the example, the frozen raw is not preserved in the new file. Moreover, if i remove the save filter and save file as ods - frozen raw is not preserved either.

What to do next?

[RoryOF]

Do without it? Or put the values in as Text and let them import, which won't affect any calculations

[paha]

Do without it?

I "do" without it for two years already. I just didn't have a time to check who is responsible for the information loss (there are two frameworks involved in document conversion and each could spoil the result).

Or put the values in as Text

thanks, but i don't want to invent another wheel to overcome this limitation. putting the values manually as the text after jodconverter/jodreport processing is like driving a horse after Lexus

[jholg]

I have the very same problem. I use this code to programmatically export to XLS (gracefully taken from http://www.xml.com/pub/a/2006/01/11/fro ... ffice.html):

Code: Select all

Sub SaveAsXLS( cFile )
   cURL = ConvertToURL( cFile )
   ' Open the document. Just blindly assume that the document 
   ' is of a type that OOo will correctly recognize and open 
   ' without specifying an import filter.
   oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, _
            Array(MakePropertyValue( "Hidden", True ),))

   cFile = Left( cFile, Len( cFile ) - 4 ) + ".xls"
   cURL = ConvertToURL( cFile )
   
   ' Save the document using a filter.   
   oDoc.storeToURL( cURL, Array(_
            MakePropertyValue( "FilterName", "MS Excel 97" ),)
   
   oDoc.close( True )
End Sub

Function MakePropertyValue( Optional cName As String, Optional uValue ) _
   As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function

Running this (by

Code: Select all

openoffice.org --headless "macro:///Standard.Conversions.SaveAsXLS(/var/tmp/myfile.ods)"

): window freeze gets lost. In contrast, when I run a similar macro from within OO, window freeze is preserved. Window freeze is also preserved when doing a simple "Save As...", as OP mentioned.

I've now found a solution that keeps the window freeze information, albeit clumsy: Changing the "Hidden" property arg of loadComponentFromURL to "False" works for me.

The downside is that the conversion takes significantly longer and an OO GUI window opens while the macro is running. It seems like the window freeze information is only respected when there actually is a window.

I.e.

Code: Select all

Sub SaveAsXLS( cFile )
   cURL = ConvertToURL( cFile )
   ' Open the document. Just blindly assume that the document 
   ' is of a type that OOo will correctly recognize and open 
   ' without specifying an import filter.
   oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, _
            Array(MakePropertyValue( "Hidden", False),))

   cFile = Left( cFile, Len( cFile ) - 4 ) + ".xls"
...

Someone got a cleaner solution?

Regards,
Holger

[paha]

will not work for me , I have debian without GUI in production environment.
i cross-posted the problem in www.oooforum.org. no single answer till now.