[Seaside] Import and export to a spreadsheet

[Philippe Marschall]

2009/7/29 Stephan Eggermont <stephan at stack.nl>:
> 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.

The biggest issue I have with CSV is that it's totally locale
dependent. This includes everything even the separator. Some locales
actually use comma others use semi colon. It's underspecified any
everybody uses his own collection of hacks to somehow hint Excel to do
what he wants. I have made better experience with the native BIFF
format.

Cheers
Philippe