PHPExcel

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 finewithin 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 CNNECTIN
echo "Unable to connect to Informix Database\n"; // DISPLAY IF
CNNECTIN FAILS
exit();
}
$sql = "SELECT * FRM 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);
}
}

// 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()->Header('&C&HPlease
treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->Footer('&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()->();
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize();
$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";

, I found PART of my problem.

$objPHPExcel->getActiveSheet()->setCellValue('A' . $stat_year);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $stat_month);

I must have mistyped and put period "." instead of comma "," to seperate the
cell number from the variable. Fixed that and now I get somethingwhat I
get now is one row of data printed out on row 65,536 in Excel (I believe
this is the very last row of data allowed by Excel)

So, instead of printing out the 128 results I should get form the loop, it
only prints out one result on the max row available in Excel.

Ideas?

Jim,

I placed it above like you said, but i get the same result. I think I need
to do something like the below:

if(!empty($query)) {
while ($row = ifx_fetch_row($query)) {
for ($i = 3; $i <= 50; $i++) {
$stat_year = $row['stat_year'];
$stat_month = $row['stat_month'];
$name = $row['_name'];
$case_number = $row['case_number'];
echo "This ".$stat_year." ".$stat_month." ".$j_name." ".$case_number."<br
/>\n";
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $stat_year);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $stat_month);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $name);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $case_number);
}
}
}

To get it to work I need to set the $i value to the starting cell I want.
so, $i = 3 means that setCellValue('A' . $i) means the output will start
populating the data in Excel cell A3. If I use the code above I get an
output of the exact same record in rows 1-50. Maybe I need to put my query
results into an array and use the $i value to select that row of data from
the array also?

So how would I go about getting each result from my query into the
columns/rows?

Should I do my query, and then assign the first column to an array, the
second to another array, etc?

And then try to loop it using $i to count each row?

And just FYI, I put Happy 4th (even though today is the 3rd) because I won't
be checking my email tomorrow :)

GT IT!!!

I changed my code to the following:


$sql = "SELECT * FRM 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);
$row = ifx_fetch_row($query);
$results = array();

echo date('H:i:s') . " Add some data<br />\n";

for ($i = 3, $j = 0; $i <= $count; $i++, $j++) {
$results[$j] = ifx_fetch_row($query);

$objPHPExcel->getActiveSheet()->setCellValue('A' . $i,
$results[$j]['stat_year']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i,
$results[$j]['stat_month']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i,
$results[$j]['name']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i,
$results[$j]['case_age_group']);
}



I created a seperate query to get the row countcreated an empty array (
$results = array(); )

I set $i = 3 so my results will start printing on line 3 of the spreadsheet

I set $j = 0 so the row data will start at the first row of the results from
my query

Then I made the loop repeat until $i became equal to the total number of
rows

And I then set the value of my previsouly empty array equal to the current
counted row of my query results

And to round it all off I echo'd out my results by using the new
$results($j) value appended with my database column names ['stat_year'] etc.

AWESME! I hope someone else learns from this or can use this!