If you implement bug report http://drupal.org/node/262323 , you can easily implement an Excel export since Excel readily opens an HTML file (but only a complete HTML file with and tag; it also recognizes the character set in the ).

Basically, you clone the Word export and change the header to:
drupal_set_header('Content-Type: application/vnd.ms-excel; charset=utf-8');

and change all file extensions from doc to xls.

Since Excel and Word export are so similar, you might want to create a shared function for that.

Comments

oplachko84’s picture

oplachko84 commented
Version: 5.x-1.2-alpha2 » 6.x-1.x-dev
enboig’s picture

enboig commented

Is this going to be ported to drupal6?

aren cambre’s picture

aren cambre commented

Is this even worthwhile? Excel can readily import XML data.

Gerald Mengisen’s picture

Gerald Mengisen commented

>>Is this even worthwhile? Excel can readily import XML data.

Sure, but will users get the idea to open the XML with Excel? I doubt it. It is far more intuitive to offer a direct Excel export. CSV Export comes close, but doesn't allow you to specify the character encoding and so for most users, umlauts and accents would end up garbled.

So these days I'll post a patch. I have this working for D5 and now I'm about to port it to D6.

enboig’s picture

enboig commented

I agree with #4; I have encoding problems. One solution would be adding encoding option to CSV; but then some programs would default to UTF8 and others to ISO-8859, ANSI, ....

+1 to HTML option if this works on excel (I suppose I will have to change extension to .xls to make the trick work). I will also check openoffice afterwards.

anrikun’s picture

anrikun commented

Please review my file with Excel export
http://drupal.org/node/444412

anrikun’s picture

anrikun commented
Status: Active » Needs review
Gerald Mengisen’s picture

Gerald Mengisen commented

@anrikun: thanks for your contribution; I'll check on the week-end if I can turn it into a patch.

@enboig: in the D5 version, it worked fine with OpenOffice; I don't have Excel. The whole trick is to include the html header with the meta tag that contains the character encoding.
===

Meanwhile, somebody created a new module for Excel export:
http://drupal.org/project/views_export_xls

The views bonus pack is a dependency of that module; therefore, I'd prefer if the Excel export goes directly into the Views Bonus pack - one module less to install.

aren cambre’s picture

aren cambre commented

I asked him to merge: #496652: Please merge with Views Bonus Pack.

anrikun’s picture

anrikun commented

Actually the way http://drupal.org/project/views_export_xls creates XLS files is better than mine as it seems to use the real binary XLS format and not the HTML format like mine.
The problem is that there are issues with multibyte characters and HTML entities.

Gerald Mengisen’s picture

Gerald Mengisen commented

@anrikun
After having tried out http://drupal.org/project/views_export_xls , I agree that it is the superior method. It also seems that accents and umlauts come across just fine.

Nevertheless, I tried to create a patch for your solution, but I'm a bit at loss what to do for new files despite the descriptions at http://drupal.org/patch/create . So for now this takes more time than I have. Sorry.

aren cambre’s picture

aren cambre commented

Just to be clear, and responding to #10 and #11, I am asking to replace this project's current XLS export with the XLS export in http://drupal.org/project/views_export_xls.

anrikun’s picture

anrikun commented

But http://drupal.org/project/views_export_xls has many issues and should be used only when dealing OK with UTF-8 characters, HTML entities, and also "rowspans": if in a row, a CCK field has multiple values, views_export_xls creates one line for each field value, without any rowspan on the other single value fields.
Besides, there are better XLS export libraries than the one views_export_xls uses:
http://pizzaseo.com/php-excel-creator-class
This library seems really complete and supports UTF-8.

creaoy’s picture

creaoy commented

Will be good to create good solution.

anrikun : We can work together on this.

anrikun’s picture

anrikun commented

Ok JK.
Can you have a look at http://pizzaseo.com/php-excel-creator-class ?
Actually, the file to download is: http://pizzaseo.com/files/articles/excel.zip

It seems to be a good class to use.
But maybe there are others even better...

neclimdul’s picture

neclimdul
He/Him
commented
Status: Needs review » Active

There isn't anything to review here...

Anonymous’s picture

Anonymous (not verified) commented
Category: feature » bug

csv export works veri well, but people using MS excel have to use text-to-column function to get the data properly displayed.
xls export give WSOD

aren cambre’s picture

aren cambre commented
Category: bug » feature

This is issue is not about a bug.

appel’s picture

appel commented

Subscribing.

bendiy’s picture

bendiy commented

The nice thing about HTML table exports vs. direct data exports is that Excel will recognize the HTML Table's formatting. This works for the following:

  • Currencies - If a field value is formatted to show $10.99, Excel will see it a a Currency Field Format with value 10.99. This allows the field to be summed as a number.
  • Hyperlinks - Excel will preserve any links in Views Fields. If this link is an Views Argument link, in Excel, you can click on the link and go directly to the web page.
  • Text Formats - Bold, Italic, etc. will carry over to Excel.

All of these features make the HTML Table Excel export much more user friendly. Your users can start to use it, email it and read it immediately. A direct data export like Views_Excel_Export does not persevere all of this info. You have to reformat all of the data in Excel before it's readable and usable.

I don't know if you can preserve the format with a direct data export by adding features to Views_Excel_Export from some libraries. However, at the moment, I see the current HTML Table export more valuable than the Views_Excel_Export.

bendiy’s picture

bendiy commented

I've created an updated patch to export to Excel via the HTML table option mentioned above. Please download and review from Issue #519424.

neclimdul’s picture

neclimdul
He/Him
commented
Status: Active » Closed (fixed)

Ok, guys, this thread has gotten pretty hard to follow with all the different questions and requests. I think we can follow up with the following issues and close this issue down.

#496652: Please merge with Views Bonus Pack - Merge Project views_export_xls
#519424: Export to Excel - HTML XLS Export for views_bonus

As far as the other libraries go, I'm not sure I'm comfortable adding those as requirements for a plugin. Extra downloads are always confusing and at this point views_bonus is a very simple set of modules to build on top of.

mag2000’s picture

mag2000 commented

This code export the data as well as all the primary,navigation header and footer in excel as well. I past this code in body of the page. The same code is working fine in php. But when i copied the same to drupal. It will not work as i required. Anybody has a clue...what's wrong...thanks in advance.

(cut the code of the connection string , query is working)

<?PHP
$filename = 'test.xls';
drupal_set_header('Content-Type: application/octet-stream');
drupal_set_header('Content-Disposition: attachement; filename=' . $filename);
$flag = false;
$result = mysql_query("SELECT symbol,price FROM price") or die('Query failed!');
while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
//array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\n";
}

?>

anrikun’s picture

anrikun commented

This issue is closed.
Please post this as a new support request.