成年人的在线免费视频,婷婷91精品国产91久久综合,人妻精品人妻888,性感美女视频一区二区三区,国产熟女精品视频免费看,国内精品九九久久精品小草,中文字幕日韩精品欧美丝袜,午夜精品久久久久蜜桃毛片,91久久在线视频观看

基于PHPoffice類去做excel表格數(shù)據(jù)導(dǎo)入導(dǎo)出，可以導(dǎo)出圖片到excel，本人親測(cè)一次性導(dǎo)入6000+條簡(jiǎn)單數(shù)據(jù)沒什么問題，很快，在多就沒試過

前端效果如下

導(dǎo)入，要點(diǎn)擊確定才會(huì)進(jìn)行導(dǎo)入數(shù)據(jù)

導(dǎo)出，如果不選擇數(shù)據(jù)會(huì)提示選擇數(shù)據(jù)然后進(jìn)行導(dǎo)出

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

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

PHPoffice

PS：在導(dǎo)入Excel的時(shí)候，要使用到文件上傳接口，所以要現(xiàn)在后臺(tái)創(chuàng)建一個(gè)文件上傳字段，完成之后，要看一下上傳接口對(duì)應(yīng)的參數(shù)拿過來，然后替換我對(duì)應(yīng)文件上傳接口中的參數(shù)，這個(gè)文件上傳字段不要?jiǎng)h除，如果不想要在后臺(tái)跟前臺(tái)顯示可以選擇隱藏。

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

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

如何查看創(chuàng)建字段的上傳接口以及參數(shù)

1. 進(jìn)入到后臺(tái)添加頁(yè)面，點(diǎn)f12進(jìn)入到network中然后進(jìn)行上傳文件，上傳之后，接口會(huì)在這里顯示，打開之后，拿到對(duì)應(yīng)的參數(shù)，并填入到，我寫的ajax文件上傳接口中對(duì)應(yīng)的參數(shù)即可

   
   

第二步，在頁(yè)面中添加導(dǎo)入導(dǎo)出按鈕，我是加在對(duì)應(yīng)模塊的頭部

代碼如下：

前端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>導(dǎo)入</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> 導(dǎo)出</a>
                        <i class="fa fa-circle"></i>
                    </li>
                </ul>

php部分

<?php namespace Phpcmf\Controllers\Admin;

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

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

    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

/**

 * 二次開發(fā)時(shí)可以修改本文件，不影響升級(jí)覆蓋

 */



class Home extends \Phpcmf\Admin\Module

{



    //獲取要導(dǎo)出的數(shù)據(jù)id并返回給ajax

    public function dataExport(){

        $arr = $_POST['data'];

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

        return json_encode($data);

    }

    //數(shù)據(jù)導(dǎo)出

    public function excelExport() {

        $id = $_GET['id'];

        $line = 0;



        $spreadsheet = new Spreadsheet();

        $image = new Drawing();



        $sheet = $spreadsheet->getActiveSheet();



        $sheet->setCellValueByColumnAndRow(1, 1, '戶主關(guān)系');//這些數(shù)據(jù)對(duì)應(yīng)excel的列信息

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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





        $styleArray = [

            'alignment' => [

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

            ],

        ];



        //查詢并獲取要導(dǎo)出的數(shù)據(jù)

        $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);



        //通過循環(huán)去匹配數(shù)據(jù)

        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']));//獲取圖片對(duì)應(yīng)的地址



            //使用圖片導(dǎo)出類

            $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);//設(shè)置高度

        }



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

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

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

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



        //設(shè)置樣式

        $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');

    }



    //表格導(dǎo)入

    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(); // 總行數(shù)

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

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



        $lines = $highestRow - 1;

        if ($lines <= 0) {

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

        }



        $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);



        //獲取表格中的數(shù)據(jù)

        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表格中獲取到的數(shù)據(jù)插入到數(shù)據(jù)庫(kù)中

        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);//刪除附件，如果導(dǎo)入成功之后不想刪除附件，刪除這句話即可

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

        }else {

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

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

        }

    }

}