How to upload an Excel spreadsheet as PSML

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.

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.

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.

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

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:

Then click on Project Files.

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

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

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.

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.

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

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

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

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