Libreoffice .xls to .pdf conversion breaks single page content into multiple pages on Ubuntu 18.04

ABHI1 · February 7, 2020, 7:10am

I am facing issue during conversion of .xls to .pdf through libreoffice command line mentioned below.

/usr/bin/soffice --headless --nologo --nofirststartwizard --norestore --convert-to pdf:writer_web_pdf_Export --outdir '/home/user/Downloads/' '/home/user/Downloads/file.xlsx'

Its converted successfully, however it breaks .xls single content page into multiple pages in pdf. I want to convert xls single content page to pdf single page through page scaling. We can do that manually through Menu Formatting → Page ->Sheet ->Scale Scaling mode. But I don’t want user interference during conversion of Xls to Pdf!

I have gone through many Articles but not found the proper solution as I want it through command line. Is there any option in command line syntax to scale it on the fly?

ls_291 · March 7, 2023, 3:29am

hi，What is your version？

Wanderer · March 7, 2023, 7:44am

Why is this important, 3 years after this thread was opened, and several month after @mikekaganski showed new options on command-line and this thread also received an update to use LO online with docker by @dokiCRO ?

ls_291 · March 7, 2023, 9:07am

sorry， @dokiCRO method is not suitable for me, i try it but no outcomes. i want to xls files convert to pdf,special the xls files have multi sheet, use the command line

libreoffice6.2 -env:UserInstallation=file:///$HOME/.libreoffice-headless/ --headless --convert-to pdf /data/shared/xls/沪2022企业3月2.xls --outdir /data/shared/xls

generate pdf file, but there is have a problem, when the number of table rows exceeds 45, the table contents below 45 will be lost, so i want split excel sheet into mulit excel files, but i haven’t found a perfect way to split the excel sheet, keep the original format of the table after splitting

can you help me, please.

mikekaganski · March 7, 2023, 9:10am

You seem to use an old version (6.2), for something opposite to what is discussed here at all (you want to split pages?). Please ask your own question.

ls_291 · March 7, 2023, 9:35am

hi, i opened a new topic in here

Thank you very much if you can help me

mikekaganski · February 7, 2020, 7:44am

2022-11-04: updated to mention the command line syntax available since version 7.4.

First of all, you used a wrong export filter name in your command line. As explained here, in this case you (not) needed to use calc_pdf_Export, which is used by default anyway when you export a document handled by Calc and use pdf output specification.

But that will not change anything. Until version 6.4, there was no way to do what you need. In 6.4, a Whole sheet export feature was implemented. It has no command-line API (yet? just like any other PDF export setting available in the PDF export dialog); but you may note that whatever settings you set in the dialog in the UI, are saved for following use when you export to PDF directly, and also when you export using command line.

So a simple way would be:

Install version 6.4;
Export a spreadsheet to PDF interactively (in UI mode), and in the export dialog, set the mentioned option checked;
Use the command line you used.

Of course, that would make your export dependent on the export setting defined in the UI, which might be not ideal in case when you use LibreOffice both for batch processing, and interactively on the same box. In this case, you might want the settings used for batch processing to be independent from interactive mode. To do that, you may use -env command line parameter to specify a separate user profile directory; so starting LibreOffice interactively with the said parameter once to initialize the profile and make necessary filter settings, and then adding that switch to batch processing command line, would allow you to have the necessary customizations independent of your UI usage.

UPDATE 2022-11-04

Starting from v.7.4, it is now possible to define this setting through the command line, without preparing a dedicated profile:

soffice --convert-to 'pdf:calc_pdf_Export:{"SinglePageSheets":{"type":"boolean","value":"true"}}' test.ods

On Windows, single quotes are not accepted in the command line, and internal double quotes in JSON must be escaped, so this becomes

soffice --convert-to "pdf:calc_pdf_Export:{\"SinglePageSheets\":{\"type\":\"boolean\",\"value\":\"true\"}}" test.ods

ABHI1 · February 7, 2020, 12:36pm

@Mike2 Thanks for the detailed information, however “To do that, you may use -env command line parameter to specify a separate user profile directory; so starting LibreOffice interactively with the said parameter once to initialize the profile and make necessary filter settings, and then adding that switch to batch processing command line, would allow you to have the necessary customizations independent of your UI usage.” still I didn’t understand how to do this through -env command!! Can you please show me step by step to set scaling parameter through -env , it would be more helpful to me.

mikekaganski · February 7, 2020, 12:52pm

Did you try what I write before that -env part, to see what I’m talking about?

ABHI1 · February 10, 2020, 5:03am

@Mike2 As above you have mentioned that after saving in dialog box configuration you can export through UI and command prompt but I don’t want to save anything on dialog box, I want to do all the configuration part also through command line only. As you have already mentioned, there is no command line API, So it will not solve my purpose.

