分类表字段： id（主键），name（分类名称）， pid（空为顶级，不为空为上级id）
产品表： no，name，spec ...根据自己的产品表填写
关联表：id（主键），domelamp（分类表id）， domelamp_part（产品表id）
HTML代码
<button onclick="ExportExcel()" class="btn btn-danger" data-toggle="dropdown" style="margin-left: 30px;"> </span>导出数据到XLS</button>
JS代码
function ExportExcel(){
???location.href = "{:url(‘Shopmodel/ExportExcel‘)}";
}php 代码
class ShopmodelController extends BaseController
{
???private $smallarr = array();
??private $classnumber = array();
??private $alllist = array();
???/*
????*
????* 查看数据格式（excel导出）
????*
????* */
???public function ExportExcelview(){
???????$filename = "售后商品数据格式_" . date("Y_m_d", time()) . ".xls"; ?????????????//名称
???????$header = array(‘一级菜单‘,‘二级菜单‘,‘三级菜单‘,‘四级菜单‘,‘五级菜单‘,‘配件名称‘,‘配件编号‘,‘规格说明‘,‘配件单位‘,‘配件颜色‘,‘配件材质‘,‘销售价格‘);
???????$data = [
???????????[‘测试‘,‘test‘],
???????????[‘名称‘,‘name‘]
???????];
???????excelExport($filename,$header,$data,‘format‘);
???}
???/*
????*
????* 导出所有数据到xls
????*
????* */
???public function ExportExcel(){
???????$data = $this->getlist();
???????$filename = "售后商品数据_" . date("Y_m_d", time()) . ".xls";
???????$header = array(‘一级菜单‘,‘二级菜单‘,‘三级菜单‘,‘四级菜单‘,‘五级菜单‘,‘配件名称‘,‘配件编号‘,‘规格说明‘,‘配件单位‘,‘配件颜色‘,‘配件材质‘,‘销售价格‘); ?
???????excelExport($filename,$header,$data,‘allinfo‘);
???}
???/*
???*
???* 导出xls 查询所有配件信息
???*
???* */
???public function getalllist($cid){
???????$info = Db::name(‘DomelampBind‘)
???????????->alias(‘a‘)
???????????->join(‘DomelampNode b‘,‘a.domelamp = b.id‘)
???????????->join(‘DomelampPart c‘,‘a.domelamp_part = c.no‘)
???????????->where(‘a.domelamp‘,‘in‘,$cid)
???????????->field(‘c.code,c.name,c.spec,c.unit,c.type,c.category,c.price‘)
???????????->select();
???????if(isset($this->number[$cid][‘contentlist‘])){//配件数量
???????????$this->contentnumber[$cid] = count($info);
???????}else{
???????????$this->contentnumber[$cid]= count($info);
???????}
?????foreach($info as $k=>$v){
????????$this->alllist[] = array_merge($this->smallarr[$cid],$v);
?????}
?????unset($this->smallarr[$cid]);
???}
???/*
????*
????* 导出xls 获取最底层数据
????*
????* */
???public function getexcelsmallarr($pid = 0){
???????if($pid == 0){
???????????$where = ‘pid is null‘;
???????}else{
???????????$where = ‘pid = ‘.$pid;
???????}
???????$arr = Db::name(‘DomelampNode‘)->where($where)->select();
???????if(empty($arr)){
????????$info = Db::name(‘DomelampNode‘)->where(‘id‘,$pid)->find();
????????
???????????if($pid != 0)$this->smallarr[$info[‘id‘]][‘class‘][] = $info[‘name‘];
????????if(!isset($this->classnumber[$info[‘id‘]])){//分类数量
???????????$this->classnumber[$info[‘id‘]]=1;
????????}
???????????$this->getallarr($info[‘id‘],$info[‘pid‘]);
???????????$this->getalllist($info[‘id‘]); //配件信息
???????}else{
???????????foreach($arr as $k=>$v){
???????????????$this->getexcelsmallarr($v[‘id‘]);
????????}
???????}
???}
???/*
????*
????* 导出xls 获取完整数据
????*
????* */
???public function getallarr($k,$id = 0){
???????$info = Db::name(‘DomelampNode‘)->where(‘id‘,$id)->find();
?????if(isset($this->classnumber[$k])){//分类数量
????????$this->classnumber[$k]++;
?????}
?????array_unshift($this->smallarr[$k][‘class‘],$info[‘name‘]);
???????if($info[‘pid‘]>0){
???????????$this->getallarr($k,$info[‘pid‘]);
???????}
???}
???/*
????*
????* 导出xls 获取方式（调用方法）
????*
????* */
???public function getlist(){
???????$this->smallarr = array();
???????$this->getexcelsmallarr();
?????return [‘max_classnumber‘=> max($this->classnumber),‘list‘=>$this->alllist];
???}
}
/*
*导出excel方法 个人定义在common文件下面
* Excel表格导出全部数据 可规定格式
* data = allinfo 全部分类数据导出，已修改数据格式
* data = format 一般数据导出， 数据为二维数组即可
* EXCEL 列如ABC 宽度个数 可根据实际情况设置
* */
function excelExport($fileName = ‘‘, $headArr = [], $data = [] ,$format = ‘‘) {
???vendor (‘PHPExcel.PHPExcel‘);
???$objPHPExcel = new \PHPExcel();
???$objPHPExcel->getProperties();
???$key = ord("A"); // 设置表头
???foreach ($headArr as $v) {
???????$colum = chr($key);
???????$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1‘, $v);
???????$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1‘, $v);
???????//设置列宽 新加
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘A‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘B‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘C‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘D‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘E‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘F‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘G‘)->setWidth(‘20‘);
???????$objPHPExcel->getActiveSheet()->getColumnDimension(‘H‘)->setWidth(‘20‘);
???????//下面注释的这行代码是让表头拥有筛选功能，根据需要取消注释即可
???????//$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
???????$key += 1;
???}
???$column = 2;
???$objActSheet = $objPHPExcel->getActiveSheet();
???if($format == ‘allinfo‘){
???????$classMax = $data[‘max_classnumber‘];
???????$list = $data[‘list‘];
???????foreach ($list as $k=>$row){
???????????$class = $row[‘class‘];
???????????$number = count($class);
???????????if($number<$classMax){
???????????????$need = $classMax - $number;
???????????????for($i=0;$i<$need;$i++){
???????????????????array_push($class,‘‘);
???????????????}
???????????}
???????????unset($row[‘class‘]);
???????????$list[$k] = array_merge($class,$row);
???????????$span = ord("A");
???????????foreach ($list[$k] as $keyName => $value) { // 列写入
???????????????$objActSheet->setCellValue(chr($span) . $column, $value);
???????????????$span++;
???????????}
???????????$column++;
???????}
???}
???if($format == ‘format‘){
???????foreach ($data as $key => $rows) { // 行写入
???????????$span = ord("A");
???????????foreach ($rows as $keyName => $value) { // 列写入
???????????????$objActSheet->setCellValue(chr($span) . $column, $value);
???????????????$span++;
???????????}
???????????$column++;
???????}
???}
???$fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
???$objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
???header(‘Content-Type: application/vnd.ms-excel‘);
???header("Content-Disposition: attachment;filename=‘$fileName‘");
???header(‘Cache-Control: max-age=0‘);
???$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘);
???$objWriter->save(‘php://output‘); // 文件通过浏览器下载
???exit();
}
无限极分类+商品导出excel（Thinkphp5,数据读取无限极，个人限制导出5级）
原文地址：https://www.cnblogs.com/www-xiaobo-com/p/9597243.html