techweb

Using Excel as a CMS

How many times have you seen a website, especially a small business one, where the blog has only one or two entries (usually "welcome to my new website!") probably written when the site was first uploaded - and probably by the developer too!

In my experience, this is often because of the effort involved in firing up Wordpress and trying to work it all out on an infrequent basis - even Wordpress can be too complicated for some non-technical business owners.

If there is no blog, then the site could be created as a static HTML site but then they would need to consult a developer every time even the smallest change needed making, such as pricelists or service details.

So for a lightweight site that needs the odd bit of editing, how about using something they are familiar with - like Excel?

Excel XML FTP HTML

We can use Excel to edit content - its grid layout makes it easy - and then provide a button that with one click will upload it to a website where it can be picked up by the web page and displayed in the relevant places.

It also means we don't have to code or style a new front-end screen, and users don't have to learn yet another interface.

The key to making this work is to get Excel to gather up all the data and bundle it into an XML or JSON file and FTP it to the web server. From here we can access these data files and slot the data into our HTML web pages.

Luckily, this is possible in Visual Basic for Applications (VBA) - an abridged Visual Basic programming language which is built into Excel (and other Office apps).

I've actually been using this Excel process for years on a site of mine! It's a historical thing really. When I first started the site databases were not included in the hosting package as standard and cost quite a bit extra.

I had PHP server side scripting and some experience of XML so I came up with a VBA script that would take Excel data and upload it as XML file that would serve as my 'database'

It also saved a lot of time designing and coding a custom front-end that would be needed anyway if I had used a database.

What Would This be Best Suited For?

While I'm not advocating the use of Excel to manage a massive blog, in my experience, most small businesses will want the ability to change these items on their website:

  • Contact details
  • Social media links
  • Price lists
  • Product information
  • Staff members

All of these bits of data can easily be laid out in an Excel sheet and using this method we can let the client make these changes and upload directly to their website as easily as pressing a button - and without fear of breaking anything.

Example and Code

Let's take an example of a small service based business like a nail salon.

View a Demo here
Info XML file
Pricelist XML file

They probably use social media for promotion and posting images etc, so they don't need a blog (we can easily integrate these posts into the site), but for a list of services and prices where things might change on a regular basis, we could use Excel to maintain this.

Here is how to set up a very basic Excel CMS for a business like this. I'm using XML as the data file format but it could just as easily be JSON or some other text format.

We need 4 main things:

  • An Excel sheet, containing the data we want to show on the site
  • An Excel VBA script to handle data capture and XML file creation
  • A DOS batch file to do the actual FTP upload of the XML file*
  • Web page on the server to open the XML file and display the data

Note: * I'm using Windows here, but it should work on a Mac with some minor script changes. VBA is not available on mobile versions of Excel

Download The Example Files

Link to Github Project

The Excel Sheet

excel sheet

Nothing much to see here, it's just a workbook with 2 sheets - one for service details and prices, and another for company information. We can style cells as required, only plain text will be placed into the XML file, although HTML tags are allowed and they will be rendered on the website.

Note that our script is programmed to stop when it reaches a blank cell in the first column - this is how it knows it has reached the end of the data.

VBA Script

Use the link above to see the VBA Script in full. Go to Developer Visual Basic and paste in this whole code. Under the developer menu, click insert, then in form controls, insert a button and link to to one of the export macros (export_info or export_pricelist).

If you don't have the developer menu, then go to file options customise ribbon and ensure that developer is ticked in the right hand pane.

This script first prepares the XML output file in the same folder as the workbook. Then starting from the 7th row down until it reaches a blank row, it will output the columns of data in a key/value pair where key is the tag name and value is its value.

For the pricelist it is similar, except we have another column of data so this file uses tags along with attributes to store some of this data.

After that, provided some data was captured, it will prompt the user to upload the file which involves invoking a shell script (DOS batch file in this case) to do the actual upload.

DOS Batch File

The Excel script calls this batch file to actually carry out the FTP side of things. This file is called auto_ftp.bat and is located in the same folder as the Excel workbook.

@echo off
"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
  /log="D:\0files\winscp.log" /ini=nul ^
  /command ^
    "open ftpes://user@yoursite.com:password@ftp.yoursite.com/ -certificate=""*""" ^
    "lcd %1" ^
    "put %2" ^
    "exit"

This opens a secure connection (ftpes) although I've used * for the certificate which means accept any certificate. Without this, the script would stop and the user would need to accept it.

A better way would be to paste in the actual certificate id, although this will become invalid when it expires and the script will again stop for input (Free Let's Encrypt certificates expire every 3 months).

The %1 and %2 represent the parameters we passed to the script in Excel: the local path and the filename to transfer.

Notice that I'm using WinSCP for FTP. This open-source FTP program provides a much better command line option than the regular DOS FTP command.

DOS FTP doesn't support passive FTP connections that most web hosting services will require. You only need to install WinSCP, no other configuring is required as we only need the command line facility.

Server Side Web Page

Use the link above to see the HTML page. I'm using some inline PHP to open the XML files and display the contents in relevant places. We could also use Javascript if required.

This simply uses simplexml_load_file to get the XML data into arrays - we can then pick out the required information at the relevant point in the scripts with some inline PHP.

Tips

  • Use an FTP account that only has access to one folder, and password protect it so that it cannot be accessed publicly
  • Consider also creating and saving a CSV file and uploading that to the folder too - this is effectively a backup that means we can rebuild the Excel sheet should the local copy get lost or damaged
  • If the content file starts getting large, then consider splitting it out (perhaps by month for blogs), and/or create a summary files (eg with just headlines and links rather than an entire blog post)

This article is filed under: TECHWEB

TAGS