Mr.Li's Blog

1.composer 安装

composer require phpoffice/phpspreadsheet 1.8.2

　　因为要兼容php5.6，所以是用的1.8.2版本

2.在公共函数文件中引入头文件

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

3.定义导出封装函数

if (!function_exists('downLoadExcel')){
    /**
     * 导出excel
     * @param $name excel名称
     * @param $titles 标题 [['name'=>'姓名'],['gender'=>'性别']]
     * @param array $data
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    function downLoadExcel($name, $titles, $data=[])
    {
        $count = count($titles);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER_CONTINUOUS,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ];
        $sheet = $spreadsheet->getActiveSheet();
        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头
            $sheet->getStyle(strtoupper(chr($i)))->applyFromArray($styleArray);
            $sheet->getCell(strtoupper(chr($i)).'1')->getStyle()->getFont()->setBold(true);
            $index = $i - 65;
            $sheet->setCellValue(strtoupper(chr($i)) . '1',  $titles[$index][key($titles[$index])] );
        }
        
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2),$item[key($titles[$i - 65])]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setAutoSize(true);
            }
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        $writer->save('php://output');
        //删除清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}

4.定义导入封装函数

if (!function_exists('importExcel')){
    
    /**
     * 导入excel
     * @param $filePath 文件路径
     * @param $cols 要导入的列 从1开始 $cols = [['1' => 'id'],['2' =>'name']];
     * @param int $sheetIndex sheet索引,默认从0开始
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    function importExcel($filePath, $cols, $sheetIndex = 0){

        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($filePath); //载入excel表格

        $sheetCount =  $spreadsheet->getSheetCount();
        if ($sheetIndex > $sheetCount - 1){
            exit('sheet不存在');
        }
        $worksheet = $spreadsheet->getSheet($sheetIndex);
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $lines = $highestRow - 2;
        if ($lines <= 0) {
            exit('Excel表格中没有数据');
        }
        $data = [];
        for ($row = 2; $row <= $highestRow; $row++) {
            for ($i = 0; $i < count($cols); $i ++){
                $index = key($cols[$i]);
                $key = array_values($cols[$i])[0];
                $data[$row][$key] = $worksheet->getCellByColumnAndRow($index, $row)->getValue(); //学号
            }
        }
        return array_values($data);
    }

}

5.导出测试使用

$titles = [['name'=>'姓名'],['gender'=>'性别']];
$data = [
    ['name'=>'小黑','gender'=>'男']
];
downLoadExcel('测试',$titles,$data);

 6.导入测试使用

    excel如下

   $path = ROOT_PATH.DS.'public'.DS.'excel'.DS.'student.xlsx';
        if (is_file($path)){
            $cols = [['2' => 'name'],['3' =>'gender'],['4' => 'class']];
            $data = importExcel($path,$cols);
            halt($data);
        }

col是第几列对应数组的key，这样就只会获取excel的姓名,性别,班级的数据