[Solved] Calc-Export changes on the same sheet to unique pdf

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

[Solved] Calc-Export changes on the same sheet to unique pdf

Post by patel »

In my sheet Report i can change the graph by changing the C5 cell content, I wrote a macro for export changes to single pdf files

Code: Select all

Sub PDF1()
Dim Entry(1 To 4) As String
Entry(1) = "A"
Entry(2) = "B"
Entry(3) = "C"
Entry(4) = "D"
Doc = ThisComponent
oSheet =  Doc.CurrentController.getActiveSheet()
oRng = oSheet.getCellRangeByName("A1:I17")
Dim mFilterData(0) As New com.sun.star.beans.PropertyValue
mFilterData(0).Name = "Selection"
Dim mStoreOpts(2) As New com.sun.star.beans.PropertyValue
mStoreOpts(0).Name = "Overwrite"
mStoreOpts(0).Value = True
mStoreOpts(1).Name = "FilterName"
mStoreOpts(1).Value = "calc_pdf_Export"
mStoreOpts(2).Name = "FilterData"
path = "file:///F:/Download/Totale" & Replace(Date,"/","-") & ".pdf"
For Count = 1 To 4
  oSheet.getCellRangeByName("C5").String = Entry(Count)
  mFilterData(0).Value = oRng
  mStoreOpts(2).Value = mFilterData()
  sUrl = "file:///F:/Download/"&  Entry(Count) & " " & Replace(Date,"/","-") & ".pdf" 'Cel.getString()
  Doc.storeToURL(sURL, mStoreOpts())
next 
end sub
My goal is an unique PDF with all changes.
File attached
Attachments
PDFexportMultipli.ods
(16.81 KiB) Downloaded 168 times
Last edited by patel on Wed Feb 06, 2019 1:54 pm, edited 1 time in total.
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Zizi64 »

There are som workaround method fo this task:
- You can merge the created pdf files by usega a third party pdf-manager software, like the PDFTK Builder.
- You can merge the sheets by usage of a virtual pdf printer software, like the PDF Creator. In this case you must use a Print command instead of the StoreT/StoreAs.
- You can copy the calculation ranges cyclically and with a shifted address of the target range (4 times) onto a helper sheet, and then you will able to export the unique pdf with all of the desired parameters and calculations.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

Re: Calc-Export some changes on the same sheet to unique pdf

Post by patel »

Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Zizi64 »

I can not give help you for a "direct way solution" of this task. I do not know any "append" parameter or command in the API.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Villeroy »

patel wrote:Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?
There are ready made solution to the problem. What you are trying to do is a workaround.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Sébastien C »

I agree Villeroy :D

It is funny ... That is exactly the job I am doing right now... There is a plethora of command-line software to do that! Just for example: PDFtk, Sejda SDK (in Java), PDFJam... I suppose other ones than I do not know yet.

For my part, I chose PDFJam because what I wanted to do was a bit complicated (more complicated than just assembling four PDFs from macros). In any case, do not forget the possible delegation, from the Basic, to the command line (via the Shell function).

You can make your four PDFs, then launch the Shell that will assemble them into one, and then destroy four starting files with the Kill instruction.
Some example of PDFtk are here. Some examples of Sejda SDK are here. And the manual for PDFjam is here (PDF).

Have a nice choice !
:D
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

Re: Calc-Export some changes on the same sheet to unique pdf

Post by patel »

Villeroy wrote:
patel wrote:Thanks for your suggestion, do you think it's not possible without workaround ? something like append ?
There are ready made solution to the problem. What you are trying to do is a workaround.
I mean Append for storeToURL
Do you think it's possible copy the used range to a new sheet, change the C5 cell content, then copy the used range to a new sheet and so on ..?
I tried it without success.
I'm able to save all sheets to unique pdf.
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

Re: Calc-Export some changes on the same sheet to unique pdf

Post by patel »

Sébastien C wrote: You can make your four PDFs, then launch the Shell that will assemble them into one, and then destroy four starting files with the Kill instruction.
Some example of PDFtk are here. Some examples of Sejda SDK are here. And the manual for PDFjam is here (PDF).

Have a nice choice !
:D
Thank You Sébastien for links, I know pdftk, but I like challenges
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Sébastien C »

If I may...

Code: Select all

Sub myCopyRange()
 Dim  mySheets As Object
 Dim myName_01 As String
 Dim         i As Integer

  mySheets = thisComponent.sheets
 myName_01 = "report"

 For i = 4 To 2 step -1
  mySheets.copyByName(myName_01, myName_01 & "_" & format(i, "00"), mySheets.getByName(myName_01).rangeAddress.Sheet + 1)
 Next i
End Sub
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

Re: Calc-Export some changes on the same sheet to unique pdf

Post by patel »

Did you test the suggested code ? the new graphs are binded to sheet Report, not to Report_2, Report_3 ...
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Calc-Export some changes on the same sheet to unique pdf

