php导入导出表格文件

于 2023-08-14 09:51:54 发布
阅读量76 收藏
点赞数
分类专栏： PHP 文章标签： php android 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41833510/article/details/132269060
版权
最近在项目中用到了php导入导出表格文件，废话不多说 上代码。

环境 tp6.1+PhpSpreadsheet+Filesystem

导入

// An highlighted block
public function land_import(Request $request)
    {
        $token = $request->get('token') ?: null;
        if (empty($token)) {
            return $this->failed($token, '密钥丢失');
        }
        $result = checkToken($token);
        if (!isset($result['data'])) {
            return $this->failed($token, '密钥失效');
        };
        $uid = $result['data']['uid'];
        $files = request()->file('file');
        if (!$files) {
            return $this->failed([], '无上传文件');
        }
        $date = date('Ymd');
        //规定上传文件的大小
        $size = 1024 * 1024 * 5;
        //规定上传文件的类型
        $fileExt = ['Xlsx', 'xlsx'];

        //获取文件后缀
        $getExt = $files->getOriginalExtension();
        //获取文件名
        $name = $files->getOriginalName();
        //验证文件
        if ($size < $files->getSize()) {
            return $this->failed([], '上传文件大于5M');
        }
        if (!in_array($getExt, $fileExt)) {
            return $this->failed([], '不是标准上传格式,请下载标准格式使用');
        }
        $name = date('YmdHis') . '.Xlsx';
        $savename = \think\facade\Filesystem::disk('public')->putFileAs($date, $files, $name);
        if (!$savename) {
            return $this->failed([], '不是标准上传格式,请下载标准格式使用');
        }
        //  return $_SERVER['DOCUMENT_ROOT'] . '/storage/' . $date . '/' . $name;

        $file = $_SERVER['DOCUMENT_ROOT'] . '/storage/' . $date . '/' . $name;

        // 自动获取文件类型
        $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);
        // 创建读操作
        if ($file_type == 'Xlsx') {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        } else {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
        }
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();
        //获取所有内容并转化为数组
        $data = $sheet->toArray();
        $time = time();
        $i = 0;
        $p = 0;
        $cont = '重复合同号：';
        foreach ($data as $key => $vul) {
            if ($key > 0) {
                $LandTransfer_mi['admin_id'] = $uid;
                $LandTransfer_mi['contract_code'] = $vul[0];
                $LandTransfer_mi['assigneer'] = $vul[1];
                $LandTransfer_mi['use'] = $vul[2];
                $LandTransfer_mi['area'] = $vul[3];
                $LandTransfer_mi['cy_total_amount'] = $vul[4];
                $LandTransfer_mi['cy_payable_amount'] = $vul[5];
                $LandTransfer_mi['cy_payable_time'] = $vul[6] ? strtotime($vul[6]) : 0;
                $LandTransfer_mi['cy_actual_amount'] = $vul[7];
                $LandTransfer_mi['cy_actual_time'] = $vul[8] ? strtotime($vul[8]) : 0;
                $LandTransfer_mi['signing_time'] = $vul[9] ? strtotime($vul[9]) : 0;
                $LandTransfer_mi['delivery_time'] = $vul[10] ? strtotime($vul[10]) : 0;
                $LandTransfer_mi['period'] = $vul[11];
                $LandTransfer_mi['sort'] = 1;
                $LandTransfer_mi['remarks'] = $vul[12];
                $LandTransfer_mi['status'] = 2;
                $LandTransfer_mi['number'] = 1;
                $LandTransfer_mi['create_time'] = $time;
                $LandTransfer_mi['update_time'] = $time;

                $check_code = LandTransfer::where(['contract_code' => $LandTransfer_mi['contract_code']])->field('*')->find();
                if (!empty($check_code)) {
                    $i += 1;
                    $cont .= $LandTransfer_mi['contract_code'] . ',';
                } else {
                    $LandTransfer = LandTransfer::create($LandTransfer_mi);
                    $LandRefund_mi['transfer_id'] = $LandTransfer['id'];
                    $LandRefund_mi['bzj_payable_amount'] = $vul[13];
                    $LandRefund_mi['bzj_payable_time'] = $vul[14] ? strtotime($vul[14]) : 0;
                    $LandRefund_mi['bzj_actual_amount'] = $vul[15];
                    $LandRefund_mi['bzj_actual_time'] = $vul[16] ? strtotime($vul[16]) : 0;
                    $LandRefund_mi['kg_refund_payable_amount'] = $vul[17];
                    $LandRefund_mi['kg_refund_payable_time'] = $vul[18] ? strtotime($vul[18]) : 0;
                    $LandRefund_mi['kg_refund_actual_amount'] = $vul[19];
                    $LandRefund_mi['kg_refund_actual_time'] = $vul[20] ? strtotime($vul[20]) : 0;
                    $LandRefund_mi['kg_breach'] = $vul[21];
                    $LandRefund_mi['jg_refund_payable_amount'] = $vul[22];
                    $LandRefund_mi['jg_refund_payable_time'] = $vul[23] ? strtotime($vul[23]) : 0;
                    $LandRefund_mi['jg_refund_actual_amount'] = $vul[24];
                    $LandRefund_mi['jg_refund_actual_time'] = $vul[25] ? strtotime($vul[25]) : 0;
                    $LandRefund_mi['jg_breach'] = $vul[26];
                    $LandRefund_mi['dc_refund_payable_amount'] = $vul[27];
                    $LandRefund_mi['dc_refund_payable_time'] = $vul[28] ? strtotime($vul[28]) : 0;
                    $LandRefund_mi['dc_refund_actual_amount'] = $vul[29];
                    $LandRefund_mi['dc_refund_actual_time'] = $vul[30] ? strtotime($vul[30]) : 0;
                    $LandRefund_mi['dc_breach'] = $vul[31];
                    $LandRefund_mi['sort'] = 1;
                    $LandRefund_mi['status'] = 2;
                    $LandRefund_mi['create_time'] = $time;
                    $LandRefund_mi['update_time'] = $time;
                    if (
                        !empty($LandRefund_mi['bzj_payable_amount']) ||
                        !empty($LandRefund_mi['kg_refund_payable_amount']) ||
                        !empty($LandRefund_mi['jg_refund_payable_amount']) ||
                        !empty($LandRefund_mi['dc_refund_payable_amount'])
                    ) {
                        LandRefund::create($LandRefund_mi);
                    }
                    $p += 1;
                }
            }
        }


        return $this->success($data, '有' . $i . '条重复，剩余' . $p . '条正在执行自动导入(' . $cont . ')');
    }

