Import and export to a spreadsheet

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Import and export to a spreadsheet

Stephan Eggermont-3
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.

_______________________________________________
seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
Reply | Threaded
Open this post in threaded view
|

Re: Import and export to a spreadsheet

Miguel Cobá
El mié, 29-07-2009 a las 23:56 +0200, Stephan Eggermont escribió:
> I'd like to add a page to seaside.st and would be interested in  
> comments and improvements
>

Good info, thanks.
In the "' delimited fields the code should handle the case when those
characters appear in the field and escape them apropriately.

Miguel Cobá

> 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

_______________________________________________
seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
Reply | Threaded
Open this post in threaded view
|

Re: Import and export to a spreadsheet

Philippe Marschall
In reply to this post by Stephan Eggermont-3
2009/7/29 Stephan Eggermont <[hidden email]>:

> 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
_______________________________________________
seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
Reply | Threaded
Open this post in threaded view
|

Re: Import and export to a spreadsheet

hernanmd
In reply to this post by Stephan Eggermont-3
2009/7/29 Stephan Eggermont <[hidden email]>

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.

Hi Stephan, it seems what I did in the Language objects:

http://www.squeaksource.com/LanguageInfo.html
 
Cheers

Hernán


_______________________________________________
seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
Reply | Threaded
Open this post in threaded view
|

Re: Import and export to a spreadsheet

carlo.t
In reply to this post by Stephan Eggermont-3
Hi Stephan

You could use the CSVParser package that i think Avi did, I haven't used it extensively but I did take a look and it seems useful enough for basic CSV handling.

Cheers
Carlo

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


_______________________________________________
seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside