excel/openoffice calc export

[anonymous]

a microsoft excel/openoffice calc export would be a great feature. it could be implemented by:

1. producting tab separated or comma separated format
2. setting the http mime type of the result to application/msexcel
3. set the extension of the result to ../xx.xls

and it should work with most (all?) browsers.

[Emmanuel Blot]

Can you give some examples of which kind of data you are thinking of ?
CSV and TSV exports are already available for ticket reports.

I don't really agree with the second/third points: I don't think Trac should export to some proprietary format. Plus, I'm not sure it is valid to generate a CSV file with a .XLS extension.

[Christian Boos]

I think that having a direct Excel export can be useful: it's more convenient than having to save the text output to a file, and then open that file.

The following patch sets the mimetype used for Tab-separated value to be application/vnd.ms-excel.

Note that the mimetype value given to the add_link is not used.

Index: trac/ticket/query.py
===================================================================
--- trac/ticket/query.py	(revision 2831)
+++ trac/ticket/query.py	(working copy)
@@ -381,8 +381,8 @@
                  'application/rss+xml', 'rss')
         add_link(req, 'alternate', query.get_href('csv'),
                  'Comma-delimited Text', 'text/plain')
-        add_link(req, 'alternate', query.get_href('tab'), 'Tab-delimited Text',
-                 'text/plain')
+        add_link(req, 'alternate', query.get_href('tab'),
+                 'Tab-delimited Text (Excel)', 'text/plain')
 
         constraints = {}
         for k, v in query.constraints.items():
@@ -406,7 +406,7 @@
         elif format == 'csv':
             self.display_csv(req, query)
         elif format == 'tab':
-            self.display_csv(req, query, '\t')
+            self.display_csv(req, query, '\t', 'application/vnd.ms-excel')
         else:
             self.display_html(req, query)
             return 'query.cs', None
@@ -572,9 +572,9 @@
            self.env.is_component_enabled(ReportModule):
             req.hdf['query.report_href'] = self.env.href.report()
 
-    def display_csv(self, req, query, sep=','):
+    def display_csv(self, req, query, sep=',', mimetype='text/plain'):
         req.send_response(200)
-        req.send_header('Content-Type', 'text/plain;charset=utf-8')
+        req.send_header('Content-Type', '%s;charset=utf-8' % mimetype)
         req.end_headers()
 
         cols = query.get_columns()

[Emmanuel Blot]

What does prevent the browser to open Excel when receiving a CSV file ?

Again, I don't think it's a good idea to add references to proprietary formats in the Trac core - may be it is possible to have an extension (plugin) for such formats ?

[Christian Boos]

It's the mimetype. If it's text/plain, the browser will show the text by itself. With a more neutral alternative to application/vnd.ms-excel, like text/csv or text/tab-separated-values, IE only propose to save the file, and Firefox starts the generic Open with… dialog.

Note that the data format itself is unchanged and non-proprietary, it's only the type annotation that has been made more specific. Nothing prevents you to choose a text editor to open it. And I'm pretty sure that free MS-office alternatives like Open Office or KOffice will know what to do with the ms-excel type.

[Christopher Lenz]

I agree with eblot that we shouldn't do this… if someone wants their report results in excel, it's easy enough.

[anonymous]

eblot, exactly, it would be great for ticket exports as additional link on the bottom to ease peoples life a little who want to take the list of issues with them, for a meeting e.g.

the purpose of the mime-type and the ending is to help the operating system or the browser to choose the way how to display the document. if the result is csv, then the mime type could be different from text to allow configuration of a different application. setting it is definitely not a crime.

btw, the patch above replaces the csv link, which was not the idea. there should be two links both producing text:

• one with mime type text, opens the same as displaying the source of a wiki page
• one with mime type differen, wich opens a spreadsheet program on any platform by default (and the proposed solution works on linux, windows, macos).

how you call this links? i'm not sure if this is important at all. e.g. csv + excel, text + csv, text + spreadsheet just to name possibilities.

am i allowed to reopen the ticket pls?

[anonymous]

sorry … how do i change the wrong statement above? it is already an additional link i think *sweat*.

[Emmanuel Blot]

My personal opinion is that Trac should not provide OS-specific format (text/csv is not, application/ms-excel is).

I don't think it would be a nice thing to clutter the interface with links which are useless on MacOS or Linux, for example. People who want to add platform specific feature can edit the Trac code, or create plugins to support proprietary file format, but I wish Trac core stayed platform independent.

[About the latest question: you cannot edit comments, only append new ones]

[anonymous]

exactly this is the point and i agree fully with you: such a link has to work on every operating system where you have a spreadsheet program and a browser. i only tested linux and windows and it does. but as the same programs exist also for macos it would be a big surprise if it would not work there.

if it is a plugin, even better.

[Christian Boos]

Additional changes on top of alect's attachment:ticket:2296:content-converter.diff​

[Christian Boos]

This ticket is now related to #2296.

I attached a single file plugin implementing that feature (attachment:tickets_to_excel_tsv.py​) which is using the extension points introduced in the attachment:ticket:2296:content-converter.diff​ patch, and some additional changes on top of that (attachment:reusable_export_csv.diff​).

I think we will be able to close this as worksforme as soon as the #2296 related changes will be in trunk.

[Christian Boos]

Simple but effective plugin implementing the requested feature (take 2)

[Alec Thomas]

