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;
}
public function export_xls(string $fileName, array $head, array $data, $row1 = 'B')
{
import("Common.Org.PHPExcel");
import("Common.Org.PHPExcel.Writer.Excel5");
import("Common.Org.PHPExcel.IOFactory.php");
$objPHPExcel = new \PHPExcel();
$objProps = $objPHPExcel->getProperties();
$objActSheet = $objPHPExcel->getActiveSheet();
$objActSheet->getStyle()->getFont()->setName('微软雅黑');
//设置字体
$objActSheet->getDefaultRowDimension()->setRowHeight(25);
//设置默认高度
$row = ord($row1);
$row2 = chr($row + count($head) - 1);
foreach ($head as $v) {
if ($v['width'] > 0) {
$objActSheet->getColumnDimension(chr($row))->setWidth($v['width']);
}
//设置列宽
$row++;
}
//设置边框
$sharedStyle1 = new \PHPExcel_Style();
$sharedStyle1->applyFromArray(array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN))));
$column = 2;
foreach ($data as $idx => $value) {
$objActSheet->setSharedStyle($sharedStyle1, "{$row1}{$column}:{$row2}{$column}");
//设置边框
$objActSheet->mergeCells("{$row1}{$column}:{$row2}{$column}");
//合并单元格
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFont()->setSize(12);
//字体
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFont()->setBold(true);
//粗体
//背景色填充
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFill()->getStartColor()->setARGB('FFB8CCE4');
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//垂直居中
$objActSheet->setCellValue($row1 . $column, $idx + 1 . ". " . $value['name']);
$column++;
$objActSheet->setSharedStyle($sharedStyle1, "{$row1}{$column}:{$row2}{$column}");
//设置边框
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getFill()->getStartColor()->setARGB('FF4F81BD');
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//垂直居中
$row = ord($row1);
foreach ($head as $k => $v) {
$row_temp = chr($row++);
$objActSheet->setCellValue($row_temp . $column, $v['label']);
if ($v['align'] == 'center') {
$objActSheet->getStyle("{$row_temp}{$column}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
//水平居中
}
foreach ($value['items'] as $index => $val) {
$column++;
$objActSheet->setSharedStyle($sharedStyle1, "{$row1}{$column}:{$row2}{$column}");
//设置边框
$objActSheet->getStyle("{$row1}{$column}:{$row2}{$column}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle("B{$column}:I{$column}")->getAlignment()->setWrapText(true);
//换行
//行写入
$row = ord($row1);
foreach ($head as $k => $v) {
$row_temp = chr($row++);
$_value = $v['name'] == '$index' ? $index + 1 : $val[$v['name']];
if ($v['filter']) {
$_value = $v['filter']($_value);
}
$objActSheet->setCellValue($row_temp . $column, $_value);
if ($v['align'] == 'center') {
$objActSheet->getStyle("{$row_temp}{$column}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
//水平居中
}
}
$column++;
$column++;
}
$date = date("Y_m_d", time());
$fileName .= "_{$date}.xls";
$fileName = iconv("utf-8", "gb2312", $fileName);
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"{$fileName}\"");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
//文件通过浏览器下载
}
require_once "../include/lib/phpexcel/PHPExcel.php";
require_once "../include/lib/phpexcel/PHPExcel/Writer/Excel2007.php";
////////////////////////////////////////////////////////////////////
$tabMember = getTabMember("", $_SESSION['sqlQuery'], -1, $_REQUEST['sort']);
$rand = rand(0, 10000);
$filename = "list-members-" . $login . "-" . $rand . ".xls";
$filepath = "../xls/" . $filename;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('QCS');
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
// STYLE TITRE
////////////////////////////////////////////////////////////
$styleTitre = new PHPExcel_Style();
$styleTitre->applyFromArray(array('font' => array('name' => 'Arial', 'bold' => true, 'size' => '12', 'color' => array('rgb' => 'FFFFFF')), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DF0101')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));
$objPHPExcel->getActiveSheet()->setSharedStyle($styleTitre, "A1:U1");
// TAILE DES COLONNES ///////////////////////////////////////////////
$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("F")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("G")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("H")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("I")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("J")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("K")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("L")->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension("M")->setWidth(30);
/**
*
* @todo Funcion que genera el xls de operadores para enviarlo por mail
* @author Jean Carlos Nuñez
* @param int $var_cod_salida
* @param int $var_tipo
* @param string $var_num_und
* @param int $var_odt
* @return json
*
*/
function generar_xls_operadores($var_montado = 1)
{
global $conn;
if ($var_montado == 1) {
$sSql = "select concat(o.nombre,' ',o.apellido) as nombre,o.cedula from operadores o where o.nombre <> '0'";
}
if ($var_montado == 2) {
$sSql = "select concat(o.nombre,' ',o.apellido) as nombre,o.cedula from operadores o where o.num_und_asig = '0' ";
}
$sSql .= " order by o.nombre ";
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
if (PHP_SAPI == 'cli') {
die('This example should only be run from a Web Browser');
}
/** Include PHPExcel */
require_once 'lib/PHPExcel/Classes/PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$sharedStyle1 = new PHPExcel_Style();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Jean Carlos Nuñez")->setLastModifiedBy("Jean Carlos Nuñez")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Archivo para Empresa de Seguro")->setKeywords("office 2007 openxml php")->setCategory("Archivo para Empresa de Seguro");
$sharedStyle1->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF')), 'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
// Add some data
$objDrawing = new PHPExcel_Worksheet_Drawing();
//$objDrawing->setPath('yellow_excel.png');
//$objDrawing->setHeight(70);
//$objDrawing->setCoordinates('A1');
//$objDrawing->setWorksheet($objPHPExcel->getActiveSheet(0));
if ($var_montado == "1") {
$var_montado_descripcion = "MONTADOS";
} else {
$var_montado_descripcion = "NO MONTADOS";
}
/*$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A5', 'Yellow Car, S.A')
->setCellValue('A6', 'Reporte Operadores')
->setCellValue('A7',$var_montado_descripcion);*/
//$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A9:B9");
// Miscellaneous glyphs, UTF-8
/*$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A9', 'Nombre')
->setCellValue('B9', 'Cedula');*/
$con = 1;
$var_monto = 0;
$rs_e = phpmkr_query($sSql, $conn) or die("Fallo al ejecutar la consulta en la linea" . __LINE__ . ": " . phpmkr_error($conn) . '<br>SQL: ' . $sSql);
while ($row_rs_e = $rs_e->fetch_assoc()) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $con, $row_rs_e['nombre'])->setCellValue('B' . $con, trim($row_rs_e['cedula']));
$con = $con + 1;
}
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment;filename="operadores.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('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("mail/operadores.xls");
//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;
}
<?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))));
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;
// Save Excel 95 file
echo date('H:i:s'), " Write to Excel5 format", EOL;
)
);
$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;
while($lrnd>1 && !cased_mysql_result($events,$evt,"r$lrnd")) $lrnd--;
$rnd = 1;
//while($rnd <= 4 && cased_mysql_result($events,$evt,"r".$rnd."_open"))
while($rnd <= $lrnd)
{
$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');
$objPHPExcel->getActiveSheet()->getStyle('A7:M1000')->getFont()->setSize(7);
// Merge cells
/** $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;
}
/*Orientacion del papel*/
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('SINCOTEL_SOLUTIONS.jpg');
$objDrawing->setHeight(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
/* ESTILOS */
///////////////////////////////////////////////////////////////////////////////
$tituloStyle = new PHPExcel_Style();
$tituloStyle->applyFromArray(array('font' => array('bold' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THICK)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF969696'))));
$areaStyle = new PHPExcel_Style();
$areaStyle->applyFromArray(array('font' => array('bold' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFC8C8C8'))));
$areaStyle2 = new PHPExcel_Style();
$areaStyle2->applyFromArray(array('font' => array('bold' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFC0C0C0'))));
$areaStyle3 = new PHPExcel_Style();
$areaStyle3->applyFromArray(array('font' => array('bold' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '#FFCC00'))));
$areaStyle4 = new PHPExcel_Style();
$areaStyle4->applyFromArray(array('font' => array('bold' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF1CBCD2'))));
$normalStyle = new PHPExcel_Style();
$normalStyle->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
////////////////////////////////////////////////////////////////////////////////////////
/*Escritura de celdas*/
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('C1', "INFORME GENERADO POR MULTICONSULTA");
$objPHPExcel->getActiveSheet()->setCellValue('C2', 'Proyecto: ' . sacar_nom_subproyecto($id_subproyecto, $link));
// ENCABEZADO TABLA "TIEMPOS RESTANTES"
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'TIEMPOS (H) RESTANTES');
$objPHPExcel->getActiveSheet()->setSharedStyle($tituloStyle, 'A4');
$objPHPExcel->getActiveSheet()->setSharedStyle($tituloStyle, 'B4');
public function unduhexcel()
{
$objPHPExcel = new PHPExcel();
//array style
$style_header = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), '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_THIN), 'left' => 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 some data
$objset = $objPHPExcel->setActiveSheetIndex(0);
//inisiasi set object
$objget = $objPHPExcel->getActiveSheet();
//inisiasi get object
// Merge cells
$objPHPExcel->getActiveSheet()->mergeCells('A9:K9');
$objPHPExcel->getActiveSheet()->setCellValue('A9', "PENILAIAN PRESTASI KERJA");
$objPHPExcel->getActiveSheet()->getStyle('A9:K9')->getFont()->setName('Times New Roman');
$objPHPExcel->getActiveSheet()->getStyle('A9')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A9:K9')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A9:K9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('A10:K10');
$objPHPExcel->getActiveSheet()->setCellValue('A10', "PEGAWAI NEGERI SIPIL");
$objPHPExcel->getActiveSheet()->getStyle('A10:K10')->getFont()->setName('Times New Roman');
$objPHPExcel->getActiveSheet()->getStyle('A10')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A10:K10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A10:K10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Nomor
$objget->setCellValue('B16', '1.');
$objget->getStyle('B16')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B16:B21')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B16')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B22', '2.');
$objget->getStyle('B22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B22:B27')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B22')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B28', '3.');
$objget->getStyle('B28')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B28:B33')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B28')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B38', '4.');
$objget->getStyle('B38')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B38:B49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B38')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C16', 'YANG DINILAI');
$objget->getStyle('C16')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C16')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(15);
// set witdh colom
$objget->getStyle('C16')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C16:J16');
$objget->setCellValue('C22', 'PEJABAT PENILAI');
$objget->getStyle('C22')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C22')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(15);
// set witdh colom
$objget->getStyle('C22')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C22:J22');
$objget->setCellValue('C28', 'ATASAN PEJABAT PENILAI');
$objget->getStyle('C28')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C28')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(15);
// set witdh colom
$objget->getStyle('C28')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C28:J28');
$objget->setCellValue('C38', 'UNSUR YANG DINILAI');
$objget->getStyle('C38')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C38')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(15);
// set witdh colom
$objget->getStyle('C38')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C38:I38');
$objget->setCellValue('J38', 'JUMLAH');
$objget->getStyle('J38')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J38')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(15);
// set witdh colom
$objget->getStyle('J38')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('J39', '53');
$objget->getStyle('J39')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J39')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(10);
// set witdh colom
$objget->getStyle('J39')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('J39:J40');
$objget->setCellValue('C17', 'a.');
$objget->getStyle('C17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C17')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C17')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C18', 'b.');
$objget->getStyle('C18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C18')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C18')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C19', 'c.');
$objget->getStyle('C19')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C19')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C19')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C20', 'd.');
$objget->getStyle('C20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C20')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C20')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C21', 'e.');
$objget->getStyle('C21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C21')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C21')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C23', 'a.');
$objget->getStyle('C23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C23')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C23')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C24', 'b.');
$objget->getStyle('C24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C24')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C24')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C25', 'c.');
$objget->getStyle('C25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C25')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C25')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C26', 'd.');
$objget->getStyle('C26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C26')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C26')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C27', 'e.');
$objget->getStyle('C27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C27')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C27')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C29', 'a.');
$objget->getStyle('C29')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C29')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C29')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C30', 'b.');
$objget->getStyle('C30')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C30')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C30')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C31', 'c.');
$objget->getStyle('C31')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C31')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C31')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C32', 'd.');
$objget->getStyle('C32')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C32')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C32')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C33', 'e.');
$objget->getStyle('C33')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C33')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C33')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C39', 'a. Sasaran Kerja Pegawai/Nilai Prestasi Akademik');
$objget->getStyle('C39')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C39')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(10);
// set witdh colom
$objget->getStyle('C39')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C39:I39');
$objPHPExcel->getActiveSheet()->mergeCells('C39:C40');
$objget->getStyle('C39')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('C41', 'b.');
$objget->getStyle('C41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('C41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('C')->setWidth(5);
// set witdh colom
$objget->getStyle('C41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('C41:C49');
$objget->setCellValue('D23', 'Nama');
$objget->getStyle('D23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D23')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D23')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D23:F23');
$objget->setCellValue('D24', 'NIP');
$objget->getStyle('D24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D24')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D24')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D24:F24');
$objget->setCellValue('D25', 'Pangkat, Golongan Ruang, TMT');
$objget->getStyle('D25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D25')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D25')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D25:F25');
$objget->setCellValue('D26', 'Jabatan/Pekerjaan');
$objget->getStyle('D26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D26')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D26')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D26:F26');
$objget->setCellValue('D27', 'Unit Organisasi');
$objget->getStyle('D27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D27')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D27')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D27:F27');
$objget->setCellValue('D29', 'Nama');
$objget->getStyle('D29')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D29')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D29')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D29:F29');
$objget->setCellValue('D30', 'NIP');
$objget->getStyle('D30')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D30')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D30')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D30:F30');
$objget->setCellValue('D31', 'Pangkat, Golongan Ruang, TMT');
$objget->getStyle('D31')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D31')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D31')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D31:F31');
$objget->setCellValue('D32', 'Jabatan/Pekerjaan');
$objget->getStyle('D32')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D32')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D32')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D32:F32');
$objget->setCellValue('D33', 'Unit Organisasi');
$objget->getStyle('D33')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D33')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D33')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D33:F33');
$objget->setCellValue('D41', 'PRILAKU KERJA');
$objget->getStyle('D41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(20);
// set witdh colom
$objget->getStyle('D41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D41:D49');
$objget->setCellValue('D17', 'Nama');
$objget->getStyle('D17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D17')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(10);
// set witdh colom
$objget->getStyle('D17')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D17:F17');
$objget->setCellValue('D18', 'NIP');
$objget->getStyle('D18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D18')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(10);
// set witdh colom
$objget->getStyle('D18')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D18:F18');
$objget->setCellValue('D19', 'Pangkat, Golongan Ruang, TMT');
$objget->getStyle('D19')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D19')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(10);
// set witdh colom
$objget->getStyle('D19')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D19:F19');
$objget->setCellValue('D20', 'Jabatan/Pekerjaan');
$objget->getStyle('D20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D20')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(10);
// set witdh colom
$objget->getStyle('D20')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D20:F20');
$objget->setCellValue('D21', 'Unit Organisasi');
$objget->getStyle('D21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('D21')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('D')->setWidth(10);
// set witdh colom
$objget->getStyle('D21')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('D21:F21');
$objget->setCellValue('E41', '1.');
$objget->getStyle('E41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E42', '2.');
$objget->getStyle('E42')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E42')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E42')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E43', '3');
$objget->getStyle('E43')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E43')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E43')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E44', '4.');
$objget->getStyle('E44')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E44')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E44')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E45', '5.');
$objget->getStyle('E45')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E45')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E45')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E46', '6.');
$objget->getStyle('E46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E46')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E46')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E47', '7.');
$objget->getStyle('E47')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E47')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E47')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E48', '8.');
$objget->getStyle('E48')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E48')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E48')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('E49', '9.');
$objget->getStyle('E49')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('E49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('E')->setWidth(5);
// set witdh colom
$objget->getStyle('E49')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('F41', 'Orientasi Pelayanan');
$objget->getStyle('F41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F41:G41');
$objget->setCellValue('F42', 'Integritas');
$objget->getStyle('F42')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F42')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F42')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F42:G42');
$objget->setCellValue('F43', 'Komitmen');
$objget->getStyle('F43')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F43')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F43')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F43:G43');
$objget->setCellValue('F44', 'Disiplin');
$objget->getStyle('F44')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F44')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F44')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F44:G44');
$objget->setCellValue('F45', 'Kerjasama');
$objget->getStyle('F45')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F45')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F45')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F45:G45');
$objget->setCellValue('F46', 'Kepemimpinan');
$objget->getStyle('F46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F46')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F46')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F46:G46');
$objget->setCellValue('F47', 'Jumlah');
$objget->getStyle('F47')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F47')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('E47')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F47:G47');
$objget->setCellValue('F48', 'Nilai Rata-rata');
$objget->getStyle('F48')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F48')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F48')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F48:G48');
$objget->setCellValue('F49', 'Nilai Perilaku Kerja');
$objget->getStyle('F49')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('F49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('F')->setWidth(10);
// set witdh colom
$objget->getStyle('F49')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('F49:G49');
$objget->setCellValue('H41', '85.00');
$objget->getStyle('H41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H42', '85.00');
$objget->getStyle('H42')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H42')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H42')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H43', '85.00');
$objget->getStyle('H43')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H43')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H43')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H44', '85.00');
$objget->getStyle('H44')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H44')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H44')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H45', '85.00');
$objget->getStyle('H45')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H45')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H45')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H46', '85.00');
$objget->getStyle('H46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H46')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H46')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H47', '85.00');
$objget->getStyle('H47')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H47')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H47')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H48', '85.00');
$objget->getStyle('H48')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H48')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H48')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('H49', '85.00');
$objget->getStyle('H49')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('H49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('H')->setWidth(5);
// set witdh colom
$objget->getStyle('H49')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I41', '');
$objget->getStyle('I41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I42', '');
$objget->getStyle('I42')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I42')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I42')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I43', '');
$objget->getStyle('I43')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I43')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I43')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I44', '');
$objget->getStyle('I44')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I44')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I44')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I45', '');
$objget->getStyle('I45')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I45')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I45')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I46', '');
$objget->getStyle('I46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I46')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I46')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I47', '');
$objget->getStyle('I47')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I47')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I47')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I48', '');
$objget->getStyle('I48')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I48')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I48')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('I49', 'X 40%');
$objget->getStyle('I49')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('I49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('I')->setWidth(5);
// set witdh colom
$objget->getStyle('I49')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J41', '');
$objget->getStyle('J41')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J41')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J41')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J42', '');
$objget->getStyle('J42')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J42')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J42')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J43', '');
$objget->getStyle('J43')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J43')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J43')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J44', '');
$objget->getStyle('J44')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J44')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J44')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J45', '');
$objget->getStyle('J45')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J45')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J45')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J46', '');
$objget->getStyle('J46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J46')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J46')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J47', '');
$objget->getStyle('J47')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J47')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J47')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J48', '');
$objget->getStyle('J48')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J48')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J48')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('J49', '34.00');
$objget->getStyle('J49')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J49')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J49')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('G17', '');
$objget->getStyle('G17')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G17')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G17')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G17:J17');
$objget->setCellValue('G18', '');
$objget->getStyle('G18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G18')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G18')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G18:J18');
$objget->setCellValue('G19', '');
$objget->getStyle('G19')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G19')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G19')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G19:J19');
$objget->setCellValue('G20', '');
$objget->getStyle('G20')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G20')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G20')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G20:J20');
$objget->setCellValue('G21', '');
$objget->getStyle('G21')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G21')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G21')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G21:J21');
$objget->setCellValue('G23', '');
$objget->getStyle('G23')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G23')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G23')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G23:J23');
$objget->setCellValue('G24', '');
$objget->getStyle('G24')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G24')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G24')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G24:J24');
$objget->setCellValue('G25', '');
$objget->getStyle('G25')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G25')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G25')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G25:J25');
$objget->setCellValue('G26', '');
$objget->getStyle('G26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G26')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G26')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G26:J26');
$objget->setCellValue('G27', '');
$objget->getStyle('G27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G27')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G27')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G27:J27');
$objget->setCellValue('G29', '');
$objget->getStyle('G29')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G29')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G29')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G29:J29');
$objget->setCellValue('G30', '');
$objget->getStyle('G30')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G30')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G30')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G30:J30');
$objget->setCellValue('G31', '');
$objget->getStyle('G31')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G31')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G31')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G31:J31');
$objget->setCellValue('G32', '');
$objget->getStyle('G32')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G32')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G32')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G32:J32');
$objget->setCellValue('G33', '');
$objget->getStyle('G33')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('G33')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('G')->setWidth(10);
// set witdh colom
$objget->getStyle('G33')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('G33:J33');
$objget->setCellValue('B50', 'NILAI PRESTASI KERJA');
$objget->getStyle('B50')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B50')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B50')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->mergeCells('B50:I50');
$objPHPExcel->getActiveSheet()->mergeCells('B50:B53');
$objget->setCellValue('J51', '87.86 (BAIK)');
$objget->getStyle('J51')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('J50:J53')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('J')->setWidth(10);
// set witdh colom
$objget->getStyle('J51')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objget->setCellValue('B55', '5.');
$objget->setCellValue('C55', 'KEBERATAN DARI PEGAWAI NEGERI SIPIL');
$objget->setCellValue('C56', 'YANG DINILAI (APABILA ADA)');
$objget->setCellValue('F60', 'Tanggal, .......');
$objget->getStyle('B54')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B54:J61')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B54')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B69', '6');
$objget->setCellValue('C69', 'TANGGAPAN PEJABAT PENILAI');
$objget->setCellValue('C70', 'ATAS KEBERATAN )');
$objget->setCellValue('I78', 'Tanggal, .......');
$objget->getStyle('B69')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B69:J81')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B69')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B83', '7.');
$objget->setCellValue('C83', 'KEPUTUSAN ATASAN PEJABAT');
$objget->setCellValue('C84', 'PENILAI ATAS KEBERATAN )');
$objget->setCellValue('I89', 'Tanggal, .......');
$objget->getStyle('B82')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B82:J92')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B82')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('B101', '8.');
$objget->setCellValue('C101', 'REKOMENDASI');
$objget->setCellValue('I110', 'Tanggal, .......');
$objget->getStyle('B100')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B100:J113')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('B100')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->setCellValue('G116', '9. DIBUAT TANGGAL, .........');
$objget->setCellValue('G117', 'PEJABAT PENILAI');
$objget->setCellValue('G122', 'Dra. Indriyati, MM');
$objget->setCellValue('G123', '195710231984032001');
$objget->setCellValue('B124', '10. DITERIMA TANGGAL, .........');
$objget->setCellValue('C125', 'PEGAWAI NEGERI SIPIL YANG');
$objget->setCellValue('D126', 'DINILAI');
$objget->setCellValue('D130', 'Drs Zulkifli., M.Si');
$objget->setCellValue('D131', '196802291994031001');
$objget->setCellValue('G133', '10. DITERIMA TANGGAL, …………………');
$objget->setCellValue('G134', 'ATASAN PEJABAT YANG MENILAI');
$objget->setCellValue('G138', 'Mochamad Teguh Pamudji, SH, MH');
$objget->setCellValue('G139', '195711121980031000');
$objget->getStyle('B114')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objget->getStyle('B114:J140')->applyFromArray($style_header);
// set font weight
$objget->getColumnDimension('B')->setWidth(10);
// set witdh colom
$objget->getStyle('G123')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->getStyle('D131')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objget->getStyle('G139')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$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_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$sharedStyle2->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_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
//Merge cell
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B53:I53");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B50:B53");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C16:J16");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C22:J22");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C28:J28");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C38:I38");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C39:I39");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C40:I40");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C39:C40");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "C41:C49");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F41:G41");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F42:G42");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F43:G43");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F44:G44");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F45:G45");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F46:G46");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F47:G47");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F48:G48");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "F49:G49");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "J38");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "J39");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "J39:J40");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D17:F17");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D18:F18");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D19:F19");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D20:F20");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D23:F23");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D24:F24");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D25:F25");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D26:F26");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D27:F27");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D29:F29");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D30:F30");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D31:F31");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D32:F32");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D33:F33");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D41:D49");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "D21:F21");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G17:J17");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G18:J18");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G19:J19");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G20:J20");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G21:J21");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G23:J23");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G24:J24");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G25:J25");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G26:J26");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G27:J27");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G29:J29");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G30:J30");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G31:J31");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G32:J32");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "G33:J33");
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="DataPenilaianSKP.xls"');
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
/**
* Inserts data into worksheet and returns it
*
* @return PHPExcel_Worksheet
*/
public function render()
{
// Set worksheet header
$this->_set_row(1, $this->columns, TRUE);
//set header style
$obj_style = new PHPExcel_Style();
$style = Kohana::$config->load('phpexcel.header');
$obj_style->applyFromArray($style);
$column_dim = PHPExcel_Cell::stringFromColumnIndex(count($this->columns) - 1);
$this->_worksheet->setSharedStyle($obj_style, 'A1:' . $column_dim . '1');
// Set data
$rows = 0;
foreach ($this->data as $row => $data) {
$this->_set_row($row + 2, $data);
$rows++;
}
// Set column styles and width
$column = 0;
foreach (array_keys($this->columns) as $key) {
$column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
$format = Arr::get($this->formats, $key);
if ($format !== NULL) {
$this->_worksheet->getStyle($column_dim . 2 . ':' . $column_dim . (2 + $rows))->getNumberFormat()->setFormatCode($format);
}
if ($this->auto_size === TRUE) {
$this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
}
$column++;
}
return $this->_worksheet;
}
/**
*
*/
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 wali_kelas_in($filename, $data = null)
{
$objPHPExcel = new PHPExcel();
//-----------------------------Excel Writing Starts Here
//Header Part Starts Here
// Title
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->mergeCells('A1:AB1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'LAPORAN PENILAIAN HASIL BELAJAR SISWA SMK NEGERI 2 CIMAHI ');
$objPHPExcel->getActiveSheet()->mergeCells('A2:AB2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'TAHUN AJARAN ' . $data[0]['tahun_ajaran']);
// Semi-Header
$objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'NISN : ');
$objPHPExcel->getActiveSheet()->setCellValue('C4', '');
$objPHPExcel->getActiveSheet()->getStyle('A4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
$objPHPExcel->getActiveSheet()->mergeCells('A5:B5');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NAMA SISWA : ');
$objPHPExcel->getActiveSheet()->setCellValue('C5', '');
$objPHPExcel->getActiveSheet()->mergeCells('A7:B7');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'NAMA GURU : ');
$objPHPExcel->getActiveSheet()->setCellValue('C6', '');
$objPHPExcel->getActiveSheet()->mergeCells('A6:B6');
$objPHPExcel->getActiveSheet()->getStyle('A4:B7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->setCellValue('D4', 'TINGKAT : ');
$objPHPExcel->getActiveSheet()->setCellValue('E4', $data[0]['tingkat']);
$objPHPExcel->getActiveSheet()->setCellValue('D5', 'JURUSAN : ');
$objPHPExcel->getActiveSheet()->setCellValue('E5', $data[0]['jurusan']);
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'KELAS : ');
$objPHPExcel->getActiveSheet()->setCellValue('E6', $data[0]['kelas']);
$objPHPExcel->getActiveSheet()->getStyle('D4:D6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->setCellValue('Q4', 'Tanggal');
$objPHPExcel->getActiveSheet()->setCellValue('Z4', ':');
$objPHPExcel->getActiveSheet()->setCellValue('AA4', '');
$objPHPExcel->getActiveSheet()->getStyle('AA4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'SEMESTER');
$objPHPExcel->getActiveSheet()->setCellValue('Z6', ':');
$objPHPExcel->getActiveSheet()->setCellValue('AA6', '');
$objPHPExcel->getActiveSheet()->setCellValue('Q7', 'TAHUN');
$objPHPExcel->getActiveSheet()->setCellValue('Z7', ':');
$objPHPExcel->getActiveSheet()->setCellValue('AA7', '');
//Header Styling
$objPHPExcel->getActiveSheet()->getStyle('A1:AB7')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A1:AB7')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A1:AB7')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:AB2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Header Part Ends Here
//Table Header Starts Here
$objPHPExcel->getActiveSheet()->mergeCells('A10:A11');
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'NO');
$objPHPExcel->getActiveSheet()->mergeCells('B10:D11');
$objPHPExcel->getActiveSheet()->setCellValue('B10', 'MATA PELAJARAN');
$objPHPExcel->getActiveSheet()->mergeCells('E10:AB11');
$objPHPExcel->getActiveSheet()->setCellValue('E10', 'NILAI');
//Table Header Styling + Bordering
$sharedStyle1 = 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)), 'font' => array('name' => 'Arial', 'size' => 12, 'bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A10:AB12");
//Dimension Sizing
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4.15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(5.71);
//Table Header Ends Here
//
//Table Body Writing Starts Here
$no = 0;
$nom = 12;
for ($i = 0; $i < count($data); $i++) {
$no++;
//Table Data
$objPHPExcel->getActiveSheet()->setCellValue('A' . $nom, $no);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $nom, $data[$i]['nama_mapel']);
$objPHPExcel->getActiveSheet()->getStyle('E' . $nom)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
//Merging
$objPHPExcel->getActiveSheet()->mergeCells("B{$nom}:D{$nom}");
$objPHPExcel->getActiveSheet()->mergeCells("E{$nom}:AB{$nom}");
$nom++;
}
$nox = $no + 11;
//Bordering Starts Here
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle2->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)), 'font' => array('name' => 'Arial', 'size' => 12)));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "A12:AB{$nox}");
$objPHPExcel->getActiveSheet()->mergeCells("B{$nox}:D{$nox}");
$objPHPExcel->getActiveSheet()->mergeCells("E{$nox}:AB{$nox}");
//Table Body Writing Ends Here
//
//Table Footer Starts Here
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($nox + 4), 'Mengetahui,');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($nox + 5), 'Wali Kelas');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($nox + 8), '……………………………………………');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($nox + 9), 'NIP.' . $data[0]['nip']);
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($nox + 4), 'Cimahi, …………………………………………');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($nox + 5), 'Orang Tua/Wali Murid');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($nox + 8), '……………………………………………');
//Table Footer Styling
$sharedStyle3 = new PHPExcel_style();
$sharedStyle3->applyFromArray(array('font' => array('name' => 'Arial', 'size' => 12)));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "A" . ($nox + 4) . ":AB" . ($nox + 9));
//Table Footer Ends Here
header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Cache-control: private");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("export/{$filename}");
header("location: " . base_url() . "export/{$filename}");
unlink(base_url() . "export/{$filename}");
}
/**
* 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;
}
public function getStyles()
{
$configurations = ['header' => ['font' => ['bold' => true, 'name' => 'Arial', 'size' => 10], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]], 'default' => ['font' => ['name' => 'Arial', 'size' => 10]], 'int' => ['font' => ['name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0;[RED]-# ##0']], 'intSummary' => ['font' => ['bold' => true, 'italic' => true, 'name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0;[RED]-# ##0']], 'float' => ['font' => ['name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.000;[RED]-# ##0.000']], 'floatSummary' => ['font' => ['bold' => true, 'italic' => true, 'name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.000;[RED]-# ##0.000']], 'money' => ['font' => ['name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.00 [$PLN];[RED]-# ##0.00 [$PLN]']], 'moneySummary' => ['font' => ['bold' => true, 'italic' => true, 'name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.00 [$PLN];[RED]-# ##0.00 [$PLN]']], 'percent' => ['font' => ['name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.000 %']], 'percentSummary' => ['font' => ['bold' => true, 'italic' => true, 'name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => '# ##0.000 %']], 'date' => ['font' => ['name' => 'Arial', 'size' => 10], 'numberformat' => ['code' => 'YY-MM-DD']]];
$result = [];
foreach ($configurations as $name => $configuration) {
$style = new \PHPExcel_Style();
$style->applyFromArray($configuration);
$result[$name] = $style;
}
return $result;
}
/**
* 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);
}
$objPHPExcel->getProperties()->setCreator("Codedrinks")->setLastModifiedBy("Codedrinks")->setTitle("Reporte de tiempos")->setSubject("Reporte de tiempos")->setDescription("Reporte de tiempos")->setKeywords("Reporte de tiempos")->setCategory("Reporte excel");
$tituloReporte = "Relación de tiempos en despachos";
$titulosColumnas = array('Viaje', 'Placa', 'Fecha', 'Turno', 'Despachador', 'Pedido', 'Doc Mercurio', 'Condicion', 'Aprovicionador', 'Destino', 'Hora Pedido', 'Hora Salida', 'Hora Llegada', 'Tiempo Reaccion', 'Tiempo Descargue', 'Observacion');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:P1');
// Se agregan los titulos del reporte 15
$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])->setCellValue('G3', $titulosColumnas[6])->setCellValue('H3', $titulosColumnas[7])->setCellValue('I3', $titulosColumnas[8])->setCellValue('J3', $titulosColumnas[9])->setCellValue('K3', $titulosColumnas[10])->setCellValue('L3', $titulosColumnas[11])->setCellValue('M3', $titulosColumnas[12])->setCellValue('N3', $titulosColumnas[13])->setCellValue('O3', $titulosColumnas[14])->setCellValue('P3', $titulosColumnas[15]);
//Se agregan los datos de los pedidos
$i = 4;
while ($fila = $resultado->fetch_array()) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $fila['viaje'])->setCellValue('B' . $i, $fila['fk_pla_id'])->setCellValue('C' . $i, $fila['via_fecha'])->setCellValue('D' . $i, $fila['via_turno'])->setCellValue('E' . $i, $fila['emp_primer_nombre'] . " " . $fila['emp_primer_apellido'])->setCellValue('F' . $i, $fila['pk_via_ped_id'])->setCellValue('G' . $i, $fila['via_ped_doc_mercurio'])->setCellValue('H' . $i, $fila['via_ped_condicion'])->setCellValue('I' . $i, $fila['apr_nombre'])->setCellValue('J' . $i, $fila['des_nombre'])->setCellValue('K' . $i, $fila['via_ped_hora_pedido'])->setCellValue('L' . $i, $fila['via_ped_hora_salida'])->setCellValue('M' . $i, $fila['via_ped_hora_llegada'])->setCellValue('N' . $i, $fila['tiempo_reaccion'])->setCellValue('O' . $i, $fila['tiempo_descargue'])->setCellValue('P' . $i, $fila['via_ped_observacion']);
$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' => '000000')));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A1:P1')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A3:P3')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:P" . ($i - 1));
for ($i = 'A'; $i <= 'P'; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}
// Se asigna el nombre a la hoja
$objPHPExcel->getActiveSheet()->setTitle('Tiempos');
// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objPHPExcel->setActiveSheetIndex(0);
// Se manda el archivo al navegador web, con el nombre que se indica (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Reporte_de_tiempos.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
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);
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$contract = $this->_mapper->detail($id);
$data = $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
$data['date_start'] = $this->view->date($data['date_start'], 'MM/dd/yyyy');
$data['date_finish'] = $this->view->date($data['date_finish'], 'MM/dd/yyyy');
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$mapperInstitute = new Register_Model_Mapper_EducationInstitute();
$contacts = $mapperInstitute->listContacts($data['fk_id_fefpeduinstitution'])->toArray();
$expenses = $this->_mapper->listExpenses($id)->toArray();
$itensExpense = $this->_mapper->listItemExpenses($id)->toArray();
$dataItensExpense = array();
foreach ($itensExpense as $item) {
if (!array_key_exists($item['fk_id_budget_category'], $dataItensExpense)) {
$dataItensExpense[$item['fk_id_budget_category']] = array();
}
$dataItensExpense[$item['fk_id_budget_category']][] = $item;
}
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/Contrato_RI_tet.xlsx');
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('S10', $data['contract']);
$activeSheet->setCellValue('W8', $data['date_inserted']);
$activeSheet->setCellValue('F12', $data['institute']);
$activeSheet->setCellValue('F14', $data['district']);
$activeSheet->setCellValue('F15', $data['sub_district']);
if (!empty($contacts)) {
$activeSheet->setCellValue('G18', $contacts[0]['contact_name']);
$activeSheet->setCellValue('S18', $contacts[0]['cell_fone']);
$activeSheet->setCellValue('S19', $contacts[0]['email']);
}
$startRow = 24;
$startSubRow = 35;
$count = 'A';
$styleCell = new PHPExcel_Style();
$borders = array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '0000000')));
$styleCell->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFFFF')), 'borders' => $borders));
$ranges = array();
foreach ($expenses as $expense) {
$marker = $count++ . '.';
$expenseName = ucwords(strtolower($expense['description']));
$activeSheet->setCellValue('C' . $startRow, $marker);
$activeSheet->setCellValue('D' . $startRow, $expenseName);
$activeSheet->setCellValueExplicit('U' . $startRow, $expense['amount'], PHPExcel_Cell_DataType::TYPE_NUMERIC);
$activeSheet->mergeCells('U' . $startRow . ':V' . $startRow);
$activeSheet->setCellValue('C' . $startSubRow, $marker);
$activeSheet->setCellValue('D' . $startSubRow, $expenseName);
$subExpenseRow = $startSubRow + 1;
$subRowCount = 0;
if (!empty($dataItensExpense[$expense['id_budget_category']])) {
foreach ($dataItensExpense[$expense['id_budget_category']] as $subExpense) {
$activeSheet->setCellValue('D' . $subExpenseRow, $subExpense['description']);
$activeSheet->setCellValue('O' . $subExpenseRow, $subExpense['quantity']);
$activeSheet->setCellValue('P' . $subExpenseRow, number_format((double) $subExpense['amount_unit'], 2, '.', ','));
$activeSheet->setCellValue('Q' . $subExpenseRow, number_format((double) $subExpense['amount_total'], 2, '.', ','));
$activeSheet->setCellValue('S' . $subExpenseRow, $subExpense['comments']);
$activeSheet->mergeCells('Q' . $subExpenseRow . ':R' . $subExpenseRow);
$activeSheet->mergeCells('S' . $subExpenseRow . ':V' . $subExpenseRow);
$subRowCount++;
$subExpenseRow++;
$activeSheet->insertNewRowBefore($subExpenseRow + 1, 1);
}
$ranges[] = 'O' . ($startSubRow + 2) . ':V' . $subExpenseRow;
}
$activeSheet->insertNewRowBefore($subExpenseRow, 3);
$startSubRow = $subExpenseRow + 2;
$startRow++;
if ($startRow > 28) {
$activeSheet->insertNewRowBefore($startRow + 1, 1);
}
}
$activeSheet->removeRow($startSubRow, 4);
foreach ($ranges as $range) {
$activeSheet->setSharedStyle($styleCell, $range);
}
$dateStartCell = 'G' . ($startSubRow + 2);
$dateFinishCell = 'G' . ($startSubRow + 3);
$formulaDate = $activeSheet->getCell('L' . ($startSubRow + 2))->getValue();
$formulaDate = str_replace('98', $dateStartCell, $formulaDate);
$formulaDate = str_replace('99', $dateFinishCell, $formulaDate);
$activeSheet->setCellValueExplicit('L' . ($startSubRow + 2), $formulaDate, PHPExcel_Cell_DataType::TYPE_FORMULA);
$activeSheet->setCellValue($dateStartCell, $data['date_start']);
$activeSheet->setCellValue($dateFinishCell, $data['date_finish']);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
$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)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
require_once '../js/Classes/PHPExcel.php';
$conn = mysql_connect('db543248245.db.1and1.com:3306', 'dbo543248245', 'M4SS1V4STUD10') or die('No se pudo conectar: ' . mysql_error());
mysql_select_db('db543248245', $conn) or die('No se pudo seleccionar la base de datos');
$idevento = $_SESSION['evento'];
if ($idevento != '') {
$r = mysql_query("select * from evento where id='{$idevento}'", $conn);
while ($f = mysql_fetch_array($r)) {
$no = $f['nombre'];
$inv = $f['invitados'];
}
$res1 = mysql_query("select * from asistente where id_evento='{$idevento}'", $conn);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Coomeb")->setLastModifiedBy("Coomeb")->setTitle("Participantes")->setSubject("Información")->setDescription("Información de los participantes del evento")->setCategory("Reportes");
$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('rgb' => '94BF42')), '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' => FALSE));
$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray(array('font' => array('name' => 'Arial', 'color' => array('rgb' => '000000')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '000000'))), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE)));
$i = '2';
$j = '2';
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Cédula')->setCellValue('B1', 'Nombre')->setCellValue('C1', 'Correo')->setCellValue('D1', 'Teléfono')->setCellValue('E1', 'Invitados');
while ($filas = mysql_fetch_array($res1)) {
$id = $filas['id_usuario'];
$asis = $filas['id_asistente'];
$res2 = mysql_query("select * from clientes where codigo={$id}", $conn);
while ($filas2 = mysql_fetch_array($res2)) {
$res3 = mysql_query("select * from invitado where id_asistente={$asis}", $conn);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $id)->setCellValue('B' . $i, $filas2['nombre'])->setCellValue('C' . $i, $filas2['correo'])->setCellValue('D' . $i, $filas2['telefono']);
$k = '0';
while ($filas3 = mysql_fetch_array($res3)) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $j, "Nombre: " . $filas3['nombre'] . " -> Edad: " . $filas3['edad'] . " -> Documento: " . $filas3['documento']);
$j++;
$k++;
->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');
$objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
/**
* Set area style.
*
* @param object $excelSheet
* @param array $style
* @param string $area
* @access public
* @return void
*/
public function setAreaStyle($excelSheet, $style, $area)
{
$styleObj = new PHPExcel_Style();
$styleObj->applyFromArray($style);
$excelSheet->setSharedStyle($styleObj, $area);
}
