[Solved] Macro: Save Graphs as Images (many of them!)

[aaa4948]

QUESTION: Is there an easy way to use a OOCalc macro to export a graph multiple times (Do While i < 10 ... i = i + 1 Loop) to an image? The only manual export option I found is to export to a PDF or to take the picture to Draw and save it as a picture.

I often build graphs frame-by-frame (point-by-point) in Excel and then use VBA to export the graph as an image as I change the value of a cell that advances the graph by one increment.

Here's a sample graph (adjust cell A1 to see what I mean):

http://amhx.net/graph.ods

I used the following mess of a code to save the workbook as an HTML file and then it gives me the jpgs of the graph. Before I paste the miles of code (which I got by recording a macro and then tweaking), here's the output for which I'm trying to get:



I'll just end up deleting the .html files and saving the pictures which I can stitch together in AfterEffects (side question...is there a Linux tool to stitch images into an .MP4?)

And here's the OOCalc code I use:

Code: Select all

sub Main
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

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

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

dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "URL"
args2(0).Value = "file:///C:/TEMP/01.html"
args2(1).Name = "FilterName"
args2(1).Value = "HTML (StarCalc)"
args2(2).Name = "FilterOptions"
args2(2).Value = ""

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

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"

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

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "2"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args5())

dim args6(2) as new com.sun.star.beans.PropertyValue
args6(0).Name = "URL"
args6(0).Value = "file:///C:/TEMP/02.html"
args6(1).Name = "FilterName"
args6(1).Value = "HTML (StarCalc)"
args6(2).Name = "FilterOptions"
args6(2).Value = ""

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args6())

dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$A$1"

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

dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "StringName"
args9(0).Value = "3"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args9())

dim args10(1) as new com.sun.star.beans.PropertyValue
args10(0).Name = "By"
args10(0).Value = 1
args10(1).Name = "Sel"
args10(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args10())

dim args11(2) as new com.sun.star.beans.PropertyValue
args11(0).Name = "URL"
args11(0).Value = "file:///C:/TEMP/03.html"
args11(1).Name = "FilterName"
args11(1).Value = "HTML (StarCalc)"
args11(2).Name = "FilterOptions"
args11(2).Value = ""

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args11())

dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$A$1"

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

dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "StringName"
args15(0).Value = "04"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args15())

dim args16(1) as new com.sun.star.beans.PropertyValue
args16(0).Name = "By"
args16(0).Value = 1
args16(1).Name = "Sel"
args16(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args16())

dim args17(2) as new com.sun.star.beans.PropertyValue
args17(0).Name = "URL"
args17(0).Value = "file:///C:/TEMP/04.html"
args17(1).Name = "FilterName"
args17(1).Value = "HTML (StarCalc)"
args17(2).Name = "FilterOptions"
args17(2).Value = ""

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args17())

rem ----

dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$A$1"

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

dim args19(0) as new com.sun.star.beans.PropertyValue
args19(0).Name = "StringName"
args19(0).Value = "05"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args19())

dim args20(1) as new com.sun.star.beans.PropertyValue
args20(0).Name = "By"
args20(0).Value = 1
args20(1).Name = "Sel"
args20(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args20())

dim args21(2) as new com.sun.star.beans.PropertyValue
args21(0).Name = "URL"
args21(0).Value = "file:///C:/TEMP/05.html"
args21(1).Name = "FilterName"
args21(1).Value = "HTML (StarCalc)"
args21(2).Name = "FilterOptions"
args21(2).Value = ""

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args21())

end sub

In OOCalc macros, I'm not sure how to loop with all those stupid args##().

In Excel, it's really easy!:

Code: Select all

Sub ExportChart()
Dim objChrt As ChartObject
Dim myChart As Chart

x = 1

Sheets("Graphs").Select

Do While x <= 10

    Range("A1").Select
    ActiveCell.FormulaR1C1 = x
    
    Set objChrt = Sheets("Graphs").ChartObjects("Chart 1")
    Set myChart = objChrt.Chart
    
    If x < 10 Then
        myFileName = "0000" & x & ".png"
    ElseIf x < 100 Then
        myFileName = "000" & x & ".png"
    ElseIf x < 1000 Then
        myFileName = "00" & x & ".png"
    ElseIf x < 10000 Then
        myFileName = "0" & x & ".png"
    Else
        myFileName = x & ".png"
    End If
    
    On Error Resume Next
    Kill ThisWorkbook.Path & "\" & myFileName
    On Error GoTo 0
    
    myChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName, Filtername:="PNG"
    x = x + 1
Loop

End Sub

Thanks for any help. Trying to avoid Windows 8 and switch full time to Ubuntu or OSX, but as an engineer I'll miss Excel

[rudolfo]

I can sympathize with you as I have left quite a lot of sweat one year ago or so trying to automate the graph generation of OpenOffice Calc. I usually work with x-y scatter charts. No pie charts or those other pretty looking images that the marketing folks prefer.
3 or 4 years ago I had some success with this in Excel. But trying to modify the working VBA macro code for some later adjustments was a terrible experience. And as a consequence I changed the tools and worked with Gnuplot and Perl. In both cases Excel and Calc.
Gnuplot is a bit old fashioned, but I am familiar with it. If you have no experience with Gnuplot it is probably better to start with R.

[aaa4948]

Thanks, rudolfo. I'm at that same fork in the road. I'm having good luck with Excel, but OOCalc isn't as easy. I've used Gnuplot, tried R, and also played with JPGraph and PHP. I'll wait and hope for someone else to reply, but any Google searches didn't turn up much info.

[hanya]

Just an example:

Code: Select all

Sub StoreDrawingOBjectAsImage
  shape = ThisComponent.getSheets().getByIndex(0).getDrawPage().getByIndex(0)
  
  Dim args(1) as new com.sun.star.beans.PropertyValue
  args(0).Name = "URL"
  args(0).Value = "file:///home/asuka/Downloads/foo.png"
  args(1).Name = "MimeType"
  args(1).Value = "image/png"
  
  gef = CreateUnoService("com.sun.star.drawing.GraphicExportFilter")
  gef.setSourceDocument(shape)
  gef.filter(args)
End Sub

[aaa4948]

Cool...is there a macro to get the graph (updated on each iteration) from Calc into Draw? Can a macro started in Calc update the graph, push it to Draw, save the image in Draw, and then loop again back to updating the graph in Calc?

I've used Excel VBA to push graphs into PowerPoint and for PowerPoint VBA to pull graphs from Excel, so the external manipulation worked in MSOffice (I believe similar to using C# to use Excel or PowerPoint...a friend explained this part to me).

[aaa4948]

Just an example:

Code: Select all

Sub StoreDrawingOBjectAsImage
  shape = ThisComponent.getSheets().getByIndex(0).getDrawPage().getByIndex(0)
  
  Dim args(1) as new com.sun.star.beans.PropertyValue
  args(0).Name = "URL"
  args(0).Value = "file:///home/asuka/Downloads/foo.png"
  args(1).Name = "MimeType"
  args(1).Value = "image/png"
  
  gef = CreateUnoService("com.sun.star.drawing.GraphicExportFilter")
  gef.setSourceDocument(shape)
  gef.filter(args)
End Sub

Also, (I'm not a programmer!) how do you find the Names and Values used in the CreateUnoService("com.sun.star.drawing.GraphicExportFilter") function? I think the "CreateUnoService()" is the function and I don't know what "com.sun.star.drawing.GraphicExportFilter" is. I think args is an array?

Thanks!

[hanya]

Cool...is there a macro to get the graph (updated on each iteration) from Calc into Draw? Can a macro started in Calc update the graph, push it to Draw, save the image in Draw, and then loop again back to updating the graph in Calc?

Why you need put it back to Calc? If you move the chart into Draw, it would forget the original data range.

Also, (I'm not a programmer!) how do you find the Names and Values used in the CreateUnoService("com.sun.star.drawing.GraphicExportFilter") function? I think the "CreateUnoService()" is the function and I don't know what "com.sun.star.drawing.GraphicExportFilter" is. I think args is an array?

See documentations:
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide
http://www.openoffice.org/api/docs/comm ... ilter.html

[aaa4948]

Cool...is there a macro to get the graph (updated on each iteration) from Calc into Draw? Can a macro started in Calc update the graph, push it to Draw, save the image in Draw, and then loop again back to updating the graph in Calc?

Why you need put it back to Calc? If you move the chart into Draw, it would forget the original data range.l

Sorry. What I meant is after I push the first frame from Calc to Draw and save, I need to get the macro back to Calc to update the graph and push frame 2 to Draw and save. Really, I'm just wondering if OOBasic can manipulate two programs from one instance (can a Macro in Calc drive Draw simultaneously?).

Thanks!

[aaa4948]

I think I have some pseudo code for what I'm trying to do:

Code: Select all

sub Main
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

i = 1

Do while i < 10

   dim args3(0) as new com.sun.star.beans.PropertyValue
   args3(0).Name = "ToPoint"
   args3(0).Value = "$A$1"

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

   dim args4(0) as new com.sun.star.beans.PropertyValue
   args4(0).Name = "StringName"
   args4(0).Value = i

   dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

   dim args5(1) as new com.sun.star.beans.PropertyValue
   args5(0).Name = "By"
   args5(0).Value = 1
   args5(1).Name = "Sel"
   args5(1).Value = false

   dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args5())

   dim args6(2) as new com.sun.star.beans.PropertyValue
   args6(0).Name = "URL"
   args6(0).Value = "file:///C:/TEMP/" & i & ".html"
   args6(1).Name = "FilterName"
   args6(1).Value = "HTML (StarCalc)"
   args6(2).Name = "FilterOptions"
   args6(2).Value = ""

   dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args6())

   i = i + 1

Loop

End Sub

This line dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3()) produces an error with the "0". Also, I'm sure that this line args6(0).Value = "file:///C:/TEMP/" & i & ".html" will cause problems. But that's really what I need to loop through!

[rudolfo]

Code: Select all

i = 1