导出

// An highlighted block
 protected function exportData($title, $list)
    {

        $CellNameList = ["序号", "合同编号", "合同签订时间", "受让人", "用途", "面积(㎡)", "出让总金额(元)", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""];
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $titCol = 'A';
        foreach ($CellNameList as $key => $value) {
            // 单元格内容写入
            $sheet->setCellValue($titCol . '3', $value);
            $sheet->getColumnDimension($titCol)->setWidth(20);
            $sheet->getStyle($titCol . '3')->getAlignment()
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER) //设置垂直居中
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER) //设置水平居中
                ->setWrapText(true); //设置自动换行
            $styleArray1 = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                    ]
                ]
            ];
            $sheet->getStyle($titCol . '1')->applyFromArray($styleArray1);
            $sheet->getStyle($titCol . '2')->applyFromArray($styleArray1);
            $sheet->getStyle($titCol . '3')->applyFromArray($styleArray1);
            $sheet->getStyle($titCol . '4')->applyFromArray($styleArray1);
            $sheet->getStyle($titCol . '5')->applyFromArray($styleArray1);
            $sheet->getStyle($titCol . '6')->applyFromArray($styleArray1);
            $titCol++;
        }
        $sheet->mergeCells('A1:Z1');
        $sheet->setCellValue('A1', '退费台账');
        $sheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


        $sheet->mergeCells('H3:K4');
        //  $sheet->mergeCells('H4:K4');
        //  $sheet->mergeCells('H3:H4');
        //  $sheet->mergeCells('I3:I4');
        //  $sheet->mergeCells('J3:J4');
        //  $sheet->mergeCells('K3:K4');
        $sheet->setCellValue('H3', '保证金收缴情况');
        $sheet->getStyle('H3')->getAlignment()->setWrapText(true);
        $sheet->getStyle('H3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('H3')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        $sheet->mergeCells('H5:I5');
        $sheet->setCellValue('H5', '应交款');
        $sheet->getStyle('H5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('H5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('H5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('J5:K5');
        $sheet->setCellValue('J5', '实交款');
        $sheet->getStyle('J5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('J5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('J5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('H6', '金额(元)');
        $sheet->getStyle('H6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('H6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('H6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('I6', '时间');
        $sheet->getStyle('I6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('I6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('I6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('J6', '金额(元)');
        $sheet->getStyle('J6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('J6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('J6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('K6', '时间');
        $sheet->getStyle('K6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('K6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('K6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        $sheet->mergeCells('L3:Z3');
        $sheet->setCellValue('L3', '保证金退费情况');
        $sheet->getStyle('L3')->getAlignment()->setWrapText(true);
        $sheet->getStyle('L3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('L3')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


        $sheet->mergeCells('L4:P4');
        $sheet->setCellValue('L4', '开工部分');
        $sheet->getStyle('L4')->getAlignment()->setWrapText(true);
        $sheet->getStyle('L4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('L4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('L5:M5');
        $sheet->setCellValue('L5', '应退款');
        $sheet->getStyle('L5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('L5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('L5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('N5:O5');
        $sheet->setCellValue('N5', '实退款');
        $sheet->getStyle('N5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('N5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('N5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('L6', '金额(元)');
        $sheet->getStyle('L6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('L6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('L6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('M6', '时间');
        $sheet->getStyle('M6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('M6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('M6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('N6', '金额(元)');
        $sheet->getStyle('N6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('N6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('N6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('O6', '时间');
        $sheet->getStyle('O6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('O6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('O6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('P5:P6');
        $sheet->setCellValue('P5', '违约情况');
        $sheet->getStyle('P5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('P5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('P5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        $sheet->mergeCells('Q4:T4');
        $sheet->setCellValue('Q4', '竣工部分');
        $sheet->getStyle('Q4')->getAlignment()->setWrapText(true);
        $sheet->getStyle('Q4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('Q4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('Q5:R5');
        $sheet->setCellValue('Q5', '应退款');
        $sheet->getStyle('Q5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('Q5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('Q5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('S5:T5');
        $sheet->setCellValue('S5', '实退款');
        $sheet->getStyle('S5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('S5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('S5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('Q6', '金额(元)');
        $sheet->getStyle('Q6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('Q6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('Q6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('R6', '时间');
        $sheet->getStyle('R6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('R6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('R6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('S6', '金额(元)');
        $sheet->getStyle('S6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('S6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('S6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('T6', '时间');
        $sheet->getStyle('T6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('T6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('T6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('U5:U6');
        $sheet->setCellValue('U5', '违约情况');
        $sheet->getStyle('U5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('U5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('U5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


        $sheet->mergeCells('V4:Y4');
        $sheet->setCellValue('V4', '达产部分（退费情况）');
        $sheet->getStyle('V4')->getAlignment()->setWrapText(true);
        $sheet->getStyle('V4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('V4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('V5:W5');
        $sheet->setCellValue('V5', '应退款');
        $sheet->getStyle('V5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('V5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('V5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('X5:Y5');
        $sheet->setCellValue('X5', '实退款');
        $sheet->getStyle('X5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('X5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('X5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('V6', '金额(元)');
        $sheet->getStyle('V6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('V6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('V6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('W6', '时间');
        $sheet->getStyle('W6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('W6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('W6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('X6', '金额(元)');
        $sheet->getStyle('X6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('X6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('X6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('Y6', '时间');
        $sheet->getStyle('Y6')->getAlignment()->setWrapText(true);
        $sheet->getStyle('Y6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('Y6')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('Z5:Z6');
        $sheet->setCellValue('Z5', '违约情况');
        $sheet->getStyle('Z5')->getAlignment()->setWrapText(true);
        $sheet->getStyle('Z5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('Z5')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


        $sheet->mergeCells('A3:A6');
        $sheet->mergeCells('B3:B6');
        $sheet->mergeCells('C3:C6');
        $sheet->mergeCells('D3:D6');
        $sheet->mergeCells('E3:E6');
        $sheet->mergeCells('F3:F6');
        $sheet->mergeCells('G3:G6');

        $row = 7;
        foreach ($list as $item) {
            $dataCol = 'A';
            // 单元格内容写入
            $sheet->setCellValue($dataCol . $row, $item['id']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            // 单元格内容写入
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['contract_code']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['signing_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['assigneer']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['use']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['area']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['LandTransfer']['cy_total_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['bzj_payable_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['bzj_payable_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['bzj_actual_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['bzj_actual_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['kg_refund_payable_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['kg_refund_payable_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['kg_refund_actual_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['kg_refund_actual_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['kg_breach']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['jg_refund_payable_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['jg_refund_payable_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['jg_refund_actual_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['jg_refund_actual_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['jg_breach']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);


            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['dc_refund_payable_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['dc_refund_payable_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['dc_refund_actual_amount']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['dc_refund_actual_time']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $dataCol++;
            $sheet->setCellValue($dataCol . $row, $item['dc_breach']);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($dataCol . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $row++;
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
    }

这个方法可以实现自定义合并单元格，修改字体大小，文件默认格式等一系列操作，还是很方便。

瑞儿-php-web
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
php导入excel表格
03-24
php读取excel表类，能读取多sheet页面的表格，可以说比较完美地解决excel的导入问题
PHP导入Excel表格
12-14
//导入客户经理     public function do_addman()     {         //导入客户经理Excel表格         if (I(‘types’) == ‘upload’) {             if (!empty($_FILES)) {                 $upload           = new \Think\Upload(); // 实例化上传类                 $upload->maxSize  = 3145728; // 设置附件上传大小                 $upload->exts  
php 导入excel
PersonalM的博客
10-28 380
public function actionImportExcelToMysql() { $filename = './1.xlsx'; $objPHPExcelReader = PHPExcel_IOFactory::load($filename); $reader = $objPHPExcelReader->getWorksh...
PHP 导入EXCEL
04-18
利用PHPEXCEL导入xls文件，将数据导入mysql数据库
php导入导出excel实例
10-26
标题中的"php导入导出excel实例"指的是使用PHP编程语言处理Excel文件的示例，而描述则提到使用PHPExcel库来实现这一功能，该库能够支持Office2007格式，并且向下兼容2003版的Excel文件。标签进一步强调了PHP在导入和...
详解PHP导入导出CSV文件
12-19
本文将详细介绍如何使用PHP来实现CSV文件的导入和导出功能，以操作MySQL数据库中的学生信息为例。 首先，我们需要创建一个MySQL数据表来存储学生信息。在本例中，我们创建了一个名为`student`的表，包含`id`（主键...
php电子表格 导入/导出excel实例
09-14
在PHP开发中，处理电子表格数据的导入与导出是一项常见的任务，特别是在处理用户上传的数据或者生成报表时。本文将详细讲解如何使用PHP实现Excel文件的导入与导出功能，以便于更好地理解这一主题。 首先，我们要...
thinkphp下 导入导出csv文件
03-23
标题"thinkphp下导入导出csv文件"涉及到的主要知识点包括： 1. CSV文件格式：CSV文件是以逗号分隔的纯文本文件，每个记录占据一行，每个字段之间由逗号分隔。这种格式使得数据可以轻松地在各种应用程序之间传递，如...
execl导入导出
12-27
"Excel导入导出"这一主题涵盖了如何将数据从外部源导入到Excel工作表，以及如何将Excel数据导出到其他格式或系统中。以下是对这个主题的详细阐述： **一、Excel数据导入** 1. **CSV导入**：CSV（逗号分隔值）是一...
phpexcell实现excel导入
12-06
PHP结合phpexcel实现excel导入 不会的参考博客
PHPExcel带图片Excel表格导入导出
03-25
在ThinkPHP5.0.10框架下使用PHPExcel实现带图片excel表格的导入导出功能。application\index\model\ExcelDown.php文件中，第93行加上判断if($res[$i][$data['field'][$j]]!='')改为if($res[$i][$data['field'][$j]]!='') $objDrawing[$i+4]->setWorksheet($objPHPExcel->getActiveSheet());，可防止某一行数据无图片时出现表格图片混乱的情况；在设置图片宽高度位置加上$objDrawing[$i+4]->setResizeProportional(false);，可设置不按原图比例缩放；在113-125行添加用于纵向单元格合并的示例代码/* * 纵向合并单元格 * count($res['users'] 获取分组数量 * list_num 分组中元素数量 * 'B'.$start、'B'.$end 要合并的开始行、结束行 */ /*$start = 4; for($m=0;$mgetActiveSheet()->mergeCells('B'.$start.':B'.$end); $objPHPExcel->getActiveSheet()->mergeCells('C'.$start.':C'.$end); $start += (int)$res['users'][$m]['list_num']; }*/，这段代码不能直接在原demo中使用，需要修改数据结构。
php excel 表格导入
woshihaiyong168的博客
10-26 380
excel表格导入 效果实例: public function lead_act(){ //包含文件 require_once IWEB_PATH.'core/util/excel/PHPExcel.php'; require_once IWEB_PATH.'core/util/excel/PHPExcel/IOFactory.php'; require_o
php导入导出excel表格
继小高的博客
03-09 807
1 简介 1.1 导出   在实际的工作项目中，经常需要将一些重要的数据库中存的数据导出成Excel,比如导出考勤报表，导出财务报表，导出业绩报表，导出销售报表等。 1.2 导入   同时有的项目也需要，将一些Excel数据导入到数据库中。比如银行给的银行流水，销售报表导入的数据库中。这个通常的做法都是使用PHPExcel。   2导出代码 $kit=new kit()
PHP 导入和导出 Excel表格
杰姆小生的博客
04-22 1671
PHP 导入和导出 Excel表格
通过phpexcel导入和导出excel表格
AzerothYang的博客
09-18 1162
首先是导入excel表格：<?php mysql_connect('localhost', 'root', 'root'); mysql_select_db('excel'); mysql_query('set names utf8'); require_once './Classes/PHPExcel.php'; require_once './Classes/PHPExcel/IOFact
phpexcel php导入导出excel表格的简单方法
离殇葬天的专栏
04-15 1268
一、导出excle表格方法 require_once("Classes/PHPExcel.php"); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0)             ->setCellValue('A1', 'Hello')             ->setCellValue('B2',
php导入excel表格数据,php页面导入excel表格数据-php导入excel 怎么获取excel表格数据...
weixin_31241561的博客
03-12 282
PHP如何导出当前页面中的表格至Excel常用的用PHP读取EXCEL的方法有以下三种，各自有各自的优缺点。个人推荐用第三种方法，因为它可以跨平台使用。1. 以.csv格式读取将.xls转换成.csv的文本格式，然后再用PHP分析这个文件，和PHP分析文本没有什么区别。优点：跨平台，效率比较高、可以读写。缺点：只能直接使用.csv的文件，如果经常接受.xls二进制文件的话需要手工转换，不能自动化。...
php表导入,php操作excel表格的导入和导出
weixin_32287387的博客
04-14 227
public function import_batch_send(){header("content-type:text/html;charset=utf-8");//上传excel文件$file = request()->file('file');//将文件保存到public/uploads目录下面$info = $file->validate(['size' => 1048...
PHP实现CSV文件数据导入导出功能详解
本文档详细介绍了如何使用PHP编程语言实现CSV（逗号分隔值）格式文件的数据导入导出功能。在PHP网站开发中，当涉及到数据管理时，批量导入和导出电子表格数据变得至关重要。这类功能有助于数据管理员高效地处理大量...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值