ThinkPHP 框架实现的读取excel导入数据库操作示例_PHP教程

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

									}