thinkphp6PhpSpreadsheet导入到excel

原创

小哥 3年前 (2022-11-05) 阅读数 150 #PHP

文章标签 thinkphp

<?php

namespace app\adminapi\controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use think\facade\Db;
use think\facade\Request;
use app\lib\UtilsLayer;
use app\service\AdminLogs;

/**

审批统计导出模块
*/
class ShenpitongjiExcel
{
public function __construct(){
//处理半小时前的废弃数据
$before_time = time()-1800;
Db::table(fee_temp_baobiao)->where(sign_time,<=,"$before_time")->delete();
Db::table(fee_temp_baobiao_time)->where(sign_time,<=,"$before_time")->delete();
}

/**

获取报表临时导出token
*/
public function get_baobiao_token(){
$arr[access_token] = time().mt_rand(100000,999999);
if(Db::table(fee_temp_baobiao_token)->insert($arr)){
//保存管理员行为日志
$work_userinfo = Request::middleware(baoxiao_userinfo);
$content = "审批统计报表导出";
AdminLogs::create_admin_log($work_userinfo, $content);

return returnSuccessJson($arr,生成成功);
}else{
return returnErrorJson(暂时无法导出，请稍后再试);
}
}

/**

获取明细临时导出token
*/
public function get_mingxi_token(){
$arr[access_token] = time().mt_rand(100000,999999);
if(Db::table(fee_temp_baobiao_token)->insert($arr)){
//保存管理员行为日志
$work_userinfo = Request::middleware(baoxiao_userinfo);
$content = "审批统计明细导出";
AdminLogs::create_admin_log($work_userinfo, $content);

return returnSuccessJson($arr,生成成功);
}else{
return returnErrorJson(暂时无法导出，请稍后再试);
}
}

/**

审批统计报表导出
http://fee.local/adminapi/baobiao\_export
*/
public function baobiao_export()
{
$sign_time = time();
//报表临时导出，限制1秒内只能1人导出用
if(Db::table(fee_temp_baobiao_time)->where(sign_time,"$sign_time")->value(id)){
$utilsLayer = new UtilsLayer();
$utilsLayer->alertError(其他人正在导出，请1秒后再试);
}
Db::table(fee_temp_baobiao_time)->insert([sign_time=>$sign_time]);

$access_token = Request::param(access_token,);
if(Db::table(fee_temp_baobiao_token)->where(access_token,"$access_token")->value(id)){
Db::table(fee_temp_baobiao_token)->where(access_token,"$access_token")->delete();
}else{
$utilsLayer = new UtilsLayer();
$utilsLayer->alertError(抱歉，您无权限操作);
}

//先清除报表导出临时计算表
//Db::query(TRUNCATE fee\_temp\_baobiao);

//开始数据导出
$list = Db::table(fee_temp_baobiao)->where(is_handle,1)->where(sign_time,"$sign_time")->order(id,asc)->select()->toArray();
if(!$list){
//删除本次临时数据
Db::table(fee_temp_baobiao)->where(sign_time,"$sign_time")->delete();
$utilsLayer = new UtilsLayer();
$utilsLayer->alertError(抱歉，没有要导出的数据);
//return 没有要导出的数据;
}
//删除本次临时数据
Db::table(fee_temp_baobiao)->where(sign_time,"$sign_time")->delete();

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// 合并单元格并赋值
$titleRow = [年份,月份,战区,市场,办公室,店长姓名,门店编号,门店名称,
开店时间,关店时间,人数,一、收入,产品收入,产品退货,团购收入,其他收入,二、成本,
产品成本,退货成本,团购成本,其他成本,三、费用];
$j = 1;
for ($i=A;$i<=V;$i++){
$worksheet->mergeCells("{$i}1:{$i}2");
$worksheet->setCellValueByColumnAndRow($j,1,$titleRow[$j-1]);
$j++;
}
$worksheet->mergeCells("W1:X1");
$worksheet->setCellValue(W1,人工成本);
$worksheet->mergeCells("Y1:AA1");
$worksheet->setCellValue(Y1,拓客费用);
$worksheet->mergeCells("AB1:AH1");
$worksheet->setCellValue(AB1,门店产生的营业费用);
$titleRow = [运费,各地市场除门店外的费用,员工福利费,差旅费,外联费、团建费,总部除工资社保外费用,财务费用,税费,其他费用];
$j = 0;
for ($i=I;$i<=Q;$i++){
$worksheet->setCellValue(A.$i.1,$titleRow[$j]);
$j++;
}
$worksheet->mergeCells("AR1:AR2");
$worksheet->setCellValue(AR1,四、利润);

$titleRow = [工资、奖金,社保,礼品,旅游];
$j = 0;
for ($i=23;$i<=26;$i++){
$worksheet->setCellValueByColumnAndRow($i,2,$titleRow[$j]);
$j++;
}

$titleRow = [红包,门店转让费、押金,门店房租,门店装修,门店暖气费,
门店物业费,门店水、电、液化气,门店其他费用,运费,业务办公费用,员工宿舍费,差旅费,
业务招待费,总部办公费用,利息收支、手续费,税费,其他费用];
$j = 0;
for ($i=A;$i<=Q;$i++){
$worksheet->setCellValue(A.$i.2,$titleRow[$j]);
$j++;
}
//上下左右居中
$worksheet->getStyle(A1:AR2)->applyFromArray([
alignment => [horizontal => Alignment::HORIZONTAL_CENTER,vertical => Alignment::VERTICAL_CENTER]
])->getFont()->setSize(12);
//字体加粗
$worksheet->getStyle(L1)->applyFromArray([font => [bold => true]])->getFont()->setSize(12);
$worksheet->getStyle(Q1)->applyFromArray([font => [bold => true]])->getFont()->setSize(12);
$worksheet->getStyle(V1)->applyFromArray([font => [bold => true]])->getFont()->setSize(12);
$worksheet->getStyle(AR1)->applyFromArray([font => [bold => true]])->getFont()->setSize(12);

$keys = [nian,yue,zhanqu,shichang,bangongshi,dianzhang_name,mendian_sn,mendian_name,kaidian_time,guandian_time,
mendian_num,yi_shouru,chanpin_shouru,chanpin_tuihuo,tuangou_shouru,qita_shouru,er_shouru,chanpin_chengben,
tuihuo_chengben,tuangou_chengben,qita_chengben,san_feiyong,gongzi_jiangjin,shebao,lipin,lvyou,hongbao,
zhuanrangfei_yajin,mendian_fangzu,mendian_zhuangxiu,mendian_nuanqifei,mendian_wuyefei,shui_dian_yehuaqi,
mendian_qitafei,yun_fei,yewu_bangongfei,yuangong_sushefei,chai_lvfei,
yewu_zhaodaifei,zongbu_bangongfei,lixi_shouzhi_shouxufei,shui_fei,qita_feiyong,li_run];

//导出文件名字
$filename = "报表导出".date(YmdHis);

// 从数据库获取表格内容
$len = count($list);
$j = 0;
for ($i=0; $i < $len; $i++){
//从表格第3行开始
$j = $i + 3;
foreach ($keys as $kk => $vv){
if($kk <= 10){
$worksheet->setCellValueByColumnAndRow($kk+1, $j, $list[$i][$vv]);
}else{
$worksheet->setCellValueByColumnAndRow($kk+1, $j, $list[$i][$vv] ? sprintf("%01.10f",$list[$i][$vv]/10000000000) : );
}
}
}
//所有行数
$total_jzInfo = $len + 2;
$styleArrayBody = [
borders => [
allBorders => [
borderStyle => Border::BORDER_THIN,
color => [argb => 666666],
],
],
alignment => [
horizontal => Alignment::HORIZONTAL_CENTER,
],
];
// 添加所有边框/居中
$worksheet->getStyle(A1:.AR.$total_jzInfo)->applyFromArray($styleArrayBody);

header(Content-Type: application/vnd.ms-excel);
header("Content-Disposition:attachment;filename={$filename}.xlsx");
header(Cache-Control: max-age=0);//禁止缓存
$writer = IOFactory::createWriter($spreadsheet, Xlsx);
$writer->save(php://output);
}

/**

审批统计明细导出
*/
public function mingxi_export()
{
$access_token = Request::param(access_token,);
if(Db::table(fee_temp_baobiao_token)->where(access_token,"$access_token")->value(id)){
Db::table(fee_temp_baobiao_token)->where(access_token,"$access_token")->delete();
}else{
$utilsLayer = new UtilsLayer();
$utilsLayer->alertError(抱歉，您无权限操作);
}

//搜索条件
$map = $this->commonMap(1);

$list = Db::table(fee_order_log)->where($map)
->order(status, asc)
->order(id, desc)
->limit(3000)
->select()->toArray();
foreach ($list as $k => $v) {
$detail = Db::table(fee_order_info)->where(order_id,"{$v[id]}")->field(sk_name,sk_kaihuhang,sk_order)->find();
if(!$detail){
$detail[sk_name] = ;
$detail[sk_kaihuhang] = ;
$detail[sk_order] = ;
}
$list[$k][sk_name] = $detail[sk_name];
$list[$k][sk_kaihuhang] = $detail[sk_kaihuhang];
$list[$k][sk_order] = $detail[sk_order];
//金额
$list[$k][money] = sprintf("%01.2f",$v[money]/100);
//审批类别fee_type表
$type_name = Db::table(fee_type)->where(id,"{$v[type]}")->value(name);
$list[$k][type_name] = $type_name ? $type_name : ;
//审批类型搜索type_lx process_type表
$type_lx_name = Db::table(fee_process_type)->where(id,"{$v[type_lx]}")->value(name);
$list[$k][type_lx_name] = $type_lx_name ? $type_lx_name : ;
//审批状态搜索状态，0,待审核，1审核中，2审核完成，3驳回，4撤销
switch ($v[status]){
case 0:
$list[$k][status_name] = 待审核;
break;
case 1:
$list[$k][status_name] = 审核中;
break;
case 2:
$list[$k][status_name] = 审核完成;
break;
case 3:
$list[$k][status_name] = 驳回;
break;
case 4:
$list[$k][status_name] = 撤销;
break;
default:
$list[$k][status_name] = 未知;
break;
}
$list[$k][faqi_time] = date(Y-m-d H:i:s,$v[add_time]);
$list[$k][wancheng_time] = date(Y-m-d H:i:s,$v[end_time]);
//支付所用卡 fee_type表
$ka_name = Db::table(fee_type)->where(id,"{$v[ka_id]}")->value(name);
$list[$k][ka_name] = $ka_name ? $ka_name : ;
}

if(!$list){
$utilsLayer = new UtilsLayer();
$utilsLayer->alertError(没有要导出的数据);
//return 暂无数据;
}

$column = [ID,审批单编号,申请人Userid,申请人姓名,申请人手机号,金额（元/人民币）,
战区名称,市场名称,团队名称,门店编号,门店名称,店长姓名,审批类别,审批类型,
审批单状态,审批单发起时间,审批单完成时间,收款人姓名,收款人开户行,收款人银行卡号,支付所用卡];
$keys = [id,order_sn,qxid,username,mobile,money,zhanqu_name,shichang_name,
tuandui_name,mendian_sn,bumen_name,dianzhang_name,type_name,type_lx_name,
status_name,faqi_time,wancheng_time,sk_name,sk_kaihuhang,sk_order,ka_name];

$filename = "审批明细".date(YmdHis);

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$count = count($column);
// 合并首行单元格
$styleArray = [
font => [bold => true],
alignment => [horizontal => Alignment::HORIZONTAL_CENTER]
];
// 设置单元格样式
$worksheet->getStyle(chr(65).1:.chr($count+64).1)->applyFromArray($styleArray)->getFont()->setSize(12);
// 设置列头内容
foreach ($column as $key => $value){
$worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}
// 从数据库获取表格内容
$len = count($list);
$j = 0;
for ($i=0; $i < $len; $i++){
$j = $i + 2; //从表格第2行开始
foreach ($keys as $kk => $vv){
$worksheet->setCellValueByColumnAndRow($kk+1, $j, $list[$i][$vv]);
}
}
$total_jzInfo = $len + 1;
$styleArrayBody = [
borders => [
allBorders => [
borderStyle => Border::BORDER_THIN,
color => [argb => 666666],
],
],
alignment => [
horizontal => Alignment::HORIZONTAL_CENTER,
],
];
// 添加所有边框/居中
$worksheet->getStyle(chr(65).1:.chr($count+64).$total_jzInfo)->applyFromArray($styleArrayBody);

}

/**

从数据库导出数据到表格
@param sring $title 首行标题内容
@param array $column 第二行列头标题
@param array $setWidth 第二行列头宽度
@param array $list 从数据库获取表格内容
@param array $keys 要获取的内容键名
@param array $lastRow 最后一行设置
@param string $filename 导出的文件名
*/
public static function export(string $title, array $column, array $setWidth, array $list, array $keys, array $lastRow=[], string $filename=)
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$count = count($column);
// 合并首行单元格
$worksheet->mergeCells(chr(65).1:.chr($count+64).1);
$styleArray = [
font => [bold => true],
alignment => [horizontal => Alignment::HORIZONTAL_CENTER,],
];
// 设置首行单元格内容
$worksheet->setTitle($title);
$worksheet->setCellValueByColumnAndRow(1, 1, $title);
// 设置单元格样式
$worksheet->getStyle(chr(65).1)->applyFromArray($styleArray)->getFont()->setSize(18);
$worksheet->getStyle(chr(65).2:.chr($count+64).2)->applyFromArray($styleArray)->getFont()->setSize(12);
// 设置列头内容
foreach ($column as $key => $value) $worksheet->setCellValueByColumnAndRow($key+1, 2, $value);
// 设置列头格式
foreach ($setWidth as $k => $v) $worksheet->getColumnDimension(chr($k+65))->setWidth(intval($v));
// 从数据库获取表格内容
$len = count($list);
$j = 0;
for ($i=0; $i < $len; $i++){
$j = $i + 3; //从表格第3行开始
foreach ($keys as $kk => $vv){
$worksheet->setCellValueByColumnAndRow($kk+1, $j, $list[$i][$vv]);
}
}
$total_jzInfo = $len + 2;
$styleArrayBody = [
borders => [
allBorders => [
borderStyle => Border::BORDER_THIN,
color => [argb => 666666],
],
],
alignment => [
horizontal => Alignment::HORIZONTAL_CENTER,
],
];
// 最后一行计算值
if (!empty($lastRow)) {
// 合并最后一行
$worksheet->mergeCells(chr(65).($len+3).:.chr(count($lastRow)+64).($len+3));
foreach ($lastRow as $item)
{
$worksheet->setCellValueByColumnAndRow(array_keys($lastRow,$item)[0], $len+3, $item);
}
$total_jzInfo = $len + 3;
}
// 添加所有边框/居中
$worksheet->getStyle(chr(65).1:.chr($count+64).$total_jzInfo)->applyFromArray($styleArrayBody);
header(Content-Type: application/vnd.ms-excel);
header("Content-Disposition:attachment;filename={$filename}.xlsx");
header(Cache-Control: max-age=0);//禁止缓存
$writer = IOFactory::createWriter($spreadsheet, Xlsx);
$writer->save(php://output);
}
$title = 第一行合并居中标题;
$column = [日期,店铺,订单号,姓名,手机号,订单金额,备注];
$setWidh = [15,30,30,15,15,15,15];
$list = $this->app->db->name($this->table)->->field($field)->order(create_at asc,id desc)->select()->toArray();
$keys = [create_at,shop_name,order_sn,user_name,user_phone,order_price,desc];
$last = [1=>合计,6=>=SUM(F3:F.(count($list)+2).),7=>=SUM(G3:G.(count($list)+2).),8=>=SUM(H3:H.(count($list)+2).),9=>=SUM(F.(count($list)+3).:H.(count($list)+3).)];
$filename = "表格名字";
ExcelExtend::export($title, $column, $setWidh, $list, $keys, $last, $filename);