Reading an Excel File from a Database and then Writing it

Topics: Developer Forum, User Forum
Mar 2, 2012 at 8:48 PM
Edited Mar 2, 2012 at 8:56 PM

I am new to PHP EXCEL and my very first test is giving me grief.  Basically what I am doing is this:

  1. Upload a spreadsheet to a database.  Spreadsheet is not huge.  it usually has less than 100 rows and around 60 columns.
  2. In a different routine, Spreadsheets are retrieved from the database and written to a cleverly named file directory called .... FILES.
  3. I have confirmed that the spreadsheet file retrieved from the database and written to the FILES directory is readable by EXCEL.
  4. After the spreadsheet file is written to the directory a fully qualified path+filename is presented to a function as a parameter. The function (code below) is supposed to
    • Open the file using PHPEXCEL
    • Do a bunch of audits and update the spreadsheet cells with error messages (ala the 15datavalidation.php example)
    • Then write the updated spreadsheet ala the 01simple-downlaod-xls.php example.That is, offer to open or download the newly named spreadsheet that contains the audits.

But here's the problem:  the file that is being written when openned by excel (a) generates the "the file you are trying to open ..." warning message, (b) when I open it anyway the file it contains three different kinds of information and is not at all a spreadsheet file:

  • The stylesheet for the overall site (how did PHPEXCEL get this???)
  • What looks like the Excel bytecodes in the middle, and finally
  • A whole bunch of "undefined Offset errors" at the end.

I am stumped.  HELP!

Here is the routine that is supposed to read, audit, and write the spreadsheet. I have removed audits.

<?php
function auditSpreadsheet(&$theOutput,
                            $theDIRandFileName,    // The fully qualified filepath+filename of the spreadsheet retrieved from the DB and placed in a host directory
                            $SheetNum,
                            $SheetName)
 {
    error_reporting(E_ALL);
    date_default_timezone_set('America/New_York');
    $IOFactory = $_SESSION['FUNCTION_utilities'].'PHPExcel/IOFactory.php';
    require_once $IOFactory;

    if (!file_exists($theDIRandFileName)) {
        $theOutput .= 'The fully qualified filepath+filename =['.$theDIRandFileName.' <strong>cannot be found</strong>.<br>';
        return;
    } else {
        $theOutput .= 'The fully qualified filepath+filename =['.$theDIRandFileName.' <strong>Has been found</strong>.<br>';
    }
    $objPHPExcel = new PHPExcel();
    $objReader = new PHPExcel_Reader_Excel5();     
    $objPHPExcel = $objReader->load( $theDIRandFileName );       
//    $objPHPExcel = PHPExcel_IOFactory::load($theDIRandFileName);  // original code.  Replaced with the above based upon slumming around the net
                                                                                                     // Same results

    $objPHPExcel->setActiveSheetIndex(0);                            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator(); // instantiate the iterator

////////////////                        Audit Spreadsheet goes here               /////////////////////
            // (1) Inner iterate through cells Columns 'A' through 'AQ'
            // (2) Outer interate row 2 through end .................. HOW do I sense End of file????
            // (3) generate query.  How????????????????
            ////////////////                        Audit Spreadsheet NED                     /////////////////////
// Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="Audited_Spreadsheet.xls"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');

     return;
 } // end of function
?>

Coordinator
Mar 3, 2012 at 8:43 AM

The chances are that there's something in your script that is echoing the stylesheet information. PHPExcel doesn't have that, but something in your code does... is this script being run from within a framework or similar that might add it's own output?

Mar 3, 2012 at 7:36 PM
Edited Mar 3, 2012 at 7:41 PM

Got it working, but in a different way.  The problem was related to my calling the above routine inside another routine that was in the process of dynamically generating HTML and PHP via $htmlset .= <<<_STUFF tags.  This was then sent to a simple echo $STUFF to paint the screen.   Bleh.

I switched to simply writing it to a server directory and doing an href= to it.  Works just ducky.

<break>

But while I'm at it I have another query:  I want to do conditional coloring and messages of cells based upon whether the contents are in error, are in a  warning state, or just require conveying of some informational.  No problem (I think) with doing the auditing.  Just bizzarely detailed -- I think I will write a code writter...  But I also want to conditionally add a comment to the colorized cell when one or more of the conditions is triggered. Pseudo-code would be something like this:

if($thisCell) == BOO-BOO-type1 {    //  Boo-boo 1 relates to, say, $objConditional1 which tests for a boo-boo.  Conditionals2 .. n might also apply and set their own boo-boo states.
    ....
    ...
   $booBoo .= 'Boy, you messed up again. ';
   $objPHPExcel->getActiveSheet()->getComment('$thisCell')->getText()->createTextRun($booBoo);

} else {    // no boo-boo

   <NO-OP>

}

Easy to Pseudo-code, but for the life of me I have no clue how to conditionally test if a cell had a $objectContitional_x_ fire and what I need to do syntactically.  I tried to slum through the various documentation sources (online and word) and have no clue how to do this! 

Is this doable?  A smidge of advice would be OH! so helpful.

Thanks,

Victor