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

Creating a macro - Writing a Script - Using the API

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

Postby patel » Mon Feb 04, 2019 1:09 pm

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   Expand viewCollapse view
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 16 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
patel
 
Posts: 25
Joined: Tue Jun 19, 2012 2:48 pm

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

Postby Zizi64 » Mon Feb 04, 2019 5:11 pm

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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 7990
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby patel » Mon Feb 04, 2019 7:27 pm

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
patel
 
Posts: 25
Joined: Tue Jun 19, 2012 2:48 pm

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

Postby Zizi64 » Mon Feb 04, 2019 9:25 pm

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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 7990
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Mon Feb 04, 2019 9:44 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Sébastien C » Tue Feb 05, 2019 4:44 am

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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

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

Postby patel » Tue Feb 05, 2019 5:25 pm

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: 25
Joined: Tue Jun 19, 2012 2:48 pm

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

Postby patel » Tue Feb 05, 2019 5:31 pm

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
patel
 
Posts: 25
Joined: Tue Jun 19, 2012 2:48 pm

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

Postby Sébastien C » Tue Feb 05, 2019 8:06 pm

If I may...

Code: Select all   Expand viewCollapse view
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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

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

Postby patel » Tue Feb 05, 2019 8:39 pm

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
patel
 
Posts: 25
Joined: Tue Jun 19, 2012 2:48 pm

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

Postby Sébastien C » Tue Feb 05, 2019 10:14 pm

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   Expand viewCollapse view
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 16 times
LibreOffice 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

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

Postby patel » Wed Feb 06, 2019 1:52 pm

Solved by Huber Lambert on italian forum
Code: Select all   Expand viewCollapse view
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
patel
 
Posts: 25
Joined: Tue Jun 19, 2012 2:48 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests