使用PHPExcel将excel文件导入MySQL表

问题描述

好的，所以我已经能够让php显示excel .xls表中的数据，但是我希望能够将这些数据插入到我的表中。我似乎无法弄清楚那部分，这是我到目前为止所得到的：

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真棒，我没有时间阅读所有内容它完全理解:(我必须在星期三之前把它转过来...先谢谢

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

编辑：这是它应该做的想法..价值部分是我不确定的。

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);
}

推荐答案

你应该创建一个数组并将其存储在像这样的数据库：

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);


}

这篇关于使用PHPExcel将excel文件导入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！