Rendering SQL Server Reports as Excel Documents with PHP
I wrote a post a while back about getting started with SQL Server Reporting Services (SSRS) and PHP that has generated lots of questions (both in the comments and in e-mail and conversations I’ve had since then). One of the most common questions has been “How do I render a report as an Excel document?” I’ve been telling folks that this is easy with the SSRS SDK for PHP (and it is easy), but when I sat down to do it, I ran into a problem. So, in this post, I’ll show you how to render a SSRS report as an Excel document and how to avoid the one problem that caused me headaches. I will assume you have read my previous post about getting started with SSRS and PHP.
Once you have generated a report (as described here) and worked through the prerequisites (here), rendering a report in Excel format simply requires creating a new RenderAsEXCEL object and passing it to the Render2 method on the SSRSReport object. The resulting stream can then be written to the desired folder:
require_once 'SSRSReport.php';
define("SERVICE_URL", "http://localhost/ReportServer_SQLEXPRESS/");
define("REPORT", "/SalesReport/SalesByCategory");$ssrs_report = new SSRSReport(new Credentials('machineName\PHPDemoUser', 'pwd'), SERVICE_URL);
$ssrs_report->LoadReport2(REPORT, NULL);
$renderAsEXCEL = new RenderAsEXCEL();
$result_EXCEL = $ssrs_report->Render2($renderAsEXCEL,
PageCountModeEnum::$Estimate,
$Extension,
$MimeType,
$Encoding,
$Warnings,
$StreamIds);$handle = fopen("C:\\Path\\to\\desired\\folder\\" . "report.xls", 'wb');
fwrite($handle, $result_EXCEL);
fclose($handle);
That is all very straightforward…very similar to rendering a report in any of the other available formats…so what was the problem that had me stumped for a while? SSRS supports the .xls format for Excel documents. Was trying to save my report in the newer .xlsx format, which just produced garbage. So, consider yourself warned: be sure to save Excel documents in the older .xls format.
Attached to this post is a simple script that will allow you to render a report in a format of your choice: HTML, PDF, or EXCEL.
That’s it for today.
Thanks.
-Brian