I had a requirement to generate an Excel report based on some filter criteria. Since I am using ColdFusion for the project, I will be using ColdFusion code to demonstrate what I did but this can pretty much apply to any language that can run loops and write files.

My requirement was to have Excel’s Auto-Filters enabled when the user downloads the file via the browser.  After searching a little bit, I realized its not that straight forward. I will probably blog about the harder way of doing it later, but here is what I ended up doing which turns out to be very simple and easy to do as well.

Run the Query, Run the Loop and generate the Excel Data

[xml]

<cfsavecontent variable="excelData">
<table width="100%">
<thead>
<tr width="100%" style="background-color:#e9ead9" />
<th filter="all">Code</th>
<th filter="all">Name</th>
<th filter="all">Area/Region</th>
<th filter="all">Model</th>
<th filter="all">BodyStyle</th>
</tr>
<cfoutput query="l.query">
<tr>
<td>#OrganisationCode#</td>
<td>#OrganisationName#</td>
<td>#Region#</td>
<td>#Model#</td>
<td>#BodyStyle#</td>
</tr>
</cfoutput>
</cfsavecontent>
[/xml]

Write the contents to a file, you can skip this step and serve directly from memory:-

[xml]
<cfset FileWrite("#expandpath(‘/reports/’)#report.xls" , "#ExcelData#" ) />
[/xml]

Send the file to Browser :-

[xml]
<cfheader name="content-disposition" value="attachment; filename=#filename#"/>
<cfcontent type="application/msexcel" file="#expandpath(‘/reports’)#/#filename#" deletefile="true">
[/xml]

The trick here is to add filter=”all” to the <th> tags while generating your HTML dynamically. Apparently, Excel knows how to treat this and applies the filters to the column.

It appears to me that Excel’s Web Query is not very broadly known but it turned out to be really useful for me today. There is some more info I found here. Apprently, you can apply formulas and specific filters as well, which sounds pretty cool.

Tagged with:  

8 Responses to Enabling AutoFilters on an Excel Template Dynamically

  1. eric says:

    That is a nifty trick. Thanks

  2. Anuj Gakhar says:

    @Eric, Glad you liked it. I was quite pleased when I found out this was doable.

  3. Don Blaire says:

    Excellent post. Thanks. I was using cf9 cfspreadsheet function but the users do not like the message that too many fonts exists when they open the spreadsheet.

  4. Jason says:

    Brilliant tip Anui! I searched Google for a way to do this, but I honestly wasn’t very hopeful. I’m not entirely sure how to run the above query, but I’ll figure it out! Thanks.

  5. Revati says:

    Thanks a lot Anuj fot the tip…..

  6. Rev says:

    Anuj,

    I am able to do Excel’s Auto-Filters enabled when the user downloads the file via the browser by using your tip. But here I have a requirement where the user want to hide some columns of that excel file. I tried so many ways but was no luck. Could you please help me out in this ASAP?

    Rev

  7. Rev says:

    Anuj,
    Yes We should inlcude those columns but should be hidden. Once the execel is generated, by default those particular columns should be hidden. After some time if user want to see the columns, he/she should be able to unhide the columns.

    Thanks a lot.
    Rev

Leave a Reply to Anuj Gakhar Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: