[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