知識(shí)付費(fèi)的導(dǎo)入導(dǎo)出功能使用了PhpSpreadsheet，所以我們首先要了解它。
一、PhpSpreadsheet 介紹
1、PhpSpreadsheet 是什么
PhpSpreadsheet是一個(gè)用純PHP編寫的庫(kù)，提供了一組類，使您可以讀取和寫入不同的電子表格文件格式
PhpSpreadsheet提供了豐富的API接口，可以設(shè)置諸多單元格以及文檔屬性，包括樣式、圖片、日期、函數(shù)等等諸多應(yīng)用，總之你想要什么樣的Excel表格，PhpSpreadsheet都能做到
· 使用 PhpSpreadsheet 開發(fā)的PHP要求 7.1或更高版本
· PhpSpreadsheet 支持鏈?zhǔn)讲僮?/span>
2、PhpSpreadsheet 支持的文件格式
3、PhpSpreadsheet 官方網(wǎng)址
· https://phpspreadsheet.readthedocs.io
4、PhpSpreadsheet 安裝
· composer require phpoffice/phpspreadsheet
二、使用PhpSpreadsheet 完成導(dǎo)出功能
項(xiàng)目中extend/service/PhpSpreadsheetService文件outdata方法為導(dǎo)出方法
/**
* 通用導(dǎo)出方法。傳入?yún)?shù)即可
* @param unknown $filename 導(dǎo)出的excel文件名稱，不包括后綴
* @param unknown $rows 要導(dǎo)出的數(shù)據(jù)，數(shù)組
* @param unknown $head 要導(dǎo)出數(shù)據(jù)的表頭，數(shù)組
* @param unknown $keys 要導(dǎo)出數(shù)據(jù)的鍵值對(duì)對(duì)應(yīng)
*/
public static function outdata($filename = '', $rows = [], $head = [])
{
$count = count($head); //計(jì)算表頭數(shù)量
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//設(shè)置樣式,設(shè)置劇中，加邊框，設(shè)置行高
$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);//設(shè)置默認(rèn)行高。
$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);
//設(shè)置樣式結(jié)束
//寫入表頭信息
for ($i = 65; $i < $count + 65; $i++) {
//數(shù)字轉(zhuǎn)字母從65開始，循環(huán)設(shè)置表頭：
$sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
}
//寫入數(shù)據(jù)信息
foreach ($rows as $key => $item) {
//循環(huán)設(shè)置單元格：
//$key+2,因?yàn)榈谝恍惺潜眍^，所以寫到表格時(shí) 從第二行開始寫
for ($i = 65; $i < $count + 65; $i++) {
//數(shù)字轉(zhuǎn)字母從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;
}
項(xiàng)目中使用，如會(huì)員記錄的導(dǎo)出；方法傳入表格名稱、要導(dǎo)出的數(shù)據(jù)（數(shù)組）、要導(dǎo)出數(shù)據(jù)的表頭（數(shù)組）
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 = '會(huì)員記錄導(dǎo)出' . time() . '.xlsx';
$head = ['編號(hào)', '昵稱/UID', '類別', '來源', '有效期/天', '優(yōu)惠價(jià)', '卡號(hào)'];
PhpSpreadsheetService::outdata($filename, $export, $head);
}三、使用PhpSpreadsheet 完成導(dǎo)入功能
引入use \PhpOffice\PhpSpreadsheet\IOFactory;
方法傳入兩個(gè)參數(shù)：$filename 文件名稱 $startLine 從哪一行開始讀取
$widt(數(shù)組) 數(shù)據(jù)讀取后的數(shù)組格式
/**文件導(dǎo)入
* @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(); // 取得總行數(shù)
$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;
}根據(jù)導(dǎo)出數(shù)組處理導(dǎo)入數(shù)據(jù)
/**批量導(dǎo)入試題
* @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完成導(dǎo)入導(dǎo)出功能完成
