3

A while back I asked a question about how to pass a list of columns to display in a views table and a solution closest to what I wanted to do was a module:

Views Dynamic Fields

I was positively shocked that the description of the project mentioned the second part of what I need to do here. Export that view to an Excel spreadsheet.

This module provides additional advantages when used with the Views Bonus Pack module or the Views Excel Export module to generate an xls file from a view. The xls file will only display the fields you have chosen on the rendered view instance in browser.

I need to let the user choose the columns, then render the view with those columns and expose filters for all chosen columns, apply them, then download the result in an excel file. Unfortunately I was not able to find a description how do I actually do that. Using the xls feed option I don't get any fields from the view itself (seems like I need to add the fields manually again) which I can't imagine works with the filters from the view itself. Exporting the file works, but it only shows the default "Title" field (without adding fields to the feed manually).

I had the same problems with Views data export, creating a new data export started with a default "Title" field and I don't think you're supposed to add the same filters again.

Let me know if you have any experience with those modules or ever had to export a view to a spreadsheet.

2

I just used Views Data Export module for a Drupal 7 project.

  1. Create a view with the configuration you require (exposed filters, etc.).
  2. Add a new display to your view of the type "data export". (As you duplicate your display the filters should inherit, but otherwise, you just want the "main" view and the "data export" view configured the same.)
  3. In the "Page Settings" of the "data export" view display, give it a unique path (This is true even for tabbed items like admin views linked in admin menus).
  4. In the "Page Settings" of the "main" view display, attach the "data export" display (This will provide a little CSV link at the bottom of your view).
  5. Under the "Format" settings for your view, make sure you select CSV. Then edit settings and check the box for "download as file" or whatever it is.

Now, clicking on that CSV icon that appears at the bottom of your view page/block will export a CSV file that will inherit the filtered table. Just ensure the exposed filters match in each display.

1

Another module you should have a look at for addressing these requirements is Forena. You would use it's report writing features (such as its WYSIWYG report writer and/or it's query builder) to first create a report that fits your needs (think of it as the equivalent of creating a view). The result of it could be a tabular output and/or graph. After the report content matches the specifications, you could save it as (e.g.) a XLS file. The Forena Video demos and tutorials contain quite some links to live demos.

Any report created with Forena can be exported to various file formats, such as CSV (or XLS). To do so, just add .csv (or .xls) as an extension to the report (output) URL. That contains the CSV (or XLS) equivalent of that report. As a sample, consider the sample report located at /reports/sample.states (in your own site, one of the samples shipped with Forena). Change the URL to /reports/sample.states.xls (= just add .xls to it) to get the same report in .xls format. If you'd add such .xls to the URL of the Simple Table of States in the demo site, the result is like so ... Same technique applies for other supported extensions (export formats), such as adding .html, .pdf or .doc (MS Word).

Forena's equivalent of "Views exposed filters" (as in your question) is what is called "Report parameters". Have a look at the sample report "Report that filters based on state lookup" for an online example of that. By default (if you don't specify a report parameter), the sample shows a report for state "Washington". Use the Parameters link to specify any other value (= another state), and note how the report title and report content changes.

On top of that, all data blocks are accessible directly via their url also, provided you have permission to access that block. Here are a few samples of that, for the sample report located at /reports/sample.states:

For short, to answer this specific question I'd write the various (custom) SQLs I need, format it as reports that fit my needs, and then just save it as an XLS (or CSV?).

Disclosure: I'm a co-maintainer of Forena.

  • There's a few reasons I don't like this solution. There's over 80 columns that may or may not appear in the report, some of which are the same field from a few nodes of the same type. The report builder I need to provide has to be pretty simple and so far I don't think creating a forena report would be easy for someone who's not a programmer (hell even I got a bit confused, also got an error "PDO driver support for sqlite not installed"). There's a lot of things to consider and since I don't know how to use Forena I'd rather finish up the solution I already have, which only lacks the export. – Marek Kalinowski May 20 '16 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.