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

Advertisement

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:

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.

You may also like...

  • Jacky

    Hi Arindam

    First of all, the macro is very helpful, thanks!

    Two quick questions

    1) what is the propose of

    path =”file:///home/arindam/” & fileName
    Open path For Append As #1
    Close #1

    I can run the macro without the above statements

    2) can we export the sheet content and append it to a PDF file (not overwrite it)
    As I need a macro to export a sheet to a PDF, updated the sheet content and then export the updated sheet again to the same PDF as page 2.

    Thanks.

    Jacky

  • Mono Roy

    Hi Arindam –

    Thanks much for this macro. Very helpful.

    Now is there a way to set the margins for the PDF and also the cell width for each column I am printing.

    I am printing 5 columns and I would like it take up all of the page at .5″ margins. I have specific sizes I have calculated for my coumns by placing them in a Writer doc by DDE so i know exactly how I want it to look.

    I guess the other option is to just place it in a Writer doc and accordingly align margins as I am doing now.

    Please let me know if you get a chance.

    Thank you again.

    This was very helpful.

    Mono

    • The pdf would be generated with exactly what size of columns/rows you have in Calc. If you want to change the column width you can use below code. Place it somewhere before the pdf generation.

      Sub col_chg()
      oDoc = ThisComponent
      oSheet = ThisComponent.getCurrentController.getActiveSheet
      oColumn = oSheet.getColumns.getByIndex( 1 ) ‘ col B
      oColumn.Width = 2.5 * 2540
      End Sub

  • Punit Patpatia

    Thanks you very much for this wonderful post!
    Just a quick question, How can I change the layout of PDF page to “Landscape” from “Portrait”?

    • Punit Patpatia

      Never Mind, I found the error.

      oObj2.IsLandscape = True
      oObj2.Width = 29700
      oObj2.Height = 21000

  • Marek Pluhař

    Hello, could you please upload this macro in .odt file? I canť use this macro in Libre Office.

    • This macro is specifically designed for LibreOffice Calc spreadsheet.

  • Marek Pluhař

    Hello, please How could be path? I use C://Desktop…, but this donť work.

    • I guess you are running LibreOffice under Windows. So try using the basic path system as below and see if it works:

      path =”c:anyfolder” & fileName

      • Marek Pluhař

        Thank you for your answer, but program says: “Fault input/output”.

        • Try all of these to see if it works.
          a) Make sure you have write permission to the folder where you are creating the file. The easiest way to do is to go to the path via explorer and create an empty text file and save.

          b) Change below two lines in code where the path URI is present for the file as below. “test” is the sample folder name I have used.

          path =”c:test” & fileName

          args1(0).Value = “file:///c:/test/” & fileName

          Try and let me know.

Translate »