PHPExcel导出生成超过26列及复杂表头样式

2023-08-22 12:08:52

最近做项目遇到一个导出excel的需求，马上想到了PHPExcel插件，以前一直以为导出挺简单的，没想到这次遇到了那么坑！在此记录一下！

要导出的excel表格样式：

基本上就这个样子吧，总共96列！

具体代码贴上：

$objPHPExcel = new \PHPExcel();

// 设置文档属性
$objPHPExcel->getProperties()->setCreator("zhuanli")
->setLastModifiedBy("zhuanli")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");

//生成列名，A-Z AA-AZ BA-BZ CA-CT
$col_arr=range("A","Z");
foreach (range("A","Z") as $val){
array_push($col_arr,'A'.$val);
}
foreach (range("A","Z") as $val){
array_push($col_arr,'B'.$val);
}
foreach (range("A","T") as $val){
array_push($col_arr,'C'.$val);
}

// 字体和样式
/**设置二 SOOPAT链接获取信息*/
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12); //设置全局默认的字体大小
$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getFont()->getColor()->setARGB('#FF0000'); //第二行【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getFont()->setBold(true);//设置【是否有结果】字体加粗

/**这里是设置K2:S2单元格背景色 start*/
$objPHPExcel->getActiveSheet()->getStyle('K2:S2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('K2:S2')->getFill()->getStartColor()->setARGB('00FFC000');

/**这里是设置K2:S2单元格背景色 end*/
$objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->setBold(true);//设置字体加粗

/**设置三百度信用网获取信息 */
$objPHPExcel->getActiveSheet()->getStyle('T2:V2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('T2:V2')->getFill()->getStartColor()->setARGB('00FFFF00');
$objPHPExcel->getActiveSheet()->getStyle('T2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('T2')->getFont()->setBold(true);//设置字体加粗

/**设置四 SIPO网站信息摘录以及图片文字分析和*/
$objPHPExcel->getActiveSheet()->getStyle('W2:AS2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('W2:AS2')->getFill()->getStartColor()->setARGB('0092D050');
$objPHPExcel->getActiveSheet()->getStyle('W2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('Y2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('Z2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AN2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AO2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AP2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AQ2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AR2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AS2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('W2')->getFont()->setBold(true);//设置字体加粗

/**设置五搜索引擎搜索结果摘录（一）背景色和*/
$objPHPExcel->getActiveSheet()->getStyle('AT2:BN2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('AT2:BN2')->getFill()->getStartColor()->setARGB('00FFFF00');
$objPHPExcel->getActiveSheet()->getStyle('BO2:BZ2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('BO2:BZ2')->getFill()->getStartColor()->setARGB('0092D050');
$objPHPExcel->getActiveSheet()->getStyle('CA2:CF2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('CA2:CF2')->getFill()->getStartColor()->setARGB('000070C0');
$objPHPExcel->getActiveSheet()->getStyle('AT2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AT2')->getFont()->setBold(true);//设置字体加粗

/**设置五搜索引擎搜索结果摘录（二）背景色和*/
$objPHPExcel->getActiveSheet()->getStyle('CG2:CR2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('CG2:CR2')->getFill()->getStartColor()->setARGB('00FFC000');
$objPHPExcel->getActiveSheet()->getStyle('CG2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色

// 设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('T1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('W1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('AT1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('CG1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// 设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('T1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('W1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('AT1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('CG1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// 设置行高度
// $objPHPExcel->getActiveSheet()->getDefaultRowDimension('2')->setRowHeight(35); //设置默认行高
// $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize();
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(35); //第二行行高

//设置列宽
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//设置默认列宽

//设置自动换行
$objPHPExcel->getActiveSheet()->getStyle("B2:CS2")->getAlignment()->setWrapText(TRUE); // a1 到a100 单元格，字符串自动换行

// 合并
$objPHPExcel->getActiveSheet()->mergeCells('A1:A2');
$objPHPExcel->getActiveSheet()->mergeCells('B1:J1');
$objPHPExcel->getActiveSheet()->mergeCells('K1:S1');
$objPHPExcel->getActiveSheet()->mergeCells('T1:V1');
$objPHPExcel->getActiveSheet()->mergeCells('W1:AS1');
$objPHPExcel->getActiveSheet()->mergeCells('AT1:CF1');
$objPHPExcel->getActiveSheet()->mergeCells('CG1:CR1');

$key = 0;
foreach($col_arr as $v){
//注意，不能少了。将列数字转换为字母\
$colum = \PHPExcel_Cell::stringFromColumnIndex($key);
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A1",'是否有结果');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("B1",'专利信息TXT文档摘录及自动形成其他专利信息');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("K1",'二 SOOPAT链接获取信息');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("T1",'三百度信用网获取信息');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("W1",'四 SIPO网站信息摘录以及图片文字分析');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AT1",'五搜索引擎搜索结果摘录（一）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CG1",'五搜索引擎搜索结果摘录（二）');

/*专利信息TXT文档摘录及自动形成其他专利信息*/
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("B2",'专利号（申请号）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("C2",'申请日期');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("D2",'专利权人');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("E2",'专利名称');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("F2",'附图链接');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("G2",'专利类型');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("H2",'专利权人类型');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("I2",'专利处于第几年');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("J2",'SOOPAT链接--构成形式');

/*二 SOOPAT链接获取信息*/
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("K2",'是否已经运行"二部分"');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("L2",'专利权人地址-SOOPAT获取');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("M2",'专利主分类号-SOOPAT获取');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("N2",'代理机构名称-SOOPAT获取');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("O2",'代理人-SOOPAT获取');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("P2",'法律状态-是否显示有“专利权终止”或“专利全部无效”');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("Q2",'专利是否部分无效');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("R2",'专利部分无效的内容');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("S2",'转让后的申请人-SOOPAT获取');

//三百度信用网获取信息
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("T2",'是否已经运行“三部分”');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("U2",'专利权人成立时间（公司）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("V2",'专利权人注册资金（公司）');

//四 SIPO网站信息摘录以及图片文字分析
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("W2",'是否已经运行“四部分”');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("X2",'案件状态-申请信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("Y2",'有否无效宣告决定书');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("Z2",'有否评价报告');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AA2",'主分类号-申请信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AB2",'代理机构名称-申请信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AC2",'第一代理人-申请信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AD2",'评价报告缴费有否-费用信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AE2",'评价报告缴费日期-费用信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AF2",'无效宣告决定书有否-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AG2",'无效宣告决定书次数-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AH2",'最近无效宣告决定书发文日-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AI2",'评价报告有否-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AJ2",'评价报告发文日-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AK2",'无效宣告请求受理通知书有否-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AL2",'最近无效宣告请求受理通知书发文时间-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AM2",'最近无效宣告请求受理通知书专利权人收件信息-发文信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AN2",'无效文件有否-审查信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AO2",'最近一次无效文件显示专利状态');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AP2",'部分有效的权利要求');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AQ2",'评价报告有否-审查信息页');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AR2",'评价报告显示专利状态');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AS2",'部分有效的权利要求');

//五搜索引擎搜索结果摘录（一）
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AT2",'是否已经运行“五（一）部分”');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AU2",'搜索是否有结果-（原告+专利号）或（原告+权利人+专利名称）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AV2",'搜索结果数之和-（原告+专利号）或（原告+权利人+专利名称）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AW2",'搜索最近快照时间-（原告+专利号）或（原告+权利人+专利名称）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AX2",'搜索最远快照时间-（原告+专利号）或（原告+权利人+专利名称）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AY2",'搜索是否有结果-（原告+专利权人+专利）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("AZ2",'搜索结果数之和-（原告+专利权人+专利）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BA2",'搜索最近快照时间-（原告+专利权人+专利）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BB2",'搜索最远快照时间-（原告+专利权人+专利）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BC2",'百度搜索结果链接1-（“原告+空格+专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BD2",'百度搜索结果数量1-（“原告+空格+专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BE2",'百度搜索结果1-最近快照时间-（“原告+空格+专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BF2",'百度搜索结果1-最远快照时间-（“原告+空格+专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BG2",'百度搜索结果2链接-（“原告+空格+权利人+空格+专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BH2",'百度搜索结果2数量-（“原告+空格+权利人+空格+专利名称”））');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BI2",'百度搜索结果2-最近快照时间-（“原告+空格+权利人+空格+专利名称”））');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BJ2",'百度搜索结果2-最远快照时间-（“原告+空格+权利人+空格+专利名称”））');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BK2",'百度搜索结果3链接-（“原告+空格+权利人+空格+专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BL2",'百度搜索结果3数量-（“原告+空格+权利人+空格+专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BM2",'百度搜索结果3-最近快照时间-（“原告+空格+权利人+空格+专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BN2",'百度搜索结果3-最远快照时间-（“原告+空格+权利人+空格+专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BO2",'搜狗结果链接1-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BP2",'搜狗搜索结果数量1-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BQ2",'搜狗搜索结果1-最近快照时间-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BR2",'搜狗搜索结果1-最远快照时间-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BS2",'搜狗搜索结果2链接-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BT2",'搜狗搜索结果2数量-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BU2",'搜狗搜索结果2-最近快照时间-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BV2",'搜狗搜索结果2-最近快照时间-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BW2",'搜狗搜索结果3链接-（“原告”AND“权利人”AND“专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BX2",'搜狗搜索结果3数量-（“原告”AND“权利人”AND“专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BY2",'搜狗搜索结果3-最近快照时间-（“原告”AND“权利人”AND“专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("BZ2",'搜狗搜索结果3-最远快照时间-（“原告”AND“权利人”AND“专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CA2",'360搜索结果链接1-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CB2",'360搜索结果数量1-（“原告”AND“专利号”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CC2",'360搜索结果2链接-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CD2",'360搜索结果2数量-（“原告”AND“权利人”AND“专利名称”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CE2",'360搜索结果3链接-（“原告”AND“权利人”AND“专利”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CF2",'360搜索结果3数量-（“原告”AND“权利人”AND“专利”）');
// $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1','');
//五搜索引擎搜索结果摘录（二）
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CG2",'是否已经运行“五（二）部分”');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CH2",'搜索是否有结果-（原告+专利权人）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CI2",'搜索结果数之和-（原告+专利权人）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CJ2",'搜索最近快照时间-（原告+专利权人）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CK2",'百度搜索结果链接1-（“原告+空格+专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CL2",'百度搜索结果数量1-（“原告+空格+专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CM2",'百度搜索结果1-最近快照时间-（“原告+空格+专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CN2",'搜狗结果链接1-（“原告”AND“专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CO2",'搜狗搜索结果数量1-（“原告”AND“专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CP2",'搜狗搜索结果1-最近快照时间-（“原告”AND“专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CQ2",'360搜索结果链接1-（“原告”AND“专利权人”）');
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("CR2",'360搜索结果数量1-（“原告”AND“专利权人”）');
$key += 1;

}

//赋值
$column = 3; //从第三行写入数据第一二行是表头
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($body as $key => $rows){ //行写入

foreach ($col_arr as $key2=>$rows2){
$span=0;
/**专利信息TXT文档摘录及自动形成其它专利信息*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_result']);//是否有结果
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_number']);//专利号（申请号）
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['apply_date']);//申请日期
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_person']);//专利权人
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_name']);//专利名称
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['atta_link']);//附图链接
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_type']);//专利类型
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_person_type']);//专利权人类型
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['in_year']);//专利处于递进
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['soopat_link']);//SOOPAT链接--构成形式
/**二 SOOPAT链接获取信息*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_run']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patentee_addr']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['patent_main_type']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['agency_name']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['agent']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['legal_status']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['invalid']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['invalid_content']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['transfer_applicant']);
/**三百度信用机构王获取信息*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_run']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['establish_time']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['register_funds']);
/**四 SIPO网站信息摘录以及图片文字分析*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_run']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['case_status']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['declaration']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_evaluate']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['main_type']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['agency']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['first_agent']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['eval_pay']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['eval_pay_time']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['declaration_content']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['declaration_content_num']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['lately_declaration_time']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_eval']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['eval_time']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AL']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AM']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AN']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AO']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AP']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AQ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AR']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AS']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AT']);
/**五搜索引擎搜索结果摘录（一）*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_run']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AV']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AW']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AX']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AY']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['AZ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BA']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BB']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BC']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BD']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BE']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BF']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BG']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BH']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BI']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BJ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BK']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BL']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BM']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BN']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BO']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BP']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BQ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BR']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BS']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BT']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BU']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BV']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BW']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BX']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BY']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['BZ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CA']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CB']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CC']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CD']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CE']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CF']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CG']);
/**五搜索引擎搜索结果摘录（二）*/
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['is_run']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CI']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CJ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CK']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CL']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CM']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CN']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CO']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CP']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CQ']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CR']);
$objActSheet->setCellValue($col_arr[$span++].$column,$rows['CS']);
}
$column++;
}

$objPHPExcel->getActiveSheet()->setTitle('目标专利导出表');

$objPHPExcel->setActiveSheetIndex(0);

// 设置输出
$tableName ='专利信息'.date('Y-m-d H:i:s').'输出文档';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' .$tableName. '.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

return $objWriter->save('php://output');//导出

码农公寓

相关文章