export data to OOO and Excel

[Vineet]

I was looking for a library to export data to OpenOffice & Excel.
I found 2 such open source projects.

For OOO ---
http://ooolib.sourceforge.net/

For Excel ---
http://www.python-excel.org/

What I intend to do is, keep the classes in web2py folder, import the
classes in controller, then parse the result fetched from MySQL and
export the data to OOO or Excel.

Reasons as to why I am posting it here are--

1] Whether anybody has tried these and knows if these are mostly bug-
free (may not be 100%)
2] Whether anybody knows any other (better suited, may be) projects
like these (considering integration into w2p, feature-rich, tested,
etc.)
3] To share my findings with the list (might be useful to someone
new).

Cheers
:-)

[Joaquin Orbe]

Hi Vineet,
I used xlwt for a non-production site and worked fine for me (just a
very simple report with a small formatting).
This is a quick example:

def excel_report():
from datetime import datetime
import xlwt
tmpfilename=os.path.join(request.folder,'private',str(uuid4()))

font0 = xlwt.Font()
font0.name = 'Arial'
font0.bold = True

style0 = xlwt.XFStyle()
style0.font = font0

style1 = xlwt.XFStyle()
style1.num_format_str = 'DD-MMMM-YYYY'

wb = xlwt.Workbook()
ws = wb.add_sheet('Sample report')

ws.write(0, 0, 'Text here', style0)
ws.write(0, 6, 'More text here', style0)
ws.write(0, 7, datetime.now(), style1)

wb.save(tmpfilename)

data = open(tmpfilename,"rb").read()
os.unlink(tmpfilename)
response.headers['Content-Type']='application/vnd.ms-excel'
return data

Hope it helps you.

Joaco.

[Vineet]

Hi Joaco,
Thanks for your reply.
The information given by you is very helpful.
Thanks again.
--Vineet

On Jul 20, 4:56 pm, Joaquin Orbe <joaquino...@gmail.com> wrote:

[Dave]

This works great, but when i download the file it is missing the extension.  Is there an easy way to add '.xls' to the file name?

Thanks,
Dave

[Joaquin Orbe]

Hi Dave,
how do you download the file? This method is an action in one controller, ie:

http://127.0.0.1:8000/myapp/printer/excel_report

and the file is downloaded as XLS.

Joaco.

[Dave]

I added it to default.py controller, so it is working as an action
there.

When i go to that URL (.../default/excel_report), the file downloads
as "excel_report" with no extension. I am testing using Mac OS, so
not sure if that matters. The file is fine. When i rename it with
the .xls extension it opens in my spreadsheet application.

Thanks,
Dave


On Jul 21, 1:51 pm, Joaquin Orbe <joaquino...@gmail.com> wrote:

[Dave]

I added these two lines and its working now!

response.headers['Content-Disposition']='attachment;
filename=test.xls'
response.headers['Content-Title']='test.xls'


Dave

[cyber]

Say Dave, is xlwt included into current w2p version? Or I need to add
the library by myself?

[Alexandre Augusto]

cyber,

You can download it from here:
http://www.python-excel.org/

If you are using ubuntu linux distribution you can install it like this:

sudo apt-get install python-xlwt

[selecta]

Have a look at
http://tlc2.hg.sourceforge.net/hgweb/tlc2/tlc2/file/d68f1405e1c5/views/generic.xls
http://tlc2.hg.sourceforge.net/hgweb/tlc2/tlc2/file/d68f1405e1c5/views/generic.xlsx

they use tablib (which includes xlwt, ...)
http://docs.tablib.org/en/latest/index.html

[António Ramos]

How about a video tutorial exporting to excel?

2011/7/25 selecta <gr...@delarue-berlin.de>

[Vineet]

@selecta,
thanks for directing to 'tablib'.
It is also a good choice.
I am assessing whether to use 'excel-python' or 'tablib'.
:-|

On Jul 25, 1:52 pm, António Ramos <ramstei...@gmail.com> wrote:
> How about a video tutorial exporting to excel?
>
> 2011/7/25 selecta <gr...@delarue-berlin.de>
>
>
>
> > Have a look at
>

> >http://tlc2.hg.sourceforge.net/hgweb/tlc2/tlc2/file/d68f1405e1c5/view...
>
> >http://tlc2.hg.sourceforge.net/hgweb/tlc2/tlc2/file/d68f1405e1c5/view...

>
> > they use tablib (which includes xlwt, ...)

> >http://docs.tablib.org/en/latest/index.html- Hide quoted text -
>
> - Show quoted text -

[Kenneth Lundström]

Please let us know which one you found to be better. I�m going to need
to switch away from CSV to a "real" Excel format in a couple of months.

