Sivo 悉见

收藏下phpexcel常用的字体设置，颜色设置和单元格保护

匿名 · 更新于 2017/11/19

  1. function createExcel($array) {  
  2.         if(empty($array)) return false;  
  3.         /** Error reporting */  
  4.         /*error_reporting(E_ALL); 
  5.         ini_set('display_errors', TRUE); 
  6.         ini_set('display_startup_errors', TRUE);*/  
  7.         set_time_limit(0);  
  8.         $o_ci = & get_instance();  
  9.         $o_ci->load->library('PHPExcel');//引入PHPExcel类库  
  10.         $objPHPExcel  = new PHPExcel();//实例化PHPExcel类  
  11.         //设置文档基本属性  
  12.         $objPHPExcel->getProperties()  
  13.                  ->setCreator('Gary.F.Dong') //设置创建者  
  14.                  ->setLastModifiedBy(date('Y-m-d',time())) //设置时间  
  15.                  ->setTitle('Filters') //设置标题  
  16.                  ->setSubject('产品Filters选项') //设置备注  
  17.                  ->setDescription('导出所有产品的Filters') //设置描述  
  18.                  ->setKeywords('Filters') //设置关键字 | 标记  
  19.                  ->setCategory('Filters'); //设置类别  
  20.         //$objPHPExcel->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中  
  21.         // 循环所有的产品线，一个产品线作为一个sheet  
  22.         $index = 0;  
  23.         foreach ($array['prodlines'] as $key => $value) {  
  24.             //设置默认行高  
  25.             $objPHPExcel->getActiveSheet($index)->getDefaultRowDimension()->setRowHeight(20);  
  26.             //设置单元格的对齐方式  
  27.             $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居右  
  28.             //设置第一列的字体大小  
  29.             $objPHPExcel->getActiveSheet()->getStyle("A1:A".(count($array['spectitle'][$value['conf_name']])+1))->getFont()->setSize(14);  
  30.             //设置单元格保护  
  31.             //$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);  // 为了使任何表保护，需设置为真  
  32.             //$objPHPExcel->getActiveSheet()->protectCells("A1:A".(count($array['spectitle'][$value['conf_name']])+1), 'Gary' ); // 加密密码是 Gary  
  33.             //取得当前行数和列数,然后取消中间部分的单元格保护  
  34.             $all_rows_num = count($array['spectitle'][$value['conf_name']])+1;  
  35.             $all_column_num = count($array['allprod'][$value['conf_name']])+1;  
  36.             $columnString = PHPExcel_Cell::stringFromColumnIndex($all_column_num);  
  37.             //$objPHPExcel->getActiveSheet()->getStyle("B2:".$columnString.$all_rows_num)->getProtection()->setLocked( PHPExcel_Style_Protection::PROTECTION_UNPROTECTED );  
  38.             //如果当前产品线的attributes为空，跳过  
  39.             if(empty($array['spectitle'][$value['conf_name']])) {continue;}  
  40.             //if(in_array($value['conf_name'], array('led_lighting','lighting','monitor'))) continue;  
  41.             //这只A1 栏位的标题  
  42.             $objPHPExcel->setActiveSheetIndex($index)->setCellValue('A1','Filter');  
  43.             $objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFont()->setSize(14);  
  44.             $objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFont()->setBold(true);  
  45.             $objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);  
  46.             //由于列数多大上百列，所以采取动态循环的方式  
  47.             $column = 2;  
  48.             foreach($array['spectitle'][$value['conf_name']] as $spectitlekey => $rows){ //行写入,循环行数  
  49.                 //$span = ord("B"); //获取A的ASCII码  
  50.                 $span = 1;  
  51.                 //设置父级填充颜色  
  52.                 if($rows['parent_id'] ==0){  
  53.                     $objPHPExcel->getActiveSheet()->getStyle('A'.$column)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);  
  54.                     $objPHPExcel->getActiveSheet()->getStyle('A'.$column)->getFill()->getStartColor()->setRGB('cd99fe');  
  55.                 }else{  //否则按照单双号去设置颜色  
  56.                     /*if($column%2==1){ 
  57.                         $objPHPExcel->getActiveSheet($index)->getStyle('A'.$column)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); 
  58.                         $objPHPExcel->getActiveSheet($index)->getStyle('A'.$column)->getFill()->getStartColor()->setRGB('ddddff'); 
  59.                     }else{ 
  60.                         $objPHPExcel->getActiveSheet($index)->getStyle('A'.$column)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); 
  61.                         $objPHPExcel->getActiveSheet($index)->getStyle('A'.$column)->getFill()->getStartColor()->setRGB('efefef'); 
  62.                     }*/  
  63.   
  64.                 }  
  65.                 $objPHPExcel->setActiveSheetIndex($index)->setCellValue('A'.$column,$rows['search_item']);  
  66.                 // 设置宽度  
  67.                 $objPHPExcel->getActiveSheet($index)->getColumnDimension('A')->setAutoSize(true);  
  68.                 //$obpe->getActiveSheet()->getColumnDimension('B')->setWidth(10);  
  69.                 foreach($array['allprod'][$value['conf_name']] as $prodid=>$products){// 列写入  
  70.                     if(empty($products)) continue;  
  71.                     //$j = chr($span);  
  72.                     $j = PHPExcel_Cell::stringFromColumnIndex($span);  
  73.                     //写入每一列第一行产品名  
  74.                     $objPHPExcel->setActiveSheetIndex($index)->setCellValue($j.'1',$products['model'] );  
  75.                     //$objPHPExcel->getActiveSheet()->getStyle( $j.'1')->getFont()->setSize(14);  
  76.                     //$objPHPExcel->getActiveSheet()->getStyle( $j.'1')->getFont()->setBold(true);  
  77.                     //$objPHPExcel->getActiveSheet()->getStyle($j.'1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);  
  78.   
  79.                     $curSpecValue = $array['specvalues'][$value['conf_name']][$products['prod_id']][$rows['search_item']];  
  80.                     if(empty($curSpecValue||!isset($curSpecValue))) $curSpecValue='';  
  81.                     $objPHPExcel->setActiveSheetIndex($index)->setCellValue($j.$column,$curSpecValue );  
  82.                     $objPHPExcel->getActiveSheet($index)->getColumnDimension($j)->setAutoSize(true);  
  83.                     $span++;  
  84.                 }  
  85.                 $column++;  
  86.             }   
  87.             // Rename worksheet  
  88.             $objPHPExcel->getActiveSheet($index)->setTitle($value['conf_name']);  
  89.             $index++;  
  90.             $objPHPExcel->createSheet();  
  91.             $objPHPExcel->setActiveSheetIndex($index);  
  92.             //if($index==3) break;  
  93.   
  94.         }  
  95.         // Set active sheet index to the first sheet, so Excel opens this as the first sheet  
  96.         $objPHPExcel->setActiveSheetIndex(0);  
  97.         ob_end_clean(); // Added by me  
  98.         ob_start(); // Added by me  
  99.         $filename = 'filters-'.date('Y-m-d',time()).'.xlsx';  
  100.   
  101.         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');  
  102.         $objWriter->save(BQ_ADM_ROOT.'/media/tmp/'.$filename);  
  103.         return BQ_ADM_URL.'/media/tmp/'.$filename;  
  104.   
  105.     }