知識付費的導入導出功能使用了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完成導入導出功能完成
