PhpSpreadsheet的简单使用

ゝ一纸荒年。 2022-10-13 05:18 72阅读 0赞

composer require phpoffice/phpspreadsheet

/**
     * excel文件导出
     */
    function export()
    {
        
        require_once __DIR__ . '/vendor/autoload.php';
     
        $data = [
            ['title1' => '111', 'title2' => '222'],
            ['title1' => '111', 'title2' => '222'],
            ['title1' => '111', 'title2' => '222']
        ];
        $title = ['第一行标题', '第二行标题'];
     
        // Create new Spreadsheet object
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
     
        // 方法一，使用 setCellValueByColumnAndRow
        //表头
        //设置单元格内容
        foreach ($title as $key => $value) {
        
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }
        $row = 2; // 从第二行开始
        foreach ($data as $item) {
        
            $column = 1;
            foreach ($item as $value) {
        
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }
     
        // 方法二，使用 setCellValue
        //表头
        //设置单元格内容
        $titCol = 'A';
        foreach ($title as $key => $value) {
        
            // 单元格内容写入
            $sheet->setCellValue($titCol . '1', $value);
            $titCol++;
        }
        $row = 2; // 从第二行开始
        foreach ($data as $item) {
        
            $dataCol = 'A';
            foreach ($item as $value) {
        
                // 单元格内容写入
                $sheet->setCellValue($dataCol . $row, $value);
                $dataCol++;
            }
            $row++;
        }
     
        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
     
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
     
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

/**
     * excel文件保存到本地
     */
    function save()
    {
        
        require_once __DIR__ . '/vendor/autoload.php';
     
        $data = [
            ['title1' => '111', 'title2' => '222'],
            ['title1' => '111', 'title2' => '222'],
            ['title1' => '111', 'title2' => '222']
        ];
        $title = ['第一行标题', '第二行标题'];
     
        // Create new Spreadsheet object
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
     
        //表头
        //设置单元格内容
        $titCol = 'A';
        foreach ($title as $key => $value) {
        
            // 单元格内容写入
            $sheet->setCellValue($titCol . '1', $value);
            $titCol++;
        }
        $row = 2; // 从第二行开始
        foreach ($data as $item) {
        
            $dataCol = 'A';
            foreach ($item as $value) {
        
                // 单元格内容写入
                $sheet->setCellValue($dataCol . $row, $value);
                $dataCol++;
            }
            $row++;
        }
     
        // Save
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('01simple.xlsx');
    }

/**
     * 读取excel文件内容
     */
    function read()
    {
        
        require_once __DIR__ . '/vendor/autoload.php';
        $inputFileName = dirname(__FILE__) . '/01simple.xlsx';
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
        // 方法二
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        return $sheetData;
    }

转载自  
https://www.cnblogs.com/woods1815/p/11372007.html