php解析Excel表格并且导入MySQL数据库

最近根据客户需求，需要增加一个导入Excel表格的功能，Excel中存放的是知识库中医知识的分类体系目录。是在thinkphp框架下编写的代码,用的是phpexcel第三方包。测试环境用的是xampp集成了mysql和phpmyadmin。

具体如下形式：

<?php

        require_once './PHPExcel/PHPExcel.php';

        require_once './PHPExcel/PHPExcel/IOFactory.php';

        require_once './PHPExcel/PHPExcel/Reader/Excel5.php';

        $objReader = PHPExcel_IOFactory::createReader('excel2007'); //use Excel5 for 2003 format 

        $excelpath='D:/wamp64/www/Book1.xlsx';
        //$excelpath = $uploadAddr;

        //$objPHPExcel = $objReader->load($excelpath); 
        $objPHPExcel = PHPExcel_IOFactory::load($excelpath);
        $sheet = $objPHPExcel->getSheet(0); 

        $highestRow = $sheet->getHighestRow();       //取得总行数 

        $highestColumn = $sheet->getHighestColumn(); //取得总列数
        error_reporting( E_ALL&~E_NOTICE );
        $str = "";
        for($j=2;$j<=$highestRow;$j++)
        { 
            $id = "";
            $name = "";
            for($k='A';$k<=$highestColumn;$k++)
            { 
                //$str .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
                $str = $sheet->getCell($k.$j)->getValue();
                //$str = $sheet->getCell($k.$j+1)->getValue(); echo $str."<br>";
                //echo $sheet->getCell($k.$j)->getValue()."  "; 
                 //explode:函数把字符串分割为数组。
                $strs = explode("\\",$str); 
                if ($k=='A') $id = $str;
                if ($k=='B') $name = $str;
            } 
            echo $id; echo $name."<br>";
            $link = mysqli_connect("localhost", "root", "", "excel");
                //$db_selected = mysql_select_db("excel", $link);
                if (mysqli_connect_errno()) {
                    printf("Connect failed: %s\n", mysqli_connect_error());
                    exit();
                } 
                mysqli_query($link,"set names utf8"); 
            try {
                    mysqli_query($link, "INSERT INTO test(id,name) VALUES('".$id."','".$name."')"); 
                    // echo "$strs[0]"." | "."$strs[1]"."<br>"; 
                    // echo "INSERT INTO test(id,classname) VALUES('".$strs[0]."','".$strs[1]."')";
                    // echo "<br>";
                } catch (Exception $e) {
                  print $e->getMessage();  
                }   
        }                                
   
?>
 </body>
</html>

php解析Excel表格并且导入MySQL数据库

推荐阅读