ppypp伦理天堂,91手机在线视频,免费在线观看黄色毛片,夜夜穞天天穞狠狠穞AV美女按摩

聯系官方銷售客服

1835022288

028-61286886

投訴 分享 基于PHPoffice類去做excel表格數據導入導出 139 0
迅睿CMS版本：4.3.4

基于PHPoffice類去做excel表格數據導入導出，可以導出圖片到excel，本人親測一次性導入6000+條簡單數據沒什么問題，很快，在多就沒試過

前端效果如下

導入，要點擊確定才會進行導入數據


image

導出，如果不選擇數據會提示選擇數據然后進行導出


image

image


第一步，下載上傳的附件并將文件解壓到你的項目中，具體目錄如下圖所示。入不放心我上傳的附件，可自行安裝composer進行下載地址如下

https://phpspreadsheet.readthedocs.io/en/stable/


image




PHPoffice


PS：在導入Excel的時候，要使用到文件上傳接口，所以要現在后臺創建一個文件上傳字段，完成之后，要看一下上傳接口對應的參數拿過來，然后替換我對應文件上傳接口中的參數，這個文件上傳字段不要刪除，如果不想要在后臺跟前臺顯示可以選擇隱藏。

這是我使用的文件上傳接口案列

http://census.cc/index.php?s=api&c=file&siteid=1&m=upload&p=020424c201c395dba90596754de3d341&fid=45


如何查看創建字段的上傳接口以及參數

  1. 進入到后臺添加頁面，點f12進入到network中然后進行上傳文件，上傳之后，接口會在這里顯示，打開之后，拿到對應的參數，并填入到，我寫的ajax文件上傳接口中對應的參數即可



image


第二步，在頁面中添加導入導出按鈕，我是加在對應模塊的頭部


image

代碼如下：


image


image

前端js部分代碼：

本部分內容設定了隱藏，需要回復后才能看到

html部分

<ul class="page-breadcrumb">
                    {$menu}
                    <li>
                        <link type="text/css" rel="stylesheet" href="{THEME_PATH}from/layui/css/layui.css" />
                        <div class="layui-upload">
                            <button type="button" style="background: #fff; color: #888; padding: 0 0" class="layui-btn layui-btn-normal" id="excelImport"><i class="fa fa-plus"></i>導入</button>
                            <i style="color: #DDDDDD" class="fa fa-circle"></i>
                        </div>
                    </li>
                    <li>
                        <a onclick="dr_module_excelExport()" id="excelExport" class="">
                            <i class="fa fa-minus"></i> 導出</a>
                        <i class="fa fa-circle"></i>
                    </li>
                </ul>

php部分

<?php namespace Phpcmf\Controllers\Admin;

    require 'vendor/autoload.php'; //寫入口文件

    use PhpOffice\PhpSpreadsheet\Spreadsheet; //引入類，這三個都要引入

    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

/**

 * 二次開發時可以修改本文件，不影響升級覆蓋

 */



class Home extends \Phpcmf\Admin\Module

{



    //獲取要導出的數據id并返回給ajax

    public function dataExport(){

        $arr = $_POST['data'];

        $data = implode(',',$arr);

        return json_encode($data);

    }

    //數據導出

