php-使用phpofficephpspreadsheet拓展操作excel

47次阅读

共计 6335 个字符，预计需要花费 16 分钟才能阅读完成。

一：安装 phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

二：phpoffice/phpspreadsheet API 接口详解

PhpSpreadsheet 提供了丰富的 API 接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的 Excel 表格，PhpSpreadsheet 都能做到

在使用 phpoffice/phpspreadsheet 的 API 接口前，确保引入了正确的文件并实例化 

use PhpOffice\PhpSpreadsheet\Spreadsheet;// 引入文件

$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();// 创建一个新的 excel 文档
$sheet = $spreadsheet->getActiveSheet();// 获取当前操作 sheet 的对象

1：设置字体：

$sheet->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')
            ->setSize(10);// 将 A7 至 B7 两单元格设置为粗体字，Arial 字体，10 号字
$sheet->getStyle('B1')->getFont()->setBold(true);// 将 B1 单元格设置为粗体字

2：设置颜色 

$sheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);// 将 A1 单元格文字颜色设为红色

3：设置列宽 

$sheet->getColumnDimension('A')->setWidth(20);// 将 A 列的宽度设为 20(字符)
$sheet->getColumnDimension('B')->setAutoSize(true);// 将 B 列的宽度设为自动宽度
$sheet->getDefaultColumnDimension()->setWidth(12);// 设置默认列宽为 12

4：设置行高 

$sheet->getRowDimension('10')->setRowHeight(100);// 将第十行的高度设为 100pt
$sheet->getDefaultRowDimension()->setRowHeight(15);// 设置默认行高为 15

5: 对齐 

$sheet->getStyle('A:D')->getAlignment()
            ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER) // 设置垂直居中
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER) // 设置水平居中
            ->setWrapText(true); // 设置自动换行

6：合并单元格 

$sheet->mergeCells('A1:D2');//A1 到 D2 合并为一个单元格

7：将合并后的单元格拆分 

$sheet->unmergeCells('A1:D2');// 将合并后的单元格拆分。

8：使用 applyFromArray 实现单元格样式设置 

// 样式变量
$style = [
// 设置字体样式
'font' => [
        'name' => 'Arial',
        'bold' => true,
        'italic' => false,
        'underline' => Font::UNDERLINE_DOUBLE,
        'strikethrough' => false,
        'color' => ['rgb' => '808080']
    ],
// 设置边框线样式
'borders' => [
         //allBorders 所有的边框线样式
         // 左边框线
       'bottom' => [
           'borderStyle' => Border::BORDER_DASHDOT,
           'color' => ['rgb' => '808080']
       ],
         // 上边框线
       'top' => [
           'borderStyle' => Border::BORDER_DASHDOT,
           'color' => ['rgb' => '808080']
       ]
],
// 对齐样式
'alignment' => [
   'horizontal' => Alignment::HORIZONTAL_CENTER,
   'vertical' => Alignment::VERTICAL_CENTER,
   'wrapText' => true,
],
// 是否使用前缀
'quotePrefix'    => true
];
$sheet->getStyle('A1:D1')->applyFromArray($style);

9：设置工作表标题 

$sheet->setTitle('Hello');;// 设置当前工作表标题。

10：设置单元格的格式 

$sheet->getStyle('D2')->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);// 将 D2 单元格的格式设为文本格式
$sheet->getStyle('A1:D2')->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);// 将 A1 到 D2 的单元格设置为文本格式

11: 换行 

$sheet->getCell('A4')->setValue("hello\nworld");// 将 A4 单元格的 hello 和 world 换行

12：超链接 

// 将 A2 单元格内容设置 blog 并点击跳转 https://www.wj0511.com
$sheet->setCellValue('A2', 'blog');
$sheet->getCell('A2')->getHyperlink()->setUrl('https://www.wj0511.com');

13：使用函数

常用函数有：总和 (SUM)，最大数 (MAX)，最小数 (MIN)，平均值 (AVERAGE)

$sheet->setCellValue('B5', '=SUM(B1:B4)');// 将 B5 单元格的内容设为 B1 到 B4 的之和

14：设置文档属性 

$spreadsheet->getProperties()
    ->setCreator("author")    // 作者
    ->setLastModifiedBy("last-author") // 最后修改者
    ->setTitle("title")  // 标题
    ->setSubject("subject") // 副标题
    ->setDescription("description")  // 描述
    ->setKeywords("keywords") // 关键字
    ->setCategory("category"); // 分类

三：简单实现生成 excel(这里我下载使用的 Yii 框架自带的下载方法)

<?php
/**
 * author: wangjian
 * date: 2019/7/15
 */
