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 is 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 our sample to the specified folder. If it exists, then copy only the content inside and inclusive of the element <action> from our sample, pasting it in the publish-config.xml file that already exists.

publish-config.xml file location

 

Validating this step:

In order to validate this step, let's upload an Excel file and check if the system displays this option.

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

Click on Upload

 

On the new screen drag and drop an Excel file in the indicated area and click on Options. If 'Upload as PSML' can be seen, then it means this step was successfully completed.

 

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 Publisher.

Back in PageSeeder, 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 the Ant script to:

  • Get file uploaded;
  • Call the ant task import-spreadsheet (ps-ant-excel-*.jar) which transforms the open xml from the excel file to a more friendly xml (these files are called interim files). It is part 1 of the below image. After the interim file is created, it calls the  films-template.xsl to transform these interim files to PS-XML. It is Part 2 of the below image;
  • Then finally, upload these new PSML files to PageSeeder.

Overview.png

This script can be changed to meet custom needs. 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 ready the Excel File(Open XML) and convert/transform it to a new XML files (Interim files) and transform the interim files to PSML by using the "films-template.xsl".  At Github, some interim files examples can be found in interim.zip. 

There are three types of interim files:

  • The first is the workbook.xml which has the mapping to the second type.
  • The second is the Sheet*.xml which has the mapping to the third type. For each sheet in the Excel, a Sheet*.xml will be created. e.g. If it has two sheets, then Sheet1.xml and Sheet2.xml will be created.
  • The third type represents each line of one sheet (excluding the first line which is the title/header line). If the sheet has 4 lines, then it generates 3 files due to the first line being the title line.

File films-template.xsl:

This is just an example of how to transform the interim files to PSML. Feel free to change this logic and customize it according to custom needs. All interim files will be transformed by this file.

Testing

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

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

Click on Upload

On the new screen, drag and drop the MoviesData.xlsx file in the indicated area, click on Options and "Upload as PSML" and Continue.

Note

When using your own spreadsheet make sure of the following:

  • There aren't any leading or trailing spaces in the titles.
  • The titles are unique as these are used as 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