I've applied the patch Christian and I'll upload a new version soon (once I've migrated the versioncontrol zip/diff downloads).

[Christian Boos]

Alternatively, that Excel stuff could also be done in a more general way by pipelining the transforms…

Registering once a 'text/csv' => 'application/vnd.ms-excel' converter, and for each of the XYZ converter of type * => 'text/csv', propose an additional XYZ (Excel) conversion…

[anonymous]

Report to Native-xls by pyExcelerator for trac 0.9.5

[anonymous]

If you were installed ​pyExcelerator (for Python 2.4, however has patch for Python<2.4) AND attachment:ticket:2669:report-by-xls-for-trac0_9_5.diff​ applied AND added show_excel_link = true in [ticket] section (trac.ini), trac will have power to render native-xls.

This patch got from my own trac source, so this patch also includes sorting problem in Report…

[Alec Thomas]

Updated version for trunk API

[Alec Thomas]

Slightly updated version of the IContentConverter interface committed in r3305 and r3306.

I think we can close this now that it can be implemented as a plugin?

[Christian Boos]

Let me first propose an implementation for the pipelining stuff…

Here's the implementation on top of r3307: attachment:pipelining_conversions.patch​

I changed the text/plain MIME Type of the CSV to text/csv, as this is the registered on (cf. ​rfc:4180).

The plugin would be extremely simple:

"""Convert any `text/csv` data to `application/vnd.ms-excel`."""

from trac.core import *
from trac.mimeview.api import IContentConverter

EXCEL_MIMETYPE = 'application/vnd.ms-excel'

class CSVToExcelConverter(Component):
    implements (IContentConverter)

    # IContentConverter methods 
    def get_supported_conversions(self): 
        yield ('excel', 'Excel', 'csv', 'text/csv', EXCEL_MIMETYPE, 8) 
         
    def convert_content(self, req, mimetype, content, k, fname=None, url=None):
        return (content, EXCEL_MIMETYPE)

[Christian Boos]

Simple pipelining of conversion (patch on r3307).

[Christian Boos]

I don't know exactly since when, but now for me the .csv files are automatically opened in Excel…

Does it worksforme for other people too?

[sid]

Yep, worksforme as well on a Mac.

[Christian Boos]

Ok, if it works on a Mac … ;)

[anonymous]

yes, it works perfect for queries. could you pls add the same mime type for reports too pls?

may i reopen it for that reason.

[Matthew Good]

Replying to anonymous:

yes, it works perfect for queries. could you pls add the same mime type for reports too pls?

The mimetypes have been updated for reports in r4471 to be consistent with those used in the query module.

[anonymous]

Replying to mgood:

Replying to anonymous:

yes, it works perfect for queries. could you pls add the same mime type for reports too pls?

The mimetypes have been updated for reports in r4471 to be consistent with those used in the query module.

is there any chance to backport it to 0.10?

[anonymous]

can it be that spreadsheet is opened because the result link is called "query.csv" in case of the query?

because the report link is called "1" (the id), and windows does not know that it should open excel?

[Christian Boos]

Right, report_1.csv would certainly be a better choice of filename anyway.

[Matthew Good]

Content-Disposition headers are added in r4478 to send filenames such as "report_1.csv".

The changes have been ported to 0.10-stable in r4479 since we should really be sending the right mimetypes for those downloads.

[anonymous]

tx a lot, now works correctly

[mapelayo]

I installed the CSVToExcelConverter plugin in my trac's plugins directory, and also set in the ini file the following fields:

[components] CSVToExcelConverter.* = enabled

the trac.log says 2007-02-13 12:29:35,887 Trac[init] DEBUG: Loading file plugin CSVToExcelConverter from /home/trac/plugins/CSVToExcelConverter.py

but, when I open a report I only keep seeing:

Download in other formats:

• RSS Feed
• Comma-delimited Text
• Tab-delimited Text
• SQL Query

What am I doing wrong?

Thank you.

[anonymous]

Replying to mapelayo:

What am I doing wrong?

Quoting the new ticket page:
Support and installation questions should be asked on the mailing list or IRC channel, not filed as tickets.

Please ask for support on the MailingList rather than re-opening tickets. You may reopen ticket if the provided feature is buggy, but not to ask for support. Thanks in advance.

[anonymous]

I ported it to trac 0.10.3, and uploaded it as a new project to www.trac-hacks.org ​http://www.trac-hacks.org/wiki/ExcelReportPatch

Replying to anonymous:

If you were installed ​pyExcelerator (for Python 2.4, however has patch for Python<2.4) AND attachment:ticket:2669:report-by-xls-for-trac0_9_5.diff​ applied AND added show_excel_link = true in [ticket] section (trac.ini), trac will have power to render native-xls.

This patch got from my own trac source, so this patch also includes sorting problem in Report…

[andreacolpo@…]

what about the possibility to have an organized xls report output with many tab, with different pie charts : one with percentage pieces differentiated for gravity, another with pieces for status, etc. Yes always as a plugin, so to not loose OS indipendence of trac. (sorry if this was not a good reason to reopen the ticket)

[Emmanuel Blot]

Replying to andreacolpo@yahoo.it:

(sorry if this was not a good reason to reopen the ticket)

Yep, as this feature won't be implemented in Trac core, there's no need to re-open the ticket. I would have said you need to report these suggestion to the ​th:ExcelReportPatch maintainer, but as trac.hacks.org web site is down for now, you'll have to wait to do so.

[harisingh06@…]

How can i export to spreadsheet ……… i want to use open office