How to export sqlform.grid with the labeled column names

[Jordan Myers]

Hey everyone,

so the default csv exporters for sqlform.grid will use the headers as the 'database' names, but I would rather it use the field.label instead. For example, take this table:

db.define_table('my_table', Field('ugly_name', label='Pretty Name'))

A sqlform.grid on my_table will display the column with the header Pretty Name, but when exporting to csv, the header becomes my_table.ugly_name.  Is there an easy way to export to csv with the columns "as seen" i.e. with the labeled names? I tried writing my own exporter, but it does not preserve the searching/sorting order. I suppose I could try to implement that myself, but that is a last resort.

Thanks

[Jordan Myers]

I figured out how to do it by extending the ExporterClass and adapting the TSV exporter to handle csv with the field labels. Perhaps this should be an option for the default ExporterCSV? Here it is for posterity:

class ExporterCSVlabel(ExportClass):

    label = 'CSV (real labels)'

    file_ext = "csv"

    content_type = "text/csv"

    def __init__(self, rows):

        ExportClass.__init__(self, rows)

    def export(self):

        out = cStringIO.StringIO()

        final = cStringIO.StringIO()

        import csv

        writer = csv.writer(out)

        if self.rows:

            import codecs

            final.write(codecs.BOM_UTF16)

            header = list()

            for col in self.rows.colnames:

                (t, f) = col.split('.')

                field = self.rows.db[t][f]

                field_label = field.label # Use the label name instead of database name

                colname = unicode(field_label).encode("utf8")

                header.append(colname)

            writer.writerow(header)

            data = out.getvalue().decode("utf8")

            data = data.encode("utf-16")

            data = data[2:]

            final.write(data)

            out.truncate(0)

        records = self.represented()

        for row in records:

            writer.writerow(

                [str(col).decode('utf8').encode("utf-8") for col in row])

            data = out.getvalue().decode("utf8")

            data = data.encode("utf-16")

            data = data[2:]

            final.write(data)

            out.truncate(0)

        return str(final.getvalue())

[Sneka R]

I am new to web2py. Which file should I make these changes?

Thanks.

[Jordan Myers]

I just put it in it's own file in the modules directory and called it "custom_exporter.py" You will need to put this at the top of the file:

from gluon.sqlhtml import ExportClass

When I want to use it in a controller with SQLFORM.grid, I must first import it at the top of the controller file:

from custom_exporter import ExporterCSVlabel

then when defining the grid, give it the parameter exportclasses=dict(csv=(ExporterCSVlabel, 'CSV'),

     csv_with_hidden_cols=False, tsv=False, tsv_with_hidden_cols=False, xml=False, html=False, json=False)

What that does is define the new ExporterCSVlabel to handle csv downloads and disables all other exports.

You should also consider looking at gluon.sqlhtml so you can see the how the other exporters work.

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

[Jordan Myers]

Oh yeah, changes to modules are not updated by default. Add this line to your models/db.py file:

from gluon.custom_import import track_changes; track_changes(True)

For more information on using modules with web2py, check out:

http://web2py.com/books/default/chapter/29/04/the-core#Sharing-the-global-scope-with-modules-using-the-current-object

On Mon, Jul 18, 2016 at 7:05 PM, Sneka R <sravee...@gmail.com> wrote:

Followed your instructions, but I am getting this Traceback

Traceback

1. 2. 3. 4. 5. 6. 7.

Traceback (most recent call last): File "/Users/sraveend/Documents/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/Users/sraveend/Documents/web2py/applications/ncmi/controllers/project_proposal.py", line 1, in <module> from custom_exporter import ExporterCSVlabel ImportError: cannot import name ExporterCSVlabel

On Friday, July 15, 2016 at 10:45:01 PM UTC-5, Jordan Myers wrote:

--

[Sneka R]

Actually I added the following line on top of the custom_exporter.py file
import cStringIO

And it works now.

Thanks.

On Friday, July 15, 2016 at 10:45:01 PM UTC-5, Jordan Myers wrote: