ThinkPHP 读取excel导入数据库

入口文件中：

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

 

PHP：

 

 

namespace Home\Controller;
class ExcelController extends CommonController
{ 
public function Import() {
// vendor('PHPExcel.PHPExcel.IOFactory'); 
vendor("PHPExcel.PHPExcel.PHPExcel"); 
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
//$excel = new PHPExcel(); 
$fileName = './trans_rate.xlsx'; 
date_default_timezone_set('PRC');
// 读取excel文件 
try { 
$objPHPExcel = \PHPExcel_IOFactory::load($fileName); 
$inputFileType = \PHPExcel_IOFactory::identify($fileName); 
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
// $objPHPExcel = $objReader->load($fileName); 
// 确定要读取的sheet $sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();
// 获取一行的数据
// $phone_str = ''; 
for ($row = 3; $row <= $highestRow; $row++) {
 $row_data = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//获取excel表中一行的数组数据 
//dump($row_data); 
$row_data = $row_data[0]; 
$time = date('Y-m-d H:i:s', strtotime(trim($row_data[0]))); 
$start_province = trim($row_data[1]); 
$start_city = trim($row_data[2]); 
...
// $phone_str .= '"' . $phone . '",'; 
$where['phone'] = $phone; 
$id_arr = M(数据表名)->where($where)->getField('id'); 
$user_id = !empty($id_arr) ? $id_arr : 0; 
$fields[] = [ 
'数据表字段' => $user_id,//用户id
                    ...  
                     ]; 
}
// dump($fields);
$rate_add = M(数据表名)->addAll($fields); 
dump($rate_add); 
echo M()->getLastSql(); 
if (!(0 < $rate_add)) { 
CommonController::logProfile('添加excel数据，SQL：' . M()->getLastSql()); $this->endBack(0); }
// echo $phone_str . '<br />';
// dump($user_id); 
} catch (Exception $e) { 
die('加载文件发生错误："' . pathinfo($fileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } }}

 

 

php读取excel表数据：

 

 

 

 

<?php
include 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';

$inputFileName = './trans_rate.xlsx';
date_default_timezone_set('PRC');
// 读取excel文件
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('加载文件发生错误："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

// 确定要读取的sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

// 获取一行的数据
for ($row = 1; $row <= $highestRow; $row++){
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//这里得到的rowData都是一行的数据，得到数据后自行处理
var_dump($rowData);
echo "<br>";
}

 

//$data为从excel中获取到的数组
for ($i =0; $i<count($data);$i++){
    echo '<br>';
    $gettime= explode('-',$data[$i][0]);
    if (checkdate($month=$gettime[0],$day=$gettime[1],$year=$gettime[2])){
        echo gmdate('Y-m-d',gmmktime(0,0,0,$month,$day,$year));
    }else{
        echo ($data[$i][0]);
    }
    echo '-----------';
    echo $data[$i][1];
}

 

 

 

<?php
include 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';

$inputFileName = './test.xlsx';
date_default_timezone_set('Asia/Shanghai');
// 读取excel文件
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);

    // 确定要读取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();//最大行
    $highestColumn = $sheet->getHighestColumn();//最大列

    $data = array();
    for($rowIndex=2;$rowIndex<=$highestRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始
        for($colIndex='A';$colIndex<=$highestColumn;$colIndex++){
            $addr = $colIndex.$rowIndex;
            if($colIndex==="A"){ //指定H列为时间所在列
                $cell = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue()));
//                $cell = PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue());
//                var_dump($cell);die;
            }else{
                $cell = $sheet->getCell($addr)->getValue();
            }
//            if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
//                $cell = $cell->__toString();
//            }
            $data[$rowIndex][$colIndex] = $cell;
        }
    }
//    return $data;
    var_dump($data);
} catch(Exception $e) {
    die('加载文件发生错误："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}