PHPExcel + Sybase

Topics: Developer Forum, User Forum
Jan 22, 2009 at 9:50 PM
I am running a Sybase database and using PHPExcel to generate report documents. Anytime I try to run multiple sybase queries only the results from the last query ran are returned, even if I try to put the results from the query into a variable before running a new query. Does anyone have any idea why this might be happening? I know it has to be something with PHPExcel, because I run the same queries on a pure PHP output and everything works properly.
Developer
Jan 22, 2009 at 10:40 PM
Can you show us some code? Maybe we can better tell what the problem is.

Jan 23, 2009 at 12:51 PM
Code is as follows:

<?php

    $Home_dir = "../";

    //Information needed to run report query
    $Approp_ID = $_COOKIE['approp_info'];
    $Fiscal_year = $_COOKIE['fy_info'];
    $User_id = $_COOKIE['submission_user_id'];
    $User_org = $_COOKIE['user_org'];
    $Org_title_query = "SELECT org_title FROM ORG_TITLE WHERE org_id = " . $User_org;
    $Org_title_result = sybase_query($Org_title_query);
    $Org_title = sybase_result($Org_title_result, 0, 0);
    $Approp_query = "SELECT approp FROM APPROPRIATION WHERE approp_id = " . $Approp_ID;
    $Approp_result = sybase_query($Approp_query);
    $Approp = sybase_result($Approp_result, 0, 0);
    
    //Connect to the database
    include ("../include/dbconnect.php");

    //Include PHPExcel information to build Excel document
    $Include_path = "../include/PHPExcel/Classes/";
    include ($Include_path . "PHPExcel.php");
    include ($Include_path . "PHPExcel/Writer/Excel2007.php");

    //Create the Excel document object
    $objPHPExcel = new PHPExcel();

    //Set the document properties for the document
    $objPHPExcel->getProperties()->setCreator('Jeremy Twidt');
    $objPHPExcel->getProperties()->setTitle('Change Report');
    $objPHPExcel->getProperties()->setSubject('Change Report');

    //Set up the document for display
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
    $objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
    $objPHPExcel->getActiveSheet()->setTitle('Change Report');
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Calibri');
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);

    //Format the column widths for the report
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13);
    
    //Set the report title information
    $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Upward Obligation Adjustments (Within Scope Adjustments) ' . $Org_title);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Appropriation: ' . $Approp . ' As of Fiscal Year ' . $Fiscal_year);
    $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setSize(12);
    $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
    $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true);

    //Set the column headers
    $objPHPExcel->getActiveSheet()->setCellValue('A4', 'PPA');
    $objPHPExcel->getActiveSheet()->setCellValue('B4', '1ST EXP FY');
    $objPHPExcel->getActiveSheet()->setCellValue('C4', '2ND EXP FY');
    $objPHPExcel->getActiveSheet()->setCellValue('D4', '3RD EXP FY');
    $objPHPExcel->getActiveSheet()->setCellValue('E4', '4TH EXP FY');
    $objPHPExcel->getActiveSheet()->setCellValue('F4', '5TH EXP FY');
    $objPHPExcel->getActiveSheet()->setCellValue('G4', 'CANCELED FY');
    $objPHPExcel->getActiveSheet()->setCellValue('H4', 'TOTAL');

    //Set the styles for the column header
    $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A4'), 'B4:H4');

    $Row_count = 1;

    $Report_Query = "EXEC getWSCCReport2 @ORG_ID = " . $User_org . ", @APPROVAL_YEAR = " . $Fiscal_year . ", @APPROP_ID = " . $Approp_ID;
    $Report = sybase_query($Report_Query);

    //Goes through each organization
    for ($Org_counter = 0; $Org_counter < sybase_num_rows($Report); $Org_counter+=6)
    {

        //Adds up the total value for each of the organizations return years
        for ($Col_counter = 0; $Col_counter < 6; $Col_counter++)
        {
            $Sum = $Sum + sybase_result($Report, $Org_counter + $Col_counter, 3);
        }

        //If the organization had activity in any of the six years selected, add it to the report
        if ($Sum <> 0)
        {
            if ($Row_count % 2 ==0)
            {
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $Row_count + 5)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $Row_count + 5)->getFill()->getStartColor()->setARGB('00C8C8C8');
                $Start_cell = 'A' . strval($Row_count + 5);
                $End_cell = 'H' . strval($Row_count + 5);
                $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle($Start_cell), $Start_cell . ':' . $End_cell);
            }
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $Row_count + 5, strval(sybase_result($Report, $Org_counter, 1)));

            //If there is a dollar amount for the year output it to the report
            for ($Col_counter = 0; $Col_counter < 6; $Col_counter++)
            {
                if (sybase_result($Report, $Org_counter + $Col_counter, 3) <> 0)
                {
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col_counter + 1 , $Row_count + 5, number_format(round(sybase_result($Report, $Org_counter + $Col_counter, 3)), 0, '', ''));
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Col_counter + 1, $Row_count + 5)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
                    switch (sybase_result($Report, $Org_counter + $Col_counter, 2))
                    {
                        case 1:
                            $First_exp_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                        case 2:
                            $Second_exp_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                        case 3:
                            $Third_exp_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                        case 4:
                            $Fourth_exp_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                        case 5:
                            $Fifth_exp_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                        case 6:
                            $Cancelled_FY_total += sybase_result($Report, $Org_counter + $Col_counter, 3);
                            break;
                    }
                }

                //Otherwise put a zero in place
                else
                {
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($Col_counter + 1 , $Row_count + 5, '0');
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($Col_counter + 1, $Row_count + 5)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
                }
            }

            //Add the organizations total UOA values for the six years to the report
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7 , $Row_count + 5, round($Sum, 0));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(7,  $Row_count + 5)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
            $Sum_total += $Sum;
            $Sum = 0;
            $Row_count += 1;
        }
        else
        {
            $Sum = 0;
        }
    }

    //Add the totals row to the report, which is a total for all organizations for each year
    $Total_row = $Row_count + 6;
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $Total_row, 'Total');
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $Total_row, round($First_exp_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $Total_row, round($Second_exp_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $Total_row, round($Third_exp_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $Total_row, round($Fourth_exp_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $Total_row, round($Fifth_exp_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $Total_row, round($Cancelled_FY_total));
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $Total_row, round($Sum_total));
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(2, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(4, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(4, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(5, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(5, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(6, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(6, $Total_row)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(7, $Total_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(7, $Total_row)->getFont()->setBold(true);

    //Set the print area for the document
    $Last_cell = 'H' . strval($Total_row);
    $objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:' . $Last_cell);

    //Set the header information so that file is sent to users Excel
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="WSCCReport.xlsx"');
    header('Cache-Control: max-age=0');


    //Prepare the file for output and output it
    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
    $objWriter->save('php://output');
?>
Developer
Jan 24, 2009 at 3:39 AM
Uh... your script is a bit long, and I'm still not sure if I understand the problem correctly.

Usually, I solve this kind of problem by injecting some echo statement to see where things go wrong. For example:

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $Total_row, round($First_exp_FY_total));
echo 'Inserting :';
var_dump(round($First_exp_FY_total));
echo 'in column 1, row ';
var_dump($Total_row);

Usually some variable is not what you would expect. This technique for debugging has never failed for me!

Jan 24, 2009 at 3:31 PM
I know that all of the code itself works because if I re-arrange the order of the database queries I always get results from the last query but none of the others. That is what my problem is
Developer
Jan 24, 2009 at 3:40 PM
It is possible that your queries are working, but maybe you are overriding the cells? Try my suggestion to see where things go wrong, echo statements always do the trick!

Jan 26, 2009 at 8:20 PM
I figured out what that problem was, but now I get a corrupt file whenever I have a database connection in my php script. I know it isn't the corrupt zip extension because if there isn't a database connection the excel sheet downloads fine. Any ideas?
Developer
Jan 27, 2009 at 6:22 AM
In that case, first try to save Excel file on disk instead of sending directly to client browser.

I'm guessing that your include script for database connection has some white space before opening <?php tag or closing ?> tag.

Jan 27, 2009 at 12:57 PM
I thought about something being wrong with the include script, but I still get a corrupted file when I copy the code directly into my script, even when I trim it down to just the line that establishes the database connection.  When I save the file to disk, the file comes out fine.  On that note if I use fpassthru do i still need the download headers or how does that work?
Developer
Jan 27, 2009 at 1:40 PM
Can you try to compare the two files (saved to disk vs. send to browser) in a HEX editor. File sizes in bytes are supposed to be exactly the same.

Look for differences especially at the beginning of the file, and post back what you find.