第一步：安装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'];//2022-08-25 补充，最近发现老是报错PHPExcel_IOFactory找不到，于是单独引入文件require_once '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';require_once '../vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';//导入 $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(){//2022-08-25 补充，最近发现老是报错PHPExcel_IOFactory找不到，于是单独引入文件require_once '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';require_once '../vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';//实例化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类
<?phpnamespace 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)
正确写法参考上面数据导出的底部边框。
导出的数据结构如下：
