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 . '<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());
}