在项目中，把数据导出到Excel，有时会表头信息是动态的，数据是动态的，在之前发布过一篇文章是，固定头部，动态数据的导出数据表格的文章。

今天做一个，表头是动态的，数据是动态的导出数据表格，在项目中会更加的实用

要注意单元格下标这块，处理不好，会出现 Invalid cell coordinate(无效单元格坐标)的错误

代码如下：

/*** thinkphp 动态表头,动态数据批量导出excel* * @return [type] [description]*/public function importexcel(){vendor("PHPExcel.PHPExcel");  $objPHPExcel = new \PHPExcel();$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);//设置sheet名称$sheets=$objPHPExcel->getActiveSheet()->setTitle('信息统计表');//表头$category  = Db('category')->field('id,category_name')->where('status',1)->order('sort_order asc,id desc')->select();//添加第一列的名称，因为数据中没有这一项$first_row_name[] = "部门";foreach ($category as $key => $value) {//获取动态表头列，仅仅包含category_name$first_row_name[] = $value['category_name'];}$ordA = ord('A'); //65$key2 = ord("@"); //64$objPHPExcel->setActiveSheetIndex(0); //表示使用的是第一个sheetforeach ($first_row_name as $key => $val){if($ordA > ord("Z")){$colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...}else{$colum = chr($ordA++);}//设置表格的第一行列名$objPHPExcel->getActiveSheet()->SetCellValue($colum.'1', $val);}//获取数据$list = Db('admin')->field('id,username,department')->where('status',1)->select();foreach ($list as $key => $value) {$list[$key]['category']  = Db('category')->field('id,category_name')->where('status',1)->order('sort_order asc,id desc')->select();foreach ($list[$key]['category'] as $kk => $vv) {$cc = Db('article')->where('status',1)->where('uid',$value['id'])->where('cid',$vv['id'])->where($where)->count();$list[$key]['category'][$kk]['count'] = $cc?$cc:0;}}$index_row = 2; //从第二行开始插入数据$ii = 0;// foreach ($excel_data as $kk => $rows)foreach ($list as $kk => $rows){$rowinfo = [];$rowinfo[] = $rows['department'];$rowinfodatacount = count($rows['category']);foreach ($rows['category'] as $ky => $value) {$rowinfo[]['count'.$ky] = $value['count'];}$ordA = ord('A');//重新从A开始$key2 = ord("@"); //64// foreach($rows as $key => $val)foreach(arrToOne($rowinfo) as $key => $val){if($ordA > ord("Z")){$colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...}else{$colum = chr($ordA++);}$sheets=$objPHPExcel->getActiveSheet()->setCellValue($colum.$index_row, $val);}$index_row++;}$excel_name = '信息统计表';// 输出Excel表格到浏览器下载header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="tongji.xls"'); //excel表格名称$objWriter->save('php://output'); }