PhpSpreadsheet 读,写,模版生产excel文件

最新推荐文章于 2026-04-06 03:12:59 发布

原创最新推荐文章于 2026-04-06 03:12:59 发布 · 4.1k 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

PHP

本文详细介绍如何利用PhpSpreadsheet库进行Excel文件的读写操作。包括如何创建新的Excel文件，向其中写入数据，以及如何从现有Excel文件中读取数据。同时，还提供了示例代码展示如何设置单元格样式和导出数据。

之前发过一张安装PhpSpreadsheet，这月发一张安装后如何使用，话不多说直接上代码！

<?php

namespace Home\Controller;

use Think\Controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;



class IndexController extends Controller {

// 写入

public function write(){

    vendor("autoload");

    $spreadsheet = new Spreadsheet();

    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValue('A1', 'a1');

    $sheet->setCellValue('A2', 'a2');

    $sheet->setCellValue('A3', 'a3');

    $sheet->setCellValue('A4', 'a4');

    $sheet->setCellValue('B1', 'b1');

    $sheet->setCellValue('B2', 'b2');

    $sheet->setCellValue('B3', 'b3');

    $sheet->setCellValue('B4', 'b4');

    $writer = new Xlsx($spreadsheet);

    $writer->save('Public/hello.xlsx');

}

// 读取

public function read(){

    vendor("autoload");

    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');

    $reader->setReadDataOnly(TRUE);

    $spreadsheet = $reader->load('Public/hello.xlsx'); //载入excel表格

    $sheet = $spreadsheet->getSheet(0); // 读取第一個工作表

    $highest_row = $sheet->getHighestRow(); // 取得总行数

    $highest_columm = $sheet->getHighestColumn(); // 取得总列数

    for ($row = 1; $row <= $highest_row; $row++){ //行号从1开始

        for ($column = 'A'; $column <= $highest_columm; $column++){ //列数是以A列开始

        $str = $sheet->getCell($column . $row)->getValue();

        dump($str);

        }
    }

}

// 读写

public function load()

{

    vendor("autoload");

    $template_path = 'Public/Admin/template/hello.xlsx';

    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_path);

    // 以下代码行将活动工作表索引设置为第一个工作表：

    $spreadsheet->setActiveSheetIndex(0);

    // 您还可以按名称/标题设置活动工作表

    $spreadsheet->setActiveSheetIndexByName('DataSheet')

    $worksheet = $spreadsheet->getActiveSheet();

    $worksheet->getCell('A1')->setValue('John');

    $worksheet->getCell('A2')->setValue('Smith');

    // 也可以

    // $worksheet->setCellValue('A'.$i,$value["name"]."\t");


    // 样式设置 加边框

    $styleThinBlackBorderOutline = array(

        'borders' => [

            'allBorders' => [

                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

                'color' => ['argb' => '666666'],

            ],
    
        ]
    );
    $objPHPExcel->getActiveSheet()->getStyle( 'A6:H'.$i)->applyFromArray($styleThinBlackBorderOutline);


    // 结束 进行导出
    ob_end_clean(); // 清空（擦除）缓冲区并关闭输出缓冲

    ob_start(); // 打开输出控制缓冲

    $date=date("Ymd",time());

    $listname = $town.$vilige["name"].$group.'hello'.$date;

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    header('Content-Disposition: attachment;filename="'.$listname . '.xlsx"');

    header('Cache-Control: max-age=0');


    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

    $writer->save('php://output');

    }

}