tp导出excel多标签
优先安装扩展包
composer require phpoffice/phpspreadsheet
代码里引用
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
public function export_all(){
$find = Db::name('user_cost_log')->whereTime('price_time','today')->find();
if(empty($find)){
$this->error('今日数据还没有生成请生成后导出');
}
//通过班级查询当天订餐人数
$jc_class = Db::name('jc_class')
->field('jc.id,jc.sid,jc.gid,jc.classname,js.schoolname,jg.gradename')
->alias('jc')
->join('jc_school js','jc.sid = js.id')
->join('jc_grade jg','jc.gid = jg.id')
->select();
foreach($jc_class as $key=>$value){
$jc_class[$key]['cost'] = db::name('user_cost_log')
->field('uc.childname,u.truename,u.mobile,js.schoolname,jg.gradename,jc.classname')
->alias('ucl')
->join('user_child uc','uc.id = ucl.child_id')
->join('user u','u.id = uc.parent_id')
->join('jc_school js','uc.sid = js.id')
->join('jc_grade jg','uc.gid = jg.id')
->join('jc_class jc','uc.cid = jc.id')
->where('uc.cid',$value['id'])
->whereTime('ucl.price_time','today')
->select();
//清除没有订餐数据的班级
if(empty($jc_class[$key]['cost'])){
unset($jc_class[$key]);
}
}
//定义一个新数组
$dataarr=[];
//设置列表字段标题
$row1[] = array(
'0'=>'学生姓名',
'1'=>'家长姓名',
'2'=>'联系方式',
'3'=>'所在学校',
'4'=>'所在年级',
'5'=>'所在班级',
);
foreach(array_values($jc_class) as $k=>$val){
//合并大标题
$dataarr[$k]['title'] = $val['schoolname'].$val['gradename'].$val['classname'];
//设置对应信息文字
$dataarr[$k]['info'] = array(
"学校：" => $val['schoolname'],
"年级：" => $val['gradename'],
"班级：" => $val['classname'],
"配送方：" => '远行餐饮',
"配送日期：" => date('Y-m-d',time()),
"配餐量：" => count($val['cost']),
);
//数组重新设置key值
$key_name = [];
foreach($val['cost'] as $key=>$value){
$key_name[] = array_values($value);
}
//合并为新的数组
$dataarr[$k]['rows'] = array_merge($row1,$key_name);
}
$this->xtexport($dataarr);
}
/**
* 多sheet的导出
* @author bwy <xxx@xxx.com>
* @param [type] $data_array
* @return void
*/
public function xtexport($data_array)
{
$time = date("Ymd", time());
$spreadsheet = new Spreadsheet();
foreach ($data_array as $key => $data) {
$this->opSheet($spreadsheet,$key,$data);
}
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=".$time.'订餐名单'.".xlsx");
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
$objWriter->save('php://output');
exit;
}
/**
* 处理多sheet
* @author bwy <xxx@xxx.com>
* @param [type] $spreadsheet
* @param [type] $n
* @param [type] $data
* @return void
*/
public function opSheet($spreadsheet,$n, $data)
{
$spreadsheet->createSheet();//创建sheet
$objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
$keys = $data['rows'][0];//这是你的数据键名
$count = count($keys);//计算你所占的列数
$infoNum = ceil(count($data['info']) / 2);//求k-v值的所占行数
$infoStart = $infoNum + 2 ;//下面的详细信息的开始行数
$cellName = array('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');
$sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['title']);//设置sheet的名称
$spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格
$spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小
$spreadsheet->getActiveSheet($n)->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('A')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('B')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('C')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('D')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('E')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle('F')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //居中
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //标题栏加粗
$objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title
/**
* 图中最下面的数据信息循环
*/
foreach ($data['rows'] as $key => $item)
{
//循环设置单元格：
//$key+$infoStart,因为第一行是表头，所以写到表格时 从第数据行开始写
for ($i = 65; $i < $count + 65; $i++)
{
//数字转字母从65开始：
//$sheet->setCellValue(strtoupper(chr($i)) . ($key + "$infoStart"), $item[[$keys][$i - 65]]);
$sheet->setCellValue(strtoupper(chr($i)) . ($key+"$infoStart"), $item[$i - 65]);
$spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
}
}
/**
* 处理图中的中间区 团长名称之类的
*/
$rowNumber = 1;
$infoIndex = 0;
foreach ($data['info'] as $key => $value) {
if ($infoIndex % 2 == 0) {
$rowNumber++;
$infoCellName1 = 'A' . $rowNumber;
$infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber;
$infoCellName2 = 'B' . $rowNumber;
} else {
$infoCellName1 = 'D' . $rowNumber;
$infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber;
$infoCellName2 = 'E' . $rowNumber;
}
$spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key);
$spreadsheet->getActiveSheet($n)->mergeCells($infoCellMegreRange);
$spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName2, $value);
$infoIndex++;
}
}
