PHP PHPExcel_RichText类代码示例

 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);
//.........这里部分代码省略.........

 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}");
 }

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

 public function testDataTypeForRichTextObject()
 {
     $objRichText = new PHPExcel_RichText();
     $objRichText->createText('Hello World');
     $expectedResult = PHPExcel_Cell_DataType::TYPE_INLINE;
     $result = call_user_func(array('PHPExcel_Cell_DefaultValueBinder', 'dataTypeForValue'), $objRichText);
     $this->assertEquals($expectedResult, $result);
 }

/**
 * 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}");
 }

 /**
  * Добавляет таблицу со списком новостей
  *
  * @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());
 }

 /**
  * 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();
     }
 }

function sales_summary($data)
{
    /**
     * PHPExcel
     *
     * Copyright (C) 2006 - 2014 PHPExcel
     *
     * This library is free software; you can redistribute it and/or
     * modify it under the terms of the GNU Lesser General Public
     * License as published by the Free Software Foundation; either
     * version 2.1 of the License, or (at your option) any later version.
     *
     * This library is distributed in the hope that it will be useful,
     * but WITHOUT ANY WARRANTY; without even the implied warranty of
     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
     * Lesser General Public License for more details.
     *
     * You should have received a copy of the GNU Lesser General Public
     * License along with this library; if not, write to the Free Software
     * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
     *
     * @category   PHPExcel
     * @package    PHPExcel
     * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
     * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
     * @version    1.8.0, 2014-03-02
     */
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
    date_default_timezone_set('Europe/London');
    /**
     * PHPExcel
     *
     * Copyright (C) 2006 - 2014 PHPExcel
     *
     * This library is free software; you can redistribute it and/or
     * modify it under the terms of the GNU Lesser General Public
     * License as published by the Free Software Foundation; either
     * version 2.1 of the License, or (at your option) any later version.
     *
     * This library is distributed in the hope that it will be useful,
     * but WITHOUT ANY WARRANTY; without even the implied warranty of
     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
     * Lesser General Public License for more details.
     *
     * You should have received a copy of the GNU Lesser General Public
     * License along with this library; if not, write to the Free Software
     * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
     *
     * @category   PHPExcel
     * @package    PHPExcel
     * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
     * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
     * @version    1.8.0, 2014-03-02
     */
    /** Error reporting */
    error_reporting(E_ALL);
    /** Include PHPExcel */
    require_once dirname(__FILE__) . '../Classes/PHPExcel.php';
    // Create new PHPExcel object
    //echo date('H:i:s') , " Create new PHPExcel object" , EOL;
    $objPHPExcel = new PHPExcel();
    // Set document properties
    //echo date('H:i:s') , " Set document properties" , EOL;
    $objPHPExcel->getProperties()->setCreator("EPSI")->setLastModifiedBy("MAC")->setTitle("Check Disbursement")->setSubject("Report")->setDescription("Deatiled Report");
    // ->setKeywords("Employee DTR Summary")
    // ->setCategory("Employee DTR Summary");
    // Create a first sheet, representing sales data
    $sheet = $objPHPExcel->getActiveSheet();
    //HEADER
    $objRichText = new PHPExcel_RichText();
    $objPayable = $objRichText->createTextRun('EXCELLENT PERFORMANCE SERVICES INC.');
    $objPayable->getFont()->setBold(true);
    $objPayable->getFont()->setSize(14);
    $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
    $sheet->getCell('A1')->setValue($objRichText);
    //DESCRIPTION
    $objRichText = new PHPExcel_RichText();
    $objPayable = $objRichText->createTextRun('CHECK DISBURSEMENT SUMMARY REPORT');
    $objPayable->getFont()->setBold(true);
    $objPayable->getFont()->setSize(12);
    $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
    $sheet->getCell('A2')->setValue($objRichText);
    //COVERED DATE
    $objRichText = new PHPExcel_RichText();
    $objPayable = $objRichText->createTextRun('MM/DD/YYYY');
    $objPayable->getFont()->setBold(true);
    $objPayable->getFont()->setSize(12);
    $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
    $sheet->getCell('A3')->setValue($objRichText);
    // Put COntent in the cell. Declare first the cell location, then put the content. -mich
    $objPHPExcel->setActiveSheetIndex(0);
    $newdata = $data->result_array();
    $sheet->setCellValue('A5', 'ACCOUNT TITLE');
    $sheet->setCellValue('B5', '');
    $sheet->setCellValue('C5', 'DEBIT');
    $sheet->setCellValue('D5', 'CREDIT');
