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