Frozen rows information is lost when exporting to xls

The Application Programming Interface and the OASIS Open Document Format

Frozen rows information is lost when exporting to xls

Postby paha » Thu Dec 16, 2010 1:00 pm

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   Expand viewCollapse view
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
Attachments
sample.zip
(7.23 KiB) Downloaded 161 times
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby FJCC » Sat Dec 18, 2010 5:57 am

This Basic code produces an xls file where the frozen row is still in place.
Code: Select all   Expand viewCollapse view
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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7490
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Frozen rows information is lost when exporting to xls

Postby paha » Sat Dec 18, 2010 1:13 pm

FJCC wrote: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".
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby FJCC » Sat Dec 18, 2010 3:33 pm

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   Expand viewCollapse view
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,))
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7490
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Frozen rows information is lost when exporting to xls

Postby paha » Sat Dec 18, 2010 5:51 pm

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.
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby FJCC » Sat Dec 18, 2010 10:48 pm

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   Expand viewCollapse view
// 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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7490
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Frozen rows information is lost when exporting to xls

Postby paha » Sun Dec 19, 2010 12:41 pm

FJCC wrote: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.
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby paha » Mon Dec 20, 2010 3:42 pm

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?
Attachments
frozen_row.zip
(9.58 KiB) Downloaded 148 times
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby RoryOF » Mon Dec 20, 2010 3:56 pm

Do without it? Or put the values in as Text and let them import, which won't affect any calculations
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30418
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Frozen rows information is lost when exporting to xls

Postby paha » Mon Dec 20, 2010 4:15 pm

RoryOF wrote: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).

RoryOF wrote: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 ;)
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm

Re: Frozen rows information is lost when exporting to xls

Postby jholg » Tue Dec 21, 2010 7:32 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
OpenOffice 3.0.1 on Linux
jholg
 
Posts: 1
Joined: Tue Dec 21, 2010 7:09 pm

Re: Frozen rows information is lost when exporting to xls

Postby paha » Wed Dec 22, 2010 11:32 am

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.
OpenOffice 3.2 on Windows 7 x64 Pro
paha
 
Posts: 7
Joined: Thu Dec 16, 2010 12:51 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 2 guests