前文
导入
excel的文本内容如下
代码演示
composer安装PhpSpreadsheet (这是新的Excel类库)，不用旧的PHPExcel的类库，旧的已被官网弃用不维护了！！
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form action="./index5.php" method="post" enctype="multipart/form-data">
<input type="file" name="fileUpload" />
<input type="submit" value="上传文件" />
</form>
</body>
</html>后端代码
require_once 'vendor/autoload.php';
$filePath = $_FILES['fileUpload']['tmp_name'];
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($filePath); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
$lines = $highestRow - 1;
if ($lines <= 0) {
exit('Excel表格中没有数据');
}
$import_data = [];
for ($i = 2; $i <= $highestRow; $i++) {
$import_data[$i]['id'] = $spreadsheet->getActiveSheet()->getCell("A" . $i)->getValue();
$import_data[$i]['nick_name'] = $spreadsheet->getActiveSheet()->getCell("B" . $i)->getValue();
$import_data[$i]['number'] = $spreadsheet->getActiveSheet()->getCell("C" . $i)->getValue();
$import_data[$i]['vip_number'] = $spreadsheet->getActiveSheet()->getCell("D" . $i)->getValue();
$import_data[$i]['card'] = $spreadsheet->getActiveSheet()->getCell("E" . $i)->getValue();
}
$import_data = array_values($import_data);//重置索引
echo "<pre>";
print_r($import_data);exit;
exit;
第二种方式
不用$spreadsheet读取的文件的变量句柄，
用 $worksheet获取所有的表格行数，先获取某一个sheet。
require_once 'vendor/autoload.php';
$filePath = $_FILES['fileUpload']['tmp_name'];
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($filePath); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
$lines = $highestRow - 1;
if ($lines <= 0) {
exit('Excel表格中没有数据');
}
for ($row = 2; $row <= $highestRow; ++$row) {
$temp = array(
'id' => $worksheet->getCellByColumnAndRow('1', $row)->getValue(),
'nick_name' => $worksheet->getCellByColumnAndRow('2', $row)->getValue(),
'number' => $worksheet->getCellByColumnAndRow('3', $row)->getValue(),
'vip_number' => $worksheet->getCellByColumnAndRow('4', $row)->getValue(),
'card' => $worksheet->getCellByColumnAndRow('5', $row)->getValue(),
'other' => $worksheet->getCellByColumnAndRow('6', $row)->getValue(),
'addr' => $worksheet->getCellByColumnAndRow('7', $row)->getValue(),
);
$list[] = $temp;
}
echo "<pre>";
print_r($list);exit;
这样就可以根据数据入数据库了
导出
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;
function exportExcel(){
//定义一个excel的header表头
$header = ['A1' => '序号', 'B1' => '姓名', 'C1' => '证件号', 'D1' => '张数', 'E1' => '证件类型', 'F1' => '省', 'G1' => '市', 'H1' => '区/镇', 'I1' => '类别', 'J1' => '设计人', 'K1' => '完成时间', 'L1' => '备注'];
$list = [
['id'=>1,'name'=>'黎明强','card'=>'13800','number'=>10],
['id'=>2,'name'=>'黎明','card'=>'13811','number'=>100],
['id'=>3,'name'=>'黎明强森','card'=>'13822','number'=>200],
['id'=>4,'name'=>'黎明强2','card'=>'13833','number'=>300],
['id'=>5,'name'=>'黎明强3','card'=>'13844','number'=>400],
];
$spreadsheet = new Spreadsheet();
$sheetMain = $spreadsheet -> getsheet(0);//主sheet
$sheetMain -> setTitle('人员信息');//设置sheet的名称
$sheetMain -> getPageSetup() -> setHorizontalCentered(true);
$sheetMain -> getPageSetup() -> setVerticalCentered(false);
foreach ($header as $key => $value) {
$sheetMain -> setCellValue($key, $value);
}
$row = 2; //从第二行开始
foreach ($list as $item) {
$column = 1;
foreach ($item as $value) {
$sheetMain->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
//定义文件名称，需要带有定义的后缀名
$filename = 'test.xlsx';
ob_end_clean(); //清除缓冲区,避免乱码
//将输出重定向到客户端的web浏览器
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
//如果浏览器为IE9
header('Cache-Control: max-age=1');
//如果通过SSL向IE提供服务
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');//HTTP/1.1
header('Pragma: public');//HTTP/1.0
$writer = IOFactory ::createWriter($spreadsheet, 'Xlsx'); //指定格式
$writer -> save('php://output');
exit;
}运行
exportExcel();
感谢分享 赞一个
(๑•̀ㅁ•́ฅ) 不客气，一起学习~