Re: PHPExcel

Dan Shirah wrote:

Happy 4th of July! I am having a little bit of a problem using PHPExcel. I think I'm doing everything right but apparently not! I am basically trying to output results of a query to Excel using PHPExcel. My query works fine, my variables are being passed correctly, my database connection is fine...within my while () loop the echo "This ".$stat_year." ".$stat_month."<br />\n"; properly returns all of the years and months for my query. But for whatever reason when I try and put those vaules in the Excel objects and export the spreadsheet, the values of my cells are always blank. Any ideas? /** PHPExcel */ include 'PHPExcel.php'; /** PHPExcel_Writer_Excel2007 */ include 'PHPExcel/Writer/Excel5.php'; // Create new PHPExcel object echo date('H:i:s') . " Create new PHPExcel object<br />\n"; $objPHPExcel = new PHPExcel(); // Add some data, we will use printing features $name_code = $_GET['name']; // echo $name; $case_age = $_GET['case_age']; // echo $case_age; $case_cat = $_GET['case_cat']; // echo $case_cat; $case_status = $_GET['case_status']; // echo $case_status; include 'Conn/prpr_ifx.php'; if (!$connect_id = ifx_connect("$database@$host", $user, $pass)) { // THE ACTUAL CONNECTION

     echo "Unable to connect to Informix Database\n"; // DISPLAY IF

CONNECTION FAILS

    exit();

} $sql = "SELECT * FROM brev_pending_summary_detail WHERE name = '$name_code'"; if (!empty($case_age)) { $sql.=" AND case_age_group = '$case_age'"; } if (!empty($case_cat)) { $sql.=" AND case_category = '$case_cat'"; } if (!empty($case_status)) { $sql.=" AND case_status = '$case_status'"; } // Start our query of the database $query = ifx_query($sql, $connect_id); echo date('H:i:s') . " Add some data<br />\n"; if(!empty($query)) { while ($row = ifx_fetch_row($query)) { $stat_year = $row['stat_year']; $stat_month = $row['stat_month']; echo "This ".$stat_year." ".$stat_month."<br />\n"; $objPHPExcel->getActiveSheet()->setCellValue('A' . $stat_year); $objPHPExcel->getActiveSheet()->setCellValue('B' . $stat_month); } }

I see you calling the method getActiveSheet(). My guess is that it does exactly what it says it does. Problem is, is that you are not **setting** the active sheet until after this point. Maybe try setting all the paramaters before you start adding data to the sheet? That might fix it.

// Set header and footer. When no different headers for odd/even are used, odd header is assumed. echo date('H:i:s') . " Set header/footer<br />\n"; $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); // Set page orientation and size echo date('H:i:s') . " Set page orientation and size<br />\n"; $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); // Rename sheet echo date('H:i:s') . " Rename sheet<br />\n"; $objPHPExcel->getActiveSheet()->setTitle('Pending Summary'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 5 file echo date('H:i:s') . " Write to Excel2005 format<br />\n"; $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save(str_replace('.php', '.xls', __FILE__)); // Echo done echo date('H:i:s') . " Done writing file.\r\n";

-- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare