Export as png with macro

[Groofy]

Hello all. This is my first post here so firstly I would like to say: Hello fellow Libre Office users.

I have some calc, and for now i’m doing it by hand, but was wondering if it’s possible to make macro that will export sheet in single button press with name that is in particular cell. Name could be conditional like:
if(a5=“FAIL”;b4" "a5;b4)
For now export as png is working fine it just requires a few more clicks and i have to make dozens of those files, so i’m looking for possible way to make this a little bit faster.

Also i read some posts about updating external links, but i think i’m just too stupid to understand them, because i would like also make 2nd macro button that will perform update of expternal links.

[elmau]

Do you want to export a range of cells?

[Groofy]

Hello, Thank You for reply.
Yes, they have image in background which is 1000x1414px and range of cells to export is pretty much same. Page size is set to be exact size mentioned above. Area to export starts at A1 cell. Right now I’m doing it by hand without much problem via file->export->from drop down list i choose png and manually change file name which as i said before is in one of cells inside exported range. Only was looking possible way to add one macro button that will perform those things with one click.

[Groofy]

WhoaPreformatted text that is totally not what i expected. 2nd answer with ready to edit solution.
I’m doing something wrong for sure, because i’m getting exception in line:
.SetSourceDocument(oDoc.CurrentController.Selection)

Type: com.sun.star.lang.IllegalArgumentException
Message: cannot coerce argument type during corereflection call:
arg no.: 0 expected: "com.sun.star.lang.XComponent" actual: "com.sun.star.uno.XInterface".

This is how I edited it for myself.

Sub Cells2PNG2 'export selected cells to PNG
	dim oDoc as object, oSheet as object, sDir$, sUrl, sFile$, oCell as object, s1$, oRange as object
	const cRange="A1:BA54" 'your range to export
	sDir=ConvertToUrl("C:\png") 'OUTPUT DIRECTORY
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet 'current sheet
	oCell=oSheet.getCellRangeByName("I25") 'cell I25
	sFile=oCell.string 'string from A5
	if sFile="FAIL" then
		sFile=oSheet.getCellRangeByName("AB9").string & "_" & sFile 'file name: AB9_I25
	end if
	sUrl=sDir & "/" & sFile & ".png" 'url of exported PNG
	rem copy&paste&export2PNG
	oRange=oSheet.getCellRangeByName(cRange) 'your range
	oDoc.CurrentController.Select(oRange)
	uno1(oDoc, "Copy") 'copy cells
	dim props(0) as new com.sun.star.beans.PropertyValue
		props(0).Name="SelectedFormat" : props(0).Value=2  'apparently this means bitmap format
	uno1(oDoc, "ClipboardFormatItems", props) 'paste as graphic
	dim oExporter as object
	dim aExportProps(1) as new com.sun.star.beans.PropertyValue
		aExportProps(0).Name="URL" : aExportProps(0).Value=sUrl
		aExportProps(1).Name="MimeType" : aExportProps(1).Value="image/png"
	oExporter=createUnoService("com.sun.star.drawing.GraphicExportFilter")
	with oExporter 'export to PNG file
		.SetSourceDocument(oDoc.CurrentController.Selection)
		.Filter(aExportProps)
	end with
	uno1(oDoc, "Delete") 'delete pasted graphic
End Sub

Sub uno1(oDoc as object, s$, optional a()) 'execute .uno command
	if isMissing(a) then a=array()
	s=".uno:" & s 'uno command
	createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(oDoc.CurrentController.Frame, s, "", 0, a)
End Sub

So yeah i know i’m asking much but is there anything missing?

EDIT1: Ok are there any more places i should edit things to make them work?
EDIT2: So after it’s working i can assign it to button and it will do it’s job? Sorry just want to be sure.
EDIT3: When i ran it 2nd time it broke image in background. Now it’s not showing image but only image name. I can “fix” that with ctrl+z but just wanted to report that.

[Groofy]

Ok i added that line but problems are still present. Here are both requested files. Note: they are not real documents and not meant to be real and/or be used in real life. That is small project for discord group i’m running for quite a while. But was just wondering about improving system a little bit.
DMV Report SF.ods (429.7 KB)

Please do not redistribute those files.
BTW yours ods is working like it’s supposed to be. Maybe that because results in my cells are from formulas is causing trouble.

[Groofy]

Ok. It seems to work more than less I have few small issues tho. When creates temp sheet png in background is slightly moved to right causing cells to move a bit and text becomes not aligned.
Also naming seems not to work as intended with naming. When I25 cell is empty it names png file as EMPTY and it should name it AB9, and when I25 is “FAIL” it does nothing and it should name file as AB9 (space) I25.
And yeah resolution in Automatic mode is also not right as you mentioned. IDK maybe there is way to rearrange cells so it will be bigger and/or ppi rate.
And solution posted by elmau there are quite a lot of parameters to set up png file.

[elmau]

In this topic you see the solution…

How to Export cell range to images

Hello and welcome! Just try to record the sequence File - Export… - File type - Selection:ON with the macro recorder. This will give you almost ready-to-use code that you just need to change a little - remove the dispatcher and replace its .ExportTo command with the usual storeToUrl spreadsheet method. For example, for export to PNG, I got the following: Sub ExportSelectionToPNGImage(sFileName As String) Dim args(2) as new com.sun.star.beans.PropertyValue args(0).Name = "FilterName" args(0)…

[KamilLanda]

It is perfectly worked solution. If you need change the name of exported PNG, then change the condition if…then (if sFile=“FAIL” …)

Sub Cells2PNG2 'export cells to PNG
	dim oDoc as object, oSheet as object, sDir$, sUrl, sFile$, oCell as object, oRange as object
	const cRange="A1:BA54" 'your range to export
	sDir=ConvertToUrl("d:\mypng") 'OUTPUT DIRECTORY
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet 'current sheet
	oCell=oSheet.getCellRangeByName("I25") 'cell I25
	sFile=oCell.string 'string from I25
	if sFile="FAIL" then
		sFile=oSheet.getCellRangeByName("AB9").string & " " & sFile 'file name: AB9 I25
	elseif sFile="" then 'empty cell I25
		sFile=oSheet.getCellRangeByName("AB9").string 'AB9
		if sFile="" then 'empty I29 and empty AB9
			msgbox("No value in I25 or AB9." & chr(13) & "PNG isn't exported.", 48)
		end if
	end if
	sUrl=sDir & "/" & sFile & ".png" 'url of exported PNG
	rem copy&paste&export2PNG
	oRange=oSheet.getCellRangeByName(cRange) 'your range
	oDoc.CurrentController.Select(oRange)
	Dim Args(2) As New com.sun.star.beans.PropertyValue
	Args(0).Name="FilterName"
	Args(0).Value="calc_png_Export"
	Args(1).Name="FilterData"
	rem .Value from macro recorder
	Args(1).Value =Array(Array("Compression",0,9,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Interlaced",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Translucent",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PixelWidth",0,1192,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PixelHeight",0,1673,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("LogicalWidth",0,27522,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("LogicalHeight",0,38628,com.sun.star.beans.PropertyState.DIRECT_VALUE))
	Args(2).Name="SelectionOnly"
	Args(2).Value=True
	oDoc.storeToUrl(ConvertToURL(sUrl), Args) 'save PNG
	createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(oDoc.CurrentController.Frame, ".uno:Deselect", "", 0, array())
End Sub

[Groofy]

You are legend! This is really working! When I started this I was not expecting that there is solution possible, and now I have complete working file. I sent you message.