phpexcell multiple files in one directory

[Paulqvz]

Good day.

 

I have just written my first php phpexcell file importing into mysql without errors. now my next step is to try and read multiple excell files in a directory " files are same format" and import into mysql.

 

Heres my code so far.

<?php
$db_host = "localhost";$db_user = "root";$db_pass = "";$db_name ="kk";$db_table = "test";
require '/Classes/PHPExcel.php';
require_once '/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load('Amersfoort_WR.xlsx');
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) 
{
$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow(); // e.g. 10
$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "File ".$worksheetTitle." has ";
   echo $nrColumns . ' columns';
   echo ' y ' . $highestRow . ' rows.';
   echo "$cellValue";
    $link = mysql_connect($db_host,$db_user,$db_pass);
if(!$link) die ('Could not connect to database: '.mysql_error());
mysql_select_db($db_name,$link);
    for ($row = 2; $row <= $highestRow; ++ $row) 
{
    $val=array();
    for ($col = 0; $col < $highestColumnIndex; ++ $col) 
    {
        $cell = $worksheet->getCellByColumnAndRow($col, $row);
        $val[] = $cell->getValue();
    }
        $sql = "insert into test (F1, F2, F3, F4) values('".$val['0']."', '".$val['1']."', '".$val['2']."','".$val['3']."')";
        mysql_query($sql);
} 
}
mysql_close($link);
?>

[CroNiX]

glob will help you get the file names from a dir in the form of an array of file names. You can even specify *.xlsx to get only that file type. Once you have the files in an array, just loop through them and use your existing code using the filename in the array instead of your hardcoded filename.

//your includes...

foreach(glob('/path/to/files/*.xlsx') as $filename)
{
  $objPHPExcel = PHPExcel_IOFactory::load($filename);
  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
  {
    //rest of your code
  }
}

Edited January 11, 2015 by CroNiX

[CroNiX]

One thing in your code...you shouldn't be connecting to mysql or selecting the database within your loop. You just need to connect once and select the db once, unless it changes. Connect and select the db just once before any loop and then close the connection after the loops. You are already closing the connection after the loop. The loop should just be inserting into the database.

[CroNiX]

//your includes...

//connect to db

//select db


foreach(glob('/path/to/files/*.xlsx') as $filename)

{

  $objPHPExcel = PHPExcel_IOFactory::load($filename);

  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)

  {

    //rest of your code

  }

}


//close db connection

 

Edited January 11, 2015 by CroNiX