export grid contents to spreadsheet

[Sean DiZazzo]

Hi,

I'm trying to make a generic widget that can export whatever records
are being shown in a DataGrid. I have the code to generate the file
and to serve it up to the user using cherrypy, but I'm brain farting
on how to pass the records in to the widget. I want to just be able
to pass them in via the template like you do with a datagrid. ie $
{export_records(records).display()}.

I started trying to make a simple form with only the submit button,
but then I realized I couldn't get the records attached to the form.
I could do it with a form the way I was trying to do it if I
reselected the records from the database again, but I want to just
have the button use the same records that were already filtered down
by the controller method. All I need the button to do is call a
function with the passed in records, and I should be able to create
the spreadsheet and serve it up. I think I am missing something
obvious. Any advice?

~Sean

[Rotem Tamir]

The way I'm using is this:

I have a template file with something like

${grid(data)}

where grid is a DataGrid object and data is a list of database records
from SqlAlchemy

To serve this I'm doing:

@expose('project.templates.excel',content_type="application/vnd.ms-
excel")

this way the browser thinks it's receiving a true MS-Excel file but
actualy receiving an html table (from DataGrid) - turns out excel
proceses this just fine!

good luck

Rotem

[Carlos Daniel Ruvalcaba Valenzuela]

You could also use xlwt to generate excel files:

Docs: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/doc/xlwt.html
Examples: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/examples/

Regards,
Carlos Ruvalcaba

> --
> You received this message because you are subscribed to the Google Groups "TurboGears" group.
> To post to this group, send email to turbo...@googlegroups.com.
> To unsubscribe from this group, send email to turbogears+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/turbogears?hl=en.
>
>

[Sean DiZazzo]

On Nov 4, 2:50 pm, Rotem Tamir <rotemta...@gmail.com> wrote:
> The way I'm using is this:
>
> I have a template file with something like
>
> ${grid(data)}
>
> where grid is a DataGrid object and data is a list of database records
> from SqlAlchemy
>
> To serve this I'm doing:
>
> @expose('project.templates.excel',content_type="application/vnd.ms-
> excel")
>
> this way the browser thinks it's receiving a true MS-Excel file but
> actualy receiving an html table (from DataGrid) - turns out excel
> proceses this just fine!
>
> good luck
>
> Rotem
>

Thanks Rotem! This is very cool. I'm not sure it solves my problem
exactly, but it might lead to a working solution.

I never thought of using the template system to create the excel
file...I would have just made a tab delimited text file in one of the
controller methods.

Need to think more about how to use this strategy...

~Sean

[Sean DiZazzo]

> I'm trying to make a generic widget that can export whatever records
> are being shown in a DataGrid.  I have the code to generate the file
> and to serve it up to the user using cherrypy, but I'm brain farting
> on how to pass the records in to the widget.  I want to just be able
> to pass them in via the template like you do with a datagrid.  ie $
> {export_records(records).display()}.
>

Hi again. I'm looking at this issue again today, and am no closer to
a solution. I have no problem serving up the file. I plan to format
the data into a string in a controller method and then call:

pylons.response.headers['Content-Type'] = 'application/vnd.ms-excel;'
# or 'application/pdf', etc
pylons.response.headers['Content-Disposition']
='attachment;filename=export.xls'

and then return the string. This serves up the file nicely.

My problem is that I would like to have a generic button that will
work with any data that is currently being displayed in the grid. The
data presented in the grid might have been filtered, and I don't want
to have to keep track of the filter terms and pass them in to the
export widget so it can generate the same list of records...I just
want to pass the same list used to populate the grid widget in to the
export widget and have it available in the defined action controller
method. Then I would use it to format the file and serve it up.

Is this possible? Can someone give me some pointers on how to do
this? Am I missing something obvious? Should I be looking at
creating a widget?

I have a hard time visualizing it, but something makes me think I
would have to store all of the data as json (or something) as a hidden
field in the export button form... hopefully I'm way off. :)

Thanks in advance.

~Sean

[Michael Pedersen]

I'm trying to think of ways to do it, and I'm coming up pretty short. My main reason is because, with SQLAlchemy at least, you're not *actually* getting a list of rows, not unless you're using the .all() method on your query. Since many people don't do that (I know I don't, and I suspect most others don't due to memory concerns), that means there's no actual record of the results.

What you get from SA, by the way, is an iterator that will be exhausted by the time you try to use it elsewhere.

Next, add in some advanced work with the datagrid (and some nifty javascript), and the result is that what is seen in the user's browser could have very little bearing on the result set that was passed in from the server. You really do have to read the grid that's being displayed. Not even work with the results passed back, but actually read the cells as they are shown. It's the only option that I see that works.

From there, you can pass the data from the browser back to a rendering method that creates whatever data format you want to return.

I'm sorry, I just don't see a better option. Maybe someone else does, but I sure don't.

~Sean