Jump to content

phpexcell multiple files in one directory


Paulqvz

Recommended Posts

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);
?>
Link to comment
Share on other sites

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 by CroNiX
Link to comment
Share on other sites

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.

Link to comment
Share on other sites


//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 by CroNiX
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.