[Seaside] Import and export to a spreadsheet

Stephan Eggermont stephan at stack.nl
Wed Jul 29 21:56:22 UTC 2009


I'd like to add a page to seaside.st and would be interested in  
comments and improvements

Stephan

Introduction
A lot of applications have a need to import and export some data.  
Spreadsheets are often used for this purpose.

Spreadsheet file formats
Spreadsheets use different formats. Excel in its different versions  
has three native file formats (BIFF, XMLSS, XLSX).

OpenOffice has ODF. In addition, both handle formats more suitable for  
simple data exchange: CSV, TSV, DBF.

The easiest format to support from Seaside is CSV/TSV, but that does  
not allow formulas or formatting to be transferred.

A quick and dirty method to get formatted data into Excel is to send  
html (table) data and use a application/xls mime type.

Export to a spreadsheet
Making a Seaside application export some data to a spreadsheet in tab  
separated format consists of the following steps:

- create a stream to contain the data;
- walk the data collection and for each object;
- write the fields to the stream;
- separated by tabs and ending each object with a cr;
- create a response and send it to the client.

Mimetype
There are different mimetypes that can be used:

- application/xls
- application/vnd.ms-excel
- application/excel
- application/x-excel
- text/tab-separated-values
Some versions of IE do interesting things with mime types (ignore  
them). Most success was reported with the first type.

Issues
- A well known bug with Excel and opening UTF-8 CSV files via file  
association: it assumes that they are in the current code page.
- Using tab as a separator instead of a comma makes sure floats arrive  
in the expected column in the spreadsheet.
   Some countries have a comma as a decimal separator.
- Adding "'s around each field that is written can help preserve  
leading zeros and allows tabs, cr and lf to be used in a field.
- The number of lines in a spreadsheet is limited.

Importing spreadsheet data.
Small amounts of unformated data can be easily copy-and-pasted from a  
spreadsheet to a textArea and then parsed.

Larger amounts are better handled as a file upload.

Making a Seaside application import some data from a spreadsheet in  
tab separated format consists of the following steps:

- extract the string from a textArea;
- split the string into lines;
- split each line into fields;
- for each wellformed line, create an object;
- map each field to the object.



More information about the seaside mailing list