Tutorials

Task-driven tutorials and recipes for PageSeeder

How to upload an Excel spreadsheet as PSML

Skills requiredXML, XSLT, Apache Ant, Pageseeder
Time required (minutes)30
Intended audienceDeveloper
DifficultyMedium
CategoryDocument

Objective

This tutorial will explain how to create PSML files from an Excel file using an Ant publishing task (upload).

Prerequisites

To complete this tutorial it is necessary that the user has Administrator privileges.

Tutorial

All the files for this tutorial are on Github .

It will use the publishing task of type 'upload'. 

The PageSeeder project name used in this tutorial is 'tutorial' and the group name is 'films'. However both names can be replaced by an alternate project and group name. 

publish-config.xml

This file adds actions to the PageSeeder screen. The sample file at Github will add a new upload task when it recognizes an Excel file (XLSX).

The correct folder in which to place this file is described in the article Publish template folder.

Uploading this file to PageSeeder:

Go to the Project files page located under the the Dev tab in the Developer perspective.

How to go to Project Files

 

On this new screen, create the folder structure shown below in case it doesn't exist.

If the publish-config.xml doesn't exist, copy the file from the sample package to the specified folder. If it already exists, copy only the content from inside that file. Copy the entire element <action> from the sample and paste it into the existing publish-config.xml file.

publish-config.xml file location

 

Validating this step:

Validate that this is working correctly by trying to upload any Excel file.

Go to the documents folder and on the right menu click on Upload.

Click on Upload

 

Choose an Excel file and click on Options. If 'Upload as PSML' can be seen, Step 1 has been properly configured.

Validating Upload action creation

More details

In case a little customization is desired, further information can be found in the articles PSML document publishing and publish-config.xml.

Publish script folders

This folder is required to contain the ANT script and associated files.  More details regarding it can be found in Publishing.

Click on Toolbox from the Dev menu:

Tool Box Menu

 

Then click on Project Files.

Project Fileslink on Toolbox Page

 

Log in to the PS Publisher and create folders shown below.

ValidatingPublisherFolderStructure.png

ANT script

In this step an ANT script will be created.

Copy the files build.xml, films-template.xsl and ps-ant-excel-*.jar to the folders created in the previous step. It should look like the image below.  For any ANT script the build.xml will need to be created (the other two files are just for this tutorial).

Publisher Files Location

File build.xml

This has an Ant script to:

  • Upload the source file (.xlsx) for processing;
  • Call the import-spreadsheet (ps-ant-excel-*.jar) ANT task to transform the OpenXML from Excel to an XML format that is easier to process. This format will be referred to as “interim”;
  • After creating the interim files, ANT calls films-template.xsl to transform them to PSML;
  • Upload and register the PSML files in PageSeeder.

Overview.png

This script can be customized for different requirements. More details regarding the parameters used in it can be found in Publishing.

File ps-ant-excel-*.jar

This has the ANT task and Java logic to read the Excel File(Open XML) and create the new XML files (Interim format).  Some interim files examples can be found in interim.zip from Github. 

There are three types of interim files:

  • One is the workbook.xml which maps to the second type;
  • Second is the Sheet*.xml which maps to the third type. Each worksheet in Excel, creates a Sheet*.xml;
  • Third represents each row of each sheet, excluding the first line which is the header row. If the sheet has 100 lines, the process generates 99 files due to ignoring the header row.

File films-template.xsl:

This is an example of transforming the interim files to PSML. Feel free to customize it according to any particular requirements.

Testing

In order to test, the MoviesData.xlsx will be uploaded to the "films" group.

Go to the documents folder and on the right menu click on Upload.

Click on Upload

Load MoviesData.xlsx , click on Options and "Upload as PSML".

Note

When using your own spreadsheet make sure of the following:

  • The values in the titles column have no leading or trailing spaces.
  • The values in the titles column are unique as these will become filenames.

 

Upload as PSML

 

On the pop-up "Upload Confirmation", click on continue and wait for the five steps to finish.

 

Upload Completed

If the files are visible on the document browse page, they were successfully converted.

Outcome Result

 

The Annie Hall content should be similar to the below image.

 

Outcome Annie Hall

Created on , last edited on