第一步：安装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)
正确写法参考上面数据导出的底部边框。
导出的数据结构如下：
转载：https://blog.csdn.net/qq_37682202/article/details/117400130
查看评论