PHP PHPExcel_RichText::createTextRun方法代码示例

 public function generarExcel($condiciones)
 {
     set_time_limit(0);
     ini_set('memory_limit', '-1');
     $AgenciaCargaDAO = new AgenciaCargaDAO();
     $AgenciaCargaDAO->setEntityManager($this->getEntityManager());
     //----------------Se configura las Etiquetas de Seleccion-----------------
     $texto_criterio_busqueda = '';
     $texto_estado = 'TODOS';
     $texto_sincronizado = 'TODOS';
     if (!empty($condiciones['criterio_busqueda'])) {
         $texto_criterio_busqueda = $condiciones['criterio_busqueda'];
     }
     //end if
     switch ($condiciones['estado']) {
         case 'A':
             $texto_estado = 'ACTIVO';
             break;
         case 'I':
             $texto_estado = 'INACTIVO';
             break;
     }
     //end switch
     switch ($condiciones['sincronizado']) {
         case 'SINCRONIZADO':
             $texto_sincronizado = 'SINCRONIZADO';
             break;
         case 'PENDIENTE':
             $texto_sincronizado = 'PENDIENTE';
             break;
     }
     //end switch
     //----------------Se inicia la configuracion del PHPExcel-----------------
     $PHPExcelApp = new PHPExcelApp();
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $PHPExcelApp->setUserName('');
     $PHPExcelApp->setMetaDataDocument($objPHPExcel);
     $objPHPExcel->setActiveSheetIndex(0);
     //Configura el tamaño del Papel
     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
     //Se establece la escala de la pagina
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
     //Se establece los margenes de la pagina
     $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
     //------------------------------Registra la cabecera--------------------------------
     $row = 1;
     $col_ini = $PHPExcelApp->getNameFromNumber(0);
     $col_fin = $PHPExcelApp->getNameFromNumber(5);
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Agencia Carga");
     $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
     $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
     $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     //------------------------------Registra criterios linea 1--------------------------
     $row = 2;
     $col_ini = $PHPExcelApp->getNameFromNumber(0);
     $col_fin = $PHPExcelApp->getNameFromNumber(5);
     $objRichText = new \PHPExcel_RichText();
     $objRichText->createText('');
     $objInventario = $objRichText->createTextRun('     Criterio: ');
     $objInventario->getFont()->setBold(true);
     $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText($texto_criterio_busqueda);
     $objInventario = $objRichText->createTextRun('     Estado: ');
     $objInventario->getFont()->setBold(true);
     $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText($texto_estado);
     $objInventario = $objRichText->createTextRun('     Sincronizado: ');
     $objInventario->getFont()->setBold(true);
     $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText($texto_sincronizado);
     $objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText);
     $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
     //------------------------------ Registro de Fecha de Generacion --------------------------------
     $row = 3;
     $col_ini = $PHPExcelApp->getNameFromNumber(0);
     $col_fin = $PHPExcelApp->getNameFromNumber(5);
     //$etiqueta = "";
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor());
     $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
     $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
     $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
     //---------------------------IMPRIME TITULO DE COLUMNA-----------------------------
     $row = $row + 1;
     $row_detalle_ini = $row;
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Agencia");
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Direccion");
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Telefono");
     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "Estado");
     //----------------------AUTO DIMENSIONAR CELDAS DE ACUERDO AL CONTENIDO---------------
     $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
