thinkphp6 + phpexcel 导入导出数据，设置特殊表格

本文主要是介绍thinkphp6 + phpexcel 导入导出数据，设置特殊表格，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

第一步：安装excel，使用composer安装，我的是在window下，直接cmd切换到项目下面，输入

composer require phpoffice/phpexcel，然后就等待安装完成。如下图：

第二步：引入相关类

<?php
namespace app\admin\controller;
use app\admin\model\Bingli;
use app\admin\model\Moneyinfo;
use think\Controller;
use think\Validate;
use think\facade\Request;
use think\facade\Db;
use think\facade\Session;
use think\facade\View;
use PHPExcel_IOFactory;   //这个是三方类

class Binglii extends Base
{ 
    /*
     * 批量导入数据详情
     */
    public function upAgent(){
        if(Request::param('html') == false){            
            // 获取表单上传文件
            $file = request()->file('file');
            if(empty($file)){
                return json(['info'=>'请选择上传文件！','status'=>0]);
            }

            // 移动到框架应用根目录/public/upload/ 目录下，并修改文件名为时间戳
            $savename = \think\facade\Filesystem::putFile('excel', $file, 'time');
            // 文件名称
            $info = explode('/', $savename);                     
            $file = public_path().'public/upload/excel/'.$info['excel'];
      
            //导入 
            $objPHPExcel = PHPExcel_IOFactory::load($file); //获取sheet表格数目
            //$objReader = PHPExcel_IOFactory::createReader('Excel5');
            //$objPHPExcel = $objReader->load($file,$encode='utf-8');
            $sheetCount = $objPHPExcel->getSheetCount(); //默认选中sheet0表
            $sheetSelected = 0;
            $objPHPExcel->setActiveSheetIndex($sheetSelected); 
            //获取表格行数
            $rowCount = $objPHPExcel->getActiveSheet()->getHighestRow(); 
            //获取表格列数
            $columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn();
            $dataArr = array();
            /* 循环读取每个单元格的数据 */
            for ($i = 2; $i <= $rowCount; $i++) {
                $data['mi_num'] = $objPHPExcel->getActiveSheet()->getCell("D3")->getValue();   
                $data['mi_time'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getFormattedValue(); // getFormattedValue 获取本来的格式
                $data['mi_chargeItems'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
                $data['mi_unit'] = $objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
                $data['mi_quantity'] = $objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
                $data['mi_unitPrice'] = $objPHPExcel->getActiveSheet()->getCell("R".$i)->getValue();
                $data['mi_money'] = $objPHPExcel->getActiveSheet()->getCell("V".$i)->getValue();
                $data['mi_payCode'] = $objPHPExcel->getActiveSheet()->getCell("X".$i)->getValue();
                $data['mi_cityCode'] = $objPHPExcel->getActiveSheet()->getCell("AD".$i)->getValue();
                $res[] = $data;  //数据赋值到数组
            }

            $lennum = 400; // 400条数据插入一次
            //记录一共插入了多少条数据
            $insertCount = 0;
            $count = count($res);  //总共多少条数据          
            $limit = ceil($count/$lennum);  //需要执行多少次插入数据的操作
            for($i = 1;$i <= $limit; $i++){
                $offset = ($i-1)*$lennum; //当前是第几次遍历，第一条数据是哪一条
                //从数组的第几条开始本次数据插入
                $datac = array_slice($res,$offset,$lennum);
                //模型可以插入大批量的数据
                $moneyinfo = new Moneyinfo();
                $result = $moneyinfo->saveAll($datac); 
                $insertCount = count($result) + $insertCount;
            }
   
            //删除excel文件
            unlink($file);
            if($insertCount > 0){
                return json(['info'=>'文件上传成功，已经导入'.$insertCount.'条数据','status'=>1]); 
            }else{
                return json(['info'=>'导入第'.$insertCount.'条失败','status'=>0]);  
            }             
        }
    }

    //数据导出
    public function downCsvB(){
        //实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);

        //数据
        $info = Request::param();        
        $id = $info['id'];
        $map1[] = ['bl_num','=',$id];
        $map2[] = ['mi_num','=',$id];
        $map3[] = ['mi_num','=',$id];
        $data1 = Db::table('zdb_bingli')->field('bl_name,bl_depart,bl_bed,bl_socialSecNum,bl_socialSecPayment,bl_balance,bl_admissionTime,bl_dischargeTime')->where($map1)->find();
        
        //引入model
        $moneyinfoModel = new Moneyinfo();
        $data = $moneyinfoModel->tab1('','',$id);

        $maxTime = Db::table('zdb_moneyinfo')->where($map3)->order('mi_time asc')->limit(0,1)->value('mi_time');
        $minTime = Db::table('zdb_moneyinfo')->where($map3)->order('mi_time desc')->limit(0,1)->value('mi_time');

        $totalm = Db::table('zdb_moneyinfo')->where($map2)->sum('mi_money'); //总金额
        $totalm = round($totalm,2);  //四舍五入
        $time1 = empty($maxTime) ? $data1['bl_admissionTime'] : $maxTime;  //时间段       
        $time2 = empty($minTime) ? $data1['bl_dischargeTime'] : $minTime;  //时间段 
        
        //设置表格头（即excel表格的第一行）
        // 合并 单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->getActiveSheet()->mergeCells('E2:F2');
        $objPHPExcel->getActiveSheet()->mergeCells('C3:G3');

        $objPHPExcel->getActiveSheet(0)->SetCellValue('A1', 'xxxxxxxxxxx费用明细清单');      
        $objPHPExcel->getActiveSheet(0)->SetCellValue('A2', '姓名:'.$data1['bl_name']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('B2', '病区:');        
        $objPHPExcel->getActiveSheet(0)->SetCellValue('C2', $data1['bl_depart']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('E2', '社保号:'.$data1['bl_socialSecNum']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('H2', '床号:'.$data1['bl_bed']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('A3', '住院号:'.$id);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('B3', '时间段:');
        $objPHPExcel->getActiveSheet(0)->SetCellValue('C3', date('Y/m/d H:i',$time1) .'至'. date('Y/m/d H:i',$time2));
        $objPHPExcel->getActiveSheet(0)->SetCellValue('H3', '病人签字:');
       
        //表头
        $objPHPExcel->getActiveSheet(0)->SetCellValue('A4','业务日期')->SetCellValue('B4','收费项目')->SetCellValue('C4','单位')->SetCellValue('D4','数量')->SetCellValue('E4','单价')->SetCellValue('F4','金额')->SetCellValue('G4','收费项目编码')->SetCellValue('H4','国家编码');

        $styleThinBlackBorder = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            ),
        );      

        //边框设置
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A4:H4')->applyFromArray($styleThinBlackBorder);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        $num = 4;
        for ($i = 0; $i < count($data); $i++) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.($i + 5),date('Y-m-d H:i',$data[$i]['mi_time']));
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.($i + 5),$data[$i]['mi_chargeItems']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.($i + 5),$data[$i]['mi_unit']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.($i + 5),$data[$i]['mi_quantity']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.($i + 5),$data[$i]['mi_unitPrice']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.($i + 5),$data[$i]['mi_money']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.($i + 5),' '.$data[$i]['mi_payCode']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H'.($i + 5),' '.$data[$i]['mi_cityCode']);
            $num++;
            //边框设置
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($i + 5).':H'.($i + 5))->applyFromArray($styleThinBlackBorder);
        }        
        // 合并 单元格
        $objPHPExcel->getActiveSheet()->mergeCells('C'.($num+2).':D'.($num+2));
        $objPHPExcel->getActiveSheet()->mergeCells('E'.($num+2).':F'.($num+2));
        $objPHPExcel->getActiveSheet(0)->SetCellValue('E'.($num+1), '合计');      
        $objPHPExcel->getActiveSheet(0)->SetCellValue('F'.($num+1), $totalm);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('B'.($num+2), '交款:'.($totalm - $data1['bl_socialSecPayment']));
        $objPHPExcel->getActiveSheet(0)->SetCellValue('C'.($num+2), '入院总费用:'.$totalm);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('E'.($num+2), '结算报销:'.$data1['bl_socialSecPayment']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('G'.($num+2), '余额:'.$data1['bl_balance']);
        $objPHPExcel->getActiveSheet(0)->SetCellValue('A'.($num+3), '制表人:'.$this->uname);
        // 水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A4:H4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);        
        //右对齐
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E'.($num+2).':G'.($num+2))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(28);
        // 设置行高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); //设置默认行高
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20); //第一行行高
        $objPHPExcel->getActiveSheet()->getRowDimension(($num+1))->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(($num+2))->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension(($num+3))->setRowHeight(20);

        // 字体大小和样式
        //$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(9);
        //第一行是否加粗  
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        
        $styleThinBlackBorderTB = array(
            'borders' => array(
                'top' => array( // 设置顶部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
                'bottom' => array( //设置底部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            ),
        );

        // 边框
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($num+2).':H'.($num+2))->applyFromArray($styleThinBlackBorderTB);       
        // 设置垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //设置保存的Excel表格名称
        $filename = 'user'.date('Ymd_his').'.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('user');
        iconv("utf-8", "gb2312", $filename);  //解决乱码的问题
        ob_end_clean();  //解决乱码核心
        //设置浏览器窗口下载表格
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename . '"');        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //生成excel文件
        //下载文件在浏览器窗口
        $objWriter->save('php://output');       
        exit();

    }
}

第三步：Model类

<?php

namespace app\admin\model;

use think\Model;

/**
 * @mixin think\Model
 */
class Moneyinfo extends Model
{
    public function tab1($startTime = 0,$endTime = 0,$mi_num = 0,$mi_chargeItems = null){
    	//查询条件
        if(!empty($startTime) && !empty($endTime)){ //时间
            $startTime = strtotime($startTime);
            $endTime = strtotime($endTime);
            $map[] = ['mi_time','between',[$startTime,$endTime]];             
        }
        //查询条件
        if(!empty($mi_chargeItems)){ //项目
            $map[] = ['mi_chargeItems','like','%'.$mi_chargeItems.'%'];
        }
        $map[] = ['mi_num','=',$mi_num];
        
        //查询  Moneyinfo 是数据表名
     	$res = Moneyinfo::field("mi_num,mi_time,mi_chargeItems,mi_unit,mi_quantity,mi_unitPrice,mi_money,mi_payCode,mi_cityCode")->where($map)->select()->toArray();
    	//echo Moneyinfo::getLastSql();
        //返回数据
    	return $res;
    }
}

关于getBottom不生效，看了一下源代码，改了一下

原写法：$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)

正确写法参考上面数据导出的底部边框。

导出的数据结构如下：

这篇关于thinkphp6 + phpexcel 导入导出数据，设置特殊表格的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！