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.phpexceother的用法l.phpexcel");vendor("phpexcel.phpexcel.writer.excel5");vendor("phpexcel.phpexcel.writer.excel2007");//$excel = new phpexcel();$filename = './trans_rate.xlsx'平均分是什么意思;date_default_timezone_t('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; $ro农林大学暨阳学院w <= $highestrow; $row++) { $row_data = $sheet->rangetoarray('a' . $row . ':' . $highestcolumn . $row, null, true, fal);//获取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');$ur_id = !empty($id_arr) ? $id_arr : 0;$fields[] = ['数据表字段' => $ur_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($ur_id);} catch (exception $e) {die('加载文件发生错误："' . pathinfo($filename, pathinfo_baname) . '": ' . $e->getmessage()); } }}

php读取excel表数据：

<?phpinclude 'thinkphp/library/vendor/phpexcel/phpexcel/iofactory.php';$inputfilename = './trans_rate.xlsx';date_default_timezone_t('prc');// 读取excel文件try {  $inputfiletype = phpexcel_iofactory::identify($inputfilename);  $objreader = phpexcel_iofactory::createreader($inputfiletype);  $objphpexcel = $objreader->load($inputfilename);} catch(exception $e) {  die('加载文件发生错误："'.pathinfo($inputfilename,pathinfo_baname).'": '.$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, fal);//这里得到的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));  }el{    echo ($data[$i][0]);  }  echo '-----------';  echo $data[$i][1];}

<?phpinclude 'thinkphp/library/vendor/phpexcel/phpexcel/iofactory.php';$inputfilename = './test.xlsx';date_default_timezone_t('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_s编程是干什么的hared_date::exceltophp($sheet->getcell($addr)->getvalue()));//        $cell = phpexcel_shared_date::exceltophp($sheet->getcell($addr滔字草书)->getvalue());//        var_dump($cell);die;      }el{        $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_baname).'": '.$e->getmessage());}