/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
if ($this->_isSupervisor) {
return $this->getSharedComponent()->getHashCode();
}
return md5($this->_borderStyle . $this->_color->getHashCode() . __CLASS__);
}
private static function readColor($color, $background = false)
{
if (isset($color["rgb"])) {
return (string) $color["rgb"];
} elseif (isset($color["indexed"])) {
return PHPExcel_Style_Color::indexedColor($color["indexed"] - 7, $background)->getARGB();
}
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5(($this->_visible ? 't' : 'f') . $this->_blurRadius . $this->_distance . $this->_direction . $this->_alignment . $this->_color->getHashCode() . $this->_alpha . __CLASS__);
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->_borderStyle . $this->_borderColor->getHashCode() . __CLASS__);
}
require_once 'const.php';
require_once 'PHPExcel.php';
$_dir = $_SERVER['DOCUMENT_ROOT'] . TEMP_DIR;
/* 読込 */
/*
$xls = PHPExcel_IOFactory::load($_dir.'/EXCEL_DATA.xlsx');
$xls->setActiveSheetIndex(0);
$sheet = $xls->getActiveSheet();
var_dump($sheet->getCell('B2').$sheet->getCell('C2'));
*/
/* 新規作成 */
$xls = new PHPExcel();
$sheet = $xls->setActiveSheetIndex(0);
$sheet->setCellValue('A1', '氏名', false);
$sheet->setCellValue('A2', '木島 仁', false);
$sheet->setCellValue('B1', '電話番号', false);
$sheet->setCellValue('B2', '03-5298-8868', false);
$sheet->setCellValue('C1', '更新日', false);
$sheet->setCellValue('C2', date('y/m/d'), false);
$sheet->getStyle('A1:C1')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$color = new PHPExcel_Style_Color();
$color->setRGB('ffcccc');
$color2 = new PHPExcel_Style_Color();
$color2->setRGB('ff6666');
$sheet->getStyle('A1:C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR);
$sheet->getStyle('A1:C1')->getFill()->setStartColor($color);
$sheet->getStyle('A1:C1')->getFill()->setEndColor($color2);
$writer = PHPExcel_IOFactory::createWriter($xls, 'Excel2007');
$writer->save($_dir . '/WriteSample.xlsx');
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->author . $this->text->getHashCode() . $this->width . $this->height . $this->marginLeft . $this->marginTop . ($this->visible ? 1 : 0) . $this->fillColor->getHashCode() . $this->alignment . __CLASS__);
}
/**
* Get indexed color
*
* @param int $pIndex Index entry point into the colour array
* @param boolean $background Flag to indicate whether default background or foreground colour
* should be returned if the indexed colour doesn't exist
* @return PHPExcel_Style_Color
*/
public static function indexedColor($pIndex, $background = FALSE)
{
// Clean parameter
$pIndex = intval($pIndex);
// Indexed colors
if (is_null(self::$_indexedColors)) {
self::$_indexedColors = array(1 => 'FF000000', 2 => 'FFFFFFFF', 3 => 'FFFF0000', 4 => 'FF00FF00', 5 => 'FF0000FF', 6 => 'FFFFFF00', 7 => 'FFFF00FF', 8 => 'FF00FFFF', 9 => 'FF800000', 10 => 'FF008000', 11 => 'FF000080', 12 => 'FF808000', 13 => 'FF800080', 14 => 'FF008080', 15 => 'FFC0C0C0', 16 => 'FF808080', 17 => 'FF9999FF', 18 => 'FF993366', 19 => 'FFFFFFCC', 20 => 'FFCCFFFF', 21 => 'FF660066', 22 => 'FFFF8080', 23 => 'FF0066CC', 24 => 'FFCCCCFF', 25 => 'FF000080', 26 => 'FFFF00FF', 27 => 'FFFFFF00', 28 => 'FF00FFFF', 29 => 'FF800080', 30 => 'FF800000', 31 => 'FF008080', 32 => 'FF0000FF', 33 => 'FF00CCFF', 34 => 'FFCCFFFF', 35 => 'FFCCFFCC', 36 => 'FFFFFF99', 37 => 'FF99CCFF', 38 => 'FFFF99CC', 39 => 'FFCC99FF', 40 => 'FFFFCC99', 41 => 'FF3366FF', 42 => 'FF33CCCC', 43 => 'FF99CC00', 44 => 'FFFFCC00', 45 => 'FFFF9900', 46 => 'FFFF6600', 47 => 'FF666699', 48 => 'FF969696', 49 => 'FF003366', 50 => 'FF339966', 51 => 'FF003300', 52 => 'FF333300', 53 => 'FF993300', 54 => 'FF993366', 55 => 'FF333399', 56 => 'FF333333');
}
if (array_key_exists($pIndex, self::$_indexedColors)) {
return new PHPExcel_Style_Color(self::$_indexedColors[$pIndex]);
}
if ($background) {
return new PHPExcel_Style_Color('FFFFFFFF');
}
return new PHPExcel_Style_Color('FFFFFFFF');
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->_author . $this->_text->getHashCode() . $this->_width . $this->_height . $this->_marginLeft . $this->_marginTop . ($this->_visible ? 1 : 0) . $this->_fillColor->getHashCode() . __CLASS__);
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
if ($this->_isSupervisor) {
return $this->getSharedComponent()->getHashCode();
}
return md5($this->_name . $this->_size . ($this->_bold ? 't' : 'f') . ($this->_italic ? 't' : 'f') . ($this->_superScript ? 't' : 'f') . ($this->_subScript ? 't' : 'f') . $this->_underline . ($this->_strikethrough ? 't' : 'f') . $this->_color->getHashCode() . __CLASS__);
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->_name . $this->_size . ($this->_bold ? 't' : 'f') . ($this->_italic ? 't' : 'f') . $this->_underline . ($this->_striketrough ? 't' : 'f') . $this->_color->getHashCode() . __CLASS__);
}
public function excelgeneration()
{
$this->load->library('excel');
$this->load->model('reportsmodel');
$data = array();
$postdesignation = $postinterest = $postproduct = $postexperience = $postprofessional = $postknowby = array();
if (!empty($this->input->post('designationselect'))) {
$postdesignation = $this->input->post('designationselect');
}
if (!empty($this->input->post('interestselect'))) {
$postinterest = $this->input->post('interestselect');
}
if (!empty($this->input->post('productselect'))) {
$postproduct = $this->input->post('productselect');
}
if (!empty($this->input->post('experienceselect'))) {
$postexperience = $this->input->post('experienceselect');
}
if (!empty($this->input->post('professionalselect'))) {
$postprofessional = $this->input->post('professionalselect');
}
if (!empty($this->input->post('knowbyselect'))) {
$postknowby = $this->input->post('knowbyselect');
}
$data['fromdate'] = $this->input->post('fromdate');
$data['todate'] = $this->input->post('todate');
$excelreport = $this->reportsmodel->fetchFeedbackDetails($data, $postdesignation, $postinterest, $postproduct, $postexperience, $postprofessional, $postknowby);
// print_r($excelreport);
// exit;
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle('Feedback Report');
$this->excel->getActiveSheet()->getStyle('L3:L1000')->getNumberFormat()->setFormatCode('###0');
$this->excel->getActiveSheet()->getStyle('G3:G1000')->getNumberFormat()->setFormatCode('###0');
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$FontColor = new PHPExcel_Style_Color();
$FontColor = new PHPExcel_Style_Color();
$FontColor->setRGB("C5D6F2");
$m = 'A';
$p = 1;
foreach ($excelreport as $exceldata) {
foreach ($exceldata as $key => $value) {
if ($p == 2) {
$this->excel->setActiveSheetIndex(0)->setCellValue($m . $p, $key);
$p = 3;
$this->excel->setActiveSheetIndex(0)->setCellValue($m . $p, $value);
$p = 2;
} else {
if ($p == 1) {
$this->excel->setActiveSheetIndex(0)->setCellValue($m . $p, $key)->getStyle("C1:Z100")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
} else {
$this->excel->setActiveSheetIndex(0)->setCellValue($m . $p, $value);
}
}
$m++;
}
if ($p == 2) {
$p = 4;
} else {
$p++;
}
$m = 'A';
}
$sheet = $this->excel->getActiveSheet();
$lastColumn = $sheet->getHighestColumn();
$lastColumn++;
//$sheet->getColumnDimension('A')->setWidth(6);
$sheet->getStyle('A1:' . $lastColumn . '2')->getFont()->setBold(true);
for ($i = 'A'; $i != $lastColumn; $i++) {
$sheet->getColumnDimension($i)->setAutoSize(true);
}
$this->excel->setActiveSheetIndex(0)->mergeCells('A1:D1');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$chartid = 'Feedback Report';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"" . $chartid . ".xls\"");
$objWriter->save('php://output');
}
/**
* Get indexed color
*
* @param int $pIndex
* point into the colour array
* @param boolean $background
* indicate whether default background or foreground colour
* should be returned if the indexed colour doesn't exist
* @return PHPExcel_Style_Color
*/
public static function indexedColor($pIndex, $background = FALSE)
{
// Clean parameter
$pIndex = intval($pIndex);
// Indexed colors
if (is_null(self::$_indexedColors)) {
self::$_indexedColors = array(
1 => 'FF000000', // System Colour #1 - Black
2 => 'FFFFFFFF', // System Colour #2 - White
3 => 'FFFF0000', // System Colour #3 - Red
4 => 'FF00FF00', // System Colour #4 - Green
5 => 'FF0000FF', // System Colour #5 - Blue
6 => 'FFFFFF00', // System Colour #6 - Yellow
7 => 'FFFF00FF', // System Colour #7- Magenta
8 => 'FF00FFFF', // System Colour #8- Cyan
9 => 'FF800000', // Standard Colour #9
10 => 'FF008000', // Standard Colour #10
11 => 'FF000080', // Standard Colour #11
12 => 'FF808000', // Standard Colour #12
13 => 'FF800080', // Standard Colour #13
14 => 'FF008080', // Standard Colour #14
15 => 'FFC0C0C0', // Standard Colour #15
16 => 'FF808080', // Standard Colour #16
17 => 'FF9999FF', // Chart Fill Colour #17
18 => 'FF993366', // Chart Fill Colour #18
19 => 'FFFFFFCC', // Chart Fill Colour #19
20 => 'FFCCFFFF', // Chart Fill Colour #20
21 => 'FF660066', // Chart Fill Colour #21
22 => 'FFFF8080', // Chart Fill Colour #22
23 => 'FF0066CC', // Chart Fill Colour #23
24 => 'FFCCCCFF', // Chart Fill Colour #24
25 => 'FF000080', // Chart Line Colour #25
26 => 'FFFF00FF', // Chart Line Colour #26
27 => 'FFFFFF00', // Chart Line Colour #27
28 => 'FF00FFFF', // Chart Line Colour #28
29 => 'FF800080', // Chart Line Colour #29
30 => 'FF800000', // Chart Line Colour #30
31 => 'FF008080', // Chart Line Colour #31
32 => 'FF0000FF', // Chart Line Colour #32
33 => 'FF00CCFF', // Standard Colour #33
34 => 'FFCCFFFF', // Standard Colour #34
35 => 'FFCCFFCC', // Standard Colour #35
36 => 'FFFFFF99', // Standard Colour #36
37 => 'FF99CCFF', // Standard Colour #37
38 => 'FFFF99CC', // Standard Colour #38
39 => 'FFCC99FF', // Standard Colour #39
40 => 'FFFFCC99', // Standard Colour #40
41 => 'FF3366FF', // Standard Colour #41
42 => 'FF33CCCC', // Standard Colour #42
43 => 'FF99CC00', // Standard Colour #43
44 => 'FFFFCC00', // Standard Colour #44
45 => 'FFFF9900', // Standard Colour #45
46 => 'FFFF6600', // Standard Colour #46
47 => 'FF666699', // Standard Colour #47
48 => 'FF969696', // Standard Colour #48
49 => 'FF003366', // Standard Colour #49
50 => 'FF339966', // Standard Colour #50
51 => 'FF003300', // Standard Colour #51
52 => 'FF333300', // Standard Colour #52
53 => 'FF993300', // Standard Colour #53
54 => 'FF993366', // Standard Colour #54
55 => 'FF333399', // Standard Colour #55
56 => 'FF333333'
) // Standard Colour #56
;
}
if (array_key_exists($pIndex, self::$_indexedColors)) {
return new PHPExcel_Style_Color(self::$_indexedColors[$pIndex]);
}
if ($background) {
return new PHPExcel_Style_Color('FFFFFFFF');
}
return new PHPExcel_Style_Color('FF000000');
}
$activeSheet->setCellValue("C{$count}", "=SUM(C3:C" . ($count - 1) . ")");
$activeSheet->getStyle("A{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("A{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle("C{$count}")->getFont()->setBold(true)->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING);
$activeSheet->getStyle("C{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->setCellValue("E{$count}", "Total");
$activeSheet->setCellValue("G{$count}", "=SUM(G3:G" . ($count - 1) . ")");
$activeSheet->getStyle("E{$count}")->getFont()->setBold(true);
$activeSheet->getStyle("E{$count}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle("G{$count}")->getFont()->setBold(true)->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING);
$activeSheet->getStyle("G{$count}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED4);
$activeSheet->mergeCells("D2:D{$count}");
$activeSheet->setCellValue("D2", "Year " . getIVal("date_format(end_date, '%Y')", "sys_financial_year", "year_id = {$_GET['fyear']}"));
$activeSheet->getStyle("D2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setTextRotation(90);
$activeSheet->getStyle("D2")->getFont()->setBold(true)->setSize(24);
$greyColor = new PHPExcel_Style_Color();
$greyColor->setRGB("D9D9D9");
$activeSheet->getStyle("A1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("D2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("A{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("B{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("C{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("E{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("F{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("G{$count}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->setStartColor($greyColor);
$activeSheet->getStyle("A1:G{$count}")->applyFromArray($tableBorderArray);
$objPHPExcel->setActiveSheetIndex(0);
$fileName = "balance_sheet.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
public function reporting()
{
$this->load->library("excel");
$q = isset($_GET['q']) ? $this->input->get('q', TRUE) : '';
$data_filter = array();
if (isset($_GET['siteid']) and !empty($_GET['siteid'])) {
$data_filter['protelindo_site_id'] = $this->input->get('siteid', TRUE);
}
if (isset($_GET['region']) and !empty($_GET['region'])) {
$data_filter['Region'] = $this->input->get('region', TRUE);
}
if (isset($_GET['status']) and !empty($_GET['status'])) {
$data_filter['sla_event_status'] = $this->input->get('status', TRUE);
}
if (isset($_GET['desc']) and !empty($_GET['desc'])) {
$data_filter['tt_status_description'] = $this->input->get('desc', TRUE);
}
if (isset($_GET['status_description']) and !empty($_GET['status_description'])) {
$data_filter['tt_status_description'] = $this->input->get('status_description', TRUE);
}
if (isset($_GET['ttno']) and !empty($_GET['ttno'])) {
$data_filter['tt_no'] = $this->input->get('ttno', TRUE);
}
//default tt open
$data_filter['tt_open'] = $this->input->get('tt_open', TRUE);
$result = $this->model_tt->reporting($data_filter);
$this->excel->setActiveSheetIndex(0);
$fields = $result->list_fields();
$setting_dimension = array('A' => 30, 'B' => 25, 'C' => 25, 'D' => 25, 'E' => 25, 'F' => 25, 'G' => 25, 'H' => 25, 'I' => 25, 'J' => 25, 'K' => 25, 'L' => 25, 'M' => 25, 'N' => 25, 'O' => 25, 'P' => 25, 'Q' => 25, 'R' => 25, 'S' => 25, 'T' => 25, 'U' => 25, 'V' => 25, 'W' => 25, 'X' => 25, 'Y' => 25, 'Z' => 25, 'AA' => 25, 'AB' => 25, 'AC' => 25, 'AD' => 25, 'AE' => 25, 'AF' => 25, 'AG' => 25, 'AH' => 25);
foreach ($setting_dimension as $col => $width) {
$this->excel->getActiveSheet()->getColumnDimension($col)->setWidth($width);
}
$this->excel->getActiveSheet()->getStyle('A1:AH1')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DA3232')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)));
$phpColor = new PHPExcel_Style_Color();
$phpColor->setRGB('FFFFFF');
$this->excel->getActiveSheet()->getStyle('A1:AH1')->getFont()->setColor($phpColor);
$this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
$this->excel->getActiveSheet()->getStyle('A1:AH1')->getAlignment()->setWrapText(true);
$col = 0;
foreach ($fields as $field) {
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
$row = 2;
foreach ($result->result() as $data) {
$col = 0;
foreach ($fields as $field) {
if ($col == 19) {
$tno = 'TT Number';
$tt = $this->model_tt->get_single('tt', 'tt_no', $data->{$tno});
$activity = @$this->model_meta->get_meta_single('tt', $tt->idtt, 'tt_description', 'text')->meta_value;
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $activity);
} elseif ($col == 32) {
$st = 'TT Status';
$susName = 'Suspected Trouble Name';
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, status_tt($data->{$st}, NULL, $data->{$susName}, NULL, FALSE, TRUE));
} elseif ($col == 9) {
$eqtype = 'Equipment Type';
if ($data->{$eqtype} == '1') {
$type = 'Indoor';
} elseif ($data->{$eqtype} == '2') {
$type = 'Outdoor';
} elseif ($data->{$eqtype} == '3') {
$type = 'Base Only';
} elseif ($data->{$eqtype} == '4') {
$type = 'Outdoor 2';
} else {
$type = '-';
}
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $type);
} else {
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
}
$col++;
}
$row++;
}
//set border
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$this->excel->getActiveSheet()->getStyle('A1:AH' . $row)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->setTitle('TT');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=TT-Reporting-' . url_title(user_admin('username')) . '-' . date("Y-M-d") . '.xls');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save('php://output');
}
/**
* Generates the sheet's workbook...
*
* @param String format extension
*/
function _generateSheets($format){
$sheets= array();
$sheets= $this->book->getSheets();
$i= 0;
if ($format=="ods"){
foreach($sheets as $sheet){
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
foreach($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$data= $cell->getFormula();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
if (substr($data, 0, 1)== '=')
$this->objPHPOds->addCell($i,$row,$col,substr($data, 1),'float');
//TODO
else /*OJO CON ESTO DISCERNIR ENTRE LOS DIFERENTES TIPOS*/
$this->objPHPOds->addCell($i,$row,$col,$data,'string');
}
$this->objPHPOds->addStyle($fontStyle, $cell);
}
$i++;
}
}
else{
foreach($sheets as $sheet){
if ($i>0)
$this->objPHPExcel->createSheet();
$this->objPHPExcel->setActiveSheetIndex($i);
$j= $i + 1;
$this->objPHPExcel->getActiveSheet()->setTitle("Sheet $j");
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
$cell= new Cell();
foreach ($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$row++;
$data= $cell->getFormula();
// $this->objPHPExcel= new PHPExcel();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
$fontName= $fontStyle->getFontName();
$fcolor= substr($fontStyle->getFontColor(),1);
if ($fcolor == "000000"){
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK);
$ncolor->setRGB($fcolor);
}
else{
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color();
$ncolor->setRGB($fcolor);
}
$style= new PHPExcel_Style();
$style->getFont()->setColor($ncolor);
$style->getFont()->setName($fontName);
$style->getFont()->setBold($fontStyle->getFontBold()== 1);
$style->getFont()->setItalic($fontStyle->getFontItalic()==1);
$style->getFont()->setSize($fontStyle->getFontSize());
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
switch ($fontStyle->fontHAlign){
case 0:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
break;
case 1:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
break;
case 2:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
break;
case 3:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
break;
}
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
switch ($fontStyle->fontVAlign){
case 0:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
break;
case 1:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_CENTER;
break;
case 2:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_TOP;
}
$style->getAlignment()->setHorizontal($HzAlign);
$style->getAlignment()->setVertical($VlAlign);
if ($fontStyle->getFontUnderline()!= 0){
$style->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
}
$this->objPHPExcel->getActiveSheet()->duplicateStyle($style, PHPExcel_Cell::stringFromColumnIndex($col) . $row);
$this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->setValueExplicit($data, PHPExcel_Cell_DataType::dataTypeForValue($data));
}
}
$i++;
}
}
}
public function reporting()
{
$this->load->library("excel");
$q = isset($_GET['q']) ? $_GET['q'] : '';
$data_filter = array();
if (isset($_GET['siteid']) and !empty($_GET['siteid'])) {
$data_filter['pmp_idsite'] = $_GET['siteid'];
}
if (isset($_GET['region']) and !empty($_GET['region'])) {
$data_filter['Region'] = $_GET['region'];
}
if (isset($_GET['priority']) and !empty($_GET['priority'])) {
$data_filter['pmp_priority'] = $_GET['priority'];
}
if (isset($_GET['status']) and !empty($_GET['status'])) {
$data_filter['pmp_status'] = $_GET['status'];
}
if (isset($_GET['period']) and !empty($_GET['period'])) {
$data_filter['pm_period_idpm_period'] = (int) $_GET['period'];
}
if (isset($_GET['task_id']) and !empty($_GET['task_id'])) {
$data_filter['idpm_plan_detil'] = (int) $_GET['task_id'];
}
if (isset($_GET['type']) and !empty($_GET['type'])) {
$type = $_GET['type'];
$idprotelqc = $this->input->get('idprotelqc');
if ($type == 'allqc') {
$data_filter['pmp_qc_protelindo'] = (int) $idprotelqc;
} elseif ($type == 'qccompletion') {
$data_filter['pmp_qc_protelindo'] = (int) $idprotelqc;
} elseif ($type == 'qcwaitingapproval') {
$data_filter['pmp_qc_protelindo'] = (int) $idprotelqc;
}
}
$result = $this->model_task->reporting($data_filter);
$this->excel->setActiveSheetIndex(0);
$fields = $result->list_fields();
$setting_dimension = array('A' => 40, 'B' => 25, 'C' => 25, 'D' => 25, 'E' => 25, 'F' => 25, 'G' => 25, 'H' => 25, 'I' => 25, 'J' => 25, 'K' => 25, 'L' => 25, 'M' => 25);
foreach ($setting_dimension as $col => $width) {
$this->excel->getActiveSheet()->getColumnDimension($col)->setWidth($width);
}
//styling
$this->excel->getActiveSheet()->getStyle('A1:M1')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DA3232')), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)));
$phpColor = new PHPExcel_Style_Color();
$phpColor->setRGB('FFFFFF');
$this->excel->getActiveSheet()->getStyle('A1:M1')->getFont()->setColor($phpColor);
$this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
$this->excel->getActiveSheet()->getStyle('A1:M1')->getAlignment()->setWrapText(true);
$col = 0;
foreach ($fields as $field) {
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
$row = 2;
foreach ($result->result() as $data) {
$col = 0;
foreach ($fields as $field) {
$this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field});
$col++;
}
$row++;
}
//set border
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$this->excel->getActiveSheet()->getStyle('A1:M' . $row)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->setTitle('TT');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=PM-Reporting-' . url_title(user_admin('username')) . '-' . date("Y-M-d") . '.xls');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save('php://output');
}
/**
* Get indexed color
*
* @param int $pIndex
* @return PHPExcel_Style_Color
*/
public static function indexedColor($pIndex)
{
// Clean parameter
$pIndex = intval($pIndex);
// Indexed colors
if (is_null(self::$_indexedColors)) {
self::$_indexedColors = array();
self::$_indexedColors[] = '00000000';
self::$_indexedColors[] = '00FFFFFF';
self::$_indexedColors[] = '00FF0000';
self::$_indexedColors[] = '0000FF00';
self::$_indexedColors[] = '000000FF';
self::$_indexedColors[] = '00FFFF00';
self::$_indexedColors[] = '00FF00FF';
self::$_indexedColors[] = '0000FFFF';
self::$_indexedColors[] = '00000000';
self::$_indexedColors[] = '00FFFFFF';
self::$_indexedColors[] = '00FF0000';
self::$_indexedColors[] = '0000FF00';
self::$_indexedColors[] = '000000FF';
self::$_indexedColors[] = '00FFFF00';
self::$_indexedColors[] = '00FF00FF';
self::$_indexedColors[] = '0000FFFF';
self::$_indexedColors[] = '00800000';
self::$_indexedColors[] = '00008000';
self::$_indexedColors[] = '00000080';
self::$_indexedColors[] = '00808000';
self::$_indexedColors[] = '00800080';
self::$_indexedColors[] = '00008080';
self::$_indexedColors[] = '00C0C0C0';
self::$_indexedColors[] = '00808080';
self::$_indexedColors[] = '009999FF';
self::$_indexedColors[] = '00993366';
self::$_indexedColors[] = '00FFFFCC';
self::$_indexedColors[] = '00CCFFFF';
self::$_indexedColors[] = '00660066';
self::$_indexedColors[] = '00FF8080';
self::$_indexedColors[] = '000066CC';
self::$_indexedColors[] = '00CCCCFF';
self::$_indexedColors[] = '00000080';
self::$_indexedColors[] = '00FF00FF';
self::$_indexedColors[] = '00FFFF00';
self::$_indexedColors[] = '0000FFFF';
self::$_indexedColors[] = '00800080';
self::$_indexedColors[] = '00800000';
self::$_indexedColors[] = '00008080';
self::$_indexedColors[] = '000000FF';
self::$_indexedColors[] = '0000CCFF';
self::$_indexedColors[] = '00CCFFFF';
self::$_indexedColors[] = '00CCFFCC';
self::$_indexedColors[] = '00FFFF99';
self::$_indexedColors[] = '0099CCFF';
self::$_indexedColors[] = '00FF99CC';
self::$_indexedColors[] = '00CC99FF';
self::$_indexedColors[] = '00FFCC99';
self::$_indexedColors[] = '003366FF';
self::$_indexedColors[] = '0033CCCC';
self::$_indexedColors[] = '0099CC00';
self::$_indexedColors[] = '00FFCC00';
self::$_indexedColors[] = '00FF9900';
self::$_indexedColors[] = '00FF6600';
self::$_indexedColors[] = '00666699';
self::$_indexedColors[] = '00969696';
self::$_indexedColors[] = '00003366';
self::$_indexedColors[] = '00339966';
self::$_indexedColors[] = '00003300';
self::$_indexedColors[] = '00333300';
self::$_indexedColors[] = '00993300';
self::$_indexedColors[] = '00993366';
self::$_indexedColors[] = '00333399';
self::$_indexedColors[] = '00333333';
}
if (array_key_exists($pIndex, self::$_indexedColors)) {
return new PHPExcel_Style_Color(self::$_indexedColors[$pIndex]);
}
return new PHPExcel_Style_Color();
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->_fillType . $this->_rotation . $this->_startColor->getHashCode() . $this->_endColor->getHashCode() . __CLASS__);
}
/**
* Set End Color
*
* @param PHPExcel_Style_Color $pValue
* @throws Exception
* @return PHPExcel_Style_Fill
*/
public function setEndColor(PHPExcel_Style_Color $pValue = null)
{
// make sure parameter is a real color and not a supervisor
$color = $pValue->getIsSupervisor() ? $pValue->getSharedComponent() : $pValue;
if ($this->_isSupervisor) {
$styleArray = $this->getEndColor()->getStyleArray(array('argb' => $color->getARGB()));
$this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
} else {
$this->_endColor = $color;
}
return $this;
}
$activeSheet->setCellValue('K5', $count3);
$activeSheet->setCellValue('L5', $count4);
$activeSheet->setCellValue('M5', $count5);
$activeSheet->setCellValue('N5', $count6);
$activeSheet->setCellValue('O5', $count7);
$activeSheet->mergeCells('B9:B' . ($num - 1));
$activeSheet->getStyle('B9:B' . ($num - 1))->applyFromArray($canhgiua);
$activeSheet->getStyle('B9:B' . ($num - 1))->applyFromArray($indam);
$activeSheet->getStyle('C9:C' . (count($arr) + 9))->applyFromArray($canhgiua);
for ($i = 'E'; $i <= 'O'; $i++) {
$activeSheet->getStyle($i . '9:' . $i . (count($arr) + 9))->applyFromArray($canhgiua);
}
$activeSheet->getStyle('D9:D' . (count($arr) + 9))->applyFromArray($canhtrai);
$activeSheet->getStyle('P9:P' . (count($arr) + 9))->applyFromArray($canhtrai);
$activeSheet->getStyle('Q9:Q' . (count($arr) + 9))->applyFromArray($canhtrai);
$phpColor = new PHPExcel_Style_Color();
$phpColor->setRGB('FF0000');
$activeSheet->setCellValue('D' . ($count + 9 + 6), 'Ghi chú:');
$activeSheet->getStyle('D' . ($count + 9 + 6))->applyFromArray($gachdit);
$activeSheet->getStyle('D' . ($count + 9 + 6))->applyFromArray($innghieng);
$activeSheet->getStyle('D' . ($count + 9 + 6))->applyFromArray($indam);
$activeSheet->getStyle('D' . ($count + 9 + 6))->getFont()->setColor($phpColor);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun('CÔNG VIỆC:');
$objBold->getFont()->setBold(true);
$objRichText->createText(' Ghi tên công việc mà mình làm thực tế.');
$activeSheet->setCellValue('D' . ($count + 9 + 7), $objRichText);
$activeSheet->mergeCells('D' . ($count + 9 + 7) . ':O' . ($count + 9 + 7));
unset($objBold);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun('MÃ DỰ ÁN:');
private static function _readColor($color, $background = FALSE)
{
if (isset($color["rgb"])) {
return (string) $color["rgb"];
} else {
if (isset($color["indexed"])) {
return PHPExcel_Style_Color::indexedColor($color["indexed"] - 7, $background)->getARGB();
} else {
if (isset($color["theme"])) {
if (self::$_theme !== NULL) {
$returnColour = self::$_theme->getColourByIndex((int) $color["theme"]);
if (isset($color["tint"])) {
$tintAdjust = (double) $color["tint"];
$returnColour = PHPExcel_Style_Color::changeBrightness($returnColour, $tintAdjust);
}
return 'FF' . $returnColour;
}
}
}
}
if ($background) {
return 'FFFFFFFF';
}
return 'FF000000';
}
$isnumber[$cols] = 1;
}
$objPHPExcel->getActiveSheet()->setCellValue($cols . ($i + 1), $v);
if (!empty($row['color']) && $k == $locate->Translate("answered duration")) {
$colorcell = $cols . ($i + 1) . ":" . $cols . ($i + 1);
$BackgroundColor = new PHPExcel_Style_Color();
$BackgroundColor->setRGB($row['color']);
$objPHPExcel->getActiveSheet()->getStyle($colorcell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($colorcell)->getFill()->setStartColor($BackgroundColor);
}
} else {
//$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);合并单元格方法
//给单元格赋背景色
if (!empty($row['color']) && $k == $locate->Translate("answered duration")) {
$colorcell = $cols . ($i + 1) . ":" . $cols . ($i + 1);
$BackgroundColor = new PHPExcel_Style_Color();
$BackgroundColor->setRGB($row['color']);
$objPHPExcel->getActiveSheet()->getStyle($colorcell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($colorcell)->getFill()->setStartColor($BackgroundColor);
}
if (is_numeric($v)) {
$isnumber[$cols] = 1;
}
$objPHPExcel->getActiveSheet()->setCellValue($cols . ($i + 1), $v);
}
}
$i++;
}
foreach ($isnumber as $key => $value) {
$objPHPExcel->getActiveSheet()->getColumnDimension($key)->setAutoSize(true);
}
private function _readColor($color) {
if (isset($color["rgb"])) {
return (string)$color["rgb"];
} else if (isset($color["indexed"])) {
return PHPExcel_Style_Color::indexedColor($color["indexed"])->getARGB();
}
}
public function setStyle($cellRange, $fontFamily = 'Arial', $fontSize = 10, $colorRGB = '00000000', $bold = false, $italic = false, $underline = false)
{
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style.php';
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style/Font.php';
require_once \GO::config()->root_path . 'go/vendor/PHPExcel/PHPExcel/Style/Color.php';
$colorObj = new \PHPExcel_Style_Color();
$colorObj->setRGB($colorRGB);
$fontObj = new \PHPExcel_Style_Font();
$fontObj->setName($fontFamily);
$fontObj->setSize($fontSize);
$fontObj->setColor($colorObj);
$fontObj->setBold($bold);
$fontObj->setItalic($italic);
$fontObj->setUnderline($underline);
$styleObj = new \PHPExcel_Style();
$styleObj->setFont($fontObj);
$this->getActiveSheet()->setSharedStyle($styleObj, $cellRange);
}
