php excel xls 匯入

標籤：new   reads   pread   mysqli   修改   cell   尾碼名   innodb   engine   

<?php
header("Content-Type:text/html;charset=utf-8");
require_once ‘reader.php‘;

$mysqli = new mysqli(‘localhost‘, ‘root‘, ‘‘, ‘c‘);
if ($mysqli->errno) {
    die(‘Connect Error‘ . $mysqli->error);
}
$mysqli->set_charset(‘UTF8‘);
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(‘utf-8‘);

$data->read(‘huojia.xls‘);

for ($i = 2; $i <= $data->sheets[0][‘numRows‘]; $i++) {
    $sql = "INSERT INTO cangku_huojia(ck_id,huojia_name) VALUES(‘" . $data->sheets[0][‘cells‘][$i][2] . "‘,‘" . $data->sheets[0][‘cells‘][$i][3] . "‘);";
    $mysqli->query($sql);
    //echo $sql.‘<br>‘;
}

/**
CREATE TABLE `cangku_huojia` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ck_id` INT(11) DEFAULT NULL,
`huojia_name` VARCHAR(35) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
 */

1.下載壓縮包
   地址https://sourceforge.net/projects/phpexcelreader/

2.解壓
   找到Excel檔案夾裡面有兩個檔案
   reader.php oleread.inc

3.修改reader.php檔案的第31行
   原：// require_once ‘Spreadsheet/Excel/Reader/OLERead.php‘;
   改成：require_once ‘oleread.php‘;

4.將    oleread.inc 檔案尾碼名修改成php
   oleread.php

5.註：下面的huojia.xls 檔案跟執行php同目錄

eg2:

<?php
header("Content-Type:text/html;charset=utf-8");
require_once ‘Excel/reader.php‘;

$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(‘utf-8‘);
$data->read(‘huojia.xls‘);

for ($i = 2; $i <= $data->sheets[0][‘numRows‘]; $i ++) {
    for ($j = 1; $j <= $data->sheets[0][‘numCols‘]; $j++) {
        echo $data->sheets[0][‘cells‘][$i][$j].",";
    }
    echo "<br />";
}
?>

php excel xls 匯入