Edgewall Software
Modify

Opened 9 years ago

Closed 7 years ago

Last modified 6 years ago

#2669 closed enhancement (fixed)

excel/openoffice calc export

Reported by: anonymous Owned by: cboos
Priority: normal Milestone: 0.10.4
Component: report system Version: devel
Severity: normal Keywords: mimetype converter
Cc:
Release Notes:
API Changes:

Description

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.

Attachments (5)

reusable_export_csv.diff (2.0 KB) - added by cboos 8 years ago.
Additional changes on top of alect's attachment:ticket:2296:content-converter.diff
tickets_to_excel_tsv.py (1.2 KB) - added by cboos 8 years ago.
Simple but effective plugin implementing the requested feature (take 2)
report-by-xls-for-trac0_9_5.diff (7.1 KB) - added by anonymous 8 years ago.
Report to Native-xls by pyExcelerator for trac 0.9.5
tickets_to_excel_tsv.2.py (1.2 KB) - added by athomas 8 years ago.
Updated version for trunk API
pipelining_conversions.patch (3.4 KB) - added by cboos 8 years ago.
Simple pipelining of conversion (patch on r3307).

Download all attachments as: .zip

Change History (37)

comment:1 Changed 9 years ago by eblot

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.

comment:2 Changed 9 years ago by cboos

  • Component changed from general to ticket system
  • Owner changed from jonas to cboos

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()

comment:3 Changed 9 years ago by eblot

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 ?

comment:4 Changed 9 years ago by cboos

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.

comment:5 Changed 9 years ago by cmlenz

  • Resolution set to wontfix
  • Status changed from new to closed

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

comment:6 Changed 8 years ago by anonymous

  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Summary changed from excel export to excel/openoffice calc export

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?

comment:7 Changed 8 years ago by anonymous

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

comment:8 Changed 8 years ago by eblot

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]

comment:9 Changed 8 years ago by 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.

Changed 8 years ago by cboos

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

comment:10 Changed 8 years ago by cboos

  • Keywords mimetype converter added
  • Version changed from 0.9.3 to devel

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.

Changed 8 years ago by cboos

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

comment:11 Changed 8 years ago by athomas

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

comment:12 Changed 8 years ago by cboos

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…

Changed 8 years ago by anonymous

Report to Native-xls by pyExcelerator for trac 0.9.5

comment:13 follow-up: Changed 8 years ago by 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…

Changed 8 years ago by athomas

Updated version for trunk API

comment:14 Changed 8 years ago by athomas

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?

comment:15 Changed 8 years ago by cboos

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)

Changed 8 years ago by cboos

Simple pipelining of conversion (patch on r3307).

comment:16 Changed 8 years ago by cboos

  • Keywords needinfo added

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?

comment:17 Changed 8 years ago by sid

Yep, worksforme as well on a Mac.

comment:18 Changed 8 years ago by cboos

  • Resolution set to worksforme
  • Status changed from reopened to closed

Ok, if it works on a Mac … ;)

comment:19 follow-up: Changed 8 years ago by anonymous

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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.

comment:20 in reply to: ↑ 19 ; follow-up: Changed 8 years ago by mgood

  • Milestone set to 0.11
  • Resolution set to fixed
  • Status changed from reopened to closed

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.

comment:21 in reply to: ↑ 20 Changed 8 years ago by 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?

comment:22 Changed 8 years ago by anonymous

  • Resolution fixed deleted
  • Status changed from closed to reopened

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?

comment:23 Changed 8 years ago by cboos

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

comment:24 Changed 8 years ago by mgood

  • Milestone changed from 0.11 to 0.10.4
  • Resolution set to fixed
  • Status changed from reopened to closed

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.

comment:25 Changed 8 years ago by mgood

  • Component changed from ticket system to report system

comment:26 Changed 7 years ago by anonymous

tx a lot, now works correctly

comment:27 follow-up: Changed 7 years ago by mapelayo

  • Resolution fixed deleted
  • Status changed from closed to reopened
  • Type changed from enhancement to defect

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.

comment:28 in reply to: ↑ 27 Changed 7 years ago by anonymous

  • Keywords needinfo removed
  • Resolution set to fixed
  • Status changed from reopened to closed
  • Type changed from defect to enhancement

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.

comment:29 in reply to: ↑ 13 Changed 7 years ago by 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…

comment:30 follow-up: Changed 7 years ago by andreacolpo@…

  • Resolution fixed deleted
  • Status changed from closed to reopened

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)

comment:31 in reply to: ↑ 30 Changed 7 years ago by eblot

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

comment:32 Changed 6 years ago by harisingh06@…

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed The owner will remain cboos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from cboos to the specified user.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.