Introduction

The hard-at-work folks on the SQL Server Reporting Services team recently announced that SQL Sever 2012 (formerly codenamed "Denali") will feature the ability to export reports to XLSX and DOCX.  These new OpenXML-based file formats were introduced in Microsoft Office 2007, and they contain a number of interesting features and capabilities beyond the older, binary XLS and DOC files.

But, if you can't wait for the SQL Server 2012 release (expected to be released in the first quarter of 2012) to get XLSX and DOCX from Reporting Services, you can use OfficeWriter. 

No more 65,536 row limitation!

This is probably the biggest reason that people want XLSX instead of XLS.  XLSX supports over a million rows.

What is OfficeWriter?

OfficeWriter is a set of tools for Excel and Word document generation, manipulation, and reading.  OfficeWriter consists of three main pieces:

1. OfficeWriter API - For .NET developers who  need to build custom Excel and Word document generation, manipulation, and reading into custom applications.
2. OfficeWriter SharePoint Solutions - Packaged, no-code solutions for SharePoint that add document generation capabilities to SharePoint lists
3. OfficeWriter for Reporting Services - Custom extensions to SSRS that allow you to publish, populate, and deliver Excel and Word reports from the report server.

The last option is what I will focus on.  Specifically, how to generate XLSX reports from SSRS.

How to Generate XLSX from SQL Server 2008 R2 Reporting Services

With OfficeWriter for Reporting Services, you can publish Excel and Word templates to the report server.  When the report executes at runtime, SSRS merges the Excel or Word template with data and delivers it to the client. 

First, I'll open Excel and flip to the Add-Ins tab where the OfficeWriter toolbar resides:

You can either build a new DataSet  for the report by clicking the Add Query button, or import a DataSet from an existing RDL by clicking Open Report.  I have an RDL with a specific query already defined in it that shows all 121,317 rows from the AdventureWorks 2008 R2 sample database.  So, I'll click Open Report and just use that.  Once I open that RDL, the DataSets and all fields from those DataSets will appear in dropdown menus:

The RDL I opened has only one DataSet called TerritorySalesLineItems, and two fields called Price and TerritoryName.  To build the report, add any headers or styles you want using regular Excel formatting techniques, and then drop data markers into cells where you want data to go by clicking on items in the Insert Field dropdown:

Publish this workbook to the report server with the Publish button:

When it's published, it will appear just like a regular report which can be secured, managed, and delivered as you would any conventional RDL:

To export the report, select the Excel designed by OfficeWriter option:

The output generated is the template merged with the data by OfficeWriter on the report server.  Note that the exported Excel has well over 100k rows:

Conclusion

 Using OfficeWriter and Reporting Services together lets you pump a huge amount of data into Excel workbooks in a high-performance, highly scalable manner while preserving all Excel formatting and leveraging the features of the report server.