Re: [libreoffice-users] Re: Automating creation of PDF from Calc

Alex Kempshall <alexkempshall -AT- btinternet.com>
Tue, 13 May 2014 09:31:48 +0100

Hi Fernand and everyone else.

Thanks for the note. The reason for my initial posting was that I realised that using the
Dispatcher was not helpful and I wanted to use the API methods. It wasn't until several days later
with lots of googling that I fould that the API method to export to a PDF was

oDocument=ThisComponent
oDocument.storeToURL(sURL, aMediaDescriptor)

plus the filter

calc_pdf_Export

and it wasn't until I fell over the link

https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export

that things became a lot clearer. Prior to this I was googling for a method like export as PDF and
as there isn't one that explicitly has this or a similar name I couldn't find what I was looking
for but suffering from information overload with the hundreds of thousands hits I was getting.

I've now decided to follow the discussions in the Apache Open Office Community Forum for "Macros
and UNO API" as it seems to contain a wealth of information. I've not found any information of any
relevance on the LibreOffice sites relating to the API. If there is any, can someone point me in
the right direction. A simple guide to the key areas of the API would be really useful.

OpenOffice has this page which can search their API - http://www.openoffice.org/api/

So if I enter "export as pdf" in the "The Office API Reference" and push the Search button I get 2
results which contains the information I was looking for. Though I do think the Description for the
page found http://www.openoffice.org/api/docs/common/ref/com/sun/star/frame/XStorable.html could
also include something like "You are here because you want to export as PDF and here are some
relevant examples.

Or am I being unhelpful and just complaining because I don't undestand how to navigate the API
documentation?

Alex

On Monday 12 May 2014 12:37:59 Fernand Vanrie wrote:

Alex ,

using the Dispatcher is not a good idea , use te API and basic to 
automate some functions as printing to PDF under specific conditions.
Please find below the code i use to print from a Writer doc

Sub export2PDF()

dim oDoc as object

     CheckReportUser()
     sParent = checkparent

    OpenParams = Array(MakePropertyValue("Hidden",True),)