//.........这里部分代码省略.........

 protected function getBold($value)
 {
     $objRichText = new PHPExcel_RichText();
     $objPayable = $objRichText->createTextRun($value);
     $objPayable->getFont()->setBold(true);
     return $objRichText;
 }

 public function stream($filename, $data = null)
 {
     if ($data != null) {
         $col = 'A';
         foreach ($data[0] as $key => $val) {
             $objRichText = new PHPExcel_RichText();
             $objPayable = $objRichText->createTextRun(str_replace("_", " ", $key));
             $this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
             $col++;
         }
         $rowNumber = 2;
         foreach ($data as $row) {
             $col = 'A';
             foreach ($row as $cell) {
                 $this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
                 $col++;
             }
             $rowNumber++;
         }
     }
     header('Content-type: application/ms-excel');
     header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
     header("Cache-control: private");
     $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     $objWriter->save("./assets/{$filename}");
     header("location: " . SITE_LINK . "/assets/{$filename}");
     unlink(SITE_LINK . "/assets/{$filename}");
 }

 /**
  * Добавляет таблицу со списком новостей
  *
  * @param PHPExcel $objPHPExcel
  * @param array $newsReportData
  */
 protected function addNewsTable(PHPExcel $objPHPExcel, array $newsReportData)
 {
     $activeSheet = $objPHPExcel->getActiveSheet();
     $row = 15;
     $linkColor = new PHPExcel_Style_Color('FF538ed5');
     foreach ($this->campaign->news as $news) {
         $objRichText = new PHPExcel_RichText();
         $objRichText->createTextRun($news->name . "\n")->getFont()->setSize(10);
         $objRichText->createTextRun($news->url)->getFont()->setColor($linkColor)->setSize(10);
         $activeSheet->setCellValue('A' . $row, Yii::app()->dateFormatter->formatDateTime($news->create_date, 'short', null))->setCellValue('B' . $row, $objRichText)->setCellValue('C' . $row, isset($newsReportData[$news->id]) ? $newsReportData[$news->id]['clicks'] + $newsReportData[$news->id]['fake_clicks'] : 0);
         //            $activeSheet->getCell('B'.$row)->getHyperlink()->setUrl($news->url);
         $row++;
     }
     $activeSheet->setCellValue('A' . $row, 'Итого:')->setCellValue('B' . $row, count($this->campaign->news) . ' новостей')->setCellValue('C' . $row, $this->campaignReportData ? $this->campaignReportData['clicks'] + $this->campaignReportData['fake_clicks'] : 0);
     $this->formatTable($activeSheet, 'A', '14', 'C', $row, array('innerRowHeight' => 27.75 * 1.05));
     $activeSheet->getStyle('B15:B' . ($row - 1))->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'wrap' => true)));
     $activeSheet->getStyle('C15:C' . $row)->getNumberFormat()->setFormatCode(self::FORMAT_NUMBER_SEPARATED);
     $activeSheet->getColumnDimension('A')->setWidth(11.29 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(56.14 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(17.43 * 1.05);
     $this->setPageFit($activeSheet, self::FIT_TO_WIDTH);
     $this->addLogo($activeSheet);
     $this->setHeader($activeSheet, $this->getHeaders());
 }

/**
 * Create a XLS template
 *
 * @param string  $group     Group Name to include in template
 * @param string  $issue     Issue title to include in template
 * @param boolean $to_string return template as string or write to file
 *
 * @return list($xlsname, $xlsfile) $xlsname: name of xls file for client /
 *                                  $xlsfile: filepath to xls file or string containing xls file
 */
function createTemplate($group, $issue, $to_string = false, $format = "Excel5")
{
    global $tmpfiles;
    // get extension and make sure it's a supported type
    switch ($format) {
        case 'Excel2007':
            $ext = 'xlsx';
            break;
        case 'Excel5':
        default:
            $ext = 'xls';
            $format = 'Excel5';
            break;
    }
    // load template
    $inputFileType = PHPExcel_IOFactory::identify(XLS_TEMPLATE);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load(XLS_TEMPLATE);
    $sheet = $objPHPExcel->getSheet(0);
    $objRichText = new PHPExcel_RichText();
    $objBold = $objRichText->createTextRun(ISSUE_TAG);
    $objBold->getFont()->setBold(true);
    $objRichText->createText($issue->Title);
    $sheet->getCell(ISSUE_CELL)->setValue($objRichText);
    $objRichText = new PHPExcel_RichText();
    $objBold = $objRichText->createTextRun(GROUP_TAG);
    $objBold->getFont()->setBold(true);
    $objRichText->createText($group->Name);
    $sheet->getCell(GROUP_CELL)->setValue($objRichText);
    $objRichText = new PHPExcel_RichText();
    $objRichText->createText($issue->Description);
    $sheet->setCellValue(DESCRIPTION_CELL, $objRichText);
    $sheet->setSelectedCells(DATA_COLUMN_STATEMENT . DATA_ROW_MIN);
    $objPHPExcel->getProperties()->setCreator("IC-Discuss");
    $xlsname = Utils::sanitizeFilename(APP_TITLE . ' - ' . $group->Name . ' - ' . $issue->Title) . '.' . $ext;
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format);
    if ($to_string) {
        ob_start();
        $objWriter->save('php://output');
        $xlsfile = ob_get_clean();
    } else {
        $xlsfile = tempnam("tmp", $ext);
        $tmpfiles[] = $xlsfile;
        $objWriter->save($xlsfile);
    }
    return array($xlsname, $xlsfile);
}

 public function stream($filename, $data = null, $columns)
 {
     if ($data != null) {
         $col = 'A';
         foreach ($columns as $key => $val) {
             $objRichText = new PHPExcel_RichText();
             $objPayable = $objRichText->createTextRun($val);
             $this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
             $col++;
         }
         $rowNumber = 2;
         foreach ($data as $row) {
             $col = 'A';
             foreach ($columns as $key => $val) {
                 $cell = $row[$key];
                 if ($key == "clad_condition") {
                     $cell = $cell == 1 ? "New" : "Used";
                 } else {
                     if ($key == "clad_active") {
                         $cell = $cell == 1 ? "Active" : "In-Active";
                     } else {
                         if ($key == "clad_category") {
                             $catIds = explode("-", $cell);
                             $catId = $catIds[count($catIds) - 2];
                             $cell = $_SESSION['categories'][$catId]['cat_name'];
                         }
                     }
                 }
                 $this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
                 $col++;
             }
             $rowNumber++;
         }
     }
     header('Content-type: application/ms-excel');
     header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
     header("Cache-control: private");
     $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     $objWriter->save("export/{$filename}");
     header("location: " . base_url() . "export/{$filename}");
     unlink(base_url() . "export/{$filename}");
 }

 $a_date = $val[0]->namlamthem . "-" . $key . "-01";
 $tu = date("01/m/Y", strtotime($a_date));
 $den = date("t/m/Y", strtotime($a_date));
 $objRichText = new PHPExcel_RichText();
 $objRichText->createText('Tháng ' . $key . ': ');
 $objBold = $objRichText->createTextRun($tu . ' - ' . $den);
 $objBold->getFont()->setItalic(true)->setName('Times New Roman')->setSize(13);
 $activeSheet->setCellValue('B5', $objRichText);
 unset($objBold);
 $activeSheet->setCellValue('B4', 'GIẤY BÁO LÀM THÊM GIỜ');
 $activeSheet->mergeCells('B4:F4')->mergeCells('B5:F5');
 $activeSheet->getStyle('B4:F4')->applyFromArray($canhgiua)->applyFromArray($indam);
 $activeSheet->getStyle('B5:F5')->applyFromArray($canhgiua);
 $objRichText = new PHPExcel_RichText();
 $objRichText->createText('Họ và tên: ');
 $objBold = $objRichText->createTextRun($val[0]->tennhanvien);
 $objBold->getFont()->setBold(true)->setSize(13);
 $activeSheet->setCellValue('A7', $objRichText);
 unset($objBold);
 $activeSheet->setCellValue('A8', "Bộ phận công tác: Nhân viên phòng Phòng Phát triển phần mềm");
 $activeSheet->setCellValue('A10', 'Ngày, tháng')->setCellValue('B10', "Những công việc đã làm")->setCellValue('C10', "Những công việc đã làm")->setCellValue('C11', 'Từ giờ')->setCellValue('D11', 'Đến giờ')->setCellValue('E11', 'Tổng giờ')->setCellValue('F11', 'Đơn giá')->setCellValue('G11', 'Thành tiền');
 $activeSheet->mergeCells('A10:A11')->mergeCells('B10:B11')->mergeCells('C10:G10');
 $activeSheet->getStyle('A10:A11')->applyFromArray($canhgiua)->applyFromArray($indam);
 $activeSheet->getStyle('B10:B11')->applyFromArray($canhgiua)->applyFromArray($indam);
 $activeSheet->getStyle('C10:G10')->applyFromArray($canhgiua)->applyFromArray($indam);
 $activeSheet->getStyle('C11:G11')->applyFromArray($canhgiua);
 $tongthoigian = 0;
 for ($i = 0; $i < count($val); $i++) {
     $stt = 12 + $i;
     $activeSheet->setCellValue('A' . $stt, $val[$i]->ngaylamthem)->setCellValue('B' . $stt, $val[$i]->congvieclamthem)->setCellValue('C' . $stt, $val[$i]->timebatdau)->setCellValue('D' . $stt, $val[$i]->timeketthuc)->setCellValue('E' . $stt, $val[$i]->thoigian);
     $tongthoigian += $val[$i]->thoigian;

	require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
	$objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel
	$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
	/**插入图片代码开始**/
	$objDrawing=new PHPExcel_WorkSheet_Drawing();//获得一个图片的操作对象
	$objDrawing->setPath($dir."/image/imooc.jpg");//加载图片路径
	$objDrawing->setCoordinates("F6");//设置图片插入位置的左上角坐标
	$objDrawing->setWidth(500);//设置插入图片的大小
	//$objDrawing->setHeight(100);
	//$objDrawing->setOffsetX(20)->setOffsetY(40);//设定单元格内偏移量
	$objDrawing->setWorkSheet($objSheet);//将图片插入到sheet
	/**代码结束**/
	/**添加丰富的文字块**/
	$objRichText=new PHPExcel_RichText();//获得一个文字块操作对象
	$objRichText->createText("慕课网(imooc)");//添加普通的文字 不能操作样式
	$objStyleFont=$objRichText->createTextRun("是国内最大的IT技能免费培训平台");//生成可以添加样式的文字块
	$objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));//加一些样式
	$objRichText->createText(",课程丰富多样");
	$objSheet->getCell("F4")->setValue($objRichText);//将文字块插入sheet里
	/**代码结束**/
	/**添加批注代码开始**/
	$objSheet->mergeCells("F4:N4");//合并单元格
	$objSheet->getComment("F4")->getText()->createTextRun("Van:\r\n慕课网\n\n时尚时尚最时尚");//添加批注
	/**代码结束**/

	/**超链接代码开始**/
	$objSheet->setCellValue("I3","慕课网");//添加文字
	$objSheet->getStyle("I3")->getFont()->setSize(16)->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//添加样式
	$objSheet->getCell("I3")->getHyperlink()->setUrl("http://www.imooc.com");//给文字加上链接地址
	/**代码结束**/

 /**
  * @param $params
  * @return array
  */
 protected function headers($params)
 {
     $col = array();
     $wColumn = 'A';
     $last = '';
     $wRows = $params['skip_line'];
     $styleHeader = array();
     foreach ($params['fields'] as $field) {
         if (in_array($field, $params['skip_fields']['export'])) {
             continue;
         }
         $col[] = $this->translator->trans('ApplicationSonataClientOperationsBundle.list.' . $params['entity'] . '.' . $field);
         if ($params['entity'] == 'V05LIC' || $params['entity'] == 'A06AIB') {
             if (!isset($styleHeader[$field]) && ($field == 'regime' || $field == 'DEB')) {
                 $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'b6dde8')));
             }
         }
         if ($params['entity'] == 'DEBIntro' || $params['entity'] == 'DEBExped') {
             if (in_array($field, array('n_ligne', 'nomenclature', 'pays_destination', 'regime', 'masse_mette', 'unites_supplementaires', 'nature_transaction', 'conditions_livraison', 'mode_transport', 'departement', 'pays_origine', 'CEE'))) {
                 $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '000000')), 'font' => array('bold' => true, 'color' => array('argb' => 'ffffff')));
             } elseif ($field == 'valeur_fiscale' || $field == 'valeur_statistique') {
                 $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'ffff00')), 'font' => array('bold' => true, 'color' => array('argb' => 'ff3900')));
             }
             $this->_sheet->getStyle($wColumn . $wRows)->getBorders()->getTop()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE));
             $this->_sheet->getStyle($wColumn . $wRows)->getBorders()->getLeft()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE));
         }
         $this->_sheet->getStyle($wColumn . $wRows)->applyFromArray($this->getCustomStyleBorders($params, $field, 'header') + (isset($styleHeader[$field]) ? $styleHeader[$field] : array()))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(true);
         $this->_sheet->getStyle($wColumn . $wRows)->getFont()->setBold(true);
         $this->setWidthSize($wColumn, $field, $params);
         //            $this->_sheet->getColumnDimension($wColumn)->setAutoSize(true);
         $last = $wColumn;
         $wColumn++;
     }
     if ($params['entity'] == 'DEBIntro' || $params['entity'] == 'DEBExped') {
         $index = 1;
         $objRichText = new \PHPExcel_RichText();
         $objPayable = $objRichText->createTextRun("DECLARATION D'ECHANGES DE BIENS ENTRE ETATS MEMBRES DE LA C.E.E. (DEB)");
         $objPayable->getFont()->setName('Arial')->setBold(true)->setSize(10);
         $this->_sheet->getCell('A' . $index)->setValue($objRichText);
         $this->_sheet->mergeCells('A' . $index . ':' . $last . $index);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $this->_sheet->getRowDimension(1)->setRowHeight(25);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true);
         $index = 3;
         $objRichText = new \PHPExcel_RichText();
         $objPayable = $objRichText->createTextRun("FLUX");
         $objPayable->getFont()->setName('Arial')->setBold(true)->setSize(10);
         $this->_sheet->getCell('A' . $index)->setValue($objRichText);
         $this->_sheet->mergeCells('A' . $index . ':' . $last . $index);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->applyFromArray($this->_styleBorders);
         $this->_sheet->getRowDimension($index)->setRowHeight(25);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true);
         $index = 4;
         $objRichText = new \PHPExcel_RichText();
         $objPayable = $objRichText->createTextRun($params['entity'] == 'DEBExped' ? "EXPEDITION" : 'INTRODUCTION');
         $objPayable->getFont()->setName('Arial')->setSize(10);
         $this->_sheet->getCell('A' . $index)->setValue($objRichText);
         $this->_sheet->mergeCells('A' . $index . ':' . $last . $index);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->applyFromArray($this->_styleBorders);
         $this->_sheet->getRowDimension($index)->setRowHeight(25);
         $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true);
         $wColumn = 'A';
         $index = 6;
         foreach ($params['fields'] as $key => $field) {
             $this->_sheet->getCell($wColumn . $index)->setValue($key + 1);
             $this->_sheet->getStyle($wColumn . $index)->applyFromArray($this->_styleBorders + array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '000000')), 'font' => array('bold' => true, 'color' => array('argb' => 'ffffff'))));
             $this->_sheet->getStyle($wColumn . $index)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $this->_sheet->getStyle($wColumn . $index)->getBorders()->getLeft()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE));
             $wColumn++;
         }
     }
     $this->setRowHeight($wRows, $params);
     //autofilter
     $this->_sheet->setAutoFilter('A' . $wRows . ':' . $last . $wRows);
     return $col;
 }

 public function generarExcelInternoCajas($condiciones)
 {
     set_time_limit(0);
     ini_set('memory_limit', '-1');
     $GrupoDispoCabDAO = new GrupoDispoCabDAO();
     $ProveedorDAO = new ProveedorDAO();
     $GrupoDispoCabDAO->setEntityManager($this->getEntityManager());
     $ProveedorDAO->setEntityManager($this->getEntityManager());
     //----------------Se configura las Etiquetas de Seleccion-----------------
     $texto_grupo_dispo_cab_id = 'TODOS';
     $texto_color_ventas_id = 'TODOS';
     $texto_calidad_variedad_id = 'TODOS';
     $inventario_id = $condiciones['inventario_id'];
     if (!empty($condiciones['grupo_dispo_cab_id'])) {
         $texto_grupo_dispo_cab_id = $condiciones['grupo_dispo_cab_id'];
     }
     //end if
     if (!empty($condiciones['color_ventas_id'])) {
         $texto_color_ventas_id = $condiciones['color_ventas_id'];
     }
     //end if
     if (!empty($condiciones['calidad_variedad_id'])) {
         $texto_calidad_variedad_id = $condiciones['calidad_variedad_id'];
     }
     //end if
     //----------------Se inicia la configuracion del PHPExcel-----------------
     $PHPExcelApp = new PHPExcelApp();
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $PHPExcelApp->setUserName('');
     $PHPExcelApp->setMetaDataDocument($objPHPExcel);
     $objPHPExcel->setActiveSheetIndex(0);
     //Configura el tamaño del Papel
     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
     //Se establece la escala de la pagina
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
     //Se establece los margenes de la pagina
     $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
     $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
     //Consulta la lista de registros
     $condiciones['opcion_dispo'] = 'BUNCH_TODOS';
     $result_dispo = $this->listadoDisponibilidadPorProveedor($condiciones, true);
     //Convertir Dispo en Cajas
     $result_HB = $this->transformarDispoEnCajas($inventario_id, 'HB', $result_dispo);
     $result_QB = $this->transformarDispoEnCajas($inventario_id, 'QB', $result_dispo);
     //Consulta las fincas
     $result_fincas = $ProveedorDAO->consultarTodos();
     $estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO01;
     $estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA01;
     $indice_hoja = -1;
     $arr_tipo_caja[] = array('tipo_caja_id' => 'HB');
     $arr_tipo_caja[] = array('tipo_caja_id' => 'QB');
     foreach ($result_fincas as $reg_finca) {
         foreach ($arr_tipo_caja as $reg_tipo_caja) {
             switch ($reg_tipo_caja['tipo_caja_id']) {
                 case 'HB':
                     $result_procesar = $result_HB;
                     break;
                 case 'QB':
                     $result_procesar = $result_QB;
                     break;
             }
             //end switch
             /*----------------------------------------------------------------------------------------------------*/
             /*----------------------------------------------------------------------------------------------------*/
             /*----------------------------------------------------------------------------------------------------*/
             /*----------------------------------------------------------------------------------------------------*/
             $indice_hoja++;
             if ($indice_hoja > 0) {
                 $objPHPExcel->createSheet($indice_hoja);
                 $estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO02;
                 $estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA02;
             }
             //end if
             $objPHPExcel->setActiveSheetIndex($indice_hoja);
             //------------------------------Registra la cabecera--------------------------------
             $row = 1;
             $col_ini = $PHPExcelApp->getNameFromNumber(0);
             $col_fin = $PHPExcelApp->getNameFromNumber(11);
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Disponibilidad Por Grupo");
             $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
             $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
             $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             //------------------------------Registra criterios linea 1--------------------------
             $row++;
             $col_ini = $PHPExcelApp->getNameFromNumber(0);
             //$col_fin 			= $PHPExcelApp->getNameFromNumber(11);
             $objRichText = new \PHPExcel_RichText();
             $objRichText->createText('');
             $objInventario = $objRichText->createTextRun('     Grupo: ');
             $objInventario->getFont()->setBold(true);
             $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
             $objRichText->createText($texto_grupo_dispo_cab_id);
             $objInventario = $objRichText->createTextRun('     Color: ');
             $objInventario->getFont()->setBold(true);
             $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
//.........这里部分代码省略.........

function setForSpec($obj_xl, $cel, $t1, $v1, $t2, $v2, $t3, $v3)
{
    $objRichText = new PHPExcel_RichText();
    $objBold = $objRichText->createTextRun("{$t1}\n");
    $objBold->getFont()->setBold(true);
    $objRichText->createText($v1);
    $objBold2 = $objRichText->createTextRun("{$t2}\n");
    $objBold2->getFont()->setBold(true);
    $objRichText->createText($v2);
    $objBold3 = $objRichText->createTextRun("{$t3}\n");
    $objBold3->getFont()->setBold(true);
    // $objRichText->createText($v3);
    $objBold3b = $objRichText->createTextRun("{$v3}\n");
    if (!$t3) {
        $objBold3b->getFont()->setBold(true);
    }
    $obj_xl->getCell($cel)->setValue($objRichText);
}

 /**
  * Write Rich Text
  *
  * @param     PHPExcel_Shared_XMLWriter    $objWriter         XML Writer
  * @param     string|PHPExcel_RichText    $pRichText        text string or Rich text
  * @param     string                        $prefix            Optional Namespace prefix
  * @throws     PHPExcel_Writer_Exception
  */
 public function writeRichTextForCharts(PHPExcel_Shared_XMLWriter $objWriter = null, $pRichText = null, $prefix = null)
 {
     if (!$pRichText instanceof PHPExcel_RichText) {
         $textRun = $pRichText;
         $pRichText = new PHPExcel_RichText();
         $pRichText->createTextRun($textRun);
     }
     if ($prefix !== null) {
         $prefix .= ':';
     }
     // Loop through rich text elements
     $elements = $pRichText->getRichTextElements();
     foreach ($elements as $element) {
         // r
         $objWriter->startElement($prefix . 'r');
         // rPr
         $objWriter->startElement($prefix . 'rPr');
         // Bold
         $objWriter->writeAttribute('b', $element->getFont()->getBold() ? 1 : 0);
         // Italic
         $objWriter->writeAttribute('i', $element->getFont()->getItalic() ? 1 : 0);
         // Underline
         $underlineType = $element->getFont()->getUnderline();
         switch ($underlineType) {
             case 'single':
                 $underlineType = 'sng';
                 break;
             case 'double':
                 $underlineType = 'dbl';
                 break;
         }
         $objWriter->writeAttribute('u', $underlineType);
         // Strikethrough
         $objWriter->writeAttribute('strike', $element->getFont()->getStrikethrough() ? 'sngStrike' : 'noStrike');
         // rFont
         $objWriter->startElement($prefix . 'latin');
         $objWriter->writeAttribute('typeface', $element->getFont()->getName());
         $objWriter->endElement();
         // Superscript / subscript
         //                    if ($element->getFont()->getSuperScript() || $element->getFont()->getSubScript()) {
         //                        $objWriter->startElement($prefix.'vertAlign');
         //                        if ($element->getFont()->getSuperScript()) {
         //                            $objWriter->writeAttribute('val', 'superscript');
         //                        } elseif ($element->getFont()->getSubScript()) {
         //                            $objWriter->writeAttribute('val', 'subscript');
         //                        }
         //                        $objWriter->endElement();
         //                    }
         //
         $objWriter->endElement();
         // t
         $objWriter->startElement($prefix . 't');
         //                    $objWriter->writeAttribute('xml:space', 'preserve');    //    Excel2010 accepts, Excel2007 complains
         $objWriter->writeRawData(PHPExcel_Shared_String::ControlCharacterPHP2OOXML($element->getText()));
         $objWriter->endElement();
         $objWriter->endElement();
     }
 }

//тут йде переприсвоєяння значень  $_SESSION['firstGroupVidnPosition_'.$i] щоб
//відлік починався з індексу 1
//
for ($i = 1; $i <= $_SESSION['num_group']; $i++) {
    $m = $i + 1;
    if ($m <= $_SESSION['num_group']) {
        $_SESSION['wr_firstGroupVidnPosition_' . $i] = $_SESSION['wr_firstGroupVidnPosition_' . $m];
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $_SESSION['wr_firstGroupVidnPosition_' . $i], 'Важливість груп');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $_SESSION['wr_firstGroupVidnPosition_' . $i], $_SESSION['r_vidn_gr_' . $i]);
    }
}
$resPosition = $_SESSION['newPosition'];
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$resPosition, 'Результати');
///////
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun('Результати');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objPHPExcel->getActiveSheet()->getCell('A' . $resPosition)->setValue($objRichText);
////////////
$resPosition += 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $resPosition, 'Стан підприємства');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $resPosition, $_SESSION['stan']);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $_SESSION['newPosition']);
$resPosition += 2;
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $resPosition);
for ($i = 1; $i <= $_SESSION['num_group']; $i++) {
    //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $resPosition);
    $text = 'Група ' . $_SESSION['name_gr_' . $i] . ' має рівень';
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $resPosition, $text);
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $resPosition, $_SESSION['level_' . $i]);

 public function setData($xlsfile, $dataArr = array(), $mode = "auto")
 {
     if ('auto' == $mode) {
         $ext = substr($xlsfile, strrpos($xlsfile, '.'));
         $xlstype = '.xls' == $ext ? 'Excel5' : 'Excel2007';
     } else {
         $xlstype = $mode;
     }
     $object = $this->LoadObject($xlstype);
     if (!$object) {
         return false;
     }
     $header = isset($dataArr['header']) ? $dataArr['header'] : null;
     $body = isset($dataArr['body']) ? $dataArr['body'] : null;
     if ($body) {
         foreach ($body as $sheet => $dataArr) {
             isset($index) ? $index++ : ($index = 0);
             $object->setActiveSheetIndex($index);
             $object->getActiveSheet()->setTitle($sheet);
             foreach ($dataArr as $row_sn => $row) {
                 foreach ($row as $col_sn => $cell) {
                     $col_letter = $this->excelColumn($col_sn);
                     //cell是一个数组
                     $value = $cell['value'];
                     $value = str_replace("\r", "\n", $value);
                     $objRichText = new PHPExcel_RichText();
                     // $objRichText->createText($value);
                     $objPayable = $objRichText->createTextRun($value);
                     if (!empty($cell['bold'])) {
                         $objPayable->getFont()->setBold(true);
                     }
                     if (!empty($cell['italic'])) {
                         $objPayable->getFont()->setItalic(true);
                     }
                     if (isset($cell['color']) && 6 == strlen($cell['color'])) {
                         $objPayable->getFont()->setColor(new PHPExcel_Style_Color('FF' . $cell['color']));
                     }
                     if (!empty($cell['autosize'])) {
                         $object->getActiveSheet()->getColumnDimension($col_letter)->setAutoSize(true);
                     }
                     // print_r(get_class_methods($objPayable->getFont()));exit;
                     // echo "$col_letter($col_sn).$row_sn =======";
                     // $objActSheet ->getStyle('A1')->getAlignment()->setShrinkToFit(true);//字体变小以适应宽
                     // $objActSheet ->getStyle('A1')->getAlignment()->setWrapText(true);//自动换行
                     $object->getActiveSheet()->setCellValue($col_letter . $row_sn, $objRichText);
                     // $object->getActiveSheet()->getStyle($col_letter.$row_sn)->getAlignment()->setShrinkToFit(true);//字体变小以适应宽
                     // $object->getActiveSheet()->getStyle($col_letter.$row_sn)->getAlignment()->setWrapText(true);//自动换行
                 }
                 // $object->getActiveSheet()->getRowDimension($row_sn)->setRowHeight(50);
                 // print_r(get_class_methods($object->getActiveSheet()->getRowDimension($row_sn)));exit;
             }
             // $object->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
             // $object->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
             // $object->getActiveSheet()->getColumnDimension('V')->setAutoSize(true);
         }
     }
     /*
     $object->getProperties()->setCreator("cty")
                          ->setLastModifiedBy("Maarten Balliauw")
                          ->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");
     
     $object->getDefaultStyle()->getFont()->setName('Arial')
                                       ->setSize(10);
     
     // Add some data, resembling some different data types
     $object->getActiveSheet()->setCellValue('A1', 'String')
                                   ->setCellValue('B1', 'Simple')
                                   ->setCellValue('C1', 'PHPExcel');
     
     $object->getActiveSheet()->setCellValue('A2', 'String')
                                   ->setCellValue('B2', 'Symbols')
                                   ->setCellValue('C2', '!+&=()~§±æþ');
     
     $object->getActiveSheet()->setCellValue('A3', 'String')
                                   ->setCellValue('B3', 'UTF-8')
                                   ->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');
     
     $object->getActiveSheet()->setCellValue('A4', 'Number')
                                   ->setCellValue('B4', 'Integer')
                                   ->setCellValue('C4', 12);
     
     $object->getActiveSheet()->setCellValue('A5', 'Number')
                                   ->setCellValue('B5', 'Float')
                                   ->setCellValue('C5', 34.56);
     
     $object->getActiveSheet()->setCellValue('A6', 'Number')
                                   ->setCellValue('B6', 'Negative')
                                   ->setCellValue('C6', -7.89);
     
     $object->getActiveSheet()->setCellValue('A7', 'Boolean')
                                   ->setCellValue('B7', 'True')
                                   ->setCellValue('C7', true);
     
     $object->getActiveSheet()->setCellValue('A8', 'Boolean')
                                   ->setCellValue('B8', 'False')
                                   ->setCellValue('C8', false);
//.........这里部分代码省略.........

 protected function _parseRichText($is = null)
 {
     $value = new PHPExcel_RichText();
     if (isset($is->t)) {
         $value->createText(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $is->t));
     } else {
         foreach ($is->r as $run) {
             if (!isset($run->rPr)) {
                 $objText = $value->createText(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
             } else {
                 $objText = $value->createTextRun(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
             }
         }
     }
     return $value;
 }