PHP PHPExcel_RichText::createText方法代码示例

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

$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
echo date('H:i:s'), " Protect cells", EOL;

 /**
  * Read LABELSST record
  * 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"
  */
 private function _readLabelSst()
 {
     $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
     $recordData = substr($this->_data, $this->_pos + 4, $length);
     // move stream pointer to next record
     $this->_pos += 4 + $length;
     // offset: 0; size: 2; index to row
     $row = $this->_GetInt2d($recordData, 0);
     // offset: 2; size: 2; index to column
     $column = $this->_GetInt2d($recordData, 2);
     $columnString = PHPExcel_Cell::stringFromColumnIndex($column);
     // Read cell?
     if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
         // offset: 4; size: 2; index to XF record
         $xfIndex = $this->_GetInt2d($recordData, 4);
         // offset: 6; size: 4; index to SST record
         $index = $this->_GetInt4d($recordData, 6);
         // add cell
         if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) {
             // then we should treat as rich text
             $richText = new PHPExcel_RichText($this->_phpSheet->getCell($columnString . ($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) {
                     if ($i == 0) {
                         // first text run, no style
                         $richText->createText($text);
                     } else {
                         $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->setFont(clone $this->_objFonts[$fontIndex]);
                         }
                     }
                 }
             }
         } else {
             $this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $this->_sst[$index]['value'], PHPExcel_Cell_DataType::TYPE_STRING);
         }
         // add style information
         if (!$this->_readDataOnly) {
             $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
         }
     }
 }

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

	require $dir."/db.php";//引入mysql操作类文件
	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");//给文字加上链接地址
	/**代码结束**/

 $activeSheet->getStyle('C2:G2')->applyFromArray($canhgiua)->applyFromArray($indam);
 $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);

 private 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) {
             $objText = $value->createTextRun(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
             if (isset($run->rPr->rFont["val"])) {
                 $objText->getFont()->setName($run->rPr->rFont["val"]);
             }
             if (isset($run->rPr->sz["val"])) {
                 $objText->getFont()->setSize($run->rPr->sz["val"]);
             }
             if (isset($run->rPr->color)) {
                 $objText->getFont()->setColor(new PHPExcel_Style_Color($this->_readColor($run->rPr->color)));
             }
             if (isset($run->rPr->b["val"]) && ($run->rPr->b["val"] == 'true' || $run->rPr->b["val"] == '1')) {
                 $objText->getFont()->setBold(true);
             }
             if (isset($run->rPr->i["val"]) && ($run->rPr->i["val"] == 'true' || $run->rPr->i["val"] == '1')) {
                 $objText->getFont()->setItalic(true);
             }
             if (isset($run->rPr->u["val"]) && ($run->rPr->u["val"] == 'true' || $run->rPr->u["val"] == '1')) {
                 $objText->getFont()->setUnderline(true);
             }
             if (isset($run->rPr->strike["val"]) && ($run->rPr->strike["val"] == 'true' || $run->rPr->strike["val"] == '1')) {
                 $objText->getFont()->setStriketrough(true);
             }
         }
     }
     return $value;
 }

 public function create_excel($cart, $data)
 {
     /** Error reporting */
     error_reporting(E_ALL);
     //define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
     date_default_timezone_set('Europe/London');
     /** Include PHPExcel */
     require_once dirname(__FILE__) . '/excel/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("Margina Radu")->setLastModifiedBy("Margina Radu")->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");
     // Create a first sheet, representing sales data
     //echo date('H:i:s') , " Add some data" , EOL;
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Corden: Invoice');
     $objPHPExcel->getActiveSheet()->setCellValue('F1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
     $objPHPExcel->getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
     //$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
     $objPHPExcel->getActiveSheet()->setCellValue('A12', 'ID');
     $objPHPExcel->getActiveSheet()->setCellValue('B12', 'Simbol');
     $objPHPExcel->getActiveSheet()->setCellValue('C12', 'Denumire');
     $objPHPExcel->getActiveSheet()->setCellValue('D12', 'Cantitatea');
     $objPHPExcel->getActiveSheet()->setCellValue('E12', 'Pret');
     $objPHPExcel->getActiveSheet()->setCellValue('F12', 'Subtotal');
     $index = 13;
     $start_index = 13;
     foreach ($cart as $item) {
         $objPHPExcel->getActiveSheet()->setCellValue('A' . $index, $item['id']);
         $objPHPExcel->getActiveSheet()->setCellValue('B' . $index, $item['simbol']);
         $objPHPExcel->getActiveSheet()->setCellValue('C' . $index, $item['name']);
         $objPHPExcel->getActiveSheet()->setCellValue('D' . $index, $item['qty']);
         $objPHPExcel->getActiveSheet()->setCellValue('E' . $index, $item['price']);
         $objPHPExcel->getActiveSheet()->setCellValue('F' . $index, $item['subtotal']);
         $index++;
     }
     $end_index = $index - 1;
     $total_index = $index + 1;
     $objPHPExcel->getActiveSheet()->setCellValue('E' . $total_index, 'Pret Total:');
     $objPHPExcel->getActiveSheet()->setCellValue('F' . $total_index, '=SUM(F13:F' . $end_index . ')');
     // Add comment
     //echo date('H:i:s') , " Add comments" , EOL;
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setAuthor('PHPExcel');
     $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('PHPExcel:');
     $objCommentRichText->getFont()->setBold(true);
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun("\r\n");
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('Total amount on the current invoice, including VAT.');
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setWidth('100pt');
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setHeight('100pt');
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setMarginLeft('150pt');
     $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getFillColor()->setRGB('EEEEEE');
     // Add rich-text string
     //echo date('H:i:s') , " Add rich-text string" , EOL;
     $objRichText = new PHPExcel_RichText();
     $objRichText->createText("Nume: ");
     $objPayable = $objRichText->createTextRun($data['nume'] . " " . $data['prenume']);
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText("\nEmail : ");
     $objPayable = $objRichText->createTextRun($data['email']);
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText("\nTelefon : ");
     $objPayable = $objRichText->createTextRun($data['telefon']);
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText("\nAdresa : ");
     $objPayable = $objRichText->createTextRun($data['adresa']['orasul'] . "," . $data['adresa']['localitatea'] . ", str. " . $data['adresa']['strada'] . " " . $data['adresa']['numarul'] . ", ap. " . $data['adresa']['apartament']);
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText("\nMetoda de plata : ");
     $objPayable = $objRichText->createTextRun($data['payment']);
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objRichText->createText("\nSunet de confirmare : ");
     $objPayable = $objRichText->createTextRun(isset($data['call_after']) ? $data['call_after'] : 'nu');
     $objPayable->getFont()->setBold(true);
     $objPayable->getFont()->setItalic(true);
     $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
     $objPHPExcel->getActiveSheet()->getCell('A3')->setValue($objRichText);
     // Merge cells
     //echo date('H:i:s') , " Merge cells" , EOL;
     $objPHPExcel->getActiveSheet()->mergeCells('A3:F10');
     // Just to test...
     // Protect cells
     //echo date('H:i:s') , " Protect cells" , EOL;
     // $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);	// Needs to be set to true in order to enable any worksheet protection!
     //$objPHPExcel->getActiveSheet()->protectCells('A3:F13', 'PHPExcel');
     // Set cell number formats
     //echo date('H:i:s') , " Set cell number formats" , EOL;
     //$objPHPExcel->getActiveSheet()->getStyle('E4:F13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
     // Set column widths
     //echo date('H:i:s') , " Set column widths" , EOL;
//.........这里部分代码省略.........

$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);
$objRichText2 = new PHPExcel_RichText();
$objRichText2->createText("black text\n");
$objRed = $objRichText2->createTextRun("red text");
$objRed->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2);
$objPHPExcel->getActiveSheet()->getStyle("C14")->getAlignment()->setWrapText(true);
$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 95 file
echo date('H:i:s'), " Write to Excel5 format", EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

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

 /**
  * Spreadsheet mit Daten befüllen
  *
  * @param $phpExcel PHPExcel-Objekt
  */
 private function xlsData(&$phpExcel)
 {
     global $LANG;
     $sheet = $phpExcel->getActiveSheet();
     $sheet->setTitle($LANG->getLL('workbook_title'));
     $data = $this->getDbData();
     $col = 0;
     $row = 0;
     // Kopf
     foreach ($this->export_cols as $spalte => $breite) {
         $sheet->getCellByColumnAndRow($col, 1)->setValueExplicit($LANG->getLL('col_' . $spalte), PHPExcel_Cell_DataType::TYPE_STRING);
         $col++;
     }
     $sheet->getStyle('A1:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . '1')->applyFromArray($this->format_head);
     // Daten
     foreach ($data as $row => $dataRow) {
         $col = 0;
         foreach ($this->export_cols as $dbName => $breite) {
             // je nach Typ als String, Integer oder Formel
             // wenn der Wert leer ist, dann natürlich gar nix
             if (isset($dataRow[$dbName])) {
                 // Textauszug hat Sonderbehandlung
                 if ($dbName == 'excerpt') {
                     $arr = $this->getTextExcerpt($dataRow['id']);
                     if (is_array($arr) && count($arr)) {
                         $content = new PHPExcel_RichText();
                         $content->createText('...' . $arr['before'] . ' ');
                         if (!empty($arr['comment'])) {
                             $selection = $content->createTextRun($arr['comment']);
                             $selection->getFont()->setBold(true);
                             $selection->getFont()->setItalic(true);
                             $selection->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
                         }
                         $content->createText(' ' . $arr['after'] . '...');
                         $sheet->getCellByColumnAndRow($col, $row + 2)->setValue($content);
                     }
                     $col++;
                     continue;
                 } elseif ($dbName == 'timestamp') {
                     $content = date('d.m.Y H:i', $dataRow[$dbName]);
                 } else {
                     $content = $dataRow[$dbName];
                 }
                 $dataType = is_numeric($content) ? PHPExcel_Cell_DataType::TYPE_NUMERIC : PHPExcel_Cell_DataType::TYPE_STRING;
             } elseif ($dbName == 'avg') {
                 $content = '=$F' . ($row + 2) . '-$G' . ($row + 2);
                 $dataType = PHPExcel_Cell_DataType::TYPE_FORMULA;
             } else {
                 $col++;
                 continue;
             }
             $sheet->getCellByColumnAndRow($col, $row + 2)->setValueExplicit($content, $dataType);
             $col++;
         }
     }
     // Spaltenbreiten
     $col = 0;
     foreach ($this->export_cols as $spalte => $breite) {
         if ($breite == 'auto') {
             $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
         } else {
             $sheet->getColumnDimensionByColumn($col)->setWidth($breite);
         }
         $col++;
     }
     // Formatierung Datenpart
     $sheet->getStyle('A2:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . (count($data) + 1))->applyFromArray($this->format_body);
     $sheet->getStyle('A1:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . (count($data) + 1))->getAlignment()->setWrapText(true);
 }

$objBold->getFont()->setBold(true);
$objRichText->createText(' Đánh dấu X vào ngày làm thực công việc đó');
$activeSheet->setCellValue('D' . ($count + 9 + 11), $objRichText);
$activeSheet->mergeCells('D' . ($count + 9 + 11) . ':O' . ($count + 9 + 11));
unset($objBold);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun('Ý KIẾN/ĐỀ XUẤT:');
$objBold->getFont()->setBold(true);
$objRichText->createText(' Ghi ý kiến đề xuất giải quyết công việc nếu có');
$activeSheet->setCellValue('D' . ($count + 9 + 12), $objRichText);
$activeSheet->mergeCells('D' . ($count + 9 + 12) . ':O' . ($count + 9 + 12));
unset($objBold);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun('TRƯỞNG PHÒNG ĐÁNH GIÁ:');
$objBold->getFont()->setBold(true);
$objRichText->createText(' Dành cho Lãnh đạo phòng Ghi nội dung đánh giá công việc');
$activeSheet->setCellValue('D' . ($count + 9 + 13), $objRichText);
$activeSheet->mergeCells('D' . ($count + 9 + 13) . ':O' . ($count + 9 + 13));
/**
 * start export
*/
ob_end_clean();
$str = $arr[0]->tennhanvien;
$arr = explode(' ', $str);
$tennv = $arr[count($arr) - 1];
for ($i = 0; $i <= count($arr) - 2; $i++) {
    $tennv .= $arr[$i][0];
}
$excelFileName = 'Báo cáo công việc tuần - ' . $tennv . '_' . date('dmY');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $excelFileName . '.xlsx"');

 protected function parseRichText($is = '')
 {
     $value = new PHPExcel_RichText();
     $value->createText(self::convertStringEncoding($is, $this->charSet));
     return $value;
 }