Phpexcel, select sheet when importing data to mysql

Asked

Viewed 2,150 times

1

I want to import data from a spreadsheet but from sheet 2 and I’m not getting it. I have indicated sheet 2 (index 1) as active in this way, but it always enters sheet 1 (index 0):

$objWorksheet = $objPHPExcel->setActiveSheetIndex(1);

What will be incorrect?

This is the code I’m using:

SCRIPT WORKING FOR IMPORTING xlsx BY CHOOSING THE TAB (INDEX)

<?php
     /** Error reporting */
     error_reporting(E_ALL);
     ini_set('display_errors', TRUE);
     ini_set('display_startup_errors', TRUE);
     date_default_timezone_set('America/Sao_Paulo');

     if (PHP_SAPI == 'cli') 
     die('This example should only be run from a Web Browser');

     /** Include PHPExcel and MySQLi db */
     require_once dirname(__FILE__) . '/Classes/DB.php';
     require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

     //Create DB object
     use DB\MySQLi;


     // Create new PHPExcel object
     $objPHPExcel = PHPExcel_IOFactory::load("arquivo.xlsx");
     $objWorksheet = $objPHPExcel->getSheet(1); // aqui indica a aba que quer importar
     $dataArr = array();

     foreach ($objWorksheet->getRowIterator() as $row) {
         $rowIndex = $row->getRowIndex();
         $cellIterator = $row->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(True); //varre todas as células
         foreach ($cellIterator as $cell) {
              $colIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
              echo ('Linha: '.$rowIndex.' Coluna: '.$colIndex.' Valor: '.$cell->getValue());
              $val = $cell->getValue();
              $dataArr[$rowIndex][$colIndex] = $val;

         }
    }

      unset($dataArr[1]); 


      $hostname = 'localhost';
      $username = 'root';
      $password = '';
      $database = 'dbase';

      $db = new MySQLi($hostname, $username, $password, $database);

      foreach($dataArr as $val){
          $query = $db->query("INSERT INTO employees SET fname = '" . $db->escape($val['1']) . "', lname = '" . $db->escape($val['2']) . "', email = '" . $db->escape($val['3']) . "', phone = '" . $db->escape($val['4']) . "', company = '" . $db->escape($val['5']) . "'");
      }
php mysql excel phpexcel

  • Have you tried to take $worksheetTitle = $Worksheet->getActiveSheet()->getTitle this way()?;

  • now gave this error: Fatal error: Call to Undefined method Phpexcel_worksheet::getWorksheetIterator() in line 25, this line: foreach ($objWorksheet->getWorksheetIterator() as $Worksheet) {

  • I’ll edit the answer, but getWorksheetIterator() scans all tabs if you want to get the data from just one why read the other?

  • but it does not have to scan all to find the indicated index? if not, what would be the correct method?

  • I didn’t understand, do you really want to take the data of all the tabs except the first one is this? Or is it all?

  • not @Nelson Aguiar, maybe I’m confusing things, I’ll clarify. I want to take an xlsx with multiple tabs and import to mysql only tab 2 or index 1.

  • in the above code, it shows this error: Fatal error: Call to Undefined method Phpexcel_worksheet::getWorksheetIterator() in import1.php on line 25. this getWorksheetIterator() method is not correct, but in my idea it would be what would see the existing tabs and select the chosen one in setActiveSheetIndex(1) but it is not working... and it is this error that I am unable to solve.

  • 1

    Face the tricky is that I am without PHP here to test this, but at first change setActiveSheet(1), to getSheet(1) and use that answer foreach there to see the output

  • Nelson, using his indications, added only these 2 lines in the $val = $Cell->getValue(); $dataArr[$rowIndex][$colIndex] = $val; and it’s already working. Thank you very much.

  • I’m glad you decided!

  • Nelson, just one more question, if I want to indicate a limit of columns, for example 10 and tab has 14, how could I make this choice column by column? Would be in this line $dataArr[$Row][$col] = $val; ?

  • 1

    you can make an if inside the second foreach with the following if instruction($colIndex > 9 ) break; or implect Phpexcel_reader_ireadfilter into your class, but it will give you a little more work, but it will already get the data filtering the columns take a look here: https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/ReadingSpreadsheetFiles/05-Reader-Options.md

Show 7 more comments

1 answer

1


To work on tabs or Sheets, as you prefer, you should use, remembering that the indexing of tabs start at 0:

$objWorksheet = $objPHPExcel->getSheet(1);

or alternatively however less productive in the way I said in my comment;

$objWorksheet = $objPHPExcel->setActiveSheetIndex(1);

And then take the data this way:

$worksheetTitle     = $worksheet->getActiveSheet->getTitle();

or:

$objWorksheet = $objPHPExcel->setActiveSheetIndex(1).getActiveSheet();

Try to do it this way in the foreach:

foreach ($objWorksheet->getRowIterator() as $row) {
  $rowIndex = $row->getRowIndex();
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(True); //varre todas as células
  foreach ($cellIterator as $cell) {
    $colIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
    echo ('Linha: '.$rowIndex.'Coluna: '.$colIndex.'Valor: '.$cell->getValue());

  }
}

Reference : Phpexcel Developer Documentation Worksheets

I hope it helps.

Browser other questions tagged php mysql excel phpexcel

You are not signed in. Login or sign up in order to post.