Later you have mentioned “-env” configuration option. What I have understood we will set same set of configuration which we have set through dialog box on server side directly in diff env and then when we will call it through command line those configuration will automatically get apply. If I am correct then I want to understand whatever configuration we want to save on server side (For exp:- Full sheet preview) what would be the syntax to configure this configuration and in which file do I need to save.

Hoping you have understood my concern!

ABHI1 · March 3, 2020, 5:48am

We can achieve this through applying macros on that specific XLSX on run time and after than we can execute PDF conversion command. The steps I followed to implement this solution mentioned below.

Step 1 :- Store a macro on server.

in my case I have stored here (/opt/libreoffice/presets/basic/Standard/Module1.xba).
Content of Module1.xba is

<?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd">
    <!--
    * This file is part of the LibreOffice project.
    *
    * This Source Code Form is subject to the terms of the Mozilla Public
    * License, v. 2.0. If a copy of the MPL was not distributed with this
    * file, You can obtain one at http://mozilla.org/MPL/2.0/.
    *
    * This file incorporates work covered by the following license notice:
    *
    *   Licensed to the Apache Software Foundation (ASF) under one or more
    *   contributor license agreements. See the NOTICE file distributed
    *   with this work for additional information regarding copyright
    *   ownership. The ASF licenses this file to you under the Apache
    *   License, Version 2.0 (the "License"); you may not use this file
    *   except in compliance with the License. You may obtain a copy of
    *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
    -->
    <script:module xmlns:script="http://openoffice.org/2000/script" script:name="Module1" script:language="StarBasic">REM  *****  BASIC  *****
    Sub FitToPage
      Dim document As Object, pageStyles As Object
      document   = ThisComponent
      pageStyles = document.StyleFamilies.getByName("PageStyles")
      For i = 0 To document.Sheets.Count - 1
       Dim sheet As Object, style As Object
       sheet = document.Sheets(i)
       style = pageStyles.getByName(sheet.PageStyle)
       style.ScaleToPagesX = 1
      Next
      On Error Resume Next
      document.storeSelf(Array())
      document.close(true)
    End Sub
    Sub Main

    End Sub
    </script:module>

Step 2:- Apply the above macro on xls file through below command.

$cmd = 'export HOME=/var/www &&  /opt/libreoffice/program/soffice --headless --nologo --nofirststartwizard --norestore  /home/usr/demo.xls  macro:///Standard.Module1.FitToPage';

Step 3 :- Generate Pdf after applying macro through command line.

$cmd = 'export HOME=/var/www && /opt/libreoffice/program/soffice --headless --nologo --nofirststartwizard --norestore  --convert-to pdf:writer_web_pdf_Export  --outdir  /home/usr/  /home/usr/demo.xls';

Note :- The only catch in this implementation is the spreadsheets file should not be read only.

dokiCRO · November 3, 2022, 1:30pm

None of these solutions worked for me.

My solutions is using libre office online, I found that solution here:
https://wiki.documentfoundation.org/ReleaseNotes/6.4#Full-Sheet_Previews

This part is important:
Available in the Online API

The new full-sheet preview feature has also been added to the Online REST API. The command below can be used to get a full-sheet preview output of a spreadsheet document in the PDF format:

curl --insecure -F "data=@YourSpreadSheetDocument.ods" -F "FullSheetPreview=true" https://localhost:9980/lool/convert-to/pdf > out.pdf

Please note that using the --insecure option is only needed while testing the feature on a local setup, and we recommended against using it for non-testing cases.

So this is how I converted XLS to PDF on the server.

Here is tutorial how to install LibreOffice online with docker: LibreOffice Online – How to Integrate into your Web Application – Ralph's Open Source Blog

There is one extra step with adding extra_params to docker-compose.yml so it can accept http requests and allow requests from machine

Here is full docker compose file

version: "3.6"
services:

  libreoffice-app:
    image: collabora/code:6.4.8.4
    container_name: libreoffice-app
    expose:
      - 9980
    ports:
      - "9980:9980"
    environment:
      - username=admin
      - password=adminadmin
      - extra_params=--o:ssl.enable=false --o:ssl.termination=true --o:net.post_allow.host[0]=.+ --o:storage.wopi.host[0]=.+
    restart: always

When you start this service you should be able to convert XLS to PDF with this command
curl -F "data=@filename.xls" -F "FullSheetPreview=true" http://localhost:9980/lool/convert-to/pdf > out.pdf

I have spend a lot of time searching for a solution how to convert XLS to PDF without breaking single page content into multiple pages and this is the only solution that worked for me…