php 读取excel 框架,ThinkPHP 框架实现的读取excel导入数据库操作示例

本文实例讲述了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 . '
';

// dump($user_id);

} catch (Exception $e) {

die('加载文件发生错误："' . pathinfo($fileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } }}

php读取excel表数据：

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 "
";

}

//$data为从excel中获取到的数组

for ($i =0; $i

echo '
';

$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];

}

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());

}

更多关于thinkPHP相关内容感兴趣的读者可查看本站专题：《ThinkPHP入门教程》、《thinkPHP模板操作技巧总结》、《ThinkPHP常用方法总结》、《codeigniter入门教程》、《CI(CodeIgniter)框架进阶教程》、《Zend FrameWork框架入门教程》及《PHP模板技术总结》。

希望本文所述对大家基于ThinkPHP框架的PHP程序设计有所帮助。