yidasphpSpreadsheet写入和读取Excel

1、使用composer安装

composer require yidas/phpspreadsheet-helper

2、写入Excel

use yidasphpSpreadsheetHelper;


/**
 * 导出明细
 */
public function export_detail()
{
    $params = $this->request->param();
    $param = input('get.');
    $where = [];
    if(!empty($param['start_date']) && !empty($param['end_date'])){
        $end_date = $param['end_date']." 23:59:59";
        $where[] = ['contract.created_at','between',[$params['start_date'],$end_date]];
    }

    $data = Contract::with(['installment','account', 'customer'])->where($where)->select();

    $orderFields = [
        'created_at' => '簽約日期',
        'contract_no' => '合約編號',
        'account_name' => '合作方',
        'customer_name' => '客戶',
        'default_currency' => '貨幣',
        'stage' => '狀態',
        'payment_term' => '付款期限',
        'fax' => '開票傳真',
        'total' => '總金額',
        'installment_stage' => '付款狀態',
    ];
    $itemFields = [
        'period' => '第幾期',
        'await_time' => '待還時間',
        'await_money' => '待還金額',
        'reality_time' => '實還時間',
        'reality_money' => '實還金額',
        'state' => '状态',
    ];
    foreach ($orderFields as $field => $label) {
        $header[] = $label;
    }
    foreach ($itemFields as $field => $label) {
        $header[] = $label;
    }
    $totalCount = 4;//设置样式开始的行数（頭部默認有3行，内容是第4开始的）
    foreach ($data as $order) {
        $items = $order['installment'];
        $itemCount = count($items);//合約的分幾期
        $rowCount = $itemCount + 1;//合約的分幾期， "+1"多出一行是要计算它的总数
        $totalCount += $rowCount;
        $orderRow = [];//合约的主数据

        //循环设置，合约的主数据值
        foreach ($orderFields as $field => $label) {
            $val = $order[$field];
            $orderRow[] = ['value' => $val, 'row' => $rowCount];//value：合约的值；row：占行数（有2个分期，则占3行）
        }
        $subAmt = 0;
        if($itemCount==0) {//沒有分期
            $row = $orderRow;
            $rows[] = $row;
        } else {
            //有分期
            foreach ($items as $index => $item) {
                $row = [];
                //TODO 判断问题
                if ($index == 0) {
                    $row = $orderRow;//一个分期开始设置合约行数
                } else {
                    $row[] = ['value' => '', 'skip' => 10];//二个分期开始。value：合约的值（没有），skip：合约占的列数（）

                }

                //赋值，合约的分期
                foreach ($itemFields as $field => $label) {
                    $row[] = $item[$field];
                }

                $rows[] = $row;
                $subAmt += floatval($item['reality_money']);//实还金额的总数
                $subAmt_style = [
                    'font' => ['bold' => true],
                    'alignment' => ['horizontal' => 'center', 'vertical' => 'center']
                ];
            }
            //實還金額合計，skip：从10列开始，col占6格
            $subTotal = [['value' => '', 'skip' => 10], ['value' => '實還金額合計：'.$subAmt, 'col' => 6,'style' => $subAmt_style]];
            $rows[] = $subTotal;
        }
    }
    $title_style = [
        'font' => ['bold' => true],
        'alignment' => ['horizontal' => 'center', 'vertical' => 'center']
    ];//标题的样式
    //$header:标题，$rows:数据
    $spredsheet = Helper::newSpreadsheet()
        ->addRows([
            [['value' => '合約明细表', 'row' => 3, 'col' => count($header), 'style' => $title_style]],
            [''], [''], $header
        ])
        ->addRows($rows);

    $cellStyle = ['borders' => ['allBorders' => ['borderStyle' => 'thin']]];
    $objSheet = Helper::getSheet();
    //getStyle：获取单元格样式，单元格样式：applyFromArray（这里是设置样式的）
    $objSheet->getStyle('A4:P' . $totalCount)->applyFromArray($cellStyle);//A - P :列，1 - 9：行；例如,B2，2列2行
    $objSheet->getStyle('A4:P4')->applyFromArray(['font' => ['bold' => true]]);//头部标题默认加黑
    ob_clean();
    $spredsheet->output('合約_' . date('YmdHis'));
}

导出的结果：