PhpSpreadsheet实现导入&导出功能

码农阿强
2021-04-22 / 2 评论 / 533 阅读 / 正在检测是否收录...

前文

导入

excel的文本内容如下
1.png

代码演示

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;

2.png

第二种方式

不用$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;

3.png

这样就可以根据数据入数据库了


导出


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();
0

评论 (2)

取消
  1. 头像
    站元素主机
    Windows 10 · FireFox

    感谢分享 赞一个

    回复
    1. 头像
      黎明强森
      Windows 10 · Google Chrome
      @ 站元素主机

      (๑•̀ㅁ•́ฅ) 不客气,一起学习~

      回复