使用phpExcel导出xls数据

使用phpExcel导出xls数据

使用phpExcel导出xls数据，将需要导出的数据格式成类似如下数组：

Array
(
    [财务] => Array
        (
            [0] => Array
                (
                    [0] => Array
                        (
                            [width] => 6
                            [value] => ID
                            [align] => center
                        )

                    [1] => Array
                        (
                            [width] => 10
                            [value] => 日期
                            [align] => center
                        )

                    [2] => Array
                        (
                            [width] => 8
                            [value] => 收入/支出
                            [align] => left
                        )

                    [3] => Array
                        (
                            [width] => 10
                            [value] => 金额
                            [align] => right
                        )

                )

            [1] => Array
                (
                    [0] => 1
                    [1] => 2020-04-21
                    [2] => 支出
                    [3] => 20.00
                )

            [2] => Array
                (
                    [0] => 2
                    [1] => 2020-04-21
                    [2] => 支出
                    [3] => 27.38
                )

        )

    [计划] => Array
        (
            [0] => Array
                (
                    [0] => Array
                        (
                            [width] => 6
                            [value] => ID
                            [align] => center
                        )

                    [1] => Array
                        (
                            [width] => 20
                            [value] => 标题
                            [align] => left
                        )

                    [2] => Array
                        (
                            [width] => 6
                            [value] => 重要程度
                            [align] => center
                        )

                    [3] => Array
                        (
                            [width] => 30
                            [value] => 内容
                            [align] => left
                        )

                )

            [1] => Array
                (
                    [0] => 2
                    [1] => 测试计划
                    [2] => 正常
                    [3] => 内容
                )

        )

)

导出方法代码：

/**
 * 使用 phpExcel 导出 Excel
 * @param array $data 内容
 * @param string $fileName 文件名
 * @throws PHPExcel_Exception
 * @throws PHPExcel_Reader_Exception
 * @throws PHPExcel_Writer_Exception
 */
function exportExcel($data = [], $fileName = '账单导出数据.xls') {
    if (empty($data)) die('内容不能为空！');
    if (empty($fileName)) die('文件名不能为空！');

    // 设置页面等待时间
    set_time_limit(60);
    // 不限制内存
    ini_set('memory_limit', -1);
    // 引入库
    require APP_ROOT . 'phpExcel/PHPExcel.php';

    $phpExcel = new PHPExcel();
    // 所有单元格居中
    //$phpExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $i = 0;
    // 外循环产生每一项 Sheet
    foreach ($data as $key => $lists) {
        // 创建新的工作空间 sheet
        if ($i > 0) {
            $phpExcel->createSheet();
        }

        $phpExcel->setActiveSheetIndex($i);
        // 给 Sheet 设置名字
        $phpExcel->getActiveSheet()->setTitle($key);

        // 内容
        $line = 1;
        // 外循环产生每一列
        foreach ($lists as $item) {
            // 内循环产生每一行
            foreach ($item as $k => $value) {
                $columnName = PHPExcel_Cell::stringFromColumnIndex($k);

                if ($line == 1) {
                    if (isset($value['width'])) {
                        $phpExcel->getActiveSheet()->getColumnDimension($columnName)->setWidth($value['width']);
                    }

                    if (isset($value['align'])) {
                        $phpExcel->getActiveSheet()->getStyle($columnName)->getAlignment()->setHorizontal($value['align']);
                    }

                    $value = isset($value['value']) ? $value['value'] : (is_string($value) ? $value : '');
                }

                // 数字列转换为字母列 如：27变为AA
                $phpExcel->getActiveSheet()->setCellValue($columnName . $line, $value);
            }
            $line++;
        }
        $i++;
    }

    $fileExt = substr($fileName, strrpos($fileName, '.'));
    $fileName = substr($fileName, 0, strrpos($fileName, '.'));
    $fileName = $fileName . '-' . date('Y-m-d H-i-s') . $fileExt;

    header('Pragma: public');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Content-Type: application/force-download');
    header('Content-Type: application/vnd.ms-execl');
    header('Content-Type: application/octet-stream');
    header('Content-Type: application/download');
    header('Content-Disposition: attachment; filename=' . iconv('UTF-8', 'UTF-8', $fileName));
    header('Content-Transfer-Encoding: binary');
    $objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}