Issue 127395

Summary: setActiveSheet does not work in Hidden mode
Product: App Dev Reporter: peterB <Peter.Binney>
Component: apiAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Normal    
Priority: P5 (lowest) CC: knmc, oooforum
Version: 4.1.3   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: 4.1.5
Developer Difficulty: ---
Attachments:
Description Flags
Sample python script demonstrating the problem
none
Original .py script hacked further to attempt to add the Basic workaround none

Description peterB 2017-04-25 10:36:06 UTC
You can't use the API to process (eg: export as CSV) all sheets in a Calc document opened in hidden mode.

See numerous examples of code attempts and queries. Eg:
- http://stackoverflow.com/questions/20714857/how-can-i-save-all-sheets-in-a-libreoffice-calc-spreadsheet-from-a-command-line
- http://stackoverflow.com/questions/41870205/openoffice-calc-setactivesheet
- http://www.linuxjournal.com/files/linuxjournal.com/ufiles/ssconverter.py_.txt
- https://github.com/whyleyc/unoconv/blob/master/unoconv (comments/code at line 847)
Comment 1 oooforum (fr) 2017-04-26 12:21:19 UTC
Why using ActiveSheet property if the document is hidden ? It's a non-sense.
Each sheet can be handling with ThisComponent.Sheets and using .getByName or .getByIndex.
Comment 2 mroe 2017-04-26 12:45:49 UTC
(In reply to oooforum (fr) from comment #1)
> Why using ActiveSheet property if the document is hidden ? It's a non-sense.
> Each sheet can be handling with ThisComponent.Sheets and using .getByName or
> .getByIndex.

Export to CSV exports only the /active/ sheet. How to export another sheet?
Comment 3 oooforum (fr) 2017-04-27 10:26:08 UTC
Not reproduce with AOO 4.1.3 and Win7 x64

Sub Main
  Dim sDoc as String
  Dim Arg(0) As New com.sun.star.beans.PropertyValue
  Arg(0).Name = "Hidden"
  Arg(0).Value = True

  sDoc = ConvertToURL("C:\Temp\export_csv.ods")
  oDoc = StarDesktop.loadComponentFromURL(sDoc, "_default", 0, Arg() )
  oSheet = oDoc.sheets.GetByIndex(2)
  oDoc.CurrentController.setActiveSheet(oSheet)
  msgbox oDoc.CurrentController.ActiveSheet.Name
  oDoc.close(true)
End Sub
Comment 4 oooforum (fr) 2017-04-27 10:29:45 UTC
These lines opens export_csv.ods in hidden mode and jump to sheet #3 with no problem.
Comment 5 mroe 2017-04-27 11:06:17 UTC
(In reply to oooforum (fr) from comment #3)

Please test if you (every time) can really save a sheet other than the first as CSV.


Sub csvexport
  Dim oDoc as Object
  Dim oSheet as Object
  Dim sDoc as String
  Dim outputUrl As String
  Dim Arg(0) As New com.sun.star.beans.PropertyValue
  Dim Arg2(0) As New com.sun.star.beans.PropertyValue
  Arg(0).Name = "Hidden"
  Arg(0).Value = True
  Arg2(0).Name = "FilterName"
  Arg2(0).Value = "Text - txt - csv (StarCalc)"
  sDoc = ConvertToURL("C:\Temp\export_csv.ods")
  oDoc = StarDesktop.loadComponentFromURL(sDoc, "_default", 0, Arg() )
  oSheet = oDoc.sheets.GetByIndex(2)
  oDoc.CurrentController.setActiveSheet(oSheet)
  outputUrl = ConvertToURL( "C:\Temp\" & oSheet.Name & ".csv")
  'msgbox oDoc.CurrentController.ActiveSheet.Name
  oDoc.storeToURL( outputUrl, Arg2() )
  oDoc.close(true)
End Sub
Comment 6 oooforum (fr) 2017-04-27 12:26:19 UTC
I'm not sure that setActiveSheet is buggy.
If I set filter as "dBase", the code works fine.
Comment 7 peterB 2017-04-29 17:00:01 UTC
Created attachment 86041 [details]
Sample python script demonstrating the problem

Not sure if this helps, but I am a python-er and I attach oo-save-ods-sheet.py - a butchered subset of the UNOCONV script (ref: https://github.com/dagwieers/unoconv) which I was trying to modify to save .ods sheets as .csv

I run on Windows 10 and so, to simplify it, this is hard-wired for that environment.
I assume you could make it run elsewhere by changing the variables at the front.

In a folder containing oo-save-ods-sheet.py and the 2-sheet oo-save-ods-sheet.ods,
I run it using: "C:\Program Files (x86)\OpenOffice 4\program/python.exe" oo-save-ods-sheet.py

Change the Hidden= setting on line 92 to illustrate the problem.
Comment 8 oooforum (fr) 2017-05-05 08:08:15 UTC
So weird, it seems ActiveSheet not compatible _only_ with CSV filter
I try with SYLK export and it works too.
I found a workaround with dispatcher help.

Sub csvexport
	Dim sDoc as String
	Dim Arg(0) As New com.sun.star.beans.PropertyValue
	Dim Args1(0) as new com.sun.star.beans.PropertyValue
	Dim Args2(0) as new com.sun.star.beans.PropertyValue
	Arg(0).Name = "Hidden"
	Arg(0).Value = True
	
	sDoc = ConvertToURL("C:\Temp\export_csv.ods")
	oDoc = StarDesktop.loadComponentFromURL(sDoc, "_default", 0, Arg() )
	'oSheet = oDoc.sheets.GetByIndex(2)
	'oDoc.CurrentController.setActiveSheet(oSheet)
	
	' Workaround with these lines
	oFrame = oDoc.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	Args1(0).Name = "Tables"
	Args1(0).Value = Array(2) 'index # for sheet
	dispatcher.executeDispatch(oFrame, ".uno:SelectTables", "", 0, args1())
	
	Arg2(0).Name = "FilterName"
	Arg2(0).Value = "Text - txt - csv (StarCalc)"
	'Arg2(0).Value = "dBase"
	
	outputUrl = ConvertToURL( "C:\Temp\test.csv")
	'msgbox oDoc.CurrentController.ActiveSheet.Name
	oDoc.storeToURL( outputUrl, Arg() )
	oDoc.close(true)
End Sub

I don't know if this issue must be reopen
Comment 9 peterB 2017-05-05 16:43:26 UTC
Minor typo in your code: Arg2(0).Name and Arg2(0).Value assignments near the end should be Arg(0).Name and Arg(0).Value

I can see that that works using a Basic macro, but can't get it to work in the Python unoconv.py script.

I replaced the document.CurrentController.setActiveSheet(sheet) FOR loop block in my code with:

oFrame = document.CurrentController.Frame
dispatcher = self.unosvcmgr.createInstanceWithContext("com.sun.star.frame.DispatchHelper", self.unocontext)
patchProps = UnoProps(NameError="Tables", Value=tuple([2]))
dispatcher.executeDispatch(oFrame, ".uno:SelectTables", "", 0, patchProps)


to no avail.
So this is still a bug!
Comment 10 mroe 2017-05-05 17:30:03 UTC
> I replaced the document.CurrentController.setActiveSheet(sheet) FOR loop
> block in my code with:
> 
> oFrame = document.CurrentController.Frame
> dispatcher =
> self.unosvcmgr.createInstanceWithContext("com.sun.star.frame.DispatchHelper",
> self.unocontext)
> patchProps = UnoProps(NameError="Tables", Value=tuple([2]))
-------------------------^^^^^^^^^^

Really?
Comment 11 peterB 2017-05-06 10:11:34 UTC
Created attachment 86071 [details]
Original .py script hacked further to attempt to add the Basic workaround

Oops, my typo this time! But no change after correcting it to:
  patchProps = UnoProps(Name="Tables", Value=tuple([2]))

I attach my updated full script.
Comment 12 peterB 2018-01-31 15:58:35 UTC
Problem still present in 4.1.5
Comment 13 Keith N. McKenna 2018-01-31 17:01:32 UTC
Please DO NOT change the Version field to note that it still does not work in a newer version. That is what the Latest Confirmation in Field is for. THe version field is used to not the first version that the issue was reported against and can be used by developers to narrow down where the problem is in the code.

I have re-set the Version field to 4.1.3 and added 4.12.5 as the latest confirmation in Field