//引入PHPexcel插件
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
/**
* 导出
*/
public function derive(){
$data=$this->table_collect(); //数据
// dd($data);
// 实例化类
$spreadsheet = new Spreadsheet();
// 创建sheet
$spreadsheet->getActiveSheet()->mergeCells('A1:S1');//合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)
$spreadsheet->getActiveSheet()->mergeCells('A2:B2');
$spreadsheet->getActiveSheet()->mergeCells('C2:G2');
$spreadsheet->getActiveSheet()->mergeCells('H2:N2');
$spreadsheet->getActiveSheet()->mergeCells('O2:S2');
$spreadsheet->setActiveSheetIndex(0)
->setCellValue('A1','产业链录入数据汇总')
->setCellValue('A2','基础数据')
->setCellValue('C2','产业链')
->setCellValue('H2','企业')
->setCellValue('O2','企业发展质量')
->setCellValue('A3','序号')
->setCellValue('B3','地区')
->setCellValue('C3','主导产业链')
->setCellValue('D3','主要分链条')
->setCellValue('E3','上游细分产业链')
->setCellValue('F3','中游细分产业链')
->setCellValue('G3','下游细分产业链')
->setCellValue('H3','园区企业')
->setCellValue('I3','园区企业年营业收入(亿元)')
->setCellValue('J3','国内企业')
->setCellValue('K3','国内企业年营业收入(亿元)')
->setCellValue('L3','国际企业')
->setCellValue('M3','国际企业年营业收入(亿元)')
->setCellValue('N3','是否规上企业')
->setCellValue('O3','是否上市企业')
->setCellValue('P3','是否高新技术企业')
->setCellValue('Q3','是否科技小巨人企业')
->setCellValue('R3','是否专精特新企业')
->setCellValue('S3','是否独角兽企业');
$index=3;起始位置从第三行起
foreach ($data as $k => $v) {
//判断是否存在子集
if (isset($v['ind'])){
// continue;
if ($v['rowspan']>1){
//合并序号和经开区 $v['rowspan']需合并格数
$spreadsheet->getActiveSheet()->mergeCells('A'.($index + 1).':A'.($index + $v['rowspan'] ));//合并格数$v['rowspan']-1 合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)
$spreadsheet->getActiveSheet()->mergeCells('B'.($index + 1).':B'.($index + $v['rowspan'] ));// 合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)
}
$indexs=$index;
foreach ($v['ind'] as $kk=>$vv){
//判断是否有二级子集 //$vv['rowspan']因业务需要 实际应判断$vv['child']
if (isset($vv['rowspan'])) {
if ($vv['rowspan']> 1){ //
$index1=$index;
foreach ($vv['child'] as $kkk=>$vvv){
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index1 + 1), $k+1)
->setCellValue('B' . ($index1 + 1), $v['name'])
->setCellValue('C' . ($index1 + 1), $vv['name'])
->setCellValue('D' . ($index1 + 1), $vvv['name'])
->setCellValue('E' . ($index1 + 1), isset($vvv['syx']) ? $vvv['syx'] : 0)
->setCellValue('F' . ($index1 + 1), isset($vvv['zyx']) ? $vvv['zyx'] : 0)
->setCellValue('G' . ($index1 + 1), isset($vvv['xyx']) ? $vvv['xyx'] : 0)
->setCellValue('H' . ($index1 + 1), $vvv['yuanqu'])
->setCellValue('I' . ($index1 + 1), sprintf("%.2f",round($vvv['yuanqu_income']/10000,2)))
->setCellValue('J' . ($index1 + 1), $vvv['guonei'])
->setCellValue('K' . ($index1 + 1), sprintf("%.2f",round($vvv['guonei_income']/10000,2)))
->setCellValue('L' . ($index1 + 1), $vvv['guoji'])
->setCellValue('M' . ($index1 + 1), sprintf("%.2f",round($vvv['guoji_income']/10000,2)))
->setCellValue('N' . ($index1 + 1), $vvv['guishang'])
->setCellValue('O' . ($index1 + 1), $vvv['shangshi'])
->setCellValue('P' . ($index1 + 1), $vvv['gaoxin'])
->setCellValue('Q' . ($index1 + 1), $vvv['keji'])
->setCellValue('R' . ($index1 + 1), $vvv['zhuanjing'])
->setCellValue('S' . ($index1 + 1), $vvv['dujiao']);
$index1++; //循环一级加1
}
$spreadsheet->getActiveSheet()->mergeCells('C'.($index + 1).':C'.($index + $vv['rowspan'] ));//合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)
$index+=$vv['rowspan'];
}else{
if (isset($vv['child'])){
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index + 1), $k+1)
->setCellValue('B' . ($index + 1), $v['name'])
->setCellValue('C' . ($index + 1), $vv['name'])
->setCellValue('D' . ($index + 1), $vv['child'][0]['name'])
->setCellValue('E' . ($index + 1), isset($vv['child'][0]['syx']) ? $vv['child'][0]['syx'] : 0)
->setCellValue('F' . ($index + 1), isset($vv['child'][0]['zyx']) ? $vv['child'][0]['zyx'] : 0)
->setCellValue('G' . ($index + 1), isset($vv['child'][0]['xyx']) ? $vv['child'][0]['xyx'] : 0)
->setCellValue('H' . ($index + 1), $vv['child'][0]['yuanqu'])
->setCellValue('I' . ($index + 1), sprintf("%.2f",round($vv['child'][0]['yuanqu_income']/10000,2)))
->setCellValue('J' . ($index + 1), $vv['child'][0]['guonei'])
->setCellValue('K' . ($index + 1), sprintf("%.2f",round($vv['child'][0]['guonei_income']/10000,2)))
->setCellValue('L' . ($index + 1), $vv['child'][0]['guoji'])
->setCellValue('M' . ($index + 1), sprintf("%.2f",round($vv['child'][0]['guoji_income']/10000,2)))
->setCellValue('N' . ($index + 1), $vv['child'][0]['guishang'])
->setCellValue('O' . ($index + 1), $vv['child'][0]['shangshi'])
->setCellValue('P' . ($index + 1), $vv['child'][0]['gaoxin'])
->setCellValue('Q' . ($index + 1), $vv['child'][0]['keji'])
->setCellValue('R' . ($index + 1), $vv['child'][0]['zhuanjing'])
->setCellValue('S' . ($index + 1), $vv['child'][0]['dujiao']);
$index++;
}else{
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index + 1), $k+1)
->setCellValue('B' . ($index + 1), $v['name'])
->setCellValue('C' . ($index + 1), $vv['name'])
->setCellValue('D' . ($index + 1), '')
->setCellValue('E' . ($index + 1), '')
->setCellValue('F' . ($index + 1), '')
->setCellValue('G' . ($index + 1), '')
->setCellValue('H' . ($index + 1), '')
->setCellValue('I' . ($index + 1), '')
->setCellValue('J' . ($index + 1), '')
->setCellValue('K' . ($index + 1), '')
->setCellValue('L' . ($index + 1), '')
->setCellValue('M' . ($index + 1), '')
->setCellValue('N' . ($index + 1), '')
->setCellValue('O' . ($index + 1), '')
->setCellValue('P' . ($index + 1), '')
->setCellValue('Q' . ($index + 1), '')
->setCellValue('R' . ($index + 1), '')
->setCellValue('S' . ($index + 1), '');
$index++;
}
}
}else{
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index + 1), $k+1)
->setCellValue('B' . ($index + 1), $v['name'])
->setCellValue('C' . ($index + 1), $vv['name'])
->setCellValue('D' . ($index + 1), '')
->setCellValue('E' . ($index + 1), '')
->setCellValue('F' . ($index + 1), '')
->setCellValue('G' . ($index + 1), '')
->setCellValue('H' . ($index + 1), '')
->setCellValue('I' . ($index + 1), '')
->setCellValue('J' . ($index + 1), '')
->setCellValue('K' . ($index + 1), '')
->setCellValue('L' . ($index + 1), '')
->setCellValue('M' . ($index + 1), '')
->setCellValue('N' . ($index + 1), '')
->setCellValue('O' . ($index + 1), '')
->setCellValue('P' . ($index + 1), '')
->setCellValue('Q' . ($index + 1), '')
->setCellValue('R' . ($index + 1), '')
->setCellValue('S' . ($index + 1), '');
$index++;
}
$indexs++;
}
$index--; //因合并数位置需-1;否则合并下有一空格
}else{
//此为插入数据 完整树状可不写
if ($v['level']==1 || $v['level']==0){
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index + 1), $k+1)
->setCellValue('B' . ($index + 1), $v['name'])
->setCellValue('C' . ($index + 1), $v['class_count'])
->setCellValue('D' . ($index + 1), $v['class_a_count'])
->setCellValue('E' . ($index + 1), $v['class_cs_count'])
->setCellValue('F' . ($index + 1), $v['class_cz_count'])
->setCellValue('G' . ($index + 1), $v['class_cx_count'])
->setCellValue('H' . ($index + 1), $v['yuanqu'])
->setCellValue('I' . ($index + 1), sprintf("%.2f",round($v['yuanqu_income']/10000,2)))
->setCellValue('J' . ($index + 1), $v['guonei'])
->setCellValue('K' . ($index + 1), sprintf("%.2f",round($v['guonei_income']/10000,2)))
->setCellValue('L' . ($index + 1), $v['guoji'])
->setCellValue('M' . ($index + 1), sprintf("%.2f",round($v['guoji_income']/10000,2)))
->setCellValue('N' . ($index + 1), $v['guishang'])
->setCellValue('O' . ($index + 1), $v['shangshi'])
->setCellValue('P' . ($index + 1), $v['gaoxin'])
->setCellValue('Q' . ($index + 1), $v['keji'])
->setCellValue('R' . ($index + 1), $v['zhuanjing'])
->setCellValue('S' . ($index + 1), $v['dujiao']);
}else{
$spreadsheet->getActiveSheet()
->setCellValue('A' . ($index + 1), $k+1)
->setCellValue('B' . ($index + 1), $v['name'])
->setCellValue('C' . ($index + 1), '')
->setCellValue('D' . ($index + 1), '')
->setCellValue('E' . ($index + 1), '')
->setCellValue('F' . ($index + 1), '')
->setCellValue('G' . ($index + 1), '')
->setCellValue('H' . ($index + 1), '')
->setCellValue('I' . ($index + 1), '')
->setCellValue('J' . ($index + 1), '')
->setCellValue('K' . ($index + 1), '')
->setCellValue('L' . ($index + 1), '')
->setCellValue('M' . ($index + 1), '')
->setCellValue('N' . ($index + 1), '')
->setCellValue('O' . ($index + 1), '')
->setCellValue('P' . ($index + 1), '')
->setCellValue('Q' . ($index + 1), '')
->setCellValue('R' . ($index + 1), '')
->setCellValue('S' . ($index + 1), '');
}
}
$index++;
}
$fileName="产业链录入数据汇总--".date('Y-m-d');
ob_end_clean();
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
}
效果图
如有不妥处请指正,仅供猿友参考