使用带有 Spreadsheet_Excel_Reader 库的 codeigniter 读取 php 中的 xls 文件

Question

我正在尝试读取 Excel 文件（即 xls）并将数据导入到 mysql 数据库，并使用 codeigniter 2.0.1 框架。

这是生成查询的代码，

    public function read_file($table = 'organization', $filename = 'test.xls') {

    $pathToFile = './uploads/' . $filename;
    $this->load->library('Spreadsheet_Excel_Reader');
    $data = new Spreadsheet_Excel_Reader($pathToFile);
    $sql = "INSERT INTO $table (";
    for($index = 1;$index <= $data->sheets[0]['numCols']; $index++){
        $sql.= strtolower($data->sheets[0]['cells'][1][$index]) . ", ";
    }

    $sql = rtrim($sql, ", ")." ) VALUES ( ";
    for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
            $sql .= "\"" . $data->sheets[0]['cells'][$i][$j] . "\", ";
        }
        echo  rtrim($sql, ", ")." ) <br>";
    }

}

循环中生成的值是重复的，我找不到循环的问题...

这是运行上面的函数后的结果：

INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" ) 
INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" ) 
INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", " [email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" ) 

抱歉，我无法发布图像，因为我是新手。

提前感谢..干杯

Answer 1

我无法测试它，但是......：

    public function read_file($table = 'organization', $filename = 'test.xls') {

    $pathToFile = './uploads/' . $filename;
    $this->load->library('Spreadsheet_Excel_Reader');
    $data = new Spreadsheet_Excel_Reader($pathToFile);
    $sql = "INSERT INTO $table (";
    for($index = 1;$index <= $data->sheets[0]['numCols']; $index++){
        $sql.= strtolower($data->sheets[0]['cells'][1][$index]) . ", ";
    }

    $sql = rtrim($sql, ", ")." ) VALUES ( ";
    for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
        $valuesSQL = '';
        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
            $valuesSql .= "\"" . $data->sheets[0]['cells'][$i][$j] . "\", ";
        }
        echo $sql . rtrim($valuesSql, ", ")." ) <br>";
    }
}