PHPExcel, select sheet when importing data to mysql

Question

PHPExcel, select sheet when importing data to mysql

Navigation

#1 by (0 votes)

0

I want to import data from a worksheet but from sheet 2 and I'm not able to. I indicated sheet 2 (index 1) as active in this way, but always goes to sheet 1 (index 0):

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

What will be incorrect?

This is the code I'm using:

SCRIPT TO RUN FOR IMPORT OF xlsx CHOOSING ABA (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

asked by anonymous 24.05.2016 / 15:04

1 answer

How to get away with the Parallax effect with angular? Delete file (photo) from the folder when performing update

score 0 · Accepted Answer

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

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

or alternatively but less productive in the way you said in my review;

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

And then get the data this way:

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

or:

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

Try to do this in 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.