ThinkPHP框架导出Excel详细教程，phpOffice——PhpSpreadsheet导入导出
导出：
案例：
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\IOFactory; /** * [数据导出] * @return [type] [description] */ function export() { $list = [ [ 'id' => 1, 'name' => '字段1' ], [ 'id' => 2, 'name' => '字段1' ] ]; $title = ['ID', '名称']; // Create new Spreadsheet object $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //表头 //设置单元格内容 $titCol = 'A'; foreach ($title as $key => $value) { // 单元格内容写入 $sheet->setCellValue($titCol . '1', $value); $spreadsheet->getActiveSheet()->getColumnDimension($titCol)->setAutoSize(true); $titCol++; } $row = 2; // 从第二行开始 foreach ($list as $item) { $dataCol = 'A'; // 单元格内容写入 $sheet->setCellValue($dataCol . $row, $item['id']);$dataCol++; $sheet->setCellValue($dataCol . $row, $item['name']);$dataCol++; $row++; } $name = 'export-'.date('YmdHi'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $name . '.xlsx"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); //删除清空： $spreadsheet->disconnectWorksheets(); }
亲测可用--自己的示例：
public function putinfo(){
$a = Db::name('testpro_dati')->select();
for ($i = 0; $i < count( $a); $i++) {
$user= Db::name('users_user')->where('id','=',$a[$i]['uid'])->find();
$list[$i]['username'] = $user['name']; //用户名称
$classs= Db::name('classs')->where('id','=',$a[$i]['cid'])->find();
$list[$i]['classname'] = $classs['classsname']; //用户名称
$list[$i]['tname'] = $a[$i]['name']; //题目名称
if( $a[$i]['stateji'] == 0 ){
$list[$i]['stateji'] ='未批改计算题';
}else{
$list[$i]['stateji'] ='已批改计算题';
}
$list[$i]['zongfen'] = $a[$i]['zongfen']; //总分
$list[$i]['times'] =date('Y-m-d H-i-s', $a[$i]['c_time']); //总分
}
// var_dump($list);die;
// $list = [
// [
// 'id' => 1,
// 'name' => '字段1'
// ],
// [
// 'id' => 2,
// 'name' => '字段1'
// ]
// ];
$title = ['姓名', '班级名称','题目名称','计算题是否批改','总分','时间'];
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//表头
//设置单元格内容
$titCol = 'A';
foreach ($title as $key => $value) {
// 单元格内容写入
$sheet->setCellValue($titCol . '1', $value);
$spreadsheet->getActiveSheet()->getColumnDimension($titCol)->setAutoSize(true);
$titCol++;
}
$row = 2; // 从第二行开始
foreach ($list as $item) {
$dataCol = 'A';
// 单元格内容写入
$sheet->setCellValue($dataCol . $row, $item['username']);$dataCol++;
$sheet->setCellValue($dataCol . $row, $item['classname']);$dataCol++;
$sheet->setCellValue($dataCol . $row, $item['tname']);$dataCol++;
$sheet->setCellValue($dataCol . $row, $item['stateji']);$dataCol++;
$sheet->setCellValue($dataCol . $row, $item['zongfen']);$dataCol++;
$sheet->setCellValue($dataCol . $row, $item['times']);$dataCol++;
$row++;
}
$name = 'export-'.date('YmdHi');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . time() . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空：
$spreadsheet->disconnectWorksheets();
}
导入：
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
/** * 导入 */ protected function import() { $this->model="调用数据表模型"; $file = $this->request->request('file'); if (!$file) { $this->error(__('Parameter %s can not be empty', 'file')); } $filePath = ROOT_PATH . DS . 'public' . DS . $file; if (!is_file($filePath)) { $this->error(__('No results were found')); } //实例化reader $ext = pathinfo($filePath, PATHINFO_EXTENSION); if (!in_array($ext, ['csv', 'xls', 'xlsx'])) { $this->error(__('Unknown data format')); } if ($ext === 'csv') { $file = fopen($filePath, 'r'); $filePath = tempnam(sys_get_temp_dir(), 'import_csv'); $fp = fopen($filePath, "w"); $n = 0; while ($line = fgets($file)) { $line = rtrim($line, "\n\r\0"); $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']); if ($encoding != 'utf-8') { $line = mb_convert_encoding($line, 'utf-8', $encoding); } if ($n == 0 || preg_match('/^".*"$/', $line)) { fwrite($fp, $line . "\n"); } else { fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n"); } $n++; } fclose($file) || fclose($fp); $reader = new Csv(); } elseif ($ext === 'xls') { $reader = new Xls(); } else { $reader = new Xlsx(); } //判断是根据注释导入还是字段导入、导入文件首行类型,默认是注释,如果需要使用字段名称请使用name $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment'; //获取数据表的字段和注释信息组成新的数组 开始 $table = $this->model->getQuery()->getTable(); $database = \think\Config::get('database.database'); $fieldArr = []; $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]); foreach ($list as $k => $v) { if ($importHeadType == 'comment') { $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME']; } else { $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME']; } } //获取数据表的字段和注释信息组成新的数组 结束 //加载文件 $insert = []; try { if (!$PHPExcel = $reader->load($filePath)) { $this->error(__('Unknown data format')); } $currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表 $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $maxColumnNumber = Coordinate::columnIndexFromString($allColumn); $fields = []; //读取表头信息 for ($currentRow = 1; $currentRow <= 1; $currentRow++) { for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { //逐行逐列读取 $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $fields[] = $val; } } //读取表头信息结束 //读取表内容 for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { //逐行逐列读取 $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $values[] = is_null($val) ? '' : $val; } $row = []; //通过合并两个数组来创建一个新数组，其中的一个数组元素为键名，另一个数组元素为键值： $temp = array_combine($fields, $values); //判断数据表是否有此字段并筛选 foreach ($temp as $k => $v) { if (isset($fieldArr[$k]) && $k !== '') { $row[$fieldArr[$k]] = $v; } } //得出需要添加的数据 if ($row) { $insert[] = $row; } } } catch (Exception $exception) { $this->error($exception->getMessage()); } if (!$insert) { $this->error(__('No rows were updated')); } try { //导入数据 $this->model->saveAll($insert); } catch (PDOException $exception) { $msg = $exception->getMessage(); if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) { $msg = "导入失败，包含【{$matches[1]}】的记录已存在"; }; $this->error($msg); } catch (Exception $e) { $this->error($e->getMessage()); } $this->success(); }
