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", "https://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

Share this on Twitter

ssrsDemo.zip

Comments

• Anonymous
  October 14, 2010
  Great news. Now I can power my reports from PHP.
• Anonymous
  October 15, 2010
  Excellent! Let us know how things work for you...we'd love feedback.-Brian
• Anonymous
  January 05, 2014
  Thanks for the short and sweet tutorial!
• Anonymous
  August 10, 2014
  BrianI've been using the SDK for a couple of years to publish reports via PHP.  I want to render to Excel but the file is always corrupted.  I've read the web articles about the additional blank space in front of <?php in the RenderAsEXCEL.php and removed it as well as all trailing empty lines from other php files in the SDK but no change.  Is there anything else that you are aware of that causes Excel files to corrupt when being rendered via the SDK?My report works when rendered to HTML, PDF and even CSV. Here is my code:

      $renderAsExcel = new RenderAsEXCEL();    $resultExcel = $ssrs_report->Render2($renderAsExcel,                 PageCountModeEnum::$Estimate,                 $Extension,                 $MimeType,                 $Encoding,                 $Warnings,                 $StreamIds    );    header("Content-Type: application/force-download");    header("Content-Disposition: attachment; filename="RebateReport.xls"");            //header("Content-Type: application/vnd.ms-excel");    header("Content-length: ".(string)(strlen($resultExcel)));    header("Expires: ".gmdate("D, d M Y H:i:s", mktime(date("H")+2, date("i"),date("s"),date("m"), date("d"),date("Y")))." GMT");    header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");    header("Cache-Control: no-cache, must-revalidate");    header("Pragma: no-cache");            echo $resultExcel;

  Struan
• Anonymous
  November 06, 2016
  the result of renderexcel i have is like corupt file. any one have answer ?