odoc = ThisComponent
'dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
aray = Split(Date,"/")
D = Join(aray,"-")
aray = Split(Time,":")
T = Join(aray,".")
'path = ConvertToUrl( "file:///C:/export/test " & D & "_" & T & ".pdf" )
sfilename = left(odoc.title,12)& ".pdf"
path = ConvertToUrl( "file:///H:/LowRes_" &  sfilename )
'********
'all documentation about  filderdata: >>> 
http://wiki.services.openoffice.org/wiki/API/Tutorials/PDF_export#PDF_Export_filter_data
'**********
pdfFilterData() = array((makepropertyvalue("UseLosslessCompression", false))
  AddPropertyValue(pdfFilterData,"Quality",90) ' only if we set 
UseLosslessCompression to FALSE ( jpg)
'AddPropertyValue(pdfFilterData,"ReduceImageResolution",false)
'AddPropertyValue(pdfFilterData,"MaxImageResolution",300) ' only if we 
set ReduceImageResolution to TRUE (only  75, 150, 300, 600 or 1200 can 
been used
AddPropertyValue(pdfFilterData,"UseTaggedPDF",false)
AddPropertyValue(pdfFilterData,"SelectPdfVersion",0) '0= pdf1.4 , 1= pdf-xa
AddPropertyValue(pdfFilterData,"ExportNotes",false)
AddPropertyValue(pdfFilterData,"ExportBookmarks",false)
AddPropertyValue(pdfFilterData,"OpenBookmarkLevels",-1)
AddPropertyValue(pdfFilterData,"UseTransitionEffects",true)
AddPropertyValue(pdfFilterData,"IsSkipEmptyPages",true)
AddPropertyValue(pdfFilterData,"IsAddStream",false)
AddPropertyValue(pdfFilterData,"EmbedStandardFonts",false)
AddPropertyValue(pdfFilterData,"FormsType",0)
AddPropertyValue(pdfFilterData,"ExportFormFields",true)
AddPropertyValue(pdfFilterData,"AllowDuplicateFieldNames",false)
AddPropertyValue(pdfFilterData,"HideViewerToolbar",false)
AddPropertyValue(pdfFilterData,"HideViewerMenubar",false)
AddPropertyValue(pdfFilterData,"HideViewerWindowControls",false)
AddPropertyValue(pdfFilterData,"ResizeWindowToInitialPage",false)
AddPropertyValue(pdfFilterData,"CenterWindow",false)
AddPropertyValue(pdfFilterData,"OpenInFullScreenMode",false)
AddPropertyValue(pdfFilterData,"DisplayPDFDocumentTitle",true)
AddPropertyValue(pdfFilterData,"InitialView",0)
AddPropertyValue(pdfFilterData,"Magnification",0)
AddPropertyValue(pdfFilterData,"Zoom",100)
AddPropertyValue(pdfFilterData,"PageLayout",0)
AddPropertyValue(pdfFilterData,"FirstPageOnLeft",false)
AddPropertyValue(pdfFilterData,"InitialPage",1)
AddPropertyValue(pdfFilterData,"Printing",2)
AddPropertyValue(pdfFilterData,"Changes",4)
AddPropertyValue(pdfFilterData,"EnableCopyingOfContent",true)
AddPropertyValue(pdfFilterData,"EnableTextAccessForAccessibilityTools",true)
AddPropertyValue(pdfFilterData,"ExportLinksRelativeFsys",false)
AddPropertyValue(pdfFilterData,"PDFViewSelection",0)
AddPropertyValue(pdfFilterData,"ConvertOOoTargetToPDFTarget",false)
AddPropertyValue(pdfFilterData,"ExportBookmarksToPDFDestination",false)
AddPropertyValue(pdfFilterData,"_OkButtonString","")
AddPropertyValue(pdfFilterData,"EncryptFile",false)
AddPropertyValue(pdfFilterData,"DocumentOpenPassword","")
AddPropertyValue(pdfFilterData,"RestrictPermissions",false)
AddPropertyValue(pdfFilterData,"PermissionPassword","")

    oExport = Array( _
       MakePropertyValue("Overwrite", True), _
       MakePropertyValue("FilterName", "writer_pdf_Export"), _
       MakePropertyValue("FilterData", pdfFilterData), _
    )

oDoc.storeTOURL(path,oExport)
  '  oDoc.close(True)
End Sub

Well eventually I seem to be getting somewhere.  There seems to be a lot of choice.

There are some people who use the method of hiding all the sheets except the sheet to be 
converted into a PDF. Which is the method I used below.

Another methed seems to be to use printareas. To me this only seemed useful if one wanted to 
print a specific section of a sheet. Though I didn't quite master the intricacies of swithing 
on/off print areas though thgis code started to look promising -

rem ----------------------------------------------------------------------
rem define variables
dim oFrame   as object
dim oDispatcher as object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oDoc As Object
Dim oSheet As Object
Dim oFirstSheet As Object
Dim iEndCol As Integer
Dim aPrintRanges(0) As  Object

oDoc = ThisComponent
oFirstSheet = oDoc.Sheets.getByIndex( 0 )
iEndCol = getLastUsedColumn(oFirstSheet)


rem ----------------------------------------------------------------------
rem get access to the document
oFrame   = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = 0
CellRangeAddress.EndColumn = 6
CellRangeAddress.EndRow = 35
aPrintRanges(0)=CellRangeAddress

oDoc.Sheets.getByIndex(0 ).setPrintAreas(aPrintRanges())
  
I used this method to erase the print areas on particular sheets so that the entire sheet 
became printable.

oDoc.Sheets.getByIndex(1 ).AutomaticPrintArea=True


The third method which seems to be the most promising selects the range of cells to be PDF'd 
and actually uses a method from the API. I cobbled this together from this tutorial

https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export

and Andrew Pitonyak’s book converting from Java as I went along

Sub exporttopdf1()

Dim oFrame as object, oDispatcher as object, oDoc as object, oController as object,  oSheet as 
object
Dim oRange as object, oDispatcherService as object, oSelection as object
Dim aFilterData(0)  as new com.sun.star.beans.PropertyValue
Dim aMediaDescriptor(1)  as new com.sun.star.beans.PropertyValue

oDocument = ThisComponent
oFirstSheet = oDocument.Sheets(0)
oSecondSheet = oDocument.Sheets(1)
oController  = oDocument.getCurrentController()
oFrame = oController.getFrame()
oDispatcherService = createUnoService("com.sun.star.frame.DispatchHelper")
oRange = oFirstSheet.getCellRangeByPosition(0, 0, getLastUsedColumn(oFirstSheet), 3)
oController.Select(oRange)
oSelection = ThisComponent.getCurrentController().getSelection()

sURL= "file:///home/alex/example1.pdf"

aFilterData(0).Name = "Selection"
aFilterData(0).Value = oSelection

aMediaDescriptor(0).Name = "FilterName"
aMediaDescriptor(0).Value = "calc_pdf_Export"
aMediaDescriptor(1).Name = "FilterData"
aMediaDescriptor(1).Value = aFilterData
  
oDocument.storeToURL(sURL, aMediaDescriptor)

End Sub


All the best

Alex





On Thursday 08 May 2014 14:23:06 you wrote:


Hi all

I have a requirement to generate PDF's from specific set sheets contained in one Calc document, 
the document has 85 individual sheets.

I've generated a macro to do this, having first recorded the steps using the macro recorder. 
Though the steps to record the macro worked perfectly as only the individual selection, i.e, 
all the cells on a specific sheet, was generated as a PDF. If I then run the macro with the 
same selection it always creates a PDF of  the entire document - all 85 sheets instead of just 
the selection!

Here's the recorded macro, note the selection "$A$1:$G$35"

sub exporttopdf
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$G$35"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "URL"
args2(0).Value = "file:///home/alex/example.pdf"
args2(1).Name = "FilterName"
args2(1).Value = "calc_pdf_Export"
args2(2).Name = "FilterData"
args2(2).Value = Array( _
Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ViewPDFAfterExport",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("FormsType",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),
 _
Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), 
_
Array("SignPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Watermark",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("Selection",,,com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SignatureLocation",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SignatureReason",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SignatureContactInfo",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SignaturePassword",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE), _
Array("SignatureCertificate",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args2())


end sub

Clearly the recorded macro has failed to record correctly, I do know that this can happen from 
time to time, and that I should tweak something in the above array, but don't not what and am 
finding it difficult to track down where these parameters are described in the documentation. 
Can anyone point me to where these parameters are described?

I've managed to derive a workaround for this which is to hide all the sheets that I'm not 
interested in then executing this block of code. It's a bit cumbersome as I have to hide 84 
sheets just to create a PDF of one sheet.

Whilst writing this note I've thought of another method that is to print the active sheet to a 
pdf file. I'm busily reading Andrew Pitonyak’s book
OpenOffice.org Macros Explained. Again I need to find the methods and properties associated 
with printin Calc sheets. Again can anyone point me in the right direction?

Could this be a question for the Developer's list?

Thanks

Alex


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context

[libreoffice-users] Automating creation of PDF from Calc · Alex McMurchy
- Re: [libreoffice-users] Automating creation of PDF from Calc · Brian Barker
  - Re: [libreoffice-users] Automating creation of PDF from Calc · Alex Kempshall
    - Re: [libreoffice-users] Automating creation of PDF from Calc · Brian Barker
- [libreoffice-users] Re: Automating creation of PDF from Calc · Alex McMurchy
  - Re: [libreoffice-users] Re: Automating creation of PDF from Calc · Fernand Vanrie
    - Re: [libreoffice-users] Re: Automating creation of PDF from Calc · Alex Kempshall

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.