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