Import and export to a spreadsheet

> 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.
>
> _______________________________________________
> seaside mailing list
> [hidden email]
> http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

> 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.