php Spreadsheet 导出功能

最新推荐文章于 2024-04-28 11:25:26 发布

admin⁠

最新推荐文章于 2024-04-28 11:25:26 发布

阅读量355

点赞数

文章标签： php 开发语言

本文链接：https://blog.csdn.net/qq_19598963/article/details/129558745

版权

这里需要注意的是 $col = 1 ，必须要等于1，如果设置为0的话，首个字段导出文件的时候跑到最后去了
在这里插入图片描述

    public function export_data($ids = ''){
        set_time_limit(0);

        $row = $this->model->get($ids);

        $excel = new Spreadsheet();
        $excel->getProperties()
            ->setCreator("KAE")
            ->setLastModifiedBy("KAE")
            ->setTitle("KAE")
            ->setSubject("KAE");
        $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $excel->getDefaultStyle()->getFont()->setSize(12);



        $excel->getDefaultStyle()->applyFromArray(
            array(
                'fill'      => array(
                    'type'  => Fill::FILL_SOLID,
                    'color' => array('rgb' => '000000')
                ),
                'font'      => array(
                    'color' => array('rgb' => "000000"),
                ),
                'alignment' => array(
                    'vertical'   => Alignment::VERTICAL_CENTER,
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'indent'     => 1
                ),
                'borders'   => array(
                    'allborders' => array('style' => Border::BORDER_THIN),
                )
            ));


        $worksheet = $excel->setActiveSheetIndex(0);
        $worksheet->setTitle('DOM项目：'.$row['name']);


        $line = 1;
        $list = [];
        \app\admin\model\produce\Domsku::where('produce_dom_id',$ids)
            ->field('id,sku,goods_name,specification_name,unit_name,number,mark')
            ->chunk(100, function ($items) use (&$list, &$line, &$worksheet) {

                $list = $items = collection($items)->toArray();
                foreach ($items as $index => $item) {
                    unset($item['id']);
                    $line++;
                    $col = 1;
                    foreach ($item as $field => $value) {
                        $worksheet->setCellValueByColumnAndRow($col, $line, $value);
                        $worksheet->getStyleByColumnAndRow($col, $line)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                        $col++;
                    }
                }

            });

        $domskuFormList = (new \app\admin\model\produce\Domsku())->getDomSkuLangList();
        $first = array_keys($list[0]);

        $fields = [];
        foreach ($first as $k => $ele) {
            $fields[] = $domskuFormList[$ele]??'';
        }
        foreach ($fields as $index => $item) {
            $worksheet->setCellValueByColumnAndRow($index, 1, __($item));
        }
        $excel->createSheet();
        $title = 'DOM-'.$row['name'].'-'.date("Y-m-d H-i-s");

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        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


        $objWriter = IOFactory::createWriter($excel, 'Xlsx');
        $objWriter->save('php://output');
        return;


    }