how to get SSRS 2008 R2 to export/render in Excel 2007 format?

Question

I have a report that has ~1k columns and ~17k rows and I'm trying to render it to Excel 2007 with SSRS 2008 R2 Nov CTP.

SSRS team members have mentioned in multiple places that Excel 2007 format is included in 2008 R2, for instance:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/69545568-73cb-4f4c-8f35-44472ba6d013

Now, it's certainly possible that it just hasn't made it into the product yet (at least as of Nov CTP), and if that's the case, that would be good to know, too (and ideally when it would be in the product).

The repro report and data (along with attempts showing the existing interface still does Excel 2003 format) are attached to the bottom of this blog post, FWIW:

http://cid-456117cf53a42144.spaces.live.com/default.aspx

Answer 1

Unfortunately, it looks like I was misinterpreting Jin Chen's answer ("Here, has good news, we have improved the ability in SQL Server Reporting Services 2008 R2") - there was an answer later by another member of SSRS that states that 2008 R2 will not support xlsx / Excel 2007 output:

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/87cfd5d4-ea3c-4250-aab8-a1a3dffa3241

As far as a full featured Excel 2007 xlsx renderer, it is definitely on our list to build but it will be post SSRS 2008 R2.

So, maybe it was in R2 but got cut, but either way, it doesn't appear to be an option now.

For those who might be in a similar situation that run across this question on SO, if programmatically generated the spreadsheet is an option for you, I would recommend EPPlus.

http://epplus.codeplex.com/

Answer 2

Excel 2007-2010 format (Office Open XML) is the default rendering format in SQL Server 2012

http://technet.microsoft.com/en-us/library/dd255234.aspx