整合PHP Excel和PHPexcelReader到
librarys下面
两个excel整合成一个excel
<?php
class Excel extends Controller {
public function __construct() {
parent::Controller();
//初始化读取excel类
@$this->load->library("reader");
$reader = @$this->reader;
$reader->setOutputEncoding('UTF-8');
}
/// 将1的内容合并到2的内容当中从而生成3
public function getExcelContent(){
header("content-type:text/html;charset=utf-8");
$this -> load -> library('Excel/PHPExcel.php');
$this->load->library('Excel/PHPExcel/Reader/PHPExcel_Reader_Excel5.php');
$objReader=new PHPExcel_Reader_Excel5();
$objPHPExcel = new PHPExcel();
$data2=array();
$excel2="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_2.xls";
$this->reader->read($excel2);
$numRows1 = $this->reader->sheets [0] [numRows];
$numCols1 = $this->reader->sheets [0] [numCols];
$project1 = $this->reader->sheets [0] [cells];
if ($numRows1 > 0 && $numCols1 > 0) {
for($i=2;$i<=$numRows1;$i++){
$data2[$i]['id']=$project1[$i][1];
$data2[$i]['company']=$project1[$i][2];
$data2[$i]['name']=$project1[$i][3];
$data2[$i]['sex']=$project1[$i][4];
$data2[$i]['birthday']=$project1[$i][5];
$data2[$i]['cankao']=$project1[$i][6];
}
}
$excel1="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_1.xls";
$objPHPExcel = $objReader->load($excel1);
//获取sheet表数目
$sheetCount = $objPHPExcel->getSheetCount();
//默认选中sheet0表
$sheetSelected = 0;
$objPHPExcel->setActiveSheetIndex($sheetSelected);
//获取表格行数
$rowCount = $objPHPExcel->getActiveSheet()->getHighestRow();
//获取表格列数
$columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn();
$dataArr = array();
/** 循环读取每个单元格的数据 */
//行数循环
for ($row = 2; $row <= $rowCount; $row++){
//列数循环 , 列数是以A列开始
for ($column = 'E'; $column < $columnCount; $column++) {
//第一出现的位置不区分大小写 ，
if(stripos($objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue(),"，")>0){
$data_arr=explode("，",$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue());
foreach($data_arr as $val){
$dataArr[$row]["E"].=$val.";";
}
$dataArr[$row]["E"]=rtrim($dataArr[$row]["E"],";");
//第一出现的位置不区分大小写 、
}elseif(stripos($objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue(),"、")){
$data_arr1=explode("、",$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue());
foreach($data_arr1 as $val){
$dataArr[$row]["E"].=$val.";";
}
$dataArr[$row]["E"]=rtrim($dataArr[$row]["E"],";");
}else{
$dataArr[$row]["E"] = $objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();
}
$dataArr[$row]["F"] = $objPHPExcel->getActiveSheet()->getCell("F".$row)->getValue();
}
}
$objPHPExcel -> getDefaultStyle() -> getFont() -> setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel -> setActiveSheetIndex(0)
-> setCellValue('A1', '干部id(不可改)')
-> setCellValue('B1', '单位(不可改)')
-> setCellValue('C1', '姓名(不可改)')
-> setCellValue('D1', '性别(不可改)')
-> setCellValue('E1', '生日(不可改)')
-> setCellValue('F1', '职务参考(任免审批表填写职务)')
-> setCellValue('G1', '职务统计(可选值:党)')
-> setCellValue('H1', '排序(填写自然数字)');
$num1=count($data2);
foreach($data2 as $i=>$val){
$objPHPExcel -> getActiveSheet()-> setCellValue('A'.$i, $data2[$i]['id']);
$objPHPExcel -> getActiveSheet()-> setCellValue('B'.$i, $data2[$i]['company']);
$objPHPExcel -> getActiveSheet()-> setCellValue('C'.$i, $data2[$i]['name']);
$objPHPExcel -> getActiveSheet()-> setCellValue('D'.$i, $data2[$i]['sex']);
$objPHPExcel -> getActiveSheet()-> setCellValue('E'.$i, $data2[$i]['birthday']);
$objPHPExcel -> getActiveSheet()-> setCellValue('F'.$i, $data2[$i]['cankao']);
$objPHPExcel -> getActiveSheet()-> setCellValue('G'.$i, ";".$dataArr[$i]['E'].";");
$objPHPExcel -> getActiveSheet()-> setCellValue('H'.$i, $dataArr[$i]['F']);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=11.xls');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
// $objWriter -> save("/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry.xls");
}
public function getExcel1(){
//excel1内容
$excel1="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_1.xls";
$this->reader->read($excel1);
$numRows =$this->reader->sheets [0] [numRows];
$numCols = $this->reader->sheets [0] [numCols];
$project = $this->reader->sheets [0] [cells];
$msg = '';$msg2='';$count=0;$count2=0;$r=0;
if ($numRows > 0 && $numCols > 0) {
for($i=2;$i<=$numRows;$i++){
if(stripos($project[$i][5],"，")>0){
$data_arr=explode("，",$project[$i][5]);
foreach($data_arr as $val){
$data[$i]['position'].=$val.";";
}
$data[$i]['position']=rtrim($data[$i]['position'],";");
}else{
$data[$i]['position']=$project[$i][5];
}
$data[$i]['sorts']=$project[$i][6];
}
}
return $data;
}
public function getExcel2(){
//excel2内容
$excel2="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_2.xls";
$this->reader->read($excel2);
$numRows1 = $this->reader->sheets [0] [numRows];
$numCols1 = $this->reader->sheets [0] [numCols];
$project1 = $this->reader->sheets [0] [cells];
$msg = '';$msg2='';$count=0;$count2=0;$r=0;
if ($numRows1 > 0 && $numCols1 > 0) {
for($i=2;$i<=$numRows1;$i++){
$data2[$i]['id']=$project1[$i][1];
$data2[$i]['company']=$project1[$i][2];
$data2[$i]['name']=$project1[$i][3];
$data2[$i]['sex']=$project1[$i][4];
$data2[$i]['birthday']=$project1[$i][5];
$data2[$i]['cankao']=$project1[$i][6];
}
}
return $data2;
}
}
?>