Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.
IronPython in Action
Follow Michael on:
Publishing Spreadsheets to the Web
Introduction
Spreadsheets are great ways to collect and organise data. However, they are not always the best format for presenting results. They may depend on access to resources not available to people who want to see the results, or they quickly become out of date. How about presenting the finished spreadsheet as a web page? This could be posted on intranet, the internet, or even just saved or emailed.
Fortunately, Resolver spreadsheets are just programs. We can save the spreadsheet as code and then have access to objects that represent the spreadsheet and everything in it. All it takes to turn it into web pages is a little bit more code...
This article presents a little utility that takes a Resolver spreadsheet exported as Python code and saves the worksheets it contains as HTML files (web pages). It supports most of the Resolver formatting, including image worksheets. Each page has a link to all the worksheets in the spreadsheet.
As well as doing a possibly useful task (although you might want to edit the HTML it produces), it is also an example of how to interact with Resolver spreadsheets exported as code.
Note
Thanks to Resolver Systems for permission to use this code. Parts of the HTML generating code were originally developed by William Reade and Michael Foord (me) as part of a demo.
Spreadsheet to HTML
'Spreadsheet to HTML' is a small Windows Forms application that converts Python files created by Resolver into a series of HTML web pages. It generates one web page per worksheet, and will also save images if there are any ImageWorksheets.
How to Install
Install is possibly a bit high-falutin, but this app only works if you run it the right way. The zipfile contains a batch file and a single directory. These must both be placed into your Resolver install directory. This is usually something like C:\Program Files\Resolver. You can then launch the program by double clicking the batch file.
The reason for having to run Resolver from here is that spreadsheets depend on the 'Library' objects, like Workbook, Worksheet, Cell and so on. They need to be executed from the right location in order to be able to import these.
Export a Spreadsheet as Code
When run, this app loads spreadsheets that have been exported as Python files. There is an example one included in the zipfile, but you can test it with any arbitrary spreadsheet by using the File -> Export -> To Python or File -> Export -> To Python Class menu options.
This brings up a save file dialog allowing you to specify the location and name of the Python file that is generated from your spreadsheet.
Load it Into the App
When you run the 'Spreadsheet to HTML' application (from the batch file), you should see a very simple form appear with a 'Choose' button on it. Clicking this should bring up an open file dialog allowing you to choose a Python file.
The zipfile includes an 'rsl' file called 'TheSpreadsheet.rsl'. There are two Python files that have been created by exporting this spreadsheet, one as a class and one as a standard Python file. You can load either of these as an example of what 'Spreadsheet to HTML' can do.
Save the HTML
Once the Python file has been loaded (assuming there are no errors), you should see a folder browse dialog. When you select a folder, the HTML files will be saved there.
The Results
'TheSpreadsheet.rsl' has four worksheets. A cell range with alternate rows colored, an array of colored cells with the grid off and the location of each cell in bold as the value, the same colored array with the grid on and an image worksheet. Each worksheet has a link 'menu bar' at the top to the other worksheets:
As you will be able to see from the examples, the output isn't bad and shouldn't require too much editing to be useful. The nice thing is, the code that produces this output is very simple and easy to customize.
Customizing the HTML
The output web pages are produces by two modules in the 'SpreadsheetToHTML' directory.
- html.py - most of the template for producing the web pages are stored as variables in this file.
- Htmliser.py - this does the actual work of turning spreadsheet objects into web pages. It uses the templates defined in html.py, but unfortunately does contain some HTML in the code as well.
Rather than editing every individual page that is produced, you can change the templates stored in html.py (and some snippets buried in Htmliser.py) to control the output.
For example the look of the whole page is controlled by the page variable at the start of html.py:
page = """
<html>
<head>
<title>%s</title>
<style type="text/css">
body { background-color: #fffff0; font-size: 10; }
body, td, th { font-family: "Tahome", "Arial"; }
.b_left { border-left: solid 2px #000000; }
The link menu for the worksheets is in the worksheetMenuTable variable:
worksheetMenuTable = """
<table border="1" cellpadding="5" width="300">
<tr>
<th colspan="%s"><strong>Worksheets</strong></th>
</tr>
Individual cells are written out in the TdFromCell function in Htmliser.py.
What it Does (and Doesn't) Do
Even though the code that generates the webpages is quite small, it actually supports most of the different formatting options for Resolver spreadsheets.
What is Supported?
The following things are supported:
Cell values (wouldn't be much good without this)
ShowGrid
If a worksheet has the grid switched off then the grid won't be shown in the generated webpage, and neither will the column and row headers.
Borders
Cell BackColor
Bold
Dates and numbers are right aligned
Image Worksheets
Worksheet level errors are shown (although not especially gracefully)
Column widths and row heights
Cell errors are shown (again, not particularly well)
Number of Decimal Places
Strip Zeroes
Show Negative Symbol
Show Separators
What isn't Supported?
The following things are not (yet!?) supported by CreateHTML. Contributions welcomed!
FontSize and FontFamily
Show Bounds
CreateHTML only generates HTML for the populated region of a worksheet anyway, so show bounds isn't relevant.
Buttons
They are just ignored. You would need a web application to properly support buttons.
Wrapping
I don't think that wrapping can be done properly from pure HTML (or at least switching wrapping off can't be done). Table cells resize automatically to show their entire contents. It could probably be done with Javascript.
Negative text color
How it Works
Everything we have looked at so far is all very well if you just want to use SpreadsheetToHTML. If you are more interested in how to interact with spreadsheets that have been exported as Python, then we need to look at the code.
Most of the work is done inside CreateHTML.py, which is the main application file. A lot of the code in this file is to do with creating and displaying the GUI components, but there are one or two important parts hidden in there.
RSIronPython
If you look in the batch file SpreadsheetToHTML.bat you will notice that CreateHTML.py is run with an executable called RSIronPython.exe. This is located in the bin directory of your Resolver install.
It is the core of the Resolver executable, but runs arbitrary IronPython scripts instead of just starting Resolver. We use it for testing and developing Resolver, but it means that you can use tools like SpreadsheetToHTML without needing IronPython available separately.
Assemblies and Imports
Like any good IronPython program, the first thing that CreateHTML does is add references to the assemblies that is uses and imports the names it needs.
import SpreadsheetToHTML.CreateHtmlRequiredAssemblies
# Do this import now to speed up loading spreadsheets
from Library.Workbook import Workbook
CreateHTML doesn't just add references to the assemblies that it uses - it adds references to all the assemblies that might be used by spreadsheets. This is done just by importing the SpreadsheetToHTML.CreateHtmlRequiredAssemblies module.
Next it imports Workbook from the Resolver library. Again this is not because CreateHTML itself needs it. The first import of the Resolver library code takes a while, so by doing it before the form is first displayed we can dramatically improve the performance of creating web pages for the first spreadsheet.
Loading Spreadsheets
Once you have chosen a Python spreadsheet from the file browser, the hard work of executing the file and extracting the spreadsheet object is done by a function called LoadSpreadsheet:
"""
Load and execute a spreadsheet exported as Python.
This function takes a path to the Python file and
returns a workbook instance.
"""
spreadsheetDirectory = Path.GetDirectoryName(path)
sys.path.append(spreadsheetDirectory)
try:
context = {'__name__': '__main__', '__file__': path, '__builtins__': __builtins__}
h = open(path)
code = h.read() + '\n'
h.close()
exec code in context
Spreadsheet = context.get('Spreadsheet')
if Spreadsheet is not None:
# Spreadsheet was exported as a class
workbook = Spreadsheet()
workbook.recalculate()
else:
# Spreadsheet exported as non-class
workbook = context.get('workbook')
if workbook is None:
# Not a spreadsheet at all
raise NoWorkbook("No workbook defined in this code. Are you sure it is a Resolver spreadsheet?")
return workbook
finally:
sys.path.remove(spreadsheetDirectory)
If you want to write a program that can interact with arbitrary spreadsheets exported as code, this is a useful function to understand. I'll take you through it step by step.
Spreadsheet Directory on the Path
One of the things that Resolver does is to add the directory of the currently executing spreadsheet to the 'system path' (sys.path). This ensures that you are able to import from modules kept in the same directory as the spreadsheet file. We need to make sure that this still works when we are running spreadsheets as code, so we do the same:
sys.path.append(spreadsheetDirectory)
The rest of the function is wrapped in a try: ... finally: that removes this directory from the path again after we have done our work - even if an error occurs.
...
finally:
sys.path.remove(spreadsheetDirectory)
Leaving extra directories in sys.path may have unintended consequences (the wrong module being imported from a different directory), this is why we are careful to remove it.
Execute the Code
This is the most important step. We need to read in the spreadsheet file and execute it. Having executed it we need to be able to get access to the objects afterwards. To do this we use the exec statement (which executes code) and provide an explicit 'context' for the code to be executed in.
h = open(path)
code = h.read() + '\n'
h.close()
exec code in context
We need to pre-populate this context with a few basic names that the Python interpreter would provide if the code was being run in the normal way.
We don't do any error handling here. If the spreadsheet fails to execute properly then it will be caught by the code that called LoadSpreadsheet and the error will be reported to the user (you!):
try:
# Load the spreadsheet
print 'Loading spreadsheet'
workbook = LoadSpreadsheet(filePath)
except Exception, e:
MessageBox.Show("An error has occurred:\r\n%s: %s" % (e.__class__.__name__, e),
"An Error has Occurred",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
Spreadsheet Exported as Class
There are two ways of exporting spreadsheets as code. You can either export the code exactly as it appears in the codebox, or you can export it as a class. (Exporting as a class is useful if you wish to change the data and recalculate.) Depending on which sort of Python file you are loading, a different object will be in the context after executing the code.
If you exported as a class, then the object in the context will be a class - the Spreadsheet class. To obtain a fully calculated workbook we must create an instance of this class and call recalculate:
if Spreadsheet is not None:
# Spreadsheet was exported as a class
workbook = Spreadsheet()
workbook.recalculate()
Spreadsheet as Code
If the spreadsheet was exported directly, and not as a class, then we can just pull the ready calculated workbook out of the context. If there is no workbook, then it obviously wasn't a Resolver spreadsheet in the first place, and we need to raise an error that will then be reported to the user:
# Spreadsheet exported as non-class
workbook = context.get('workbook')
if workbook is None:
# Not a spreadsheet at all
raise NoWorkbook("No workbook defined in this code. Are you sure it is a Resolver spreadsheet?")
And that is it. One way or another we have got a valid workbook, or we have raised an error. If no error has been raised, LoadSpreadsheet returns the workbook - happy with a job well done.
An Alternative Approach
The code in CreateHTML is designed to work with any spreadsheet, stored in any location on the filesystem. This is why it uses exec and the context to execute the code and extract the workbook.
If you were writing an application designed to interact with specific spreadsheets, then there is a better approach that uses the normal Python import machinery to get access to the workbook.
Place the exported Python file within your application module system. You can then do:
workbook = Spreadsheet()
workbook.recalculate()
Much more straightforward...
Sometime I will write about putting a simple C# facade around this, so that you can interact with spreadsheets from other .NET applications and not just IronPython ones.
HTML from Workbook
Once we have extracted the workbook from our spreadsheet file we have access to the full Workbook and Cell API to work out what is in it and generate the HTML.
I'm not going to go through this code in detail, but it is very simple and contained in the Htmliser module. The important function is HtmlFromWorkBook which takes a workbook and returns a list of (name, html) tuples. It also needs to know the output directory, so that it can save images from image worksheets.
In order to generate HTML it iterates over all the worksheets, and all the cells in each worksheet checking attributes as it goes. If you are interested in understanding how to work with cells and worksheets from code then have a browse and see if you can understand what it is doing.
Last edited Fri Feb 15 13:45:04 2008.