欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-21 12:46:24
本文实例讲述了thinkphp 框架实现的读取excel导入数据库操作。分享给大家供大家参考,具体如下:入口文件中:require_once vendor_path.'phpexcel/phpexce...

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