PHP Excel导出性能小怪兽 PHP_XLSXWriter标准说明

亦凉 2022-02-04 07:49 822阅读 0赞

### ***自行稍作修改，更加方便[github地址][github]*** ###

性能好不好看图⬇️，还是感觉老外搞事比较?X

![20190507104143671.png][]

官方地址：[github-PHP\_XLSXWriter][github-PHP_XLSXWriter]

[xlsxwriter.class.php][]文件是核心文件，其他基本都是示例文件

能有的都在下面，粘来即用。

最不嗨皮的地方就是不支持单个单元格的定义，但看源代码实现其实也是单元格去实现的……有开发潜力

<?php
    include_once("xlsxwriter.class.php");
    ini_set('display_errors', 0);
    ini_set('log_errors', 1);
    error_reporting(E_ALL & ~E_NOTICE);
    
    //设置 header，用于浏览器下载
    $filename = "example.xlsx";
    header('Content-disposition: attachment; filename="' . XLSXWriter::sanitize_filename($filename) . '"');
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header('Content-Transfer-Encoding: binary');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    
    # 表格样式
    $styles1 = array(
        'font'         => 'Arial',
        'font-size'    => 12,
        'font-style'   => 'bold',  #bold, italic, underline, strikethrough or multiple ie: 'bold,italic'
        'color'        => '#333',
        'fill'         => '#fff',  # 背景填充
        'halign'       => 'center',  # 水平位置 general, left, right, justify, center
        'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
        'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
        'border-color' => '#333',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
        'valign'       => 'center', # 垂直位置 bottom, center, distributed
        'height'       => 50,  # 行高
        // 'collapsed'       => true,  # 未知
        // 'hidden'       => true,  # 隐藏行
    );
    
    # 每列标题头
    $header = array(
        'created'     => 'date',
        'product_id'  => 'integer',
        'quantity'    => '#,##0.00', #价格 #,##0.00表示小数位两个，减少或增加改变长度
        'amount'      => 'price',
        'description' => 'string',
        'tax'         => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
    );
    
    # 列样式
    $col_options = [
        'widths'       => [20, 30, 20, 40, 40],  # 宽度
        'auto_filter'  => true,  # 筛选
        // 'freeze_rows'=>true,  # 冻结
        // 'freeze_columns'=>true,  # 冻结
        // 'suppress_row' => true,
    ];
    
    # 表数据
    $rows = array(
        array('2015-01-01', '1', '-50.5', '2010-01-01 23:00:00', '2012-12-31 23:00:00', '=D2'),
        array('2003', '=B1', '23.5', '2010-01-01 00:00:00', '2012-12-31 00:00:00', '=D2*0.05'),
    );
    
    $writer = new XLSXWriter();
    
    $writer->setTitle('标题');
    $writer->setSubject('主题');
    $writer->setAuthor('作者名字');
    $writer->setCompany('公司名字');
    $writer->setKeywords('关键字');
    $writer->setDescription('描述');
    $writer->setTempDir('临时目录');
    # 合并单元格，第一行的大标题
    $writer->markMergedCell('Sheet1', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 5);
    # 每列标题头
    $writer->writeSheetHeader('Sheet1', $header, $col_options);
    
    # 表数据行插入
    foreach ($rows as $row) {
        $writer->writeSheetRow('Sheet1', $row, $styles1);
    }
    
    #统计行数 返回行数
    $writer->countSheetRows('Sheet1');
    
    # 输出文档
    $writer->writeToStdOut();
    // $writer->writeToFile('example.xlsx');
    // echo $writer->writeToString();  #没什么卵用
    // $writer->log('错误信息');  # 控制台输出错误信息  数据支持数组、字符串
    exit(0);

[github]: https://github.com/sym134/PHP_XLSXWriter_pro
[20190507104143671.png]: /images/20220204/8474880d90b548b596bd2c3115c30770.png
[github-PHP_XLSXWriter]: https://github.com/mk-j/PHP_XLSXWriter
[xlsxwriter.class.php]: https://github.com/mk-j/PHP_XLSXWriter/blob/master/xlsxwriter.class.php