phpoffice/phpspreadsheet导出

protected $is_export = false;//是否开启导出
protected $exportName = '';//导出excel名称
protected $exportField = '';//导出excel名称

public function create()
    {
        if(!$this->is_export) $this->error('未开启导出');
        $request = $this->request;
        try{
            $page = $request->post('page', 1);

            $tableName = $this->model->getTable();//获取表名
            $prefix = getenv('DB_PREFIX');//获取表前缀
            $tables = Db::select("SHOW FULL COLUMNS FROM `{$prefix}{$tableName}`");//查询表结构
            $exportField = [];
            foreach ($tables as $key=>$value){
                $exportField[] = $value->Comment?:$value->Field;//处理表结构
            }
            $page_pre = $request->post('limit')??$this->model->count();
            $data = $this->model->index( $this->filter, $page, $page_pre, $this->field, $this->order, $this->join);
            $exportName = "导出".(!empty($this->exportName)?$this->exportName:$tableName)."信息".date('Y-m-d',time());
            if(!empty($this->exportField)){
                $exportField = $this->exportField;
            }
            $path = Excel::excelExport($exportName,$exportField,$data->toArray()['data']);
            $this->back['path'] = $path;
        }catch (\Exception $e){
            $this->error($e->getMessage());
        }
        $this->success('导出成功');
    }

public static function excelExport($title="",$headArr = [], $data = [],$fileName = '') {
        $fileName       .= "_" . date("Y_m_d", time());
        $spreadsheet    = new Spreadsheet();

        $objPHPExcel    = $spreadsheet->getActiveSheet();
        $objPHPExcel->setTitle($title);
        $objPHPExcel->setCellValueByColumnAndRow(1, 1, $title);
        // 设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) { // 列写入
                $objPHPExcel->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $path = public_path().'/excel/'.$title.'.xls';
        $writer->save($path);
        //删除临时的sheet
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        return $path;
    }
275 3 2
3个评论

$this->model->index()是封装的查询数据操作

  • 暂无评论
cbasil

列写入值那里还是要加入设置单元格格式,不然长数字就会变成科学计数法

$objPHPExcel->setCellValue(chr($span) . $column,$value,PHPExcel_Cell_DataType::TYPE_STRING);
  • 旧城·离人·半城烟 2022-08-24

    你这个在最新版已经没有第三个参数了,第一种$objPHPExcel->setCellValueExplicitByColumnAndRow($keyName+1,$key+2,$value,DataType::TYPE_STRING); $keyName必须是int,$key类型int,第二种$objPHPExcel->setCellValue(chr($span) . $column, ' '.$value);

  • 旧城·离人·半城烟 2022-08-24

    我这里只能使用第二种,因为$keyName是对应数据库字段,不可能是int类型

  • cbasil 2022-08-26

    嗯嗯,也可以先判断类型再设置,

liziyu

曾经看过一篇文章是说yield生成器的(看的去里雾里的),如果大佬能把它融入进去就更完美了!

旧城·离人·半城烟

586
积分
0
获赞数
0
粉丝数
2019-12-04 加入
🔝