[Fixed]named sheets

Topics: Developer Forum, User Forum
Aug 30, 2010 at 1:48 PM
Edited Aug 30, 2010 at 1:51 PM

Hi to everyone and excuse me for my english.

I have an xls-file  with set named sheets.

I can't  iterate thou them to grab the data. 

Doing like that:

$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly($sheets_names);
$objXLS = $objReader->load($dir."/".$file->filename);

foreach ($sheets as $sheet){
   $objWorksheet = $objXLS->setActiveSheetIndexByName($sheet['name']);

   /*        some actions */
}


  WHERE 
$sheets = array(
array('name' => 'sheet name',
'language' => 'ru',
'vid' => 7
))


Help me please

 

Coordinator
Sep 1, 2010 at 1:51 PM
Edited Sep 1, 2010 at 2:06 PM

You don't show how you're creating the $sheets_names array:

I've created a workbook with three sheets ('Sheet1', 'Sheet2' and 'Sheet3'), and executed the following code without any problems.

$sheets = array( array('name' => 'Sheet1' ),
                 array('name' => 'Sheet4' ),
                 array('name' => 'Sheet2' )
               );
$sheets_names = array();
foreach ($sheets as $sheet){
   $sheets_names[] = $sheet['name'];
}
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly($sheets_names);
$objXLS = $objReader->load('formula.xls');

echo 'Loaded sheet names<br />';
echo '<pre>';
var_dump($objXLS->getSheetNames());
echo '</pre>';
echo 'Iterate through sheets<br />';
foreach ($sheets as $sheet){
   try {
      $objWorksheet = $objXLS->setActiveSheetIndexByName($sheet['name']);
      echo $objWorksheet->getTitle(),'<br />';
   } catch (Exception $e) {
      echo $e->getMessage(),'<br />';
   }
}

 This gives me the result:

Loaded sheet names

array
  0 => string 'Sheet1' (length=6)
  1 => string 'Sheet2' (length=6)

Iterate through sheets
Sheet1
Workbook does not contain sheet:Sheet4
Sheet2
Sep 2, 2010 at 6:25 AM

You are absolutely right about how $sheet_names array were created

$sheets_names = array();
   foreach ($sheets as $sheet){
     $sheets_names[] = $sheet['name'];   
   }

Thank you for your response. But I could not understand why same code doesn't work for me.

Sep 2, 2010 at 7:32 AM
Edited Sep 2, 2010 at 11:51 AM

I have tested tour code and you absolutely right - it works, but not how it should be.

It successfully iterates through sheet names but if you want to get data from sheet it returns data for sheet $sheets['0'] only.

This is the code and sample xls i used

I know that now it's not phpExcell question, but I'm hoping for help: i can't understand why why code is not working, if doing so:

foreach ($objWorksheet->getRowIterator() as $row) {
        $c = 0;
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        foreach ($cellIterator as $cell) {


          var_dump($cell->getValue());
        }
       
     }

It's working perfect, but if i try to do some thing like that:

foreach ($objWorksheet->getRowIterator() as $row) {
        $c = 0;
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        foreach ($cellIterator as $cell) {

            $iqmenu_data[$objWorksheet->getTitle()][$r][$c] = trim($cell->getValue());
            $c++;
       
        }
        $r++;
     }

It's do what i said before - it just writing values of sheet with first index in all other arrays


Coordinator
Sep 2, 2010 at 12:22 PM
echo 'Iterate through sheets<br />';
foreach ($sheets as $sheet) {
    try {
        $r = 0;
        $objWorksheet = $objXLS->setActiveSheetIndexByName($sheet['name']);
        echo '<b>Sheet: ',$objWorksheet->getTitle(),'</b><br />';
        foreach ($objWorksheet->getRowIterator() as $row) {
            $c = 0;
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            foreach ($cellIterator as $cell) {
                $iqmenu_data[$objWorksheet->getTitle()][$r][$c] = trim($cell->getValue());
                $c++;
            }
            $r++;
        }
        var_dump($iqmenu_data[$objWorksheet->getTitle()]['2']);
        var_dump($iqmenu_data[$objWorksheet->getTitle()]['3']);
    } catch (Exception $e) {
        echo $e->getMessage(),'<br />';
    }
}

Seems to work quite well. $iqmenu_data is being populated correctly as a 3d array indexed by [sheetName][row][column] then displaying a dump of rows 2 and 3 for each sheet.

 

Note that there is also a toArray() method for the worksheet object, which can simplify your code (thpough it won't automatically trim the cell values)

echo 'Iterate through sheets<br />';
foreach ($sheets as $sheet) {
    try {
        $objWorksheet = $objXLS->setActiveSheetIndexByName($sheet['name']);
        echo '<b>Sheet: ',$objWorksheet->getTitle(),'</b><br />';
        $iqmenu_data[$objWorksheet->getTitle()] = $objWorksheet->toArray();
        var_dump($iqmenu_data[$objWorksheet->getTitle()]['2']);
        var_dump($iqmenu_data[$objWorksheet->getTitle()]['3']);
    } catch (Exception $e) {
        echo $e->getMessage(),'<br />';
    }
}

 

Sep 2, 2010 at 12:34 PM

Thank you vary much!

I've just saw my mistake - I haven't set to zero $r row index on each iteration with sheet.

Method toArray is great. If I found it before it would prevent this annoying error

 

Thanks for good product and for your help. Pay a bit more attention on documentation ;)

Good luck