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);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet();
//----------------------CONSULTA LOS REGISTROS A EXPORTAR---------------
$result = $this->listado($condiciones);
$cont_linea = 0;
foreach ($result as $reg) {
$reg['nombre'] = trim($reg['nombre']);
$reg['direccion'] = trim($reg['direccion']);
$reg['telefono'] = trim($reg['telefono']);
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['direccion']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['telefono']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['estado']);
}
// end foreach
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(5);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Listado Agencias');
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "ListadoAgencias.xlsx");
}
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()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
// Set fonts
//echo date('H:i:s') , " Set fonts" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getFont()->setBold(true);
// Set alignments
//echo date('H:i:s') , " Set alignments" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B13')->getAlignment()->setShrinkToFit(true);
// Set thin black border outline around column
//echo date('H:i:s') , " Set thin black border outline around column" , EOL;
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A13:F' . $end_index)->applyFromArray($styleThinBlackBorderOutline);
// Set thick brown border outline around "Total"
//echo date('H:i:s') , " Set thick brown border outline around Total" , EOL;
$styleThickBrownBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('argb' => 'FF993300'))));
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index . ':F' . $total_index . '')->applyFromArray($styleThickBrownBorderOutline);
// Set fills
//echo date('H:i:s') , " Set fills" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('80FFFFFF');
// Set style for header row using alternative method
//echo date('H:i:s') , " Set style for header row using alternative method" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A12:F12')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF'))));
$objPHPExcel->getActiveSheet()->getStyle('A12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('B12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
$objPHPExcel->getActiveSheet()->getStyle('F12')->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
// Unprotect a cell
//echo date('H:i:s') , " Unprotect a cell" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Add a drawing to the worksheet
//echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath($_SERVER['DOCUMENT_ROOT'] . '/assets/img/images/logo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
//echo date('H:i:s') , " Set header/footer" , EOL;
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
//echo date('H:i:s') , " Set page orientation and size" , EOL;
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Rename first worksheet
//echo date('H:i:s') , " Rename first worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Invoice');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/excel/PHPExcel/IOFactory.php';
// Save Excel 2007 file
//echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', 'invoice.xlsx'));
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
//echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
//echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
//echo date('H:i:s') , " Done writing files" , EOL;
//echo 'Files have been created in ' , getcwd() , 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));
$objRichText->createText($texto_color_ventas_id);
$objInventario = $objRichText->createTextRun(' Calidad: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_calidad_variedad_id);
$objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText);
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
//------------------------------ Registro de Fecha de Generacion --------------------------------
$row++;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
//$col_fin = $PHPExcelApp->getNameFromNumber(11);
$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_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Variedad");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Color");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "40");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "50");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "60");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "70");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "80");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "90");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "100");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "110");
//----------------------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);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(11)->setWidth(6);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($estilo_titulo));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
// $objPHPExcel->getActiveSheet()->getStyle($col_ini.$row.':'.$col_fin.$row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
//----------------------CONSULTA LOS REGISTROS A EXPORTAR---------------
//$result = $this->listado($condiciones);
$cont_linea = 0;
$row_detalle_info_ini = $row + 1;
foreach ($result_procesar as $reg) {
if (!empty($reg['variedad_id'])) {
$reg['variedad_id'] = trim($reg['variedad_id']);
}
//end if
if (!empty($reg['color_ventas_nombre'])) {
$reg['color_ventas_nombre'] = trim($reg['color_ventas_nombre']);
}
//end if
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['variedad_id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
if ($reg['tallos_x_bunch'] == 25) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
} else {
$objRichText = new \PHPExcel_RichText();
$objRichText->createText($reg['variedad']);
$objInventario = $objRichText->createTextRun(' (' . $reg['tallos_x_bunch'] . ')');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setItalic(true);
$col_variedad = $PHPExcelApp->getNameFromNumber(2);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\Application\Classes\PHPExcelApp::COLOR_ORANGE));
$objPHPExcel->getActiveSheet()->getCell($col_variedad . $row)->setValue($objRichText);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad'] );
}
//end if
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['color_ventas_nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['40']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['50']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['60']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['70']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['80']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['90']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
}
// end foreach
//Formato de Numeros
$col_ini = $PHPExcelApp->getNameFromNumber(4);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$row_detalle_info_ini = $row_detalle_ini + 1;
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ':' . $col_fin . $row)->getNumberFormat()->setFormatCode("#,###");
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($reg_finca['id'] . ' - ' . $reg_tipo_caja['tipo_caja_id']);
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
}
//end foreach
}
//end foreach($result_fincas as $reg_finca)
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Dispo Grupo.xlsx");
}
/**
* 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;
}
/**
*
* @param array $condiciones (criterio_busqueda, busqueda_color , busqueda_estado)
*/
public function generarExcel($condiciones)
{
set_time_limit(0);
ini_set('memory_limit', '-1');
$ClienteDAO = new ClienteDAO();
$ClienteDAO->setEntityManager($this->getEntityManager());
//----------------Se configura las Etiquetas de Seleccion-----------------
$texto_criterio_busqueda = 'TODOS';
$texto_estado = 'TODOS';
if (!empty($condiciones['criterio_busqueda'])) {
$texto_criterio_busqueda = $condiciones['criterio_busqueda'];
}
//end if
switch ($condiciones['busqueda_estado']) {
case 'A':
$texto_estado = 'ACTIVO';
break;
case 'I':
$texto_estado = 'INACTIVO';
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(10);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "LISTADO DE CLIENTES");
$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(11);
$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);
$objCalidad = $objRichText->createTextRun(' Estado: ');
$objCalidad->getFont()->setBold(true);
$objCalidad->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_estado);
$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(10);
//$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);
//-------------------------ESTABLECE TITULO DE COLUMNAS----------------------------
$row = $row + 1;
$row_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Nombre");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Direccion");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Pais");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "Estado");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "Cod. Postal");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "Telefono");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "Fax");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "Email");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "Estado");
//-------------------------ESTABLECE ANCHO DE COLUMNAS----------------------------
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet();
$result = $this->listado($condiciones);
$cont_linea = 0;
foreach ($result as $reg) {
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['direccion']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['pais_nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['nombre_estados']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['codigo_postal']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['telefono1']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['fax1']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['email']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['estado']);
}
//end foreach
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(10);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Listado Clientes');
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Listado_Clientes.xlsx");
}
if (!empty($_REQUEST['pageNow'])) {
$fenyePage->pageNow = $_REQUEST['pageNow'];
}
//调用getFenyePage，该方法可以把fenyePage完成
$nationService->getFenyePage($fenyePage);
include '../Nation/nationFrontQuery.php';
} else {
if ($action == "OutToExcel") {
require_once './PHPExcel/PHPExcel.php';
require_once './PHPExcel/phpExcel/IOFactory.php';
require_once '../service/NationService.class.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
$objPHPExcel->setActiveSheetIndex(0);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('');
$objPayable = $objRichText->createTextRun('民族信息信息记录');
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$objPayable->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFill()->getStartColor()->setARGB('00FFFF00');
// 底纹
$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
$objPHPExcel->getActiveSheet()->getCell('A1')->setValue($objRichText);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
// 加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
// 字体大小
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
// 文本颜色
$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);
$objRichText3 = new PHPExcel_RichText();
$objRichText3->createText("Hello ");
$objUnderlined = $objRichText3->createTextRun("underlined");
$objUnderlined->getFont()->setUnderline(true);
$objRichText3->createText(' World.');
$objPHPExcel->getActiveSheet()->getCell("C15")->setValue($objRichText3);
$objPHPExcel->getActiveSheet()->setCellValue('A17', 'Hyperlink');
$objPHPExcel->getActiveSheet()->setCellValue('C17', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->setCellValue('C18', '=HYPERLINK("mailto:abc@def.com","*****@*****.**")');
// 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
/**
*
* @param array $condiciones (inventario_id, proveedor_id, clasifica, color_ventas_id, calidad_variedad_id)
*/
public function generarExcel($condiciones)
{
set_time_limit(0);
ini_set('memory_limit', '-1');
$InventarioDAO = new InventarioDAO();
$ProveedorDAO = new ProveedorDAO();
$CalidadDAO = new CalidadDAO();
$ColorVentasDAO = new ColorVentasDAO();
$CalidadVariedadDAO = new CalidadVariedadDAO();
$DispoDAO = new DispoDAO();
$InventarioDAO->setEntityManager($this->getEntityManager());
$ProveedorDAO->setEntityManager($this->getEntityManager());
$CalidadDAO->setEntityManager($this->getEntityManager());
$ColorVentasDAO->setEntityManager($this->getEntityManager());
$CalidadVariedadDAO->setEntityManager($this->getEntityManager());
$DispoDAO->setEntityManager($this->getEntityManager());
//----------------Se configura las Etiquetas de Seleccion-----------------
$texto_inventario = 'TODOS';
$texto_proveedor = 'TODOS';
$texto_calidad = 'TODAS';
$texto_color_ventas = 'TODOS';
$texto_calidad_variedad = 'TODAS';
if (!empty($condiciones['inventario_id'])) {
$InventarioData = $InventarioDAO->consultar($condiciones['inventario_id']);
$texto_inventario = $InventarioData->getNombre();
}
//end if
if (!empty($condiciones['proveedor_id'])) {
$ProveedorData = $ProveedorDAO->consultar($condiciones['proveedor_id']);
$texto_proveedor = $ProveedorData->getNombre();
}
//end if
if (!empty($condiciones['clasifica'])) {
$CalidadData = $CalidadDAO->consultarPorClasificaFox($condiciones['clasifica']);
$texto_calidad = $CalidadData->getNombre();
}
//end if
if (!empty($condiciones['color_ventas_id'])) {
$ColorVentasData = $ColorVentasDAO->consultar($condiciones['color_ventas_id']);
$texto_color_ventas = $ColorVentasData->getNombre();
}
//end if
if (!empty($condiciones['calidad_variedad_id'])) {
$CalidadVariedadData = $CalidadVariedadDAO->consultar($condiciones['calidad_variedad_id']);
$texto_calidad_variedad = $CalidadVariedadData->getNombre();
}
//end if
//----------------Se inicia la configuracion del PHPExcel-----------------
$PHPExcelApp = new PHPExcelApp();
$objPHPExcel = new \PHPExcel();
// Set document properties
$PHPExcelApp->setUserName('');
$PHPExcelApp->setMetaDataDocument($objPHPExcel);
//Se consultan los bunchs por fechas
$result_fechas = $DispoDAO->listadoAgrupadoPorFechaBunch($condiciones);
/*
* Se añade el primer elemento en nulo en las fechas para indicar que
* se obtendra toda la dispo de bunches en la primera pestaña del excel
*/
array_unshift($result_fechas, null);
$indice_hoja = -1;
foreach ($result_fechas as $reg_fecha) {
if (empty($reg_fecha)) {
$ind_fecha_dispo_final = count($result_fechas) - 1;
$print_fecha_dispo = $result_fechas[1]['fecha_bunch'] . ' al ' . $result_fechas[$ind_fecha_dispo_final]['fecha_bunch'];
} else {
$print_fecha_dispo = $reg_fecha['fecha_bunch'];
}
//end if
$indice_hoja++;
switch ($indice_hoja) {
case 0:
//El primer elemento debe ser NULL de reg_fecha
$condiciones_sin_fecha = $condiciones;
$result = $this->listadoSinVacios($condiciones_sin_fecha);
$titulo_hoja = 'Dispo General';
break;
default:
$condiciones_con_fecha = $condiciones;
$condiciones_con_fecha['fecha_bunch'] = $reg_fecha['fecha_bunch'];
$result = $this->listadoSinVacios($condiciones_con_fecha);
$objPHPExcel->createSheet($indice_hoja);
$titulo_hoja = $reg_fecha['fecha_bunch'];
break;
}
//end switch
$objPHPExcel->setActiveSheetIndex($indice_hoja);
//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(13);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "DISPONIBILIDAD GENERAL");
$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));
//------------------------------Registra criterios linea 1--------------------------
$row = 2;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(13);
$objRichText = new \PHPExcel_RichText();
$objRichText->createText('');
$objInventario = $objRichText->createTextRun('Inventario: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_inventario);
$objCalidad = $objRichText->createTextRun(' Calidad: ');
$objCalidad->getFont()->setBold(true);
$objCalidad->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_calidad);
$objProveedor = $objRichText->createTextRun(' Proveedor: ');
$objProveedor->getFont()->setBold(true);
$objProveedor->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_proveedor);
$objColor = $objRichText->createTextRun(' Color: ');
$objColor->getFont()->setBold(true);
$objColor->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_color_ventas);
$objCategoria = $objRichText->createTextRun(' Categoria: ');
$objCategoria->getFont()->setBold(true);
$objCategoria->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_calidad_variedad);
$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(13);
//$etiqueta = "";
$texto_fechas = "Fecha Flor: " . $print_fecha_dispo . " Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor();
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $texto_fechas);
$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);
//---------------------------------------------------------------------------------
$row = $row + 1;
$row_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Variedad");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, " ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Color");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "40");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "50");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "60");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "70");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "80");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "90");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "100");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, "110");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, "Total");
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(11)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(12)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(13)->setWidth(6);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet();
//$result = $this->listadoSinVacios($condiciones); //MORONITOR
$totales['40'] = 0;
$totales['50'] = 0;
$totales['60'] = 0;
$totales['70'] = 0;
$totales['80'] = 0;
$totales['90'] = 0;
$totales['100'] = 0;
$totales['110'] = 0;
$totales['total'] = 0;
$cont_linea = 0;
foreach ($result as $reg) {
$reg['variedad'] = trim($reg['variedad']);
$reg['total'] = $reg['40'] + $reg['50'] + $reg['60'] + $reg['70'] + $reg['80'] + $reg['90'] + $reg['100'] + $reg['110'];
//Array de Totales
$totales['40'] = $totales['40'] + $reg['40'];
$totales['50'] = $totales['50'] + $reg['50'];
$totales['60'] = $totales['60'] + $reg['60'];
$totales['70'] = $totales['70'] + $reg['70'];
$totales['80'] = $totales['80'] + $reg['80'];
$totales['90'] = $totales['90'] + $reg['90'];
$totales['100'] = $totales['100'] + $reg['100'];
$totales['110'] = $totales['110'] + $reg['110'];
$totales['total'] = $totales['total'] + $reg['total'];
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['variedad_id']);
if ($reg['tallos_x_bunch'] == 25) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
} else {
$objRichText = new \PHPExcel_RichText();
$objRichText->createText($reg['variedad']);
$objInventario = $objRichText->createTextRun(' (' . $reg['tallos_x_bunch'] . ')');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setItalic(true);
$col_variedad = $PHPExcelApp->getNameFromNumber(2);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\Application\Classes\PHPExcelApp::COLOR_ORANGE));
$objPHPExcel->getActiveSheet()->getCell($col_variedad . $row)->setValue($objRichText);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad'] );
}
//end if
if (!empty($reg['url_ficha'])) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, 'FOTO');
$objPHPExcel->getActiveSheet()->getCell('D' . $row)->getHyperlink()->setUrl($reg['url_ficha']);
} else {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, '');
}
//end if
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['color_ventas_nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['40']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['50']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['60']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['70']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['80']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['90']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $reg['100']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, $reg['110']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, $reg['total']);
//$objPHPExcel->getActiveSheet()->setCellValue('N'.$row,$reg['total'] );
}
//end foreach
//Totales
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, 'TOTALES');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $totales['40']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $totales['50']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $totales['60']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $totales['70']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $totales['80']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $totales['90']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $totales['100']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, $totales['110']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, $totales['total']);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
//Formato de Numeros
$col_ini = $PHPExcelApp->getNameFromNumber(5);
$col_fin = $PHPExcelApp->getNameFromNumber(13);
$row_detalle_info_ini = $row_detalle_ini + 1;
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ':' . $col_fin . $row)->getNumberFormat()->setFormatCode("#,###");
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(13);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($titulo_hoja);
}
//end foreach
$objPHPExcel->setActiveSheetIndex(0);
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Dispo General.xlsx");
}
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);
}
$objPHPExcel->getActiveSheet()->mergeCells("C{$cell_row}:E{$cell_row}");
$VendorPerson = stripslashes(utf8_encode($VendorPerson));
$objPHPExcel->getActiveSheet()->setCellValue("C{$cell_row}", "{$VendorPerson}");
//Contact Value Formatting
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}")->getFont()->setName('Calibri');
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}")->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides);
//Terms Header
$cell_row += 1;
$objPHPExcel->getActiveSheet()->mergeCells("C{$cell_row}:E{$cell_row}");
//Bold Terms Header
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun("TERMS: ");
$objBold->getFont()->setBold(true);
$VendorTerms = utf8_encode($VendorTerms);
$objRichText->createText("{$VendorTerms}");
$objPHPExcel->getActiveSheet()->getCell("C{$cell_row}")->setValue($objRichText);
//Terms Header Formatting
$objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setName('Calibri');
$objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides);
//Include PO Details Header
$cell_row += 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$cell_row}", "ITEM")->setCellValue("B{$cell_row}", "DESCRIPTION")->setCellValue("C{$cell_row}", "QTY")->setCellValue("D{$cell_row}", "PRICE")->setCellValue("E{$cell_row}", "AMOUNT");
//Header Formatting
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setName('Calibri');
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides);
// offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values
$matrixChunks = array();
for ($r = 1; $r <= $nr + 1; ++$r) {
$items = array();
for ($c = 1; $c <= $nc + 1; ++$c) {
$constant = self::_readBIFF8Constant($arrayData);
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;
}
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) {
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 ) );
if (isset($run->rPr->rFont["val"])) {
$objText->getFont()->setName((string) $run->rPr->rFont["val"]);
}
if (isset($run->rPr->sz["val"])) {
$objText->getFont()->setSize((string) $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"]) && ((string) $run->rPr->b["val"] == 'true' || (string) $run->rPr->b["val"] == '1'))
|| (isset($run->rPr->b) && !isset($run->rPr->b["val"])) ) {
$objText->getFont()->setBold(true);
}
if ( (isset($run->rPr->i["val"]) && ((string) $run->rPr->i["val"] == 'true' || (string) $run->rPr->i["val"] == '1'))
|| (isset($run->rPr->i) && !isset($run->rPr->i["val"])) ) {
$objText->getFont()->setItalic(true);
}
if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
$vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
if ($vertAlign == 'superscript') {
$objText->getFont()->setSuperScript(true);
}
if ($vertAlign == 'subscript') {
$objText->getFont()->setSubScript(true);
}
}
if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
} else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
}
if ( (isset($run->rPr->strike["val"]) && ((string) $run->rPr->strike["val"] == 'true' || (string) $run->rPr->strike["val"] == '1'))
|| (isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) ) {
$objText->getFont()->setStrikethrough(true);
}
}
}
}
return $value;
}
$objPHPExcel->getActiveSheet()->getStyle('B8:C8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B8:C8')->getFill()->getStartColor()->setARGB('FF808080');
//-----------------------------------设置边框------------------------/
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('C9:D10')->applyFromArray($styleThinBlackBorderOutline);
//----------------设置单元格宽度--------------------------------------/
//自动
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
//指定宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
//---------------合并表格(基本上已经满足所有)--------------------------/
$objPHPExcel->getActiveSheet()->mergeCells('F2:J2');
//------------------------表格文字样式相关-----------------------------/
$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));
$objPHPExcel->getActiveSheet()->setCellValue('B2', $objRichText);
//------------------------设置头样式，特别适合表头------------------------/
$objPHPExcel->getActiveSheet()->getStyle('A13:E13')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF'))));
//---------------------------------------------------------------------/
//--------------------------表名称转换---------------------------------/
$fileName = iconv("gbk", "utf-8", $fileName);
//---------------------------工作表区域重命名表---------------------------------/
