public function index()
{
$this->load->model('Juego_model');
$rs = $this->Juego_model->get_entries();
foreach ($rs as $key => $value) {
$rs[$key]['total_incorrectas'] = (int) $value['puntos_total'] - (int) $value['puntos'];
$rs[$key]['more'] = $this->Juego_model->get_entries_detalle($value['id_juego']);
}
$this->load->library('Excel');
//$this->excel->setActiveSheetIndex(0);
$objPHPExcel = $this->excel;
// set hoja 2
$objPHPExcel = $this->_reportDetalle($objPHPExcel, $rs);
$sheet = $objPHPExcel->getSheet(1);
// Sheet 1
$objPHPExcel->getProperties()->setCreator("PLATICOM")->setLastModifiedBy("PLATICOM")->setTitle("REGISTROS PLATICOM-RESPONDE")->setSubject("REGISTROS PLATICOM-RESPONDE")->setDescription("REGISTROS PLATICOM-RESPONDE")->setKeywords("REGISTROS PLATICOM-RESPONDE")->setCategory("plataticom-records");
$tituloReporte = "REGISTROS PLATICOM-PLAY";
$titulosColumnas = array('INDICE', 'CODIGO USUARIO', 'RPTA CORRECTAS', 'RPTA INCORRECTAS', 'TOTAL PREGUNTAS', 'FECHA', '.', '.');
// set headers *excel*
$sheet->mergeCells('A1:E2');
$sheet->setCellValue('A1', $tituloReporte)->setCellValue('A4', $titulosColumnas[0])->setCellValue('B4', $titulosColumnas[1])->setCellValue('C4', $titulosColumnas[2])->setCellValue('D4', $titulosColumnas[3])->setCellValue('E4', $titulosColumnas[4])->setCellValue('F4', $titulosColumnas[5])->setCellValue('G4', $titulosColumnas[6])->setCellValue('H4', $titulosColumnas[7]);
// print data
$i = 5;
$contador = 1;
foreach ($rs as $item) {
$sheet->setCellValue('A' . $i, $contador)->setCellValue('B' . $i, $item['codigo_usuario'])->setCellValue('C' . $i, $item['puntos'])->setCellValue('D' . $i, $item['total_incorrectas'])->setCellValue('E' . $i, $item['puntos_total'])->setCellValue('F' . $i, $item['fecha_fin']);
$i++;
$contador++;
}
// STYLE EXCEL
$estiloTituloReporte = array('font' => array('name' => 'Verdana', 'bold' => true, 'italic' => false, 'strike' => false, 'size' => 16, 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF244062')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE));
$estiloTituloColumnas = array('font' => array('name' => 'Arial', 'bold' => true, 'size' => '11', 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFA5A5A5')), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860'))), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFDCE6F1')), 'borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '3a2a47')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '3a2a47')))));
$sheet->getStyle('A1:E2')->applyFromArray($estiloTituloReporte);
$sheet->getStyle('A4:H4')->applyFromArray($estiloTituloColumnas);
$sheet->setSharedStyle($estiloInformacion, "A5:H" . ($i - 1));
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(20);
$sheet->getColumnDimension('D')->setWidth(20);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('F')->setWidth(20);
$sheet->getColumnDimension('G')->setWidth(20);
$sheet->getColumnDimension('H')->setWidth(20);
// PRINT EXCEL
$fecha = date('d-m-Y');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . __FUNCTION__ . '-' . $fecha . '.xls"');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Content-Type: text/html; charset=UTF-8');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
protected function _signupSheet($event, $includeEndingTerms, $includeNotEndingTerms)
{
$sheet = new PHPExcel_Worksheet($this->_excelDoc, 'Signup Sheet for Workshop ' . $event['workshopTitle']);
// Set up the margins so the header doesn't bleed into the page
$sheet->getPageMargins()->setTop(1.5);
// Make a three column page layout
$sheet->getColumnDimension('A')->setWidth(16);
$sheet->getColumnDimension('B')->setWidth(16);
$sheet->getColumnDimension('C')->setWidth(45);
$config = new Zend_Config_Xml(APPLICATION_PATH . '/configs/config.xml', 'production');
$date = new DateTime($event['date']);
$startTime = new DateTime($event['startTime']);
$endTime = new DateTime($event['endTime']);
// Set the header on odd pages.
// The code formatting is off because the header doesn't ignore spaces.
/*
* Format:
* Title
* Room name
* date('D, M d, Y') (startTime('g:i A') - endTime('g:i A'))
* Instructors
*
*/
$sheet->getHeaderFooter()->setOddHeader('&C&B&14' . $event['workshopTitle'] . '&14&B&12 ' . chr(10) . $event['location'] . chr(10) . $date->format('l, M d, Y') . '(' . $startTime->format('g:i A') . ' - ' . $endTime->format('g:i A') . ')' . chr(10) . 'Instructor: ' . implode(',', $event['instructors']) . '&12&C');
// Write Column Headers for the table
$sheet->setCellValue('A1', 'First Name');
$sheet->setCellValue('B1', 'Last Name');
$sheet->setCellValue('C1', 'Signature');
// reformat it a little bit in a simpler way for us to use it in our
// spreadsheet printin' loop
$rows = array();
foreach ($event['attendeeList'] as $a) {
$rows[] = array($a['firstName'], $a['lastName']);
}
$signin = new PHPExcel_Style();
$signin->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$rowCounter = 3;
foreach ($rows as $row) {
$row = array_values($row);
// put the totals in the row
$char = self::A;
foreach ($row as $cell) {
$sheet->setCellValue(chr($char) . $rowCounter, $cell);
$char++;
}
$rowCounter++;
}
$tableHeaderStyle = new PHPExcel_Style();
$tableHeaderStyle->applyFromArray($this->_tableHeaderStyleArray);
$tableBodyStyle = new PHPExcel_Style();
$tableBodyStyle->applyFromArray($this->_contentStyleArray);
$sheet->setSharedStyle($tableHeaderStyle, 'A1:C1');
$sheet->setSharedStyle($tableBodyStyle, 'A3:B' . ($rowCounter - 1));
$sheet->setSharedStyle($signin, 'C3:C' . ($rowCounter - 1));
return $sheet;
}
/**
* (non-PHPdoc)
* @see \scipper\Datatransfer\TransferService::generateEmptyDocument()
*/
public function generateDocument(Map $map)
{
if (!class_exists("PHPExcel")) {
throw new GenerationException("dependency 'PHPExcel' not found");
}
$excel = new \PHPExcel();
$excel->removeSheetByIndex(0);
$excel->getProperties()->setCreator($map->getCreator());
$excel->getProperties()->setTitle($map->getTitle());
$protectedStyle = new \PHPExcel_Style();
$protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM))));
$i = 0;
foreach ($map->getSheets() as $sheet) {
$active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i);
$active->getProtection()->setSheet(true);
$active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
foreach ($sheet->getCells() as $cell) {
//Convert content to list format ist necessary
if ($cell->getType() == "select") {
$dataValidation = $active->getCell($cell->getCoord())->getDataValidation();
$dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$dataValidation->setAllowBlank(false);
$dataValidation->setShowInputMessage(true);
$dataValidation->setShowDropDown(true);
$dataValidation->setFormula1($cell->getContent());
} else {
$active->setCellValue($cell->getCoord(), $cell->getValue());
}
//Add protection is necessary
if ($cell->isProtected()) {
$active->protectCells($cell->getCoord(), "123");
$active->setSharedStyle($protectedStyle, $cell->getCoord());
// } elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) {
// $active->unprotectCells($cell->getCoord());
}
$active->getColumnDimension($cell->getX())->setAutoSize(true);
if (!$cell->isVisible()) {
$active->getColumnDimension($cell->getX())->setVisible(false);
}
}
$i++;
}
$excel->setActiveSheetIndex(0);
$writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx";
$writer->save($filename);
return $filename;
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode() {
return md5(
$this->_conditionType
. $this->_operatorType
. $this->_condition
. $this->_style->getHashCode()
. __CLASS__
);
}
/**
* Property Begin Bind
*
* If no PHPExcel_Style_Protection has been bound to PHPExcel_Style then bind this one. Return the actual bound one.
*
* @return PHPExcel_Style_Protection
*/
private function propertyBeginBind() {
if(!isset($this->_parent))
return $this; // I am already bound
if($this->_parent->propertyIsBound($this->_parentPropertyName))
return $this->_parent->getProtection(); // Another one is already bound
$this->_parent->propertyCompleteBind($this, $this->_parentPropertyName); // Bind myself
$this->_parent = null;
return $this;
}
/**
*
*/
protected function _initSharedStyles()
{
$headerTable = new PHPExcel_Style();
$rowEven = new PHPExcel_Style();
$rowOdd = new PHPExcel_Style();
$footerTable = new PHPExcel_Style();
$borders = array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FFDDDDDD')));
$alignment = array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER);
$headerTable->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFCFCFC')), 'borders' => $borders, 'font' => array('bold' => true), 'alignment' => $alignment));
$footerTable->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFCFCFC')), 'borders' => $borders, 'font' => array('bold' => true), 'alignment' => $alignment, 'numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)));
$rowEven->applyFromArray(array('borders' => $borders, 'alignment' => $alignment, 'numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)));
$rowOdd->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFCF9F9')), 'borders' => $borders, 'alignment' => $alignment, 'numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)));
$this->_styles['table_header'] = $headerTable;
$this->_styles['table_footer'] = $footerTable;
$this->_styles['row_even'] = $rowEven;
$this->_styles['row_odd'] = $rowOdd;
}
public function reporteCarteraClientes()
{
$idZona = $_REQUEST['lstZona'];
$idPadre = $_REQUEST['lstRegionCobranza'];
$idCategoria = $_REQUEST['lstCategoriaPrincipal'];
$idVendedor = $_REQUEST['idVendedor'];
$idCliente = $_REQUEST['idCliente'];
$idOrdenVenta = $_REQUEST['idOrdenVenta'];
$idDepartamento = $_REQUEST['lstDepartamento'];
$idProvincia = $_REQUEST['lstProvincia'];
$idDistrito = $_REQUEST['lstDistrito'];
$condicion = $_REQUEST['lstCondicion'];
$situacion = $_REQUEST['lstSituacion'];
$fechaInicio = !empty($_REQUEST['txtFechaInicio']) ? date('Y-m-d', strtotime($_REQUEST['txtFechaInicio'])) : "";
$fechaFin = !empty($_REQUEST['txtFechaFin']) ? date('Y-m-d', strtotime($_REQUEST['txtFechaFin'])) : "";
$condiciones = "";
if (strtolower($condicion) == "contado") {
$condiciones = " and ov.es_contado=1 and ov.es_credito!=1 and ov.es_letras!=1 ";
} elseif (strtolower($condicion) == "credito") {
$condiciones = " and ov.es_credito=1 and ov.es_letra!=1 ";
} elseif (strtolower($condicion) == "letras banco") {
$condiciones = " and ov.es_letras=1 and ov.tipo_letra=1 ";
} elseif (strtolower($condicion) == "letras cartera") {
$condiciones = " and ov.es_letras=1 and ov.tipo_letra=2 ";
}
//traemos los datos
$reporte = $this->AutoLoadModel('reporte');
$datos = $reporte->carteraClientes($idZona, $idPadre, $idCategoria, $idVendedor, $idCliente, $idOrdenVenta, $idDepartamento, $idProvincia, $idDistrito, $condiciones, $situacion, $fechaInicio, $fechaFin);
$cantidadData = count($datos);
//Creamos en nombre de archivo
$baseURL = ROOT . 'descargas' . DS;
$idActor = $_SESSION['idactor'];
$fechaCreacion = date('Y-m-d_h.m.s');
$basenombre = 'CarteraClientes.xls';
$filename = $baseURL . $idActor . '_' . $fechaCreacion . '_' . $basenombre;
//traemos la libreria de Excel e instanciamos
$this->AutoLoadLib('PHPExcel');
$objPHPExcel = new PHPExcel();
//llenamos los datos
$titulos = array('Orden Venta', 'FECHA', 'COD', 'CLIENTE', 'EMAIL', 'RUC', 'TELEFONO', 'IMPORTE ($/.)', 'DIRECCION', 'DISTRITO', 'DPTO');
//poniendo stylo al encabezado
//Lineas para cuadros
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFCCFFCC')), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))));
//estableciendo a automatico el ancho
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$importe = 0;
$zona = 0;
$cont = 0;
for ($i = 0; $i < $cantidadData; $i++) {
if ($zona != $datos[$i]['idzona']) {
$zona = $datos[$i]['idzona'];
if ($i != 0) {
$cont++;
}
$cont++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $cont, $datos[$i]['nombrezona']);
$cont++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $cont, $titulos[0])->setCellValue('B' . $cont, $titulos[1])->setCellValue('C' . $cont, $titulos[2])->setCellValue('D' . $cont, $titulos[3])->setCellValue('E' . $cont, $titulos[4])->setCellValue('F' . $cont, $titulos[5])->setCellValue('G' . $cont, $titulos[6])->setCellValue('H' . $cont, $titulos[7])->setCellValue('I' . $cont, $titulos[8])->setCellValue('J' . $cont, $titulos[9])->setCellValue('K' . $cont, $titulos[10]);
//negrita
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A" . $cont . ":K" . $cont);
//Negrita a los encabezados
$objPHPExcel->getActiveSheet()->getStyle("A" . $cont . ":K" . $cont)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A" . $cont . ":K" . $cont)->getFill()->setRotation(1);
}
$cont++;
$fila = array($datos[$i]['codigov'], $datos[$i]['fordenventa'], $datos[$i]['codantiguo'], html_entity_decode($datos[$i]['razonsocial'], ENT_QUOTES, 'UTF-8'), utf8_decode(html_entity_decode($datos[$i]['email'], ENT_QUOTES, 'UTF-8')), $datos[$i]['ruc'], $datos[$i]['telefono'], number_format($datos[$i]['importeov'], 2), utf8_decode(html_entity_decode($datos[$i]['direccion'], ENT_QUOTES, 'UTF-8')), html_entity_decode($datos[$i]['nombredistrito'], ENT_QUOTES, 'UTF-8'), html_entity_decode($datos[$i]['nombredepartamento'], ENT_QUOTES, 'UTF-8'));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $cont, $datos[$i]['codigov'])->setCellValue('B' . $cont, $datos[$i]['fordenventa'])->setCellValue('C' . $cont, $datos[$i]['codantiguo'])->setCellValue('D' . $cont, html_entity_decode($datos[$i]['razonsocial'], ENT_QUOTES, 'UTF-8'))->setCellValue('E' . $cont, utf8_decode(html_entity_decode($datos[$i]['email'], ENT_QUOTES, 'UTF-8')))->setCellValue('F' . $cont, $datos[$i]['ruc'])->setCellValue('G' . $cont, $datos[$i]['telefono'])->setCellValue('H' . $cont, round($datos[$i]['importeov'], 2))->setCellValue('I' . $cont, utf8_decode(html_entity_decode($datos[$i]['direccion'], ENT_QUOTES, 'UTF-8')))->setCellValue('J' . $cont, html_entity_decode($datos[$i]['nombredistrito'], ENT_QUOTES, 'UTF-8'))->setCellValue('K' . $cont, html_entity_decode($datos[$i]['nombredepartamento'], ENT_QUOTES, 'UTF-8'));
$importe += round($datos[$i]['importeov'], 4);
}
$objPHPExcel->getActiveSheet()->setTitle('Reporte_Cartera_Clientes');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($filename);
header('Content-Description: File Transfer');
header('Content-type: application/force-download');
header('Content-Disposition: attachment; filename=' . basename($filename));
header('Content-Transfer-Encoding: binary');
header("Content-type: application/octet-stream");
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($filename));
ob_clean();
flush();
readfile($filename);
unlink($filename);
}
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(16.14);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(11);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(6.86);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(7.43);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(6.29);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15.29);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(11.1);
// Set autofilter
// Always include the complete filter range!
// Excel does support setting only the caption
// row, but that's not a best practise...
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM), 'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A7:M{$nox}");
// Set style for header row using alternative method
$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), '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'))));
// Add a drawing to the worksheet
//$objDrawing = new PHPExcel_Worksheet_Drawing();
//$objDrawing->setName('Logo');
//$objDrawing->setDescription('Logo');
//$objDrawing->setPath('../images/logo2.png');
//$objDrawing->setCoordinates('B2');
//$objDrawing->setHeight(120);
//$objDrawing->setWidth(120);
//$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objPHPExcel->getActiveSheet()->getStyle('A7:M1000')->getFont()->setName('Arial');
/**
* Set value in specific cell
* @param \PHPExcel_Worksheet $sheet The worksheet
* @param integer $col The selected column
* @param integer $row The selected row
* @param array $value The values to insert
* @param array $format Associative arrays with formats
* @param array $styleArray An array representing the style
* @param $row
*/
protected function applyValue(\PHPExcel_Worksheet $sheet, $col, $row, $value, $format, $styleArray = array())
{
if (0 == count($styleArray)) {
$styleArray = $this->getDefaultStyleArrayForRow($row);
}
$style = new \PHPExcel_Style();
$style->applyFromArray($styleArray);
switch ($format) {
case Format::DATE:
if (!$value instanceof \DateTime) {
$date = new \DateTime($value);
} else {
$date = $value;
}
$value = \PHPExcel_Shared_Date::PHPToExcel($date);
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
break;
case Format::DATETIME:
if (!$value instanceof \DateTime) {
$date = new \DateTime($value);
} else {
$date = $value;
}
$value = \PHPExcel_Shared_Date::PHPToExcel($date);
$style->getNumberFormat()->setFormatCode(self::FORMAT_DATETIME);
break;
case Format::FLOAT2:
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
break;
case Format::INTEGER:
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
break;
case Format::MONEY:
case Format::MONEY2:
$style->getNumberFormat()->setFormatCode(self::FORMAT_EUR);
break;
case Format::PCT:
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
break;
case Format::PCT2:
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
break;
case Format::STRING:
$style->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
break;
}
$sheet->setCellValueByColumnAndRow($col, $row, $value);
$sheet->duplicateStyle($style, \PHPExcel_Cell::stringFromColumnIndex($col) . $row);
}
/** $operationtype：固定卡/随机卡，$money：金额，$km：卡密类型，$isrepeat：是否一次性，$rechargetime：过期时间，
$codepwd：密码，$maxrepeatcount：（固定卡）最多可重复性次数，$zhang ：总张数，
**/
public function download_cardrecharge($operationtype, $money, $km, $isrepeat, $rechargetime, $codepwd, $maxrepeatcount, $zhang)
{
//PHPExcel.php文件的物理路径
$path = str_replace('index.php', '', $_SERVER['SCRIPT_FILENAME']) . 'system/modules/phpexcel/';
$path = $path . "PHPExcel.php";
require_once $path;
//卡密充值导出Excel
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:G1')->setCellValue('A1', '此次生成卡信息')->setCellValue('A2', '序号')->setCellValue('B2', '卡号')->setCellValue('C2', '密码')->setCellValue('D2', '卡密类型')->setCellValue('E2', '金额')->setCellValue('F2', '过期时间')->setCellValue('G2', '最多可重复次数');
//表头结束
$i = 3;
$k = 0;
while ($k < $zhang) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($k + $i), $k + 1);
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($k + $i), $km[$k]);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('C' . ($k + $i), $codepwd[$k], PHPExcel_Cell_DataType::TYPE_STRING);
if ($isrepeat == 'Y') {
$isrepeat = "一次性充值卡";
}
if ($isrepeat == 'N') {
$isrepeat = "可重复性性充值";
}
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($k + $i), $isrepeat);
if (is_array($money)) {
#数组
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($k + $i), $money[$k]);
} else {
#字符串
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($k + $i), $money);
}
$objPHPExcel->getActiveSheet()->setCellValue('F' . ($k + $i), date("Y-m-d", $rechargetime));
$objPHPExcel->getActiveSheet()->setCellValue('G' . ($k + $i), $maxrepeatcount);
$k++;
}
$objPHPExcel->getActiveSheet()->freezePane('A4');
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('123');
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle3 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'C0C0C0')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$sharedStyle2->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFF00')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'font' => array('size' => 12)));
$sharedStyle3->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'font' => array('size' => 18, 'color' => array('argb' => '1E90FF'))));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A2:G" . ($k + 2));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "A2:G2");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A1:G1");
// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="此次生成卡信息.xlsx"');
//表格导出的文件名
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->conditionType . $this->operatorType . implode(';', $this->condition) . $this->style->getHashCode() . __CLASS__);
}
/**
* Report encuesta
*/
public function reporte_encuesta()
{
$this->load->model('Encuesta_model');
$this->load->library('Excel');
$this->excel->setActiveSheetIndex(0);
$objPHPExcel = $this->excel;
$objPHPExcel->getProperties()->setCreator("PLATICOM")->setLastModifiedBy("PLATICOM")->setTitle("REGISTROS PLATICOM-RESPONDE")->setSubject("REGISTROS PLATICOM-RESPONDE")->setDescription("REGISTROS PLATICOM-RESPONDE")->setKeywords("REGISTROS PLATICOM-RESPONDE")->setCategory("plataticom-records");
$tituloReporte = "REGISTROS PLATICOM-ENCUESTA";
$titulosColumnas = array('N', 'USUARIO', 'PREGUNTA-1', 'PREGUNTA-2', 'PREGUNTA-3', 'PREGUNTA-4', 'PREGUNTA-5', 'FECHA');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:E2');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $tituloReporte)->setCellValue('A4', $titulosColumnas[0])->setCellValue('B4', $titulosColumnas[1])->setCellValue('C4', $titulosColumnas[2])->setCellValue('D4', $titulosColumnas[3])->setCellValue('E4', $titulosColumnas[4])->setCellValue('F4', $titulosColumnas[5])->setCellValue('G4', $titulosColumnas[6])->setCellValue('H4', $titulosColumnas[7]);
$rs = $this->Encuesta_model->get_entries();
$i = 5;
$contador = 1;
foreach ($rs as $item) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $contador)->setCellValue('B' . $i, $item['codigo_usuario']);
// 01
if (!empty($item['p1'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $i, $this->getOption(0, $item['p1']));
}
// 02
if (!empty($item['p2'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $i, $this->getOption(1, $item['p2']));
}
// 03
if (!empty($item['p3'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $i, $this->getOption(2, $item['p3']));
}
// 04
if (!empty($item['p4'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $i, $this->getOption(3, $item['p4']));
}
// 05
if (!empty($item['p5'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $i, $this->getOption(4, $item['p5']));
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $i, $item['fecha_fin']);
$i++;
$contador++;
}
// STYLE EXCEL
$estiloTituloReporte = array('font' => array('name' => 'Verdana', 'bold' => true, 'italic' => false, 'strike' => false, 'size' => 16, 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF244062')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE));
$estiloTituloColumnas = array('font' => array('name' => 'Arial', 'bold' => true, 'size' => '11', 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFA5A5A5')), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860'))), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFDCE6F1')), 'borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '3a2a47')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '3a2a47')))));
$objPHPExcel->getActiveSheet()->getStyle('A1:E2')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A4:H4')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A5:H" . ($i - 1));
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(5);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
// PRINT EXCEL
date_default_timezone_set("America/Lima");
$fecha = date('d-m-Y');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . __FUNCTION__ . '-' . $fecha . '.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Content-Type: text/html; charset=UTF-8');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
<?php
$estiloTituloReporte = array('font' => array('name' => 'Arial', 'bold' => true, 'italic' => false, 'strike' => false, 'size' => 12, 'color' => array('rgb' => '000000')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)));
$estiloTituloColumnas = array('font' => array('name' => 'Arial', 'bold' => true, 'color' => array('rgb' => 'FFFFFF'), 'size' => 10), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('rgb' => '112CF2'), 'endcolor' => array('argb' => 'FF431a5d')), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E7EAFF')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
)
)
);
$ssBG1 = new PHPExcel_Style();
$ssBG1->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FFCC99')
)
)
);
$ssBG2 = new PHPExcel_Style();
$ssBG2->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'CCCCFF')
)
)
);
//-------- competitors ----------
createSheetComps($xlsx->getActiveSheet());
//----------- events ------------
$l = sql_num_rows($events);
for ($evt=0;$evt<$l;$evt++)
{
$lrnd = 4;
/**
* Duplicate cell style to a range of cells
*
* Please note that this will overwrite existing cell styles for cells in range!
*
* @param PHPExcel_Style $pCellStyle Cell style to duplicate
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
* @throws PHPExcel_Exception
* @return PHPExcel_Worksheet
*/
public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
{
// make sure we have a real style and not supervisor
$style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
// Add the style to the workbook if necessary
$workbook = $this->_parent;
if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
// there is already such cell Xf in our collection
$xfIndex = $existingStyle->getIndex();
} else {
// we don't have such a cell Xf, need to add
$workbook->addCellXf($pCellStyle);
$xfIndex = $pCellStyle->getIndex();
}
// Calculate range outer borders
list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange . ':' . $pRange);
// Make sure we can loop upwards on rows and columns
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
$tmp = $rangeStart;
$rangeStart = $rangeEnd;
$rangeEnd = $tmp;
}
// Loop through cells and apply styles
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$this->getCell(PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row)->setXfIndex($xfIndex);
}
}
return $this;
}
/**
* Add a cellStyleXf to the workbook
*
* @param PHPExcel_Style $pStyle
*/
public function addCellStyleXf(PHPExcel_Style $pStyle)
{
$this->cellStyleXfCollection[] = $pStyle;
$pStyle->setIndex(count($this->cellStyleXfCollection) - 1);
}
/**
* Create CSS style
*
* @param PHPExcel_Style $pStyle PHPExcel_Style
* @return string
*/
private function _createCSSStyle(PHPExcel_Style $pStyle)
{
// Construct HTML
$html = '';
// Create CSS
$html .= ' .style' . $pStyle->getHashCode() . ' {' . "\r\n";
$html .= $this->_createCSSStyleAlignment($pStyle->getAlignment());
$html .= $this->_createCSSStyleFont($pStyle->getFont());
$html .= $this->_createCSSStyleBorders($pStyle->getBorders());
$html .= $this->_createCSSStyleFill($pStyle->getFill());
$html .= ' }' . "\r\n";
// Return
return $html;
}
->setKeywords("reporte alumnos carreras")
->setCategory("Reporte excel");*/
$tituloReporte = "Listado de los Tipos de Establecimientos";
$titulosColumnas = array('Código', 'Tipo Establecimiento', 'Estatus');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:C1')->mergeCells('A2:C2');
// Se agregan los titulos del reporte
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $tituloReporte)->setCellValue('A3', $titulosColumnas[0])->setCellValue('B3', $titulosColumnas[1])->setCellValue('C3', $titulosColumnas[2]);
//Se agregan los datos de los alumnos
$i = 5;
while ($row = $mysql->Respuesta($query)) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['codigo_tipo_establecimiento'])->setCellValue('B' . $i, $row['descripcion'])->setCellValue('C' . $i, $row['estatus']);
$i++;
}
$estiloTituloReporte = array('font' => array('name' => 'Verdana', 'bold' => true, 'italic' => false, 'strike' => false, 'size' => 16, 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '969696')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE));
$estiloTituloColumnas = array('font' => array('name' => 'Arial', 'bold' => true, 'color' => array('rgb' => 'FF0000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array('rgb' => 'FAFAFA')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'bold' => true, 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE)));
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A2:C2')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A5:C" . ($i - 1));
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
for ($i = 'A'; $i <= 'C'; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}
// Se asigna el nombre a la hoja
$objPHPExcel->getActiveSheet()->setTitle('Tipo Establecimientos');
// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objPHPExcel->setActiveSheetIndex(0);
// Inmovilizar paneles
//$objPHPExcel->getActiveSheet(0)->freezePane('A4');
/**
* Create CSS style
*
* @param PHPExcel_Style $pStyle PHPExcel_Style
* @return array
*/
private function _createCSSStyle(PHPExcel_Style $pStyle)
{
// Construct CSS
$css = '';
// Create CSS
$css = array_merge($this->_createCSSStyleAlignment($pStyle->getAlignment()), $this->_createCSSStyleBorders($pStyle->getBorders()), $this->_createCSSStyleFont($pStyle->getFont()), $this->_createCSSStyleFill($pStyle->getFill()));
// Return
return $css;
}
/**
* Write Cell Style Dxf
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Style $pStyle Style
* @throws Exception
*/
private function _writeCellStyleDxf(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Style $pStyle = null)
{
// dxf
$objWriter->startElement('dxf');
// font
$this->_writeFont($objWriter, $pStyle->getFont());
// numFmt
$this->_writeNumFmt($objWriter, $pStyle->getNumberFormat());
// fill
$this->_writeFill($objWriter, $pStyle->getFill());
// alignment
$objWriter->startElement('alignment');
$objWriter->writeAttribute('horizontal', $pStyle->getAlignment()->getHorizontal());
$objWriter->writeAttribute('vertical', $pStyle->getAlignment()->getVertical());
$textRotation = 0;
if ($pStyle->getAlignment()->getTextRotation() >= 0) {
$textRotation = $pStyle->getAlignment()->getTextRotation();
} else {
if ($pStyle->getAlignment()->getTextRotation() < 0) {
$textRotation = 90 - $pStyle->getAlignment()->getTextRotation();
}
}
$objWriter->writeAttribute('textRotation', $textRotation);
$objWriter->endElement();
// border
$this->_writeBorder($objWriter, $pStyle->getBorders());
// protection
if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT || $pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->startElement('protection');
if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->writeAttribute('locked', $pStyle->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false');
}
if ($pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) {
$objWriter->writeAttribute('hidden', $pStyle->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false');
}
$objWriter->endElement();
}
$objWriter->endElement();
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
$zip = new ZipArchive();
$zip->open($pFilename);
$rels = simplexml_load_string($zip->getFromName("_rels/.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($zip->getFromName("{$dir}/_rels/" . basename($rel["Target"]) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = $xmlStyles->numFmts[0];
if ($numFmts) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$styles[] = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "applyAlignment" => isset($xf["applyAlignment"]) && ((string) $xf["applyAlignment"] == 'true' || (string) $xf["applyAlignment"] == '1'), "applyBorder" => isset($xf["applyBorder"]) && ((string) $xf["applyBorder"] == 'true' || (string) $xf["applyBorder"] == '1'), "applyFill" => isset($xf["applyFill"]) && ((string) $xf["applyFill"] == 'true' || (string) $xf["applyFill"] == '1'), "applyFont" => isset($xf["applyFont"]) && ((string) $xf["applyFont"] == 'true' || (string) $xf["applyFont"] == '1'), "applyNumberFormat" => isset($xf["applyNumberFormat"]) && ((string) $xf["applyNumberFormat"] == 'true' || (string) $xf["applyNumberFormat"] == '1'), "applyProtection" => isset($xf["applyProtection"]) && ((string) $xf["applyProtection"] == 'true' || (string) $xf["applyProtection"] == '1'));
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
$cellStyles[] = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "applyAlignment" => true, "applyBorder" => true, "applyFill" => true, "applyFont" => true, "applyNumberFormat" => true, "applyProtection" => true);
}
}
$dxfs = array();
if (!$this->_readDataOnly) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style();
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new PHPExcel_Style();
$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
PHPExcel_Style::setDefaultStyle($style);
}
}
}
}
$xmlWorkbook = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string) $xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0;
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
continue;
}
// Load sheet
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($zip->getFromName("{$dir}/{$fileWorksheet}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines($xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["bestFit"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
break;
}
}
// Read cell!
switch ($cellDataType) {
case "s":
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
$value = (string) $c->v;
if ($value == '0') {
$value = false;
} else {
if ($value == '1') {
$value = true;
} else {
$value = (bool) $c->v;
}
}
break;
case "inlineStr":
$value = $this->_parseRichText($c->is);
break;
case "e":
if (!isset($c->f)) {
$value = (string) $c->v;
} else {
$value = "={$c->f}";
}
break;
default:
if (!isset($c->f)) {
$value = (string) $c->v;
} else {
// Formula
$value = "={$c->f}";
$cellDataType = 'f';
// Shared formula?
if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') {
$instance = (string) $c->f['si'];
if (!isset($sharedFormulas[(string) $c->f['si']])) {
$sharedFormulas[$instance] = array('master' => $r, 'formula' => $value);
} else {
$master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
$current = PHPExcel_Cell::coordinateFromString($r);
$difference = array(0, 0);
$difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
$difference[1] = $current[1] - $master[1];
$helper = PHPExcel_ReferenceHelper::getInstance();
$x = $helper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]);
$value = $x;
}
}
}
break;
}
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
// Assign value
if ($cellDataType != '') {
$docSheet->setCellValueExplicit($r, $value, $cellDataType);
} else {
$docSheet->setCellValue($r, $value);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
if (isset($styles[intval($c["s"])])) {
$this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]);
}
if ($cellDataType != 's' && PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
$docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value));
}
}
}
// Set rich text parent
if ($value instanceof PHPExcel_RichText && !$this->_readDataOnly) {
$value->setParent($docSheet->getCell($r));
}
}
}
$conditionals = array();
if (!$this->_readDataOnly) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
if ((string) $cfRule["text"] != '') {
$objConditional->setText((string) $cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string) $formula);
}
} else {
$objConditional->addCondition((string) $cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if (!$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if (!$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]));
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) > 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]));
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) > 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]));
}
}
if (!$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
$docHeaderFooter->setDifferentOddEven($xmlSheet->headerFooter["differentOddEven"] == 'true');
$docHeaderFooter->setDifferentFirst($xmlSheet->headerFooter["differentFirst"] == 'true');
$docHeaderFooter->setScaleWithDocument($xmlSheet->headerFooter["scaleWithDoc"] == 'true');
$docHeaderFooter->setAlignWithMargins($xmlSheet->headerFooter["alignWithMargins"] == 'true');
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($range);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough hyperlinks
if ($xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
if (isset($linkRel['id'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]);
}
if (isset($hyperlink['location'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($zip->getFromName($relPath));
// Utility variables
$authors = array();
// Loop trough authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string) $author;
}
// Loop trough contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
$docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
}
}
// Loop trough VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string($zip->getFromName($relPath));
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string) $shape['style'];
$fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData)) {
$clientData = $clientData[0];
if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) {
$row = $temp[0];
}
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) {
$column = $temp[0];
}
}
}
if (!is_null($column) && !is_null($row)) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
$comment->getFillColor()->setRGB($fillColor);
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') {
$comment->setMarginLeft($stylePair[1]);
}
if ($stylePair[0] == 'margin-top') {
$comment->setMarginTop($stylePair[1]);
}
if ($stylePair[0] == 'width') {
$comment->setWidth($stylePair[1]);
}
if ($stylePair[0] == 'height') {
$comment->setHeight($stylePair[1]);
}
if ($stylePair[0] == 'visibility') {
$comment->setVisible($stylePair[1] == 'visible');
}
}
}
}
}
}
// Header/footer images
if ($xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($zip->getFromName(dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($zip->getFromName($vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($zip->getFromName(dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($zip->getFromName($fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop trough definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
if (strpos($extractedRange, '!') !== false) {
$extractedRange = substr($extractedRange, strpos($extractedRange, '!') + 1);
}
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ($definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
if (isset($extractedRange[0])) {
$range = explode(':', $extractedRange[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[0]) != '') {
$range[0] = PHPExcel_Worksheet::extractSheetTitle($range[0]);
}
$range[0] = str_replace('$', '', $range[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[1]) != '') {
$range[1] = PHPExcel_Worksheet::extractSheetTitle($range[1]);
}
$range[1] = str_replace('$', '', $range[1]);
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft($range);
}
if (isset($extractedRange[1])) {
$range = explode(':', $extractedRange[1]);
if (PHPExcel_Worksheet::extractSheetTitle($range[0]) != '') {
$range[0] = PHPExcel_Worksheet::extractSheetTitle($range[0]);
}
$range[0] = str_replace('$', '', $range[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[1]) != '') {
$range[1] = PHPExcel_Worksheet::extractSheetTitle($range[1]);
}
$range[1] = str_replace('$', '', $range[1]);
$docSheet->getPageSetup()->setRowsToRepeatAtTop($range);
}
break;
case '_xlnm.Print_Area':
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true));
break;
}
} else {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName);
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
// Garbage collect...
$docSheet->garbageCollect();
// Next sheet id
++$sheetId;
}
if (!$this->_readDataOnly) {
$excel->setActiveSheetIndex(intval($xmlWorkbook->bookView->workbookView["activeTab"]));
}
break;
}
}
return $excel;
}
/**
* Set default style - should only be used by PHPExcel_IReader implementations!
*
* @param PHPExcel_Style $value
*/
public static function setDefaultStyle(PHPExcel_Style $value)
{
self::$_defaultStyle = $value;
}
/**
* Get the currently active sheet. Only used for supervisor
*
* @return PHPExcel_Worksheet
*/
public function getActiveSheet()
{
return $this->_parent->getActiveSheet();
}
/**
* Duplicate cell style to a range of cells
*
* Please note that this will overwrite existing cell styles for cells in range!
*
* @param PHPExcel_Style $pCellStyle Cell style to duplicate
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
*
* @throws PHPExcel_Exception
* @return PHPExcel_Worksheet
*/
public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
{
// make sure we have a real style and not supervisor
$style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
// Add the style to the workbook if necessary
$workbook = $this->_parent;
if ($this->_parent->cellXfExists($pCellStyle)) {
// there is already this cell Xf in our collection
$xfIndex = $pCellStyle->getIndex();
} else {
// we don't have such a cell Xf, need to add
$workbook->addCellXf($pCellStyle);
$xfIndex = $pCellStyle->getIndex();
}
// Uppercase coordinate
$pRange = strtoupper($pRange);
// Is it a cell range or a single cell?
$rangeA = '';
$rangeB = '';
if (strpos($pRange, ':') === false) {
$rangeA = $pRange;
$rangeB = $pRange;
} else {
list($rangeA, $rangeB) = explode(':', $pRange);
}
// Calculate range outer borders
$rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
$rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
// Translate column into index
$rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]) - 1;
$rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]) - 1;
// Make sure we can loop upwards on rows and columns
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
$tmp = $rangeStart;
$rangeStart = $rangeEnd;
$rangeEnd = $tmp;
}
// Loop through cells and apply styles
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$this->getCell(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
}
}
return $this;
}
date_default_timezone_set('Europe/London');
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../PHPExcel/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("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Add some data
echo date('H:i:s'), " Add some data", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFCCFFCC')), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))));
$sharedStyle2->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:T100");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "C5:R95");
// 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', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
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;
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);
date_default_timezone_set('Europe/London');
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();
$worksheet = $objPHPExcel->getActiveSheet();
echo date('H:i:s'), " Create styles array", EOL;
$styles = array();
for ($i = 0; $i < 10; $i++) {
$style = new PHPExcel_Style();
$style->getFont()->setSize($i + 4);
$styles[] = $style;
}
echo date('H:i:s'), " Add data (begin)", EOL;
$t = microtime(true);
for ($col = 0; $col < 50; $col++) {
for ($row = 0; $row < 100; $row++) {
$str = $row + $col;
$style = $styles[$row % 10];
$coord = PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 1);
$worksheet->setCellValue($coord, $str);
$worksheet->duplicateStyle($style, $coord);
}
}
$d = microtime(true) - $t;
/**
* Set default style - should only be used by PHPExcel_IReader implementations!
*
* @param PHPExcel_Style $value
* @throws Exception
*/
public function setDefaultStyle(PHPExcel_Style $value)
{
$this->_styles['default'] = $value;
// just a reference for PHPExcel_IWriter
PHPExcel_Style::setDefaultStyle($value);
}
// Se asignan las propiedades del libro
$objPHPExcel->getProperties()->setCreator("Codedrinks")->setLastModifiedBy("Codedrinks")->setTitle("Reporte de usuarios")->setSubject("Reporte Usuarios")->setDescription("Reporte de usuarios")->setKeywords("reporte de usuarios")->setCategory("Reporte excel");
$tituloReporte = "Relación de usuarios";
$titulosColumnas = array('User_ID', 'Name', 'Last Name', 'Telephone', 'Role', 'Status');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:D1');
// Se agregan los titulos del reporte
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $tituloReporte)->setCellValue('A3', $titulosColumnas[0])->setCellValue('B3', $titulosColumnas[1])->setCellValue('C3', $titulosColumnas[2])->setCellValue('D3', $titulosColumnas[3])->setCellValue('E3', $titulosColumnas[4])->setCellValue('F3', $titulosColumnas[5]);
//Se agregan los datos de los alumnos
$i = 4;
while ($fila = $resultado->fetch_array()) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $fila['User_ID'])->setCellValue('B' . $i, $fila['Name'])->setCellValue('C' . $i, $fila['Last_Name'])->setCellValue('D' . $i, utf8_encode($fila['Telephone']))->setCellValue('E' . $i, $fila['Role'])->setCellValue('F' . $i, utf8_encode($fila['Status']));
$i++;
}
$estiloTituloReporte = array('font' => array('name' => 'Verdana', 'bold' => true, 'italic' => false, 'strike' => false, 'size' => 16, 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF220835')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE));
$estiloTituloColumnas = array('font' => array('name' => 'Arial', 'bold' => true, 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('rgb' => 'c47cf2'), 'endcolor' => array('argb' => 'FF431a5d')), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '143860')), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'rgb' => '143860')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => TRUE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFd9b7f4')), 'borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '3a2a47')))));
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:F" . ($i - 1));
for ($i = 'A'; $i <= 'F'; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}
// Se asigna el nombre a la hoja
$objPHPExcel->getActiveSheet()->setTitle('Alumnos');
// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objPHPExcel->setActiveSheetIndex(0);
// Inmovilizar paneles
//$objPHPExcel->getActiveSheet(0)->freezePane('A4');
$objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
// Se manda el archivo al navegador web, con el nombre que se indica (Excel2007)
/**
* Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record.
*/
private function _readDefault()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
// $recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
}
/**
* The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions,
* this record stores a note (cell note). This feature was significantly enhanced in Excel 97.
*/
private function _readNote()
{
// echo '<b>Read Cell Annotation</b><br>';
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if ($this->_readDataOnly) {
return;
}
$cellAddress = $this->_readBIFF8CellAddress(substr($recordData, 0, 4));
if ($this->_version == self::XLS_BIFF8) {
$noteObjID = self::_GetInt2d($recordData, 6);
$noteAuthor = self::_readUnicodeStringLong(substr($recordData, 8));
$noteAuthor = $noteAuthor['value'];
// echo 'Note Address=',$cellAddress,'<br>';
// echo 'Note Object ID=',$noteObjID,'<br>';
// echo 'Note Author=',$noteAuthor,'<hr />';
//
$this->_cellNotes[$noteObjID] = array('cellRef' => $cellAddress, 'objectID' => $noteObjID, 'author' => $noteAuthor);
} else {
$extension = false;
if ($cellAddress == '$B$65536') {
// If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation
// note from the previous cell annotation. We're not yet handling this, so annotations longer than the
// max 2048 bytes will probably throw a wobbly.
$row = self::_GetInt2d($recordData, 0);
$extension = true;
$cellAddress = array_pop(array_keys($this->_phpSheet->getComments()));
}
// echo 'Note Address=',$cellAddress,'<br>';
$cellAddress = str_replace('$', '', $cellAddress);
$noteLength = self::_GetInt2d($recordData, 4);
$noteText = trim(substr($recordData, 6));
// echo 'Note Length=',$noteLength,'<br>';
// echo 'Note Text=',$noteText,'<br>';
if ($extension) {
// Concatenate this extension with the currently set comment for the cell
$comment = $this->_phpSheet->getComment($cellAddress);
$commentText = $comment->getText()->getPlainText();
$comment->setText($this->_parseRichText($commentText . $noteText));
} else {
// Set comment for the cell
$this->_phpSheet->getComment($cellAddress)->setText($this->_parseRichText($noteText));
}
}
}
/**
* The TEXT Object record contains the text associated with a cell annotation.
*/
private function _readTextObject()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if ($this->_readDataOnly) {
return;
}
// recordData consists of an array of subrecords looking like this:
// grbit: 2 bytes; Option Flags
// rot: 2 bytes; rotation
// cchText: 2 bytes; length of the text (in the first continue record)
// cbRuns: 2 bytes; length of the formatting (in the second continue record)
// followed by the continuation records containing the actual text and formatting
$grbitOpts = self::_GetInt2d($recordData, 0);
$rot = self::_GetInt2d($recordData, 2);
$cchText = self::_GetInt2d($recordData, 10);
$cbRuns = self::_GetInt2d($recordData, 12);
$text = $this->_getSplicedRecordData();
$this->_textObjects[$this->textObjRef] = array('text' => substr($text["recordData"], $text["spliceOffsets"][0] + 1, $cchText), 'format' => substr($text["recordData"], $text["spliceOffsets"][1], $cbRuns), 'alignment' => $grbitOpts, 'rotation' => $rot);
// echo '<b>_readTextObject()</b><br>';
// var_dump($this->_textObjects[$this->textObjRef]);
// echo '<br>';
}
/**
* Read BOF
*/
private function _readBof()
{
$length = self::_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: 2; size: 2; type of the following data
$substreamType = self::_GetInt2d($recordData, 2);
switch ($substreamType) {
case self::XLS_WorkbookGlobals:
$version = self::_GetInt2d($recordData, 0);
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
throw new Exception('Cannot read this Excel file. Version is too old.');
}
$this->_version = $version;
break;
case self::XLS_Worksheet:
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
break;
default:
// substream, e.g. chart
// just skip the entire substream
do {
$code = self::_GetInt2d($this->_data, $this->_pos);
$this->_readDefault();
} while ($code != self::XLS_Type_EOF && $this->_pos < $this->_dataSize);
break;
}
}
/**
* FILEPASS
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readFilepass()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
// $recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
throw new Exception('Cannot read encrypted file');
}
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readCodepage()
{
$length = self::_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; code page identifier
$codepage = self::_GetInt2d($recordData, 0);
$this->_codepage = PHPExcel_Shared_CodePage::NumberToName($codepage);
}
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readDateMode()
{
$length = self::_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; 0 = base 1900, 1 = base 1904
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
if (ord($recordData[0]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
/**
* Read a FONT record
*/
private function _readFont()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
if (!$this->_readDataOnly) {
$objFont = new PHPExcel_Style_Font();
// offset: 0; size: 2; height of the font (in twips = 1/20 of a point)
$size = self::_GetInt2d($recordData, 0);
$objFont->setSize($size / 20);
// offset: 2; size: 2; option flags
// bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8)
// bit: 1; mask 0x0002; italic
$isItalic = (0x2 & self::_GetInt2d($recordData, 2)) >> 1;
if ($isItalic) {
$objFont->setItalic(true);
}
// bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8)
// bit: 3; mask 0x0008; strike
$isStrike = (0x8 & self::_GetInt2d($recordData, 2)) >> 3;
if ($isStrike) {
$objFont->setStrikethrough(true);
}
// offset: 4; size: 2; colour index
$colorIndex = self::_GetInt2d($recordData, 4);
$objFont->colorIndex = $colorIndex;
// offset: 6; size: 2; font weight
$weight = self::_GetInt2d($recordData, 6);
switch ($weight) {
case 0x2bc:
$objFont->setBold(true);
break;
}
// offset: 8; size: 2; escapement type
$escapement = self::_GetInt2d($recordData, 8);
switch ($escapement) {
case 0x1:
$objFont->setSuperScript(true);
break;
case 0x2:
$objFont->setSubScript(true);
break;
}
// offset: 10; size: 1; underline type
$underlineType = ord($recordData[10]);
switch ($underlineType) {
case 0x0:
break;
// no underline
// no underline
case 0x1:
public function setStyle($cellRange, $fontFamily = 'Arial', $fontSize = 10, $colorRGB = '00000000', $bold = false, $italic = false, $underline = false)
{
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style.php';
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style/Font.php';
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style/Color.php';
$colorObj = new \PHPExcel_Style_Color();
$colorObj->setRGB($colorRGB);
$fontObj = new \PHPExcel_Style_Font();
$fontObj->setName($fontFamily);
$fontObj->setSize($fontSize);
$fontObj->setColor($colorObj);
$fontObj->setBold($bold);
$fontObj->setItalic($italic);
$fontObj->setUnderline($underline);
$styleObj = new \PHPExcel_Style();
$styleObj->setFont($fontObj);
$this->getActiveSheet()->setSharedStyle($styleObj, $cellRange);
}