    public function excelExport() {

        $id = $_GET['id'];

        $line = 0;



        $spreadsheet = new Spreadsheet();

        $image = new Drawing();



        $sheet = $spreadsheet->getActiveSheet();



        $sheet->setCellValueByColumnAndRow(1, 1, '戶主關系');//這些數據對應excel的列信息

        $sheet->setCellValueByColumnAndRow(2, 1, '姓名');

        $sheet->setCellValueByColumnAndRow(3, 1, '證件名稱');

        $sheet->setCellValueByColumnAndRow(4, 1, '證件號碼');

        $sheet->setCellValueByColumnAndRow(5, 1, '籍貫');

        $sheet->setCellValueByColumnAndRow(6, 1, '年齡');

        $sheet->setCellValueByColumnAndRow(7, 1, '性別');

        $sheet->setCellValueByColumnAndRow(8, 1, '戶籍地');

        $sheet->setCellValueByColumnAndRow(9, 1, '手機座機');

        $sheet->setCellValueByColumnAndRow(10, 1, '座機電話');

        $sheet->setCellValueByColumnAndRow(11, 1, '出生時間');

        $sheet->setCellValueByColumnAndRow(12, 1, '死亡時間');

        $sheet->setCellValueByColumnAndRow(13, 1, '是否低保');

        $sheet->setCellValueByColumnAndRow(14, 1, '是否享受生活救助');

        $sheet->setCellValueByColumnAndRow(15, 1, '是否暫住');

        $sheet->setCellValueByColumnAndRow(16, 1, '暫住地');

        $sheet->setCellValueByColumnAndRow(17, 1, '是否流動人口');

        $sheet->setCellValueByColumnAndRow(18, 1, '是否矯正人員');

        $sheet->setCellValueByColumnAndRow(19, 1, '是否涉毒人員');

        $sheet->setCellValueByColumnAndRow(20, 1, '是否殘疾');

        $sheet->setCellValueByColumnAndRow(21, 1, '用人單位');

        $sheet->setCellValueByColumnAndRow(22, 1, '社保信息');

        $sheet->setCellValueByColumnAndRow(23, 1, '個人健康狀況');

        $sheet->setCellValueByColumnAndRow(24, 1, '實際居住地');

        $sheet->setCellValueByColumnAndRow(25, 1, '實際居住詳細地址');

        $sheet->setCellValueByColumnAndRow(26, 1, '照片');





        $styleArray = [

            'alignment' => [

                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

            ],

        ];



        //查詢并獲取要導出的數據

        $data = \Phpcmf\Service::M()->db->table(SITE_ID.'_census')

            ->select('

                relation,name,head,card_name,card_id,hometown,age,sex,domicile,phone,

                tel,birthday,deadtime,is_guarantee,is_assistance,is_residence,temporary,

                population,correction,is_poison,is_deformity,employer,shebaoxinxi,health,

                residence,address

            ')

            ->where('id in('.$id.')')

            ->get()->getResultArray();



        $relation = dr_field_options(16);

        $card_name = dr_field_options(19);

        $sex = dr_field_options(23);

        $is_guarantee = dr_field_options(30);

        $is_assistance = dr_field_options(31);

        $is_residence = dr_field_options(32);

        $population = dr_field_options(34);

        $correction = dr_field_options(35);

        $is_poison = dr_field_options(36);

        $is_deformity = dr_field_options(37);

        $shebaoxinxi = dr_field_options(39);

        $health = dr_field_options(40);



        //通過循環去匹配數據

        for ($i = 0; $i < count($data); $i++) {

            $line = $i + 2;//代表從第幾行開始



            //$sheet->setCellValueByColumnAndRow(1)中的1，2，3代表上面的那一列

            $sheet->setCellValueByColumnAndRow(1, $line, $relation[$data[$i]['relation']]);

            $sheet->setCellValueByColumnAndRow(2, $line, $data[$i]['name']);

            $sheet->setCellValueByColumnAndRow(3, $line, $card_name[$data[$i]['card_name']]);

            $sheet->setCellValueByColumnAndRow(4, $line, $data[$i]['card_id']);

            $sheet->setCellValueByColumnAndRow(5, $line, $data[$i]['hometown']);

            $sheet->setCellValueByColumnAndRow(6, $line, $data[$i]['age']);

            $sheet->setCellValueByColumnAndRow(7, $line, $sex[$data[$i]['sex']]);

            $sheet->setCellValueByColumnAndRow(8, $line, $data[$i]['domicile']);

            $sheet->setCellValueByColumnAndRow(9, $line, $data[$i]['phone']);

            $sheet->setCellValueByColumnAndRow(10, $line, $data[$i]['tel']);

            $sheet->setCellValueByColumnAndRow(11, $line, dr_date($data[$i]['birthday'],'Y-m-m H:i:s'));

            $sheet->setCellValueByColumnAndRow(12, $line, dr_date($data[$i]['deadtime'],'Y-m-m H:i:s'));

            $sheet->setCellValueByColumnAndRow(13, $line, $is_guarantee[$data[$i]['is_guarantee']]);

            $sheet->setCellValueByColumnAndRow(14, $line, $is_assistance[$data[$i]['is_assistance']]);

            $sheet->setCellValueByColumnAndRow(15, $line, $is_residence[$data[$i]['is_residence']]);

            $sheet->setCellValueByColumnAndRow(16, $line, $data[$i]['temporary']);

            $sheet->setCellValueByColumnAndRow(17, $line, $population[$data[$i]['population']]);

            $sheet->setCellValueByColumnAndRow(18, $line, $correction[$data[$i]['correction']]);

            $sheet->setCellValueByColumnAndRow(19, $line, $is_poison[$data[$i]['is_poison']]);

            $sheet->setCellValueByColumnAndRow(20, $line, $is_deformity[$data[$i]['is_deformity']]);

            $sheet->setCellValueByColumnAndRow(21, $line, $data[$i]['employer']);

            $sheet->setCellValueByColumnAndRow(22, $line, $shebaoxinxi[$data[$i]['shebaoxinxi']]);

            $sheet->setCellValueByColumnAndRow(23, $line, $health[$data[$i]['health']]);

            $sheet->setCellValueByColumnAndRow(24, $line, dr_linkagepos('address', $data[$i]['residence'], ' - '));

            $sheet->setCellValueByColumnAndRow(25, $line, $data[$i]['address']);



            $url =  parse_url(dr_thumb($data[$i]['head']));//獲取圖片對應的地址



            //使用圖片導出類

            $drawing[$line] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();

            $drawing[$line]->setName('頭像');

            $drawing[$line]->setDescription('頭像');

            $drawing[$line]->setPath('.'.$url['path']);

            $drawing[$line]->setWidth(100);

            $drawing[$line]->setHeight(100);

            $drawing[$line]->setCoordinates('Z'.$line);//z代表某一咧

            $drawing[$line]->setOffsetX(0);

            $drawing[$line]->setOffsetY(0);

            $drawing[$line]->setWorksheet($spreadsheet->getActiveSheet());

            $spreadsheet->getActiveSheet()->getRowDimension($line)->setRowHeight(100);//設置高度

        }



        $filename = '戶籍信息管理表('.date('YmdHis',SYS_TIME).').xlsx';//導出表格名稱

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Content-Disposition: attachment;filename="'.$filename.'"');

        header('Cache-Control: max-age=0');



        //設置樣式

        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12);

        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);

        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(25);

        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(5);

        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5);

        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(18);

        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(18);

        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(9);

        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(17);

        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(9);

        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(35);

        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(13);

        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(13);

        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(13);

        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(9);

        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(35);

        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(85);

        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(13);

        $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(35);

        $spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(35);

        $spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(26);



        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

        $writer->save('php://output');

    }



    //表格導入

    public function excelImport() {

        $excel = parse_url($_POST['url']);

        $type = ucfirst($_POST['type']);

        $fileid = $_POST['id'];

        $this->db = \Config\Database::connect('default');



        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($type);

        $reader->setReadDataOnly(TRUE);

        $spreadsheet = $reader->load('.'.$excel['path']); //載入excel表格



        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow(); // 總行數

        $highestColumn = $worksheet->getHighestColumn(); // 總列數

        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5



        $lines = $highestRow - 1;

        if ($lines <= 0) {

            exit('Excel表格中沒有數據');

        }



        $sql = "INSERT INTO 'dr_".SITE_ID."_census' ('relation','name','head','card_name','card_id','hometown','age','sex','domicile','phone',

                'tel','birthday','deadtime','is_guarantee','is_assistance','is_residence','temporary',

                'population','correction','is_poison','is_deformity','employer','shebaoxinxi','health',

                'residence','address') VALUES ";

        $data = [];



        $relation = dr_field_options(16);

        $card_name = dr_field_options(19);

        $sex = dr_field_options(23);

        $is_guarantee = dr_field_options(30);

        $is_assistance = dr_field_options(31);

        $is_residence = dr_field_options(32);

        $population = dr_field_options(34);

        $correction = dr_field_options(35);

        $is_poison = dr_field_options(36);

        $is_deformity = dr_field_options(37);

        $shebaoxinxi = dr_field_options(39);

        $health = dr_field_options(40);



        //獲取表格中的數據

        for ($row = 0; $row < $lines; $row++) {

            $data[$row]['url'] = $worksheet->getCellByColumnAndRow(1, $row+2)->getValue();

            foreach ($relation as $key => $re) {

                if ($re === $worksheet->getCellByColumnAndRow(2, $row+2)->getValue()) {

                    $data[$row]['relation'] = $key;

                }

            }

            $data[$row]['name'] = $worksheet->getCellByColumnAndRow(3, $row+2)->getValue();

            foreach ($card_name as $key => $ca) {

                if ($ca === $worksheet->getCellByColumnAndRow(4, $row+2)->getValue()) {

                    $data[$row]['card_name'] = $key;

                }

            }

            $data[$row]['card_id'] = $worksheet->getCellByColumnAndRow(5, $row+2)->getValue();

            $data[$row]['hometown'] = $worksheet->getCellByColumnAndRow(6, $row+2)->getValue();

            $data[$row]['age'] = $worksheet->getCellByColumnAndRow(7, $row+2)->getValue();

            foreach ($sex as $key => $se) {

                if ($se === $worksheet->getCellByColumnAndRow(8, $row+2)->getValue()) {

                    $data[$row]['sex'] = $key;

                }

            }

            $data[$row]['domicile'] = $worksheet->getCellByColumnAndRow(9, $row+2)->getValue();

            $data[$row]['phone'] = $worksheet->getCellByColumnAndRow(10, $row+2)->getValue();

            $data[$row]['tel'] = $worksheet->getCellByColumnAndRow(11, $row+2)->getValue();

            $data[$row]['birthday'] = strtotime($worksheet->getCellByColumnAndRow(12, $row+2)->getValue());

            $data[$row]['deadtime'] = strtotime($worksheet->getCellByColumnAndRow(13, $row+2)->getValue());

            foreach ($is_guarantee as $key => $is_gu) {

                if ($is_gu === $worksheet->getCellByColumnAndRow(14, $row+2)->getValue()) {

                    $data[$row]['is_guarantee'] = $key;

                }

            }

            foreach ($is_assistance as $key => $is_as) {

                if ($is_as === $worksheet->getCellByColumnAndRow(15, $row+2)->getValue()) {

                    $data[$row]['is_assistance'] = $key;

                }

            }

            foreach ($is_residence as $key => $is_re) {

                if ($is_re === $worksheet->getCellByColumnAndRow(16, $row+2)->getValue()) {

                    $data[$row]['is_residence'] = $key;

                }

            }

            $data[$row]['temporary'] = $worksheet->getCellByColumnAndRow(17, $row+2)->getValue();

            foreach ($population as $key => $po) {

                if ($po === $worksheet->getCellByColumnAndRow(18, $row+2)->getValue()) {

                    $data[$row]['population'] = $key;

                }

            }

            foreach ($correction as $key => $co) {

                if ($co === $worksheet->getCellByColumnAndRow(19, $row+2)->getValue()) {

                    $data[$row]['correction'] = $key;

                }

            }

            foreach ($is_poison as $key => $is_po) {

                if ($is_po === $worksheet->getCellByColumnAndRow(20, $row+2)->getValue()) {

                    $data[$row]['is_poison'] = $key;

                }

            }

            foreach ($is_deformity as $key => $is_de) {

                if ($is_de === $worksheet->getCellByColumnAndRow(21, $row+2)->getValue()) {

                    $data[$row]['is_deformity'] = $key;

                }

            }

            $data[$row]['employer'] = $worksheet->getCellByColumnAndRow(22, $row+2)->getValue();

            foreach ($shebaoxinxi as $key => $she) {

                if ($she === $worksheet->getCellByColumnAndRow(23, $row+2)->getValue()) {

                    $data[$row]['shebaoxinxi'] = $key;

                }

            }

            foreach ($health as $key => $he) {

                if ($he === $worksheet->getCellByColumnAndRow(24, $row+2)->getValue()) {

                    $data[$row]['health'] = $key;

                }

            }

            $address = explode(' - ',$worksheet->getCellByColumnAndRow(25, $row+2)->getValue());

            foreach ($address as $add) {}

            $area = \Phpcmf\Service::M()->db->table('linkage_data_1')->where('name', $add)->get()->getResultArray();

            $data[$row]['residence'] = $area[0]['id'];

            $data[$row]['address'] = $worksheet->getCellByColumnAndRow(26, $row+2)->getValue();

            $data[$row]['head'] = $worksheet->getCellByColumnAndRow(27, $row+2)->getValue();

            $data[$row]['catid'] = 1;

            $data[$row]['inputip'] = \Phpcmf\Service::L('input')->ip_address();

            $data[$row]['inputtime'] = SYS_TIME;

            $data[$row]['updatetime'] = SYS_TIME;

        }

        //將從Excel表格中獲取到的數據插入到數據庫中

        foreach ($data as $key => $da){

            if ($da['url'] == null || $da['url'] == ' '){

                $da['fid'] = 0;

                $info = $this->db->table(SITE_ID.'_census')->insert($da);

                if ($info){

                    $fid = $this->db->insertID();

                    $card_id = $da['card_id'];

                    if ($info) {

                        $code = 1;

                    }else{

                        $code = 0;

                        $total = $key+2;

                    }

                }

            }else{

                if ($da['url'] == $card_id){

                    $da['fid'] = $fid;

                    $da['url'] = ' ';

                    $info = $this->db->table(SITE_ID.'_census')->insert($da);

                    if ($info) {

                        $code = 1;

                    }else{

                        $code = 0;

                        $total = $key+2;

                    }

                }

            }

        }

        if ($info) {

            \Phpcmf\Service::M('Attachment')->file_delete($this->member['id'],$fileid);//刪除附件，如果導入成功之后不想刪除附件，刪除這句話即可

            return json_encode(['code'=>$code]);

        }else {

            \Phpcmf\Service::M('Attachment')->file_delete($this->member['id'],$fileid);

            return json_encode(['code'=>$code],['total'=>$total]);

        }

    }

}
解決方案