知识付费的导入导出功能使用了PhpSpreadsheet，所以我们首先要了解它。
一、PhpSpreadsheet 介绍
1、PhpSpreadsheet 是什么
PhpSpreadsheet是一个用纯PHP编写的库，提供了一组类，使您可以读取和写入不同的电子表格文件格式
PhpSpreadsheet提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到
· 使用 PhpSpreadsheet 开发的PHP要求 7.1或更高版本
· PhpSpreadsheet 支持链式操作
2、PhpSpreadsheet 支持的文件格式
3、PhpSpreadsheet 官方网址
· https://phpspreadsheet.readthedocs.io
4、PhpSpreadsheet 安装
· composer require phpoffice/phpspreadsheet
二、使用PhpSpreadsheet 完成导出功能
项目中extend/service/PhpSpreadsheetService文件outdata方法为导出方法
/**
* 通用导出方法。传入参数即可
* @param unknown $filename 导出的excel文件名称，不包括后缀
* @param unknown $rows 要导出的数据，数组
* @param unknown $head 要导出数据的表头，数组
* @param unknown $keys 要导出数据的键值对对应
*/
public static function outdata($filename = '', $rows = [], $head = [])
{
$count = count($head); //计算表头数量
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//设置样式,设置剧中，加边框，设置行高
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '6184542'],
],
],
];
$rows_count = count($rows);
$sheet->getDefaultRowDimension()->setRowHeight(18);//设置默认行高。
$sheet->getStyle('A1:' . strtoupper(chr($count + 65 - 1)) . strval($rows_count + 1))->applyFromArray($styleArray);
$sheet->getStyle('A4:' . strtoupper(chr($count + 65 - 1)) . '1')->getFont()->setBold(true)->setName('Arial')->setSize(10)->applyFromArray($styleArray);
//设置样式结束
//写入表头信息
for ($i = 65; $i < $count + 65; $i++) {
//数字转字母从65开始，循环设置表头：
$sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
}
//写入数据信息
foreach ($rows as $key => $item) {
//循环设置单元格：
//$key+2,因为第一行是表头，所以写到表格时 从第二行开始写
for ($i = 65; $i < $count + 65; $i++) {
//数字转字母从65开始：
$sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$i - 65]);
$spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(30); //固定列宽
// 支持换行
// $sheet->getStyle(strtoupper(chr($i)))->getAlignment()->setWrapText(true);
}
}
//header('Content-Type: application/vnd.ms-excel');xls
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//xlsx
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
项目中使用，如会员记录的导出；方法传入表格名称、要导出的数据（数组）、要导出数据的表头（数组）
public static function getPurchaseRecordList($where){
$model = new self();
if (isset($where['excel']) && $where['excel'] == 1) {$list = $model->select();}
if (isset($where['excel']) && $where['excel'] == 1) {self::SaveExcel($list);}
} /**
* 保存并下载excel
* $list array
* return
*/
public static function SaveExcel($list)
{
$export = [];
foreach ($list as $index => $item) {
$export[] = [
$item['id'],
$item['uid'],
$item['title'],
$item['source'],
$item['validity'],
$item['price'],
$item['code']
];
}
$filename = '会员记录导出' . time() . '.xlsx';
$head = ['编号', '昵称/UID', '类别', '来源', '有效期/天', '优惠价', '卡号'];
PhpSpreadsheetService::outdata($filename, $export, $head);
}三、使用PhpSpreadsheet 完成导入功能
引入use \PhpOffice\PhpSpreadsheet\IOFactory;
方法传入两个参数：$filename 文件名称 $startLine 从哪一行开始读取
$widt(数组) 数据读取后的数组格式
/**文件导入
* @param string $filename
* @param int $startLine
* @param array $width
* @return array
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
*/
public static function GetExcelData($filename = '1.xlsx', $startLine = 4)
{
$width = [
'question_type' => 'A',
'pid' => 'B',
'stem' => 'C',
'image' => 'D',
'is_img' => 'E',
'a' => 'F',
'b' => 'G',
'c' => 'H',
'd' => 'I',
'e' => 'J',
'f' => 'K',
'answer' => 'L',
'difficulty' => 'M',
'analysis' => 'N',
'sort' => 'O'
];
$filename = ROOT_PATH . 'public' . $filename;
$extension = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
switch ($extension) {
case 'xlsx':
$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filename);
break;
case 'xls':
$reader = IOFactory::createReader('Xls');
$spreadsheet = $reader->load($filename);
break;
case 'csv':
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$reader->setInputEncoding('GBK');
$reader->setDelimiter(',');
$reader->setEnclosure('');
$reader->setSheetIndex(0);
$spreadsheet = $reader->load($filename);
break;
}
$highestRow = $spreadsheet->getSheet(0)->getHighestRow(); // 取得总行数
$getvalue = $spreadsheet->getActiveSheet();
$data = [];
for ($j = $startLine; $j <= (int)$highestRow; $j++) {
$value = [];
foreach ($width as $key => $val) {
if ($v = $getvalue->getCell($val . $j)->getValue()) $value[$key] = $v;
else $value[$key] = '';
}
if ($value) $data[] = $value;
}
return $data;
}根据导出数组处理导入数据
/**批量导入试题
* @param array $data
*/
public static function importQuestions($data = [])
{
foreach ($data as $key => $value) {
$dat = [];
switch ($value['question_type']) {
case 1:
if ($value['a']) $dat['A'] = $value['a'];
if ($value['b']) $dat['B'] = $value['b'];
if ($value['c']) $dat['C'] = $value['c'];
if ($value['d']) $dat['D'] = $value['d'];
case 2:
if ($value['a']) $dat['A'] = $value['a'];
if ($value['b']) $dat['B'] = $value['b'];
if ($value['c']) $dat['C'] = $value['c'];
if ($value['d']) $dat['D'] = $value['d'];
if ($value['e']) $dat['E'] = $value['e'];
if ($value['f']) $dat['F'] = $value['f'];
break;
case 3:
if ($value['a']) $dat['A'] = $value['a'];
if ($value['b']) $dat['B'] = $value['b'];
break;
}
$array['question_type'] = $value['question_type'];
$array['pid'] = $value['pid'];
$array['stem'] = $value['stem'];
$array['image'] = $value['image'];
$array['is_img'] = $value['is_img'];
$array['answer'] = trim($value['answer'], " ");
$array['difficulty'] = $value['difficulty'];
$array['analysis'] = $value['analysis'];
$array['sort'] = (int)$value['sort'];
$array['option'] = json_encode($dat);
$array['add_time'] = time();
if (self::be(['stem' => $value['stem'], 'question_type' => $value['question_type'], 'pid' => $value['pid'], 'is_del' => 0, 'mer_id' => 0])) continue;
self::set($array);
}
return true;
}
如此使用PhpSpreadsheet完成导入导出功能完成
