Reading Excel5

Topics: User Forum
Aug 24, 2009 at 3:50 PM

Sorry if I'm asking for something thats already in the documentation, but I've looked everywhere I could without finding much to help.

I just setup PHPExcel with my CakePHP project and it seems to be working great, but I am just unsure on how to use it for my needs. I need to read in an excel 5 file and pretty much pull the data out of it. No formatting and nothing complicated in the spreadsheet, just a few basic columns with strings and currency formatted numbers.

Is there a good way for me to convert a basic excel spreadsheet into an array or something easy to work with? I've printed the dump from getSheet(0), but its not exactly an easy format for me to then import into my MySQL tables.

As I said, sorry if I'm asking a stupid question thats been covered. Pointing me to any docs out there to help with this would be great too.

Thanks.

Coordinator
Aug 24, 2009 at 6:11 PM

 $highestRow  = $objWorksheet->getHighestRow();
 $highestColumn = $objWorksheet->getHighestColumn();

 $rowData = array();
 $row = 1;
 do {
  $columnData = array();
  $column = 'A';
  do {
   if ($objWorksheet->cellExists($column.$row)) {
    $actualValue = $objWorksheet->getCell($column.$row)->getValue();
    $displayValue = $objWorksheet->getCell($column.$row)->getCalculatedValue();
   } else {
    $actualValue = $displayValue = null;
   }
   $columnData[] = array( 'columnID'  => $column,
           'cellID'   => $column.$row,
           'actualValue' => $actualValue,
           'displayValue' => $displayValue
         );
  } while ($column++ != $highestColumn);
  $rowData[] = array( 'rowID'  => $row,
       'columns' => $columnData
        );
 } while ($row++ != $highestRow);

Aug 26, 2009 at 6:42 PM

Hi, blakej

 

Can you share your experience on how to setup PHPExcel with cakePHP? I googled this topic and looks like many people can successfully create an excel file with plain PHP (the test files that come with PHPExcel), but will get a "file corrupted ..." message with cakePHP. I am having the same problem, too. Thanks in advance.

 

Tony

Aug 26, 2009 at 8:22 PM

Thanks Mark. I should be able to tweak that around to fit my needs.

 

Tony, so far all I did was copy the PHPExcel files into the vendors directory, then in my controller action I included the lines:

App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php'));

App::import('Vendor', 'phpexcelreader', array('file' => 'PHPExcel'.DS.'Reader'.DS.'Excel5.php'));

Which covered all I needed. I haven't dealt with writing files with it yet, so I'm not sure what all I will need to do to get that working. From the looks of things so far though, that shouldn't be too bad if its anything like reading.

 

 

-Blake

App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php'));
App::import('Vendor', 'phpexcelreader', array('file' => 'PHPExcel'.DS.'Reader'.DS.'Excel5.php')); App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php'));
App::import('Vendor', 'phpexcelreader', array('file' => 'PHPExcel'.DS.'Reader'.DS.'Excel5.php')); App::import('Vendor', 'phpexcel', array('file' => 'PHPExcel.php'));
App::import('Vendor', 'phpexcelreader', array('file' => 'PHPExcel'.DS.'Reader'.DS.'Excel5.php'));

 

Aug 27, 2009 at 2:32 PM

Alright so all my reading in the file works great now. But one more question: is there a good way to handle invalid or damaged files? Right now it kills the entire script with "Fatal error: Uncaught exception 'Exception' with message....". Thats not a very friendly error to give users. Just wondering if theres a better way to handle this.

 

Thanks,

-Blake

Developer
Aug 27, 2009 at 2:41 PM
blakej wrote:

Alright so all my reading in the file works great now. But one more question: is there a good way to handle invalid or damaged files? Right now it kills the entire script with "Fatal error: Uncaught exception 'Exception' with message....". Thats not a very friendly error to give users. Just wondering if theres a better way to handle this.

 

Thanks,

-Blake

You can use a try-catch block. Try to look at this thread:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=61483

 

Aug 27, 2009 at 3:44 PM

Thanks. Worked great.