PhpSpreadsheet 基本使用和导入导出模版生成Excel文件

597次阅读

使用 composer 加载

composer require phpoffice/phpspreadsheet
一. 基本使用

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Helper\Html as HtmlHelper;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$spreadsheet = new Spreadsheet(); // 新建文件
// $spreadSheet = IOFactory::load($inputFileName); // 载入文件
$sheet = $spreadsheet->getActiveSheet();
//$spreadSheet->getProperties()->setCreator("hellow");// 设置作者
//$spreadSheet->getSheetCount();// 工作表总数
//$spreadSheet->getSheetNames();// 工作表名数组
//$sheet = $spreadSheet->getSheetByName('Sheet1');// 根据表名获取工作表
//$sheet = $spreadSheet->getSheet(0);// 根据表索引获取工作表
//$sheet = $spreadSheet->setActiveSheetIndex(0);// 切换当前工作表
//$sheet = $spreadsheet->setActiveSheetIndexByName('DataSheet')

// 设置打印选项
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
$sheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)->setPaperSize(PageSetup::PAPERSIZE_A4);;
$sheet->getPageMargins()->setTop(1)->setRight(0.75)->setLeft(0.75)->setBottom(1);
$sheet->getPageSetup()->setHorizontalCentered(true)->setVerticalCentered(false);
$sheet->setPrintGridlines(true);
$sheet->getPageSetup()->setPrintArea('A1:E5,G4:M20');

// 设置缩放
$sheet->getSheetView()->setZoomScale(75);
// 设置 worksheet 的颜色
$sheet->getTabColor()->setRGB('FF0000');
// 设置 worksheet 名字
$sheet->setTitle('sheet 1');

// 设置默认样式
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

// 获取文档所有值
$data = $sheet->toArray();
// 获取最大行数 
$res = $sheet->getHighestRow();
// 获取最大列数 
$res = $sheet->getHighestColumn();

// 单元格信息
$cell = $sheet->getCellByColumnAndRow(2, 1); // 获取单元格 B1
$cell = $sheet->getCell('A1'); // 获取单元格 A1
$cell->getValue(); // 获取单元格的数据值
$cell->getCoordinate(); // 获取行列信息 A1
$column = $cell->getColumn(); // 获取列信息 A
$row   = $cell->getRow(); // 获取行信息 1
$cell->getDataType(); // 获取数据类型
$cell->setValue('8888'); // 设置值
$cell->getStyle()->getFont()->getName(); // 单元格样式

// 设置行高
$sheet->getDefaultRowDimension()->setRowHeight(10, 'mm');
$sheet->getRowDimension(1)->setRowHeight(10, 'mm');
// 设置列宽
// $sheet->getColumnDimension('A')->setAutoSize(true);
//$sheet->getColumnDimension('A')->setWidth(300);// 设置 A 列的宽度
$sheet->getDefaultColumnDimension()->setWidth(20); // 设置列默认宽度

// 设置换行
$sheet->setCellValue('A2', "hellow\nphp"); // 设置换行 双引号 +\n+setWrapText
$sheet->getStyle('A2')->getAlignment()->setWrapText(true); // 设置换行

// 合并拆分单元格
$sheet->mergeCells('B2:B3');
// $sheet->unmergeCells('B2:B3');

// 设置值
// $sheet->setCellValue('A1', "hellow\nphp");
$sheet->setCellValueByColumnAndRow(2, 2, '6666'); // 设置 B2 的值
// $sheet->fromArray($arr, null, 'D5'); // 数组, 空值时填充值, 开始单元格坐标

// 设置超链接
$sheet->setCellValue('B3', "百度"); // 设置 A 链接
$sheet->getCell('B3')->getHyperlink()->setUrl('https://www.baidu.com');
// $sheet->setCellValue('E26', 'www.phpexcel.net');
// $sheet->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");

// 设置字体粗细大小颜色
$sheet->getStyle('B3')->getFont()->setBold(true)->setName('Arial')->setSize(20);
$sheet->getStyle('B3')->getFont()->getName(); // 字体名
$sheet->getStyle('B3')->getFont()->getColor()->setRGB('#AEEEEE'); // 设置颜色
$sheet->getStyle('B3')->getFont()->getColor()->getRGB(); // 获取颜色值
$sheet->getCell('C3')->setValue('2021-03-27 23:22:59');
//$sheet->getStyle('D2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);// 设置日期格式 
$sheet->getStyle('D3')->getNumberFormat()->setFormatCode('dd/mm/yyyy'); // 设置日期格式 与上文相同


// 设置单元格背景色
$sheet->getStyle('E3')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);

// 设置单元格对齐方式
$sheet->getStyle('B2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('B2')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

// 设置单元格边框
$sheet->getStyle('C2:E2')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);

// 富文本
$html = 'bold, italic, bold+italic';;
$wizard = new HtmlHelper();
$richText = $wizard->toRichTextObject($html);
$sheet->setCellValue('D5', $richText);

// 公式
$sheet->setCellValue('A4', '=IF(C4>500,"profit","loss")');
// $formula = $sheet->getCell('A4')->getValue();
// $value = $sheet->getCell('A4')->getCalculatedValue();

// 插入删除行列
$sheet->insertNewRowBefore(7, 2);
$sheet->removeRow(7, 2);

// 写入图片
$drawing = new Drawing();
$drawing->setName('Logo')->setDescription('Logo')->setPath('../files/1.jpg')->setHeight(30)->setCoordinates('D6')->setOffsetX(50)->setOffsetY(6);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);

$drawing->setWorksheet($sheet);

// 设置单元格数据类型
$sheet->getCell('A1')->setValueExplicit('25', DataType::TYPE_NUMERIC);

// 格式
$styleArray = [
    'font' => ['bold' => true,],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_RIGHT,],
    'borders' => [
        'top' => ['borderStyle' => Border::BORDER_THIN,],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => ['argb' => 'FFA0A0A0',],
        'endColor' => ['argb' => 'FFFFFFFF',],
    ],
];
$filename = '01simple.xlsx';
$sheet->getStyle('A5:E5')->applyFromArray($styleArray);

// // 保存 xlsx 在本地
// $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// $writer->save('01simple.xlsx');

// 下载 xlsx
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
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 = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

二、导出 (写入)

namespace app\admin\controller\Index;
 
use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
 
class Index extends Backend
{// 导出 ( 写入)
    public function export(){$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 = IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');
        return;
    }
}

三、导入 (读取)

namespace app\admin\controller\Index;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class Index extends Backend
{// 导入 ( 读取)
    public function import(){$reader = 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);
            }
        }
    }
}

四. 模版导出 (读写)

namespace app\admin\controller\Index;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class Index extends Backend
{// 模版导出 ( 读写)
    public function templateExport()
    {
        $template_path = __DIR__ . '/PayrollTemplate.xls';
        // 读取模板
        $spreadsheet = IOFactory::load($template_file_address);

        // 以下代码行将活动工作表索引设置为第一个工作表：设置 Excel Sheet
        $spreadsheet->setActiveSheetIndex(0);

        // 指向激活的工作表
        $worksheet = $spreadsheet->getActiveSheet();

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

        // 样式设置 加边框
        $styleThinBlackBorderOutline = array(
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ]
        );
        $worksheet->getStyle('A6:H'.$i)->applyFromArray($styleThinBlackBorderOutline);

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

        $date = date("Ymd",time());
        $listname = '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 = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }
}

正文完

有偿技术支持加微信