Do while i < 10

   dim args3(0) as new com.sun.star.beans.PropertyValue
   args3(0).Name = "ToPoint"
   args3(0).Value = "$A$1"
   dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

   dim args4(0) as new com.sun.star.beans.PropertyValue
   args4(0).Name = "StringName"
   args4(0).Value = i
   dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

   dim args5(1) as new com.sun.star.beans.PropertyValue
   args5(0).Name = "By"
   args5(0).Value = 1
   args5(1).Name = "Sel"
   args5(1).Value = false
   dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args5())

   dim args6(2) as new com.sun.star.beans.PropertyValue
   args6(0).Name = "URL"
   args6(0).Value = "file:///C:/TEMP/" & i & ".html"
   args6(1).Name = "FilterName"
   args6(1).Value = "HTML (StarCalc)"
   args6(2).Name = "FilterOptions"
   args6(2).Value = ""
   dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args6())

   i = i + 1

Loop

How did you get this code? Looks very much like the macro recorder, because it has the typical args1(), args(2), ... blocks of the macro recorder. But it seems like you have removed the first two steps as the sequence starts with (3). Never mind I guess this is some no-operation code that is not really needed and can be skipped.

I will try to translate your code into human lanuage and will ask you to see if this makes sense:

• 3 GoToCell $A$1: place the cursor at the absolute position A1 (the first cell in the sheet). Usually in a macro it is not needed to move the cursor somewhere. Typically you want to do something with the object at the specified position/cell. Don't go there, but directly address the wanted object: theCell = thisSheet.getCellByPosition(0,0) (Counting start at zero, Column A is 0, B is 1, and so forth). thisSheet is a variable that you need to set before with something like thisSheet = ThisComponent.getSheets().getByIndex(0). And no ThisComponent is not another variable, that you need to set before. It is a predefined (global) variable in every macro that is a reference to the currently active document (Calc.ods or Writer.odt, ...)
• 4 EnterString: sets the text of the cell where the cursor is currently located. In other words the text content of Cell A1 to i (which is 1, 2, 3, ... depending on how often we have run through the loop). As we have a reference to the cell, this part is short and quick: theCell.setString(i) or alternatively theCell.setValue(i) (works also because i is a numeric value, the loop counter)
• 5 GoUp By 1 (don't select) Now, this is a bit strange, because there is nothing above cell A1. Nevertheless you won't use such a movement in a macro, but instead you will again address the wanted cell directly with: secondCell = thisSheet.getCellByPosition(1,2) (this is Cell B3)
• 6 SaveAs HTML in file:///C:/TEMP/i.html This saves the current sheet as HTML. Again the preferred method to do this, is to use ThisComponent.storeToURL("file:///C:/TEMP/i.html", args). See the section Saving and Exporting Documents on the page StarDesktop for a good introduction. In short storeToURL() uses nearly the same parameters as executeDispatch() with "uno:SaveAs", but it doesn't integrate the URL into the property args parameter, but instead passes the URL as a parameter on its own. But all the other settings from index (1) and the following indexes can be used in storeToURL

So here is the code where the dispatcher code is replaced by methods of UNO objects.

Code: Select all

thisSheet = ThisComponent.getSheets().getByIndex(0)  ' (0) is the first sheet in the document

i = 1

Do while i < 10

   theCell = thisSheet.getCellByPosition(0,0)
   theCell.setString(i)

   args(0).Name = "FilterName"
   args(0).Value = "HTML (StarCalc)"
   args(1).Name = "FilterOptions"
   args(1).Value = ""
   ThisComponent.storeToURL("file:///C:/TEMP/" & i & ".html", args)

   i = i + 1

Loop

There is one crucial point we have to think about: I have said that ThisComponent is the current document. If it is saved to HTML, what does this mean. After all we depend on the current document to execute our macro and a HTML document doesn't have A1 or H7 spreadsheet cells ... In other words we saw the branch on that we are sitting. Chances are that storeToURL is rather comparable to "Export as" and not to "Save As" from the File menu, because there is also a storeAsURL() method that sounds more like "Save As". But I don't know, ... you have to experiment with this.

Talking about experimenting. I don't know all these things because I have read several books, but based on experiencing. There is an extremly clever and helpful extension MRI, that can tell you all the properties and methods of UNO objects. The author of this extension sometimes hears on the name hanya. And other folks have written an introduction for this MRI extension, that you can find in the tutorial section of this forum: Introduction into object inspection with MRI.

[aaa4948]

Thanks for the reply! Lots of info! It's so close, but I think I need a line in the macro to refresh the workbook formulas. Updating the target to value (cell A1) to equal the value i worked. When the Macro is done, each i.html file has cell A1 set to i. But the graphs are all identical and didn't update. I added a "Wait 5000" after it changes the value, but I realized that the formulas in column E aren't updating. I added the following where args1() is unset:

Code: Select all

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

I'm not sure how to refresh a all formulas from a macro. But so close now!

To start up at the top of your reply, I did get my code from the macro recorder (I love them and as a non-programmer, that's my only option). And I definitely agree about not needing to move the cursor but rather just set the value. Also, the Save As does seem to function more as an export. Thx again for the help!!