Kenneth

> @selecta,
> thanks for directing to 'tablib'.
> It is also a good choice.
> I am assessing whether to use 'excel-python' or 'tablib'.
> :-|
>

[Vineet]

Sure.
I will post here my observations after trying out the 2 excel
libraries.

Cheers :-)

On Jul 26, 12:26 pm, Kenneth Lundström <kenneth.t.lundst...@gmail.com>
wrote:

> >>>http://docs.tablib.org/en/latest/index.html-Hide quoted text -
> >> - Show quoted text -- Hide quoted text -

[Vineet]

I assessed both the libraries for exporting data to excel.
"python-excel" is far better in terms of features.
"tablib" doesn't have any feature for formatting of cells (such as
fore/back color, font, border, etc.)
Syntax of python-excel is more human-readable (especially the "easysf"
class in it).

I communicated regarding the cell-formatting issue with "Kenneth
Reitz", the originator of tablib.
His reply is reproduced below:--
=====
You can't do any of those things :-/
You might have more luck with http://pypi.python.org/pypi/xlwt
=====

So, better to use python-excel (in my opinion).

Cheers!
:-)

> > >>>http://docs.tablib.org/en/latest/index.html-Hidequoted text -

[Philippe ENTZMANN]

Another solution : You can simply link data to a Excel/OOo spreadsheet.
From web2Py, you only need a view with a HTML table of your data.
In Excel or OOo you link your spreadsheet to your URL.

Advantages :
- you don't have to build your Excel file in w2p (no extra module)
- you can link your data with different worksheets
- you can refresh data in Excel/OOo (manual or automatic)
- layout and formatting is preserved at each refresh

2011/7/20 Vineet <vineet....@gmail.com>:

[Vineet]

@Philippe,
Thanks a lot for your post.
I'm interested in this.

I did not understand what you mean by saying ---

>"In Excel or OOo you link your spreadsheet to your URL".

"Linking excel or OO sheet to url" means what?
Do you want to say that an excel file to be opened by "File--Open--
<url of page>"?


On Jul 29, 1:14 pm, Philippe ENTZMANN <philippe.entzm...@gmail.com>
wrote:

> Another solution : You can simply link data to a Excel/OOo spreadsheet.
> From web2Py, you only need a view with a HTML table of your data.
> In Excel or OOo you link your spreadsheet to your URL.
>
> Advantages :
> - you don't have to build your Excel file in w2p (no extra module)
> - you can link your data with different worksheets
> - you can refresh data in Excel/OOo (manual or automatic)
> - layout and formatting is preserved at each refresh
>

> 2011/7/20 Vineet <vineet.deod...@gmail.com>:

>
>
>
> > I was looking for a library to export data to OpenOffice & Excel.
> > I found 2 such open source projects.
>
> > For OOO ---
> >http://ooolib.sourceforge.net/
>
> > For Excel ---
> >http://www.python-excel.org/
>
> > What I intend to do is, keep the classes in web2py folder, import the
> > classes in controller, then parse the result fetched from MySQL and
> > export the data to OOO or Excel.
>
> > Reasons as to why I am posting it here are--
>
> > 1] Whether anybody has tried these and knows if these are mostly bug-
> > free (may not be 100%)
> > 2] Whether anybody knows any other (better suited, may be) projects
> > like these (considering integration into w2p, feature-rich, tested,
> > etc.)
> > 3] To share my findings with the list (might be useful to someone
> > new).
>
> > Cheers

> > :-)- Hide quoted text -

[黄祥]

just an idea why not put this on sqlform.grid as an export (seen csv, html, etc, but no excel on sqlform.grid export)?

best regards,
stifan

[Anthony]

You can already open CSV files in Excel. Anyway, you can specify custom exporters in the grid via the "exportclasses" argument -- so you can use this if you implement a class for it.

Anthony

[黄祥]

yes, i know that CSV files can be open with excel, i think it's enough, if i need *.xls file i think, i can just open CSV files with Excel save it on Excel with *.xls format

thanks and best regards,
stifan

[Massimo Di Pierro]

i like handsontable. It displays data like excel and allows cut&paste to/from excel. It has a select all button

[Yebach]

Is it possible to stram the file without creating a temp file and how can I zip it (without temp file)

[Massimo Di Pierro]

Can you tell us more. What do you want to zip? uploads/downloads should be automatically gzipped by browser.

[Vid Ogris]

Hello

On request from user, I create a CSV file and an excel file. I would like to zip them both without creating additional temp file and serve them together to user.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/3IZ8uEAAXyU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Lep pozdrav 

Vid Ogris