phpexcel数据导入

phpexcel数据导入

$keyNames = [
    'keyname1',
    'keyname2',
    'keyname3',
    'keyname4',
];
$filename = "";//xlsx文件地址
//获取数据
$data = getFileData($filename,$keyNames);
//存入数据库
//.......

//方法
function getFileData($fileName,$keyNames){

    $extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION) );

    if ($extension =='xlsx') {
        $objReader = new PHPExcel_Reader_Excel2007();
        $objPhpExcel = $objReader ->load($fileName);
    } else if ($extension =='xls') {
        $objReader = new PHPExcel_Reader_Excel5();
        $objPhpExcel = $objReader ->load($fileName);
    } else if ($extension=='csv') {
        $PHPReader = new PHPExcel_Reader_CSV();

        //默认输入字符集
        $PHPReader->setInputEncoding('GBK');

        //默认的分隔符
        $PHPReader->setDelimiter(',');

        //载入文件
        $objPhpExcel = $PHPReader->load($fileName);
    }

    $sheet = $objPhpExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();//获得总行数
    $highestColumn = $sheet->getHighestColumn();//获得总列数
    $to = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI'];
    $k = 0;
    $data = [];
    $hightNum = array_search($highestColumn,$to);

    for($j=2;$j<=$highestRow;$j++)
    {
        for ($i=0;$i<=$hightNum;$i++){
            $data[$j][$keyNames[$i]] = $objPhpExcel->getActiveSheet()->getCell($to[$i].$j)->getValue();
        }
    }
    return $data;
}

```