示例1: 导出提现单并合并单元格
public function actionExportCondition()
{
//导出列表筛选条件
$fields = ['settlementStatus', 'createTimeBegin', 'createTimeEnd', 'consignmentOrderCode', 'memberCode'];
$model = new DynamicModel($fields);
$model->addRule($fields, 'safe');
$model->load(Yii::$app->request->get());
$filters = $model->toArray();
//导出具体操作
$filename = '寄售提现单列表.xls';
$titles = [
'createTime|sctonum' => '申请日期',
'payment' => '付款用途',
'accountName|sctonum' => '收款单位',
'bankBranchName' => '收款单位银行',
'bankCardNo|sctonum' => '银行卡号',
'appraisalPrice' => '结算金额',
'consignmentOrderCode|sctonum' => '订单号',
'shareOrderAmount' => '表库成交金额',
'channelName' => '支付方式',
'actualRetreadPrice' => '翻新费用',
'withdrawPrice' => '最终成交金额',
'commissionPrice' => '成交佣金',
'brandName' => '品牌',
];
//利用生成器读取数据(这里可直接查出数据)
$dataArray = $this->yieldData($filters);
//合并的单元格数据
$mergeData = [
0 => [
0 => ['info' => '财务审核:', 'startCol' => 1, 'mergeCol' => 2, 'mergeRow' => 2],
1 => ['info' => '利润负责人:', 'startCol' => 3, 'mergeCol' => 2, 'mergeRow' => 2],
2 => ['info' => '部门负责人:', 'startCol' => 5, 'mergeCol' => 2, 'mergeRow' => 2],
3 => ['info' => '经办人:', 'startCol' => 7, 'mergeCol' => 0, 'mergeRow' => 2],
],
1 => [
0 => ['info' => '总裁审批:','startCol'=>1,'mergeCol'=>0, 'mergeRow' => 2],
]
];
ExcelHelper::export($titles, $dataArray, $filename, '万表名匠（广州）科技有限公司', [], $mergeData);
}
/**
* 利用生成器读取数据
* @param array $filters 筛选
* @return
*/
public function yieldData($filters)
{
$service = $this->service(ConsignmentWithdrawService::className());
//是否有下一页: true-有;false-无
$hasNext = true;
$pageNo = 1;
$filters['pageRow'] = 2000;
while($hasNext) {
$filters['pageNo'] = $pageNo;
$apiResponse = $service->exportList($filters);
$hasNext = $apiResponse->getData('hasNext');
if ($hasNext) {
$pageNo += 1;
}
foreach ((array)$apiResponse->getData('pageData') as $key => $value) {
$value['payment'] = '平台用户款';
yield $value;
}
}
}
示例2:多表导出:
/**
*问答列表导出,累计回答导出
*/
public function actionExport()
{
//筛选条件
$fields = ['brandCode', 'seriesCode', 'type', 'goodsCode', 'memberCode', 'auditStatus', 'beginCreateTime', 'endCreateTime'];
$model = new DynamicModel($fields);
$model->addRule($fields, 'safe');
$model->load(Yii::$app->request->get());
$auditStatus = Yii::$app->request->get('auditStatus','1');
$filters = $model->toArray();
if ($auditStatus == 0) {
if ($filters['auditStatus'] == '') {
$filters['auditStatus'] = [0,2];
}else{
$filters['auditStatus'] = [$filters['auditStatus']];
}
} elseif($auditStatus == 1){
$filters['auditStatus'] = [1];
}
$filename = '问答导出EXCEL.xls';
$qasTitles = [
'questionId' => '提问ID',
'memberCode' => '提问用户ID',
'brandName' => '品牌',
'seriesName' => '系列',
'models' => '型号',
'createTime' => '提问时间',
'typeName' => '所属业务',
'questionContent' => '提问内容',
'answerTotalUser' => '真实回答数',
'answerTotalOperate' => '后台添加回答数',
'pv' => '浏览数',
];
//问答列表数据
$questionDataArray = [];
$hasNext = true;
$pageNo = 1;
$filters['pageRow'] = 100;
$qusService = $this->service(QuestionService::className());
while($hasNext) {
$filters['pageNo'] = $pageNo;
$apiResponse = $qusService->getList($filters);
$hasNext = $apiResponse->getData('hasNext');
if ($hasNext) {
$pageNo += 1;
}
foreach ((array)$apiResponse->getData('pageData') as $key => $value) {
array_push($questionDataArray, $value);
}
}
$questionIds = !empty($questionDataArray) ? ArrayHelper::getColumn($questionDataArray, 'questionId') : [];
//累计回答列表
$answerTitles = [
'questionId' => '提问ID',
'answerId' => '回答ID',
'memberCode' => '回答用户ID',
'identity' => ' 用户身份',
'userAttributeName' => '用户属性',
'app|extra.appOptions' => '回答客户端',
'createTime' => '回答时间',
'answerContent' => '回答内容',
'skrTotal' => '点赞数',
'showStatusName' => '显示状态',
];
$answerDataArray = [];
if (!empty($questionIds)) {
$answerDataArray = $this->answerListYieldData(['questionId' => $questionIds]);
}
$titleArray = [0=>$qasTitles, 1=>$answerTitles];
$dataArray = [0=>$questionDataArray, 1=>$answerDataArray];
//工作表名
$sheetNameArray = [0=>'问答列表导出', 1=>'累计回答导出'];
$extra[1] = [
'appOptions' => $this->appOptions,
];
ExcelHelper::exportSheets($titleArray, $dataArray, $filename, $sheetNameArray, [], $extra);
}
//导出组件 ExcelHelper.php
<?php
namespace common\helpers;
use yii\helpers\ArrayHelper;
/**
* Excel 助手
*/
class ExcelHelper
{
public static $styleFormat = [];
/**
* @see \PHPExcel_Style_NumberFormat
*/
public static function setStyleFormat($format)
{
self::$styleFormat = $format;
}
/**
* **导出一个工作表**
* @param array $titles 标题，一维数组，可传map或单纯标题
* @param array $dataArray 数据，二维数组，可传map或单纯数据
* @param string $filename 文件名，要带后缀
* @param string $bigTitle 居中加粗的大标题，默认为空
* @param array $extra 扩展数据
* @param array $mergeData 合并数据
* @return file
*/
public static function export(array $titles, $dataArray, $filename, $bigTitle='', $extra=[], $mergeData=[])
{
//require_once(CORE_PATH . '/vendor/phpoffice/phpexcel/Classes/PHPExcel.php');
set_time_limit(0);
ini_set('memory_limit', '512M');
// 后缀
$suffix = substr($filename, strrpos($filename, '.'));
empty($titles) && die('标题数组不能为空！');
empty($dataArray) && die('数据数组不能为空！');
!in_array($suffix, ['.xls', '.xlsx']) && die('文件名格式错误！');
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$cacheSettings = array('memoryCacheSize ' => '512MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$oExcel = new \PHPExcel();
$oExcel->setActiveSheetIndex(0);
$sheet = $oExcel->getActiveSheet();
// 填入数据
self::excelData($sheet, $titles, $dataArray, $bigTitle, $extra, $mergeData);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
if ($suffix == '.xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} else {
header('Content-Type: application/vnd.ms-excel');
}
header('Content-Disposition: attachment;filename="'. $filename .'"');
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
$oWriter = \PHPExcel_IOFactory::createWriter($oExcel, 'Excel2007');
$oWriter->save('php://output');
$oExcel->disconnectWorksheets();
exit;
}
/**
* **导出多个工作表**
* @param array $titles 标题，一维数组，可传map或单纯标题
* @param array $dataArray 数据，二维数组，可传map或单纯数据
* @param string $filename 文件名，要带后缀
* @param array $sheetNameArray 工作表名,一维数组
* @param string $bigTitle 居中加粗的大标题，默认为空,一维数组
* @param array $extra 扩展数据
* @return file
*/
public static function exportSheets(array $titleArray, $dataArray, $filename, $sheetNameArray, $bigTitles=[], $extraArray=[])
{
require_once(CORE_PATH . '/vendor/phpoffice/phpexcel/Classes/PHPExcel.php');
set_time_limit(0);
ini_set('memory_limit', '512M');
// 后缀
$suffix = substr($filename, strrpos($filename, '.'));
empty($titleArray) && die('标题数组不能为空！');
empty($dataArray) && die('数据数组不能为空！');
empty($sheetNameArray) && die('工作表数组不能为空！');
!in_array($suffix, ['.xls', '.xlsx']) && die('文件名格式错误！');
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$cacheSettings = array('memoryCacheSize ' => '512MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$oExcel = new \PHPExcel();
//需要导出工作表的个数
$count = count($sheetNameArray);
for ($i=0; $i <= $count-1; $i++) {
if ($i > 0) {
生成工作表
$oExcel ->createSheet();
}
$oExcel->setActiveSheetIndex($i);
$sheet = $oExcel->getActiveSheet();
$sheet->setTitle($sheetNameArray[$i]);
//填入数据
$bigTitle = isset($bigTitles[$i]) ? $bigTitles[$i] : '';
$extra = isset($extraArray[$i]) ? $extraArray[$i] : '';
self::excelData($sheet, $titleArray[$i], $dataArray[$i], $bigTitle, $extra);
}
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
if ($suffix == '.xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} else {
header('Content-Type: application/vnd.ms-excel');
}
header('Content-Disposition: attachment;filename="'. $filename .'"');
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
$oWriter = \PHPExcel_IOFactory::createWriter($oExcel, 'Excel2007');
$oWriter->save('php://output');
$oExcel->disconnectWorksheets();
exit;
}
/**
* **导出excel数据处理**
* @param array $titles 标题，一维数组，可传map或单纯标题
* @param array $dataArray 数据，二维数组，可传map或单纯数据
* @param string $filename 文件名，要带后缀
* @param array $sheetNameArray 工作表名,一维数组
* @param string $bigTitle 居中加粗的大标题，默认为空,一维数组
* @param array $extra 扩展数据
* @param array $mergeData 合并数据
* @return file
*/
public static function excelData($sheet, array $titles, $dataArray, $bigTitle='', $extra=[], $mergeData = [])
{
// 设置列数据格式
if (!empty(self::$styleFormat)) {
$fields = array_keys($titles);
foreach (self::$styleFormat as $field => $formatCode) {
$offset = array_search($field, $fields);
$col = chr(65+$offset);
$sheet->getStyle($col)->getNumberFormat()->setFormatCode($formatCode);
}
}
// 行索引
$rowIndex = $bigTitle!=''? 2:1;
$chr = [
'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
];
// 设置大标题
if ($bigTitle != '') {
$sheet->mergeCells('A1:'. $chr[count($titles)-1] .'1');
$sheet->getStyle('A1')->applyFromArray([
'font' => ['bold'=>true],
'alignment' => ['horizontal'=>\PHPExcel_Style_Alignment::HORIZONTAL_CENTER]
]);
$sheet->setCellValue('A1', $bigTitle);
}
// 设置标题 A1 B1 C1 ....
$colIndex = 0;
$fieldsMap = [];
foreach ($titles as $key => $title) {
$fieldsMap[] = $key;
$sheet->setCellValue($chr[$colIndex] . $rowIndex, $title);
$colIndex++;
}
// 设置内容 A1 B1 C1 .... A2 B2 C2 ....
$rowIndex++;
foreach ($dataArray as $key => $value)
{
foreach ($fieldsMap as $colIndex => $field) {
if (strrpos($field, '|') !== false) {
$temp1 = explode('|', $field);
$pos = strrpos($temp1[1], '.');
$pos === false && $pos = strlen($temp1[1]);
$temp2 = [];
$temp2[0] = substr($temp1[1], 0, $pos);
$temp2[1] = substr($temp1[1], $pos+1);
$val = ArrayHelper::getValue($value, $temp1[0]);
//$val = self::$temp2[0]($extra, $temp2[1], $val);
$val = call_user_func_array(array('\common\helpers\ExcelHelper',$temp2[0]),array($extra, $temp2[1], $val, $value));
} else {
$val = $field? ArrayHelper::getValue($value, $field) : $value;
}
$sheet->setCellValue($chr[$colIndex].$rowIndex, $val);
}
$rowIndex++;
}
//合并单元格数据处理
if ($mergeData) {
foreach ($mergeData as $kay => $val)
{
foreach ($val as $k => $v)
{
//合并列数 $v['mergeCol']
if (isset($v['mergeCol']) && $v['mergeCol'] > 0) {
$endCol = $chr[$v['startCol']-1+$v['mergeCol']-1];
} else {
$v['mergeCol'] = count($titles);
$endCol = $chr[$v['mergeCol']-1];
}
$nowCol = $chr[$v['startCol']-1];
//合并行数
$mergeRow = isset($v['mergeRow'])?$v['mergeRow']:0;
//mergeCells('合并开始单元格:合并结束单元格')
$sheet->mergeCells($nowCol.$rowIndex.':'. $endCol.($rowIndex+$mergeRow-1));
$sheet->setCellValue($nowCol.$rowIndex, $v['info']);
}
$rowIndex = $mergeRow>0 ? ($rowIndex+$mergeRow) : $rowIndex+1;
}
}
}
/*********************************分割线************************************/
//以下方法是进行单元格数据进行转化的
public static function extra($extra, $extra_key, $val, $row)
{
$arr = ArrayHelper::getValue($extra, $extra_key, []);
return ArrayHelper::getValue($arr, $val, '');
}
public static function dateIsEmpty($extra, $extra_key, $val, $row)
{
return strtotime($val)>1000? $val:'';
}
public static function toFixed($extra, $extra_key, $val, $row)
{
return (string)sprintf("%.{$extra_key}f", floatval($val));
}
public static function dateFormat($extra, $extra_key, $val, $row)
{
if ($val > 0) {
return date('Y-m-d H:i:s',$val/1000);
} else {
return '';
}
}
public static function trim($extra, $extra_key, $val, $row)
{
return str_replace(["\r", "\n", ","], ["", "", "，"], $val);
}
public static function extraConcat($extra, $extra_key, $val, $row)
{
$arr = explode('-', $extra_key);
foreach ($arr as $key => $value) {
$val .= ArrayHelper::getValue($extra[$value.'Options'], $row[$value], '');
}
return $val;
}
//两个数相减
public static function subtract($extra, $extra_key, $val, $row){
$temp = explode('-', $extra_key);
$res = $row[$temp[0]] - $row[$temp[1]];
return $res;
}
public static function implode($extra, $extra_key, $val, $row)
{
return implode($val, $extra_key);
}
//将数据进行字符串输出,防止excel转成科学计数法
public static function sctonum($extra, $extra_key, $val, $row)
{
if(false !== stripos($val, "e")){
return (string)number_format($val, 0, '', '')."\t";
} else {
return (string)$val."\t";
}
}
//计算备付金-商家账户-今日可用信用金
public static function computeTodayEarnestAmount($extra, $extra_key, $val, $row)
{
//今日总的信用金=信用总额度*每日可用度;
$dayAllAmount = $row['earnestAmount'] * $row['dayRatio'];
//今日剩余总的可用信用金=今日总的信用金-今日已用信用金
$daySurplusAmount = $dayAllAmount - $row['todayEarnestAmount'];
//剩余总的可用信用金=信用总额度-已用信用金;
$surplusAllAmount = $row['earnestAmount'] - $row['expendAmount'];
//实际今日可用信用金:取 (今日剩余总的可用信用金,剩余总的可用信用金)最小值
$lastAmount = min($daySurplusAmount, $surplusAllAmount);
//今日最终可用信用金
return round($lastAmount, 2);
}
//计算备付金-商家账户-风控等级
public static function riskCompute($extra, $extra_key, $val, $row)
{
$riskConfig = ArrayHelper::index($extra[$extra_key],'riskLevel');
if ($row['earnestAmount'] != 0) {
$num = round($row['expendAmount'] / $row['earnestAmount'], 2);
$riskArr = ArrayHelper::index($riskConfig, 'riskLevel');
$ratioHigh = $riskArr['lev3']['ratio'];
$ratioMiddle = $riskArr['lev2']['ratio'];
$ratioLow = $riskArr['lev1']['ratio'];
if ($num >= $ratioHigh) {
$riskLevel = '高';
} elseif ($num >= $ratioMiddle) {
$riskLevel = '中';
} elseif ($num >= $ratioLow) {
$riskLevel = '低';
} else {
$riskLevel = '--';
}
return $riskLevel;
} else {
return '--';
}
}
//保留二维数组中的第一个指定key的值
public static function getNew($extra, $extra_key, $val, $row)
{
return !empty($val) ? $val[0][$extra_key] : '';
}
}
示例1单表导出并合并单元格,导出结果展示:
示例2多表导出结果展示:
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
