Export or Save As PDF A Specific Range in LibreOffice Calc Sheets using Macro

3 min


Exporting a LO sheet’s content to PDF is often necessary because of wide use of PDF files for distributions, reporting etc. In the earlier tutorial I have shown how to export an entire sheet’s content to a pdf file. In this tutorial I will show how to export a specific range (e.g. A1:B2 etc) as a content of a pdf file.

How to export an entire sheet to pdf tutorial can be found here.

Objective

I have a source data like this (see below). The pdf should contain the color and values from A1 to I3 range and the pdf file name should contain the current Date, the Sheet Name (i.e. in this example ‘mySheet’) and a fixed value from a cell (i.e. in this example B5).

Source LO Sheet
Source LO Sheet

Details of the Macro

There are two parts of this macro. First to dynamically prepare the pdf file name based on certain value. The pdf file name consists of the sheet name, a value from B5 cell and date. Below piece of code does this part:

   dim fileName
   fileName = ThisComponent.Sheets(0).Name & _
   "_ratie_" & ThisComponent.Sheets(0).getCellRangebyName("B5").String & _
   "_" & Replace(Date,"/","-") & ".pdf"

[update#1]
One of my reader asked about on how to get the Active sheet name Or the name of current Calc sheet is selected. To get the current active selected sheet’s name, use below::

fileName = ThisComponent.getCurrentController.getActiveSheet.Name & _

Now, create a Frame object and dispatcher object to Uno service com.sun.star.frame.DispatchHelper which would help to export the pdf via function.

Create an empty file as well in any path of your own and close the file.

   ' change the path below as per your needs
   path ="file:///home/arindam/" & fileName
   Open path For Append As #1
   Close #1

Now, its time to put the content inside the pdf. To do that, we need to use Filters. Filters are the types and value pairs which can be put in an array and can be passed to executeDispatch function as an argument. The types are pre-defined and when passed they will be processed what they are supposed to do. In this example, we need to pass the filename and the Range of LO Sheet which needed to be printed only in the pdf, nothing else form the active sheet. The in-built Filter property FilterData to be used here.

   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   oSheet =  ThisComponent.CurrentController.getActiveSheet()
   oCellRange = oSheet.getCellRangeByName("A1:I3")
   
   dim aFilterData(0) as new com.sun.star.beans.PropertyValue
   aFilterData(0).Name = "Selection"
   aFilterData(0).Value = oCellRange

   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "URL"
   args1(0).Value = "file:///home/arindam/" & fileName  ' change the path below as per your needs
   
   args1(1).Name = "FilterData"
   args1(1).Value = aFilterData()

Now pass this argument to the executeDispatch function as an argument.

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

Running the Macro

Here is the pdf file that is created with the range contents for this tutorial:

SEE ALSO:   Get the Selection Cell and Range Address using Macro in LibreOffice
PDF File Created After Run
PDF File Created After Run
Run - pdf Contents
Run – pdf Contents

Complete Macro

Sub ExportRangeToPDF()
   dim document as object
   dim dispatcher as object
   dim fileName
   fileName = ThisComponent.Sheets(0).Name & _
   					"_ratie_" &  ThisComponent.Sheets(0).getCellRangebyName("B5").String & _
   					 "_" & Replace(Date,"/","-") & ".pdf"
   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   oSheet =  ThisComponent.CurrentController.getActiveSheet()
   oCellRange = oSheet.getCellRangeByName("A1:I3")
   
   dim aFilterData(0) as new com.sun.star.beans.PropertyValue
   aFilterData(0).Name = "Selection"
   aFilterData(0).Value = oCellRange
 
   ' change the path below as per your needs
   path ="file:///home/arindam/" & fileName
   Open path For Append As #1
   Close #1
 
   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "URL"
   args1(0).Value = "file:///home/arindam/" & fileName  ' change the path below as per your needs
   
   args1(1).Name = "FilterData"
   args1(1).Value = aFilterData()
 
   dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
End Sub

If you face any problem running this, drop a comment using comment box below.


We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!

Join our Telegram channel and stay informed on the move.

Also Read


Like it? Share with your friends!

Arindam

Creator of debugpoint.com. All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

12 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
12
0
Would love your thoughts, please comment.x
()
x