Post by Sébastien C »

patel wrote:Did you test the suggested code ?
Not enough, that's for sure ...

But the problem is interesting. If it does not pose any problem to set the C5 cell of each newly created sheet, it remains that reattaching the chart's parameters is a disaster. I arrive without worry to specify the right page's number to each graph. But it erases all the other data in the graph!

Does anyone have a known solution for this?

Code: Select all

Sub myCopyRange()
 Dim  mySheets As Object, mySheet As Object, myChart As Object, myZones() As Object
 Dim myName_01 As String,  myName As String
 Dim         i As Integer,      j As Integer

  mySheets = thisComponent.sheets
 myName_01 = "report"

 ' Add four sheets and set the cell C5.
 For i = 4 To 2 step -1
  myName = myName_01 & "_" & format(i, "00")
  mySheets.copyByName(myName_01, myName, mySheets.getByName(myName_01).rangeAddress.Sheet + 1)
  mySheets.getByName(myName).getCellRangeByName("C5").string = chr(64 + i) 
 Next i

 ' Now, fix the datas of charts.
 For i = 2 To 4
  myName = myName_01 & "_" & format(i, "00")
  myChart = mySheets.getByName(myName).charts(0)
  myZones = myChart.ranges

  For j = 0 To ubound(myZones)
   myZones(j).sheet = i
  Next j

  myChart.ranges = myZones()
 Next i
End sub
Attachments
PDFexportMultipli.ods
(16.77 KiB) Downloaded 176 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
patel
Posts: 36
Joined: Tue Jun 19, 2012 2:48 pm

Re: Calc-Export some changes on the same sheet to unique pdf

Post by patel »

Solved by Huber Lambert on italian forum

Code: Select all

sub PDF_tutti() ' hubert lambert
  on error goto unlock
    doc = thiscomponent
    sheets = doc.Sheets
    status = doc.CurrentController.StatusIndicator
    report = doc.Sheets.report
    existing = doc.Sheets.Count-1
    rappresantante = report.getCellByPosition(2, 4)
    memval = rappresantante.String
    rappresentanti = doc.Sheets.getCellRangeByPosition(1, 7, 1, 10, 0).DataArray
    status.start("Wait please...", ubound(rappresentanti)+3)
 
    for n = 0 to ubound(rappresentanti)
        nome = rappresentanti(n)(0)
        rappresantante.setString(nome)
        if sheets.hasByName("report " + nome) then
            sheets.removeByName("report " + nome)
        end if
        sheets.copyByName("report", "report " + nome, sheets.Count)
        f = sheets.getByName("report " + nome)
        f.AutomaticPrintArea = True
        status.setValue(n+1)
        chart = f.Charts(0).EmbeddedObject
        chart.Data.Data = chart.Data.Data
    next n
    ' memorizza printing areas
    dim printareas(existing)
    for n = 0 to existing
        foglio = doc.Sheets(n)
        printareas(n) = array(foglio.AutomaticPrintArea, foglio.PrintAreas)
        foglio.AutomaticPrintArea = False
        foglio.PrintAreas = array()
    next n
    ' esporta PDF
    dest = "F:/Download/chris1971_tutti.pdf"
    dim filterdata(0) as new com.sun.star.beans.PropertyValue
    filterdata(0).Name = "PageLayout"
    filterdata(0).Value = 1
    dim args(1) as new com.sun.star.beans.PropertyValue
    args(0).Name = "FilterName"
    args(0).Value = "calc_pdf_Export"
    args(1).Name = "FilterData"
    args(1).Value = filterdata
    status.setValue(n+2)
    doc.storeToURL(convertToUrl(dest), args())
    ' ripristina printing areas
    for n = 0 to existing
        status.setValue(n+3)
        foglio = doc.Sheets(n)
        printareas(n) = array(foglio.AutomaticPrintArea, foglio.PrintAreas)
        foglio.AutomaticPrintArea = printareas(n)(0)
        foglio.PrintAreas = printareas(n)(1)
    next n
    ' cancella fogli temporanei
    for each row in rappresentanti
        status.setValue(n+4)
        nome = "report " + row(0)
        if sheets.hasByName(nome) then
            sheets.removeByName(nome)
        end if
    next row
  unlock:
    rappresantante.setString(memval)
    status.end()
    if err = 0 then
        ' apri il pdf
        scelta = msgbox("File created in <" + dest + ">" + string(2, chr(10)) + "Open ?", 4, "")
        if scelta = 6 then
            systemshell = com.sun.star.system.SystemShellExecute.create()
            systemshell.execute(convertFromURL(dest), "", 0)
        end if
    else
        msgbox "Error " & err & ": " & error + chr(10) + "alla riga " + erl + chr(10), 16 ,"error occurred"
    end if
end sub
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
Post Reply