StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCant export to excel 2010 student edition via Zend Framework
text
Body
copied!<p>I'm trying to export data to ms excel, I have Office 2010 student edition for my application by following Pablo Viquez's post <a href="http://www.pabloviquez.com/2009/08/export-excel-spreadsheets-using-zend-framework/" rel="nofollow">http://www.pabloviquez.com/2009/08/export-excel-spreadsheets-using-zend-framework/</a>. </p> <p>However I can't get it working the following happens </p> <p>1.On entering reports/report/todaysappointmentsreport/format/excel into the address bar the workbook attempts to save as excel.xls </p> <p>2.When I open the file Excel gives me the following warning: The file you are trying to open 'excel.xls', is in a different format than specified by the file extension verify the file is not corrupt and is from a trusted source before opening the file. </p> <ol> <li>On opening the file all that is shown is the code in my todaysappointmentsreport.export.phtml file </li> </ol> <p>Can anyone tell me where I'm going wrong, as I need to get this working. </p> <p>I can verify that the query works and the data appears in my todaysappointmentsreport.phtml file on screen. I can also verify that the component is installed in pear along with the OLE component that it is dependent on. </p> <p>My Controller code </p> <pre><code>class Reports_ReportController extends Zend_Controller_Action { public function init() { // Excel format context $excelConfig = array('excel' => array ('suffix' => 'excel', 'headers' => array( 'Content-type' => 'application/vnd.ms-excel')), ); //initalise context switch $contextSwitch = $this->_helper->contextSwitch(); // Add the new context $contextSwitch->setContexts($excelConfig); // Set the new context to the reports action $contextSwitch->addActionContext('todaysappointmentsreport', 'excel'); $contextSwitch->initContext(); } // action to redirect user straight to login page public function preDispatch() { // set admin layout // check if user is authenticated // if not, redirect to login page $url = $this->getRequest()->getRequestUri(); if (!Zend_Auth::getInstance()->hasIdentity()) { $session = new Zend_Session_Namespace('petmanager.auth'); $session->requestURL = $url; $this->_redirect('/login'); } } // report to print todays appointments public function todaysappointmentsreportAction() { $t=date('y-m-d'); $q = Doctrine_Query::create() ->from('PetManager_Model_Groomappointments g') ->leftJoin('g.PetManager_Model_Clients c') ->leftJoin('g.PetManager_Model_Pets p') ->leftJoin('g.PetManager_Model_Users u') ->leftJoin('g.PetManager_Model_Groomservices s') ->leftJoin('s.PetManager_Model_Groomprocedures r') ->where('g.gapmtStatus = 1 AND g.gapmtDate = ?',$t) ->orderBy('g.gapmtSTime,g.gapmtSTime,u.name'); $result = $q->fetchArray(); if (count($result) >= 1) { $this -> view -> records = $result; } } </code></pre> <p>My todaysappointmentsreport.excel.phtml code as I said this is what appears in the excel file when I open it. </p> <pre><code> // Change error reporting for compatibility // Spreadsheet Excel Writter was built using PHP4, // so there's a lot of DEPRECATED notices error_reporting(E_ERROR | E_WARNING | E_PARSE); /** * PEAR package * * @link http://pear.php.net/package/Spreadsheet_Excel_Writer * @see PEAR/Spreadsheet/Excel/Writer.php */ require_once 'Spreadsheet/Excel/Writer.php'; // Lets define some custom colors codes define('CUSTOM_DARK_BLUE', 20); define('CUSTOM_BLUE', 21); define('CUSTOM_LIGHT_BLUE', 22); define('CUSTOM_YELLOW', 23); define('CUSTOM_GREEN', 24); // First, we create a Workbook $workbook = new Spreadsheet_Excel_Writer(); // Add one sheet, called: Users Report $worksheet = &$workbook->addWorksheet('Todays Grooming Appointments Report'); // Create the custom colors on our new workbook // This function takes 4 params: // - Code index [1 to 64] // - RGB colors (0-255) $workbook->setCustomColor(CUSTOM_DARK_BLUE, 31, 73, 125); $workbook->setCustomColor(CUSTOM_BLUE, 0, 112, 192); $workbook->setCustomColor(CUSTOM_LIGHT_BLUE, 184, 204, 228); $workbook->setCustomColor(CUSTOM_YELLOW, 255, 192, 0); $workbook->setCustomColor(CUSTOM_GREEN, 0, 176, 80); // Lets hide gridlines //$worksheet->hideScreenGridlines(); // Lets create some custom styles $formatHeader = &$workbook->addFormat(); $formatHeader = &$workbook->addFormat( array('Size' => 16, 'VAlign' => 'vcenter', 'HAlign' => 'center', 'Bold' => 1, 'Color' => 'white', 'FgColor' => CUSTOM_DARK_BLUE)); $formatReportHeader = &$workbook->addFormat( array('Size' => 9, 'VAlign' => 'bottom', 'HAlign' => 'center', 'Bold' => 1, 'FgColor' => CUSTOM_LIGHT_BLUE, 'TextWrap' => true)); $formatData = &$workbook->addFormat( array( 'Size' => 8, 'HAlign' => 'center', 'VAlign' => 'vcenter')); /** * First, format the worksheet, adding the headers * and row/columns custom sizes */ // Create a nice header with a dark blue background // The function setRow takes 3 parameters: // - row index // - row height // - Format to apply to row [Optional] $worksheet->setRow(0, 11, $formatHeader); $worksheet->setRow(1, 46, $formatHeader); $worksheet->setRow(2, 11, $formatHeader); $worksheet->setRow(3, 11, $formatHeader); $worksheet->setRow(4, 11, $formatHeader); // Set the size of the columns // The function setColumn takes 5 params: // - First column // - Last column // - Column Width // - Format [Optional, default = 0] // - Hidden [Optional, default = 0] $worksheet->setColumn(0, 0, 7); // shrink it to 7 $worksheet->setColumn(1, 1, 12); // set the width to 12 $worksheet->setColumn(1, 1, 15); // set the width to 15 $worksheet->setColumn(1, 1, 15); // set the width to 15 $worksheet->setColumn(1, 1, 15); // set the width to 15 /** * * Once we have the format ready, add the text to the spreadsheet * */ // Write a text header $worksheet->write(1, 1, 'Todays Grooming Appointments Report', $formatHeader); // Create the header for the data starting @ row 6 $indexCol = 0; $indexRow = 6; $worksheet->write($indexRow, $indexCol++, 'Scheduled Time', $formatReportHeader); $worksheet->write($indexRow, $indexCol++, 'Client', $formatReportHeader); $worksheet->write($indexRow, $indexCol++, 'Pet', $formatReportHeader); $worksheet->write($indexRow, $indexCol++, 'Procedure', $formatReportHeader); $worksheet->write($indexRow, $indexCol++, 'Groomer', $formatReportHeader); $indexRow++; // Advance to the next row $indexCol = 0; // Start @ column 0 // Print the report data if(count($this->records) == 0) { // No data $worksheet->write( $indexRow, $indexCol, 'No Appointments', $formatData); } else { // Write the data foreach ($this->records as $r) { $worksheet->write( $indexRow, $indexCol++, $this->$r['gapmtSTime'] - $this->substr$r['gapmtETime'], $formatData); $worksheet->write( $indexRow, $indexCol++, $this->$r['PetManager_Model_Clients']['firstName'] $this->$r ['PetManager_Model_Clients']['lastName'], $formatData); $worksheet->write( $indexRow, $indexCol++, $this->$r['PetManager_Model_Pets']['name'], $formatData); $worksheet->write( $indexRow, $indexCol++, $this->$r['PetManager_Model_Groomservices']['PetManager_Model_Groomprocedures']['groomprocedure'], $formatData); $worksheet->write( $indexRow, $indexCol++, $this->$r['PetManager_Model_Users']['name'], $formatData); // Advance to the next row $indexRow++; } } /** * * Response with the excel file * */ // Sends HTTP headers for the Excel file. $workbook->send('todaysappointmentsreport.xls'); // Calls finalization methods. // This method should always be the last one to be called on every workbook $workbook->close(); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload