How to Use PHPexcel to Read Data and Insert into Database

How to use phpexcel to read data and insert into database?

Using the PHPExcel library to read an Excel file and transfer the data into a database

//  Include PHPExcel_IOFactory
include 'PHPExcel/IOFactory.php';

$inputFileName = './sampleData/example1.xls';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Insert row data array into your database of choice here
}

Anything more becomes very dependent on your database, and how you want the data structured in it

PHPEXCEL : how to read each row from excel file and insert into database

Try something like this.

    for ($row = 2; $row <= $highestRow; $row++) {
        $column_1 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('A' . $row)->getValue()));
        $column_2 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('B' . $row)->getValue()));
        $column_3 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('C' . $row)->getValue()));
        $column_4 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('D' . $row)->getValue()));
        $column_5 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('E' . $row)->getValue()));
        $column_6 = mysql_real_escape_string(trim($objPHPExcel->getActiveSheet()->getCell('F' . $row)->getValue()));

        $values = "('$column_1','$column_2','$column_3','$column_4','$column_5','$column_6')";
        if ($values != '') {
            $sqlInsert = "INSERT INTO table (column_1,column_2,column_3,column_4,column_5,column_6) VALUES $values";
            mysql_query($sqlInsert) or die(mysql_error());
        }
    }

How do I use PHPExcel to read data from an Excel file?

Mark Baker was extremely helpful in guiding me to the right answer. I don't use Composer with PHP (I should probably learn), but given that, in order to get this to work I went to the GitHub page for PHPExcel (https://github.com/PHPOffice/PHPExcel), clicked the green Clone and download button, and then the Download ZIP link.

After unzipping the file, I got a folder called PHPExcel-1.8. I moved that folder to the same folder as both the Excel file I wanted to read (in my code below test.xlsx) and the PHP file that has the code below.

The key to getting it to work was inputting the correct path to the IOFactory.php file. It may seem simple to some, but it was tripping me up.

Given the above and Mark Baker's comments, the following code worked perfectly for me (note the commented parts):

<?php

  //Had to change this path to point to IOFactory.php.
  //Do not change the contents of the PHPExcel-1.8 folder at all.
  include('PHPExcel-1.8/Classes/PHPExcel/IOFactory.php');

  //Use whatever path to an Excel file you need.
  $inputFileName = 'test.xlsx';

  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
  } catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . 
        $e->getMessage());
  }

  $sheet = $objPHPExcel->getSheet(0);
  $highestRow = $sheet->getHighestRow();
  $highestColumn = $sheet->getHighestColumn();

  for ($row = 1; $row <= $highestRow; $row++) { 
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
                                    null, true, false);

    //Prints out data in each row.
    //Replace this with whatever you want to do with the data.
    echo '<pre>';
      print_r($rowData);
    echo '</pre>';
  }

SQL to insert data read from excel using PHPExcel

If I can assume that your Excel column header names never change, why not simply use a mapping array?

$dbMapping = array(
    'col1' => header1,
    'col2' => header2,
     ..
    'colN' => headerN
);

So when you're ready to insert to the database, you iterate through each row with the column header names you already have in your 2D array and pass it into your mapping array i.e. $dbMapping['col1'] and that will get you your header name and you can grab the correct row value.

psuedo

 foreach ($rows as $row) {
     insert into col1, col2, ... colN
       values ($rows[$dbMapping['col1']], $rows[$dbMapping['col2']], ...
 }

Of course it would be in your best interest to use parameterized values.

Reading spreadsheet using PHPExcel

When a file is uploaded to your webserver, The file will be saved in the temporary folder of your system with a random name.

What you were trying to do was giving the actual name of the file you uploaded, But since the file was created with a random name in the tmp folder.
You will need to use tmp_name instead, Which actually point the that random named file.

Also note, in name You only have the name of the file that was uploaded and not the path,
But with tmp_name you have the actual path to the file.

See the following example of a file upload you would get.

array(
 [UploadFieldName]=>array(
    [name] => MyFile.jpg
    [type] => image/jpeg
    [tmp_name] => /tmp/php/php6hst32
    [error] => UPLOAD_ERR_OK
    [size] => 98174
  )
)

change your code to this instead

 //Check valid spreadsheet has been uploaded
if(isset($_FILES['spreadsheet'])){
if($_FILES['spreadsheet']['tmp_name']){
if(!$_FILES['spreadsheet']['error'])
{

    $inputFile = $_FILES['spreadsheet']['tmp_name'];
    $extension = strtoupper(pathinfo($inputFile, PATHINFO_EXTENSION));
    if($extension == 'XLSX' || $extension == 'ODS'){

        //Read spreadsheeet workbook
        try {
             $inputFileType = PHPExcel_IOFactory::identify($inputFile);
             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                 $objPHPExcel = $objReader->load($inputFile);
        } catch(Exception $e) {
                die($e->getMessage());
        }

        //Get worksheet dimensions
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow(); 
        $highestColumn = $sheet->getHighestColumn();

        //Loop through each row of the worksheet in turn
        for ($row = 1; $row <= $highestRow; $row++){ 
                //  Read a row of data into an array
                $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                //Insert into database
        }
    }
    else{
        echo "Please upload an XLSX or ODS file";
    }
}
else{
    echo $_FILES['spreadsheet']['error'];
}
}
}

?>

---

Related Topics