Import an excel file into a MySQL table with PHPExcel

Question

Ok so I have been able to get php to show the data in excel .xls sheet but this same data I wanna be able to insert into my table. I can't seem to figure that part out, here's what I got so far:

    $path = $_GET['file'];
include("../class/sql.php");
require '../class/PHPExcel.php';
require_once '../class/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($path);
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 '<br>Data: <table width="100%" cellpadding="3" cellspacing="0"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {

        echo '<tr>';
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            if($row === 1)
            echo '<td style="background:#000; color:#fff;">' . $val . '</td>';
            else
                echo '<td>' . $val . '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

btw PHPExcel is awesome and I haven't had the time to read through all of it to fully understand :( I have to turn this in by wednesday.. Thanks in advance

Edit: this is the idea that it should do..the values part is the one I am unsure about.

$sql = "insert into tablename (col1, col2, col3) values(...)";
//start at row 2 so headers are not inserted
for ($row = 2; $row <= $highestRow; ++ $row) {

    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
        $cell = $worksheet->getCellByColumnAndRow($col, $row);
        $val = $cell->getValue();
        //here's my prob..
        echo $val;
    }
    $result = mysql_query($sql);
}

Oh no it's not homework, it's a freelance that I tackled and last minute they asked for this option and the whole project has to be in by Wednesday. If it would of been in .net I would of been done by now but this had to be done in php :( — Andres, Nov 22 '11 at 03:15
What does you excel file look like compared to your MySQL table? — Jason, Nov 22 '11 at 11:03
@Jason, ayou mean they differ in the structure? @Andres, you have so save the `$val` values to the database for each row (tr) also this post describes this: http://phpexcel.codeplex.com/discussions/264916 — , Nov 22 '11 at 11:14
@Kaosforge i looked at that link but all it says is what your saying which I know, it goes through each row and saves but how or when do I save it to the table? thats my question. I have edited my question to include an idea I have, so you see that I am putting some effort into it :) — Andres, Nov 22 '11 at 14:57
If they are the same as you are looping through the cells add them to an array to build the SQL statement - KAOSFORGE's answer should do the trick. — Jason, Nov 22 '11 at 15:55

score 7 · Accepted Answer · answered Nov 22 '11 at 15:07

You should create an array and store it in the database like this for example:

for ($row = 2; $row <= $highestRow; ++ $row) {
$val=array()
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);
    $val[] = $cell->getValue();
    //here's my prob..
    //echo $val;
}

$sql="insert into tablename (col1, col2, col3) values(`".$val[0]."`, `".$val[1]."`, `".$val[2].")";
$result = mysql_query($sql);


}

this looks awesome, I haven't tested yet because I currently don't have access to the ftp to upload the changes, but as soon as I try it I'll mark it as answered. Thanks! — Andres, Nov 22 '11 at 21:16

score 1 · Answer 2 · answered Nov 27 '14 at 11:13

if you want to use PHPExcel to do this :

<?php
//include the following 2 files
require 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$SERVER = 'localhost';
$USERNAME = 'username';
$PASSWORD =  'password';
$DB = 'database';
$DSN = "mysql:host=".$SERVER.";dbname=".$DB."";
$connection = new PDO($DSN,$USERNAME,$PASSWORD);

$path = "test.xlsx";

$objPHPExcel = PHPExcel_IOFactory::load($path);
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 "<br>The worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' row.';
    echo '<br>Data: <table border="1"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {
        echo '<tr>';
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

for ($row = 2; $row <= $highestRow; ++ $row) {
    $val=array();
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
   $cell = $worksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();
}

 $Connection="INSERT INTO `users` (name, family, type) VALUES ('".$val[1] . "','" . $val[2] . "','" . $val[3]. "')";

}
?>

score 0 · Answer 3 · answered Aug 14 '13 at 17:58

0

this is a good article using pear library Spreadsheet...

http://major.io/2008/11/07/importing-excel-files-into-mysql-with-php/

Check it

answered Aug 14 '13 at 17:58

sonseiya

488
3
14
31

Import an excel file into a MySQL table with PHPExcel

3 Answers3

Linked