namespace app\controllers;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Yii;
use yii\web\Controller;
class ExcelController extends Controller
{
    /**
     * 数字转字母（类似于 Excel 列标）* @param Int $index 索引值
     * @param Int $start 字母起始值
     * @return String 返回字母
     */
    public function intToChr($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {$str .= $this->intToChr(floor($index / 26)-1);
        }
        return $str . chr($index % 26 + $start);
    }
    
    
    public function actionIndex()
    {
        // 头信息
        $header = [
            '姓名',
            '性别',
            '学历',
            '年龄',
            '身高',
        ];
        // 内容
        $data = [
            [
                '小明',
                '男',
                '专科',
                '18',
                '175'
            ],
            [
                '小红',
                '女',
                '本科',
                '18',
                '155'
            ],
            [
                '小蓝',
                '男',
                '专科',
                '20',
                '170'
            ],
            [
                '张三',
                '男',
                '本科',
                '19',
                '165'
            ],
            [
                '李四',
                '男',
                '专科',
                '22',
                '175'
            ],
            [
                '王二',
                '男',
                '专科',
                '25',
                '175'
            ],
            [
                '麻子',
                '男',
                '本科',
                '22',
                '180'
            ],
        ];
        $header = array_values($header);
        $data = array_values($data);
        // 获取列信息
        $column = []; //['A','B','C','D','E']
        foreach ($header as $k => $item) {$column[$k] = $this->intToChr($k);
        }
        // 获取初始列和最终列
        $firstColum = $column[0];
        $lastColum = $column[count($column) - 1];
        // 获取初始行和最终行
        $firstRow = 1;
        $lastRow = count($data) + 1;
        $row = 1;
        $spreadsheet = new Spreadsheet();// 创建一个新的 excel 文档
        $sheet = $spreadsheet->getActiveSheet();// 获取当前操作 sheet 的对象
        $sheet->setTitle('标题'); // 设置标题
        $sheet->getStyle("{$firstColum}:{$lastColum}")->getAlignment()
            ->setVertical(Alignment::VERTICAL_CENTER) // 设置垂直居中
            ->setHorizontal(Alignment::HORIZONTAL_CENTER) // 设置水平居中
            ->setWrapText(true); // 设置自动换行
        // 设置宽度
        $sheet->getDefaultColumnDimension()->setWidth(20);
        $headerStyle = [
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
            'font' => [
                'bold' => true,
                'size' => 14,
            ],
        ];
        $cellStyle = [
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ]
            ],
            'font' => ['size' => 10,],
        ];
        // 将 excel 的单元格格式设为文本格式
        $sheet->getStyle("{$firstColum}{$firstRow}:{$lastColum}{$lastRow}")->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
        // 设置头信息样式
        $sheet->getRowDimension($row)->setRowHeight(30);// 设置行高
        $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($headerStyle);
        // 设置头信息
        foreach ($header as $key => $item) {$sheet->setCellValue("{$column[$key]}{$row}", $item);
        }
        $row++;
        foreach ($data as $key => $model) {$sheet->getRowDimension($row)->setRowHeight(30);// 设置行高
            $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($cellStyle);
            $i = 0;
            foreach ($model as $value) {$sheet->setCellValue("{$column[$i]}{$row}", $value);
                $i++;
            }
            $row++;
        }
        $file = '表格' . '.xlsx';// 保存地址
        $writer = new Xlsx($spreadsheet);
        $writer->save($file);// 生成 excel 文件
        Yii::$app->response->sendFile($file, '下载的 excel 名称.xlsx')->send();}
}

四：读取 excel 文件 

$title = [];//excel 工作表标题
$info = [];//excel 内容
$fileName = "表格.xlsx";
$spreadsheet = IOFactory::load($fileName);
//$worksheet = $spreadsheet->getActiveSheet();   // 获取当前文件内容
$sheetAllCount = $spreadsheet->getSheetCount(); // 工作表总数
for ($index = 0; $index < $sheetAllCount; $index++) {   // 工作表标题
    $title[] = $spreadsheet->getSheet($index)->getTitle();}
// 读取第一個工作表
$whatTable = 0;
$sheet = $spreadsheet->getSheet($whatTable);
$highest_row = $sheet->getHighestRow(); // 取得总行数
$highest_column = $sheet->getHighestColumn(); /// 取得列数  字母 abc...
$highestColumnIndex = Coordinate::columnIndexFromString($highest_column);  // 转化为数字;
for ($i = 1; $i <= $highestColumnIndex; $i++) {for ($j = 1; $j <= $highest_row; $j++) {$conent = $sheet->getCellByColumnAndRow($i, $j)->getCalculatedValue();
        $info[$j][$i] = $conent;
    }
}
var_dump($info);

参考：https://phpspreadsheet.readth…

正文完
excel php yii
发表至：无分类
2019-07-15
 0