thinkphp5.1 框架导入/导出excel文件操作示例

本文实例讲述了thinkphp5.1 框架导入/导出excel文件操作。分享给大家供大家参考，具体如下：

thinkphp5.1 导入excel文件

public function importexcel()  {    try {      //获取表格的大小，限制上传表格的大小      if ($_files['file']['size'] > 10 * 1024 * 1024) { //文件过大        log_debug($log_title . 'end === msg：' . '文件过大');        parent::endback(['state' => 0, 'msg' => '文件过大']);      }       //限制上传表格类型      $ext = substr(strrchr($_files['file']["name"], '.'), 1);      if ($ext != 'xls' && $ext != 'xlsx') {        log_debug($log_title . 'end === msg：' . '文件格式不正确');        parent::endback(['state' => 0, 'msg' => '上传文件必须为excel表格']);      }        //读取表格      $filename = $_files['file']['tmp_name'];      $reader = iofactory::createreader('xlsx'); //xls,xlsx都可读取      $canread = $reader->canread($filename);      if (!$canread) {        log_debug($log_title . 'end，文件格式不正确，sql：' . db::name('')->getlastsql());        parent::endback(['state' => 0, 'msg' => '文件格式不正确', 're_login' => fal]);      }       $spreadsheet = $reader->load($filename); //载入excel表格      $worksheet = $spreadsheet->getactivesheet(); //选中sheet表      $highestrow = $worksheet->gethighestrow(); // 总行数//      $highestcolumn = $worksheet->gethighestcolumn(); /立冬古诗/ 总列数       if (!(0 < $highestrow)) {        log_debug($log_title . 'end，文件内容空，sql：' . db::name('')->getlastsql());        parent::endback(['state' => 0, 'msg' => '文件没有数据', 're_login' => fal]);      }        //循环读取--有效判断      $sst_word_arr = []; //存放敏感词的数组      for ($row = 1; $row <= $highestrow; $row++) {        //取列数a列的数据        $tmp_word = $spreadsheet->getactivesheet()->getcell('a' . $row)->getvalue();        if ('' != trim($tmp_word) && null != $tmp_word) {          $sst_word_arr[] = $tmp_word;          break; //发现有效数据,直接退出,接下来插入数据        }      }//      $sst_word_arr = array_unique($sst_word_arr);       if (empty($sst_word_arr)) {        log_debug($log_title . 'end，文件无有效数据，sql：' . db::name('')->getlastsql());        parent::en手抄报春节2021dback(['state' => 0, 'ms大象耳朵g' => '文件无有效数据', 're_login' => fal]);      }       //判断和数据库操作      for ($row = 2; $row <= $highestrow; $row++) {        //取列数a列的数据        $tmp_old_car_num = $spreadsheet->getactivesheet()->getcell('a' . $row)->getvalue();         $car_num = trim($tmp_old_car_num);        if ('' != $car_num && null != $car_num) {          //数据库操作          }        }      }       $ret_arr = [        'state' => 1,         //返回数据      ];       log_debug($log_title . 'end，success');      parent::endback($ret_arr);  } catch (\exception $e) {      //    }  }

excel文件格式为：

thinkphp5.1 导出excel文件

namespa app\test;u phpoffice\phpspreadsheet\iofactory;u phpoffice\phpspreadsheet\spreadsheet;u phpoffice\phpspreadsheet\writer\xlsx;class test {  public function carnumxport()  {    $log_title = '测试 => 车牌列表导出[' . __method__ . '] ';    try {       $file_name = '《车牌列表》from y8zh - ' . $ur_info社会主义矛盾['uid'] . '.xlsx';      $file_relative_path = parent::$module_name . directory_parator . 'fcb_car_nums' . directory_parator;      $file_path = parent::$api_file_root_path . $file_relative_path;       // 已生成过则直接返回      if (file_exists($file_path . $file_name)) {        $ret_arr = [          'state' => 1,          'download_url' => parent::$api_file_get_url . $file_relative_path . $file_name,        ];        parent::endback($ret_arr);      }       if (!is_dir($file_path)) {        mkdir($file_path, 0777, true);      }       $spreadsheet = new spreadsheet();      $sheet = $spreadsheet->getactivesheet();       //获取所有车牌号      $car_nums = db::connect('db_config_yun')->name('vechicle')->column('distinct number');       $i = 1;        $sheet->tcellvalue('a' . $i, '车牌号')->getstyle('a' . $i)->getfont()->tbold(true);        $i++;       // 表内容      if (!empty($car_nums)) {        f大学学生会oreach ($car_nums as $k_c => $v_c) {          $sheet->tcellvalue('a' . $i, $v_c);          $i++;        }       }       $writer = new xlsx($spreadsheet);      $writer->save($file_path . $file_name);      $ret_arr = [        'state' => 1,        'download_url' => parent::$api_file_get_url . $file_relative_path . $file_name,      ];       log_debug($log_title . 'end === download_url：' . $ret_arr['download_url']);      parent::endback($ret_arr);  } catch (\exception $e) {      //    }  }}