//.........这里部分代码省略.........

$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');
// Add rich-text string
echo date('H:i:s'), " Add rich-text string", EOL;
$objRichText = new PHPExcel_RichText();
$objRichText->createText('This invoice is ');
$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);
// Merge cells
echo date('H:i:s'), " Merge cells", EOL;
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');
// Just to test...
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
// Just to test...
// Protect cells

 private function _parseRichText($is = '')
 {
     $value = new PHPExcel_RichText();
     $value->createText(self::_convertStringEncoding($is, $this->_charSet));
     return $value;
 }

$objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')->setCellValue('B2', 'Symbols')->setCellValue('C2', '!+&=()~§±æþ');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')->setCellValue('B3', 'UTF-8')->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')->setCellValue('B4', 'Integer')->setCellValue('C4', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')->setCellValue('B5', 'Float')->setCellValue('C5', 34.56);
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')->setCellValue('B6', 'Negative')->setCellValue('C6', -7.89);
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')->setCellValue('B7', 'True')->setCellValue('C7', true);
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')->setCellValue('B8', 'False')->setCellValue('C8', false);
$dateTimeNow = time();
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Rename worksheet
echo date('H:i:s'), " Rename worksheet", EOL;
$objPHPExcel->getActiveSheet()->setTitle('Datatypes');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file

 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);
}

//.........这里部分代码省略.........
                     }
                     */
                     // offset 4; size: 2; index to XF record
                     $xfindex = $this->_getInt2d($recordData, 4);
                     // add BIFF8 style information
                     if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
                         $sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
                         if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
                             $numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
                         }
                     }
                     //$this->_addcell($row, $column, $string, $raw);
                     //$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
                     $sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
                     break;
                 case self::XLS_Type_LABELSST:
                     /**
                      * LABELSST
                      *
                      * This record represents a cell that contains a string. It
                      * replaces the LABEL record and RSTRING record used in
                      * BIFF2-BIFF5.
                      *
                      * --	"OpenOffice.org's Documentation of the Microsoft
                      * 		Excel File Format"
                      */
                     $row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
                     $column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
                     $xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
                     $index = $this->_GetInt4d($this->_data, $spos + 6);
                     //$this->_addcell($row, $column, $this->_sst[$index]);
                     if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
                         // then we have rich text
                         $richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
                         $charPos = 0;
                         for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
                             if (isset($fmtRuns[$i])) {
                                 $text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
                                 $charPos = $fmtRuns[$i]['charPos'];
                             } else {
                                 $text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
                             }
                             if (mb_strlen($text) > 0) {
                                 $textRun = $richText->createTextRun($text);
                                 if (isset($fmtRuns[$i - 1])) {
                                     if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
                                         $fontIndex = $fmtRuns[$i - 1]['fontIndex'];
                                     } else {
                                         // this has to do with that index 4 is omitted in all BIFF versions for some strange reason
                                         // check the OpenOffice documentation of the FONT record
                                         $fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
                                     }
                                     $textRun->getFont()->applyFromArray($this->_font[$fontIndex]);
                                 }
                             }
                         }
                     } else {
                         $sheet->setCellValueByColumnAndRow($column, $row + 1, $this->_sst[$index]['value']);
                     }
                     // add BIFF8 style information
                     if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
                         $sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
                     }
                     break;
                 case self::XLS_Type_MULRK:
                     /**