/** * * @param array $data * @param $plotType PHPExcel_Chart_DataSeries::TYPE_* * @param $plotGrouping PHPExcel_Chart_DataSeries::GROUPING_* * @param array $columns * @param string $format format for values * @param int $positionOffset * @return \PHPExcel_Chart */ public static function getChart($data, $plotType, $plotGrouping, $columns = null, $format = null, $positionOffset = 0) { $columnCount = count($data[0]); $rowCount = count($data); $keys = array_keys($data[0]); $labels = array(); $categories = array(); $values = array(); for ($i = 1; $i < $columnCount; $i++) { if (!is_array($columns) || in_array($keys[$i], $columns)) { $col = PHPExcel_Cell::stringFromColumnIndex($i); $labels[] = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . $col . '$1', null, 1); $categories[] = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$' . ($rowCount + 1), null, $rowCount); $values[] = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$' . $col . '$2:$' . $col . '$' . ($rowCount + 1), $format, $rowCount); } } $series = new PHPExcel_Chart_DataSeries($plotType, $plotGrouping, range(0, count($values) - 1), $labels, $categories, $values); $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); $plotarea = new PHPExcel_Chart_PlotArea(null, array($series)); $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false); $chart = new PHPExcel_Chart('chart' . uniqid(), null, $legend, $plotarea, true, 0, null, null); $chart->setTopLeftPosition(PHPExcel_Cell::stringFromColumnIndex($columnCount + 1) . '' . (2 + $positionOffset)); $chart->setBottomRightPosition(PHPExcel_Cell::stringFromColumnIndex($columnCount + 19) . '' . (20 + $positionOffset)); return $chart; }
public function getExportMintic() { $param = (object) $_POST; if (!isset($param->percentStartDownload) || !is_numeric($param->percentStartDownload)) { $param->percentStartDownload = 95; } if (!isset($param->percentEndDownload) || !is_numeric($param->percentEndDownload)) { $param->percentEndDownload = 110; } if (!isset($param->percentStartUpload) || !is_numeric($param->percentStartUpload)) { $param->percentStartUpload = 95; } if (!isset($param->percentEndUpload) || !is_numeric($param->percentEndUpload)) { $param->percentEndUpload = 110; } $where = "AND ( `pdownload` BETWEEN '" . $param->percentStartDownload . "' AND '" . $param->percentEndDownload . "' )" . " AND ( `pupload` BETWEEN '" . $param->percentStartUpload . "' AND '" . $param->percentEndUpload . "' )" . " AND ( `testDate` BETWEEN '" . $param->from . " 00:00:00' AND '" . $param->to . " 23:59:59')"; $getSpeedtestSQL = "SELECT H.`id_host`, HD1.idLocation, \r\n\t\t\t\t\t\t\t'100' as 'mEESperada',\r\n\t\t\t\t\t\t\tSUM(IF(RP.`pdownload` >= 100,1,0)) AS 'mDownloadOk', \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pdownload` < 100,1,0)) AS 'mDownloadFallidas' , \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pupload` >= 100,1,0)) AS 'mUploadOk', \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pupload` < 100,1,0)) AS 'mUploadFallidas', \r\n\t\t\t\t \t\t\tROUND(AVG(RP.`download`) / 1024) AS 'vavgdown', \r\n\t\t\t\t \t\t\tROUND(AVG(RP.`upload`) / 1024) AS 'vavgupload',\r\n\t\t\t\t\t\t\t'0' as 'cumplimientoDown',\r\n\t\t\t\t \t\t\t'0' as 'cumplimientoUpload' , \r\n\t\t\t\t \t\t\tL.`additionalForm` , \r\n\t\t\t\t \t\t\tL.`minTest` , \r\n\t\t\t\t \t\t\tCONCAT('SDS ', HD2.city ,' (', L.`city`,')') AS 'name'\r\n\t\t\t\t\t\t\tFROM `bm_host` H \r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN (SELECT `id_host`, `value` as 'idLocation' FROM `bm_host_detalle` WHERE `id_feature` = 78) HD1 ON ( H.`id_host`=HD1.`id_host`)\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN (SELECT `id_host`, `value` as 'city' FROM `bm_host_detalle` WHERE `id_feature` = 79) HD2 ON ( H.`id_host`=HD2.`id_host`)\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN `bm_location` L ON L.`idLocation`=HD1.`idLocation`\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN `report_speedtest` RP ON RP.`idHost`=H.`id_host` {$where}\r\n\t\t\t\t\t\tWHERE H.`groupid` = " . $param->groupid . " AND H.`borrado` = 0 AND L.`additionalForm` IS NOT NULL \r\n\t\t\t\t\t\tGROUP BY name"; $getSpeedtestRESULT = $this->conexion->queryFetch($getSpeedtestSQL); if ($getSpeedtestRESULT) { foreach ($getSpeedtestRESULT as $key => $value) { $detailJson = json_decode($value['additionalForm']); foreach ($detailJson as $keyJson => $valueJson) { $detail[$valueJson->key] = $valueJson->value; } $cumplimientoDown = 100 * $value['mDownloadOk'] / $value['minTest']; $cumplimientoUpload = 100 * $value['mUploadOk'] / $value['minTest']; $pageData[$detail['division']][] = array('name' => $value['name'], 'mEESperada' => $value['minTest'], 'mDownloadOk' => $value['mDownloadOk'], 'mDownloadFallidas' => $value['mDownloadFallidas'], 'mUploadOk' => $value['mUploadOk'], 'mUploadFallidas' => $value['mUploadFallidas'], 'vavgdown' => is_null($value['vavgdown']) ? 0 : $value['vavgdown'], 'vavgupload' => is_null($value['vavgupload']) ? 0 : $value['vavgupload'], 'cumplimientoDown' => number_format($cumplimientoDown, 0, ',', ''), 'cumplimientoUpload' => number_format($cumplimientoUpload, 0, ',', '')); } } $header = array('SDS (Municipio)', 'Muestras a Realizar', 'Muestras Down Ok', 'Muestras Down Fallidas', 'Muestras Up Ok', 'Muestras Up Fallidas', 'Vel. prom. Down', 'Vel. prom. Up', 'Cumplimiento Cantidad Muestras Down OK', 'Cumplimiento Cantidad Muestras Up OK'); include APPS . "plugins/PHPExcel.php"; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Baking software")->setLastModifiedBy("bMonitor")->setTitle($this->language->GENERAL_REPORT)->setSubject("Export")->setDescription("Documento generado por report manager de bMonitor")->setKeywords("report")->setCategory("bMonitor"); $page = 0; $lineStart = 38; $styleHeader = array('font' => array('bold' => true, 'size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array('argb' => 'e3ae8c'), 'endcolor' => array('argb' => 'e3ae8c'))); $styleCell = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array('argb' => 'a0e8bd'), 'endcolor' => array('argb' => 'a0e8bd'))); $styleCellWarning = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'F47C00'), 'endcolor' => array('argb' => 'F47C00'))); $styleCellCritical = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'D11600'), 'endcolor' => array('argb' => 'D11600'))); //Llenado paginas foreach ($pageData as $key => $value) { $letter = 65; $line = $lineStart; if ($page > 0) { $objPHPExcel->createSheet(); } $objPHPExcel->setActiveSheetIndex($page); $objPHPExcel->getActiveSheet()->setTitle($key); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(31); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Resultados Pruebas MinTic desde SDS - ' . $key); $objPHPExcel->getActiveSheet()->mergeCells('A1:J1'); $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //Graficos $locationCount = count($value); $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$38', NULL, 1)); $dataseriesLabels2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$F$38', NULL, 1)); $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$39:$A$' . (39 + $locationCount), NULL, $locationCount)); $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$39:$B$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$39:$C$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$39:$D$' . (39 + $locationCount), NULL, $locationCount)); $dataSeriesValues2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$39:$B$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$39:$E$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$F$39:$F$' . (39 + $locationCount), NULL, $locationCount)); $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1); $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels2, $xAxisTickValues, $dataSeriesValues2); $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); $series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); $plotarea1 = new PHPExcel_Chart_PlotArea(NULL, array($series1)); $plotarea2 = new PHPExcel_Chart_PlotArea(NULL, array($series2)); $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_BOTTOM, NULL, false); $title1 = new PHPExcel_Chart_Title('Muestras Down'); $title2 = new PHPExcel_Chart_Title('Muestras UP'); // Create the chart $chart1 = new PHPExcel_Chart('chart1', $title1, $legend, $plotarea1, true, 0, NULL, NULL); $chart2 = new PHPExcel_Chart('chart2', $title2, $legend, $plotarea2, true, 0, NULL, NULL); $chart1->setTopLeftPosition('A12'); $chart1->setBottomRightPosition('F31'); $chart2->setTopLeftPosition('H12'); $chart2->setBottomRightPosition('N31'); $objPHPExcel->getActiveSheet()->addChart($chart1); $objPHPExcel->getActiveSheet()->addChart($chart2); //Tabla foreach ($header as $keyHeader => $valueHeader) { $objPHPExcel->setActiveSheetIndex($page)->setCellValue(chr($letter) . $line, $valueHeader); $letter++; } $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(65) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleHeader); $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(66) . $line . ":" . chr($letter - 1) . $line)->getAlignment()->setWrapText(true); $line++; foreach ($value as $keyLine => $valueLine) { $letter = 65; foreach ($valueLine as $celdaKey => $celdaValue) { if ($celdaKey == 'cumplimientoDown' || $celdaKey == 'cumplimientoUpload') { $unit = '%'; } else { $unit = ''; } $objPHPExcel->setActiveSheetIndex($page)->setCellValue(chr($letter) . $line, $celdaValue . $unit); $letter++; } $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(71) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCell); $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(66) . $line . ":" . chr($letter - 1) . $line)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); if ($valueLine['cumplimientoDown'] < 100 && $valueLine['cumplimientoDown'] > 0) { $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(73) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCellWarning); } elseif ($valueLine['cumplimientoDown'] == 0) { $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(71) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCellCritical); } $line++; } $page++; } $objPHPExcel->setActiveSheetIndex(0); $file = 'qoe_report_mintic_' . $param->from . "_" . $param->to . "_" . date("Ymd") . '.xlsx'; $path = site_path . '/upload/'; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save($path . $file); $result['status'] = true; $result['file'] = $file; $this->basic->jsonEncode($result); exit; }
public function reporteEvaluacion($num) { $this->load->model("Aula_model", "Aula"); $this->load->model("Evaluacion_model", "Evaluacion"); $evaluaciones_all = $this->Evaluacion->getEvaluacioNumero($num); //todas for ($i = 0, $len = count($evaluaciones_all); $i < $len; $i++) { $aulas[$i] = $this->Evaluacion->count_diagnostico($evaluaciones_all[$i]->idAula, $evaluaciones_all[$i]->id); } // configuramos las propiedades del documento $this->phpexcel->getProperties()->setCreator("SoftGroup Perú")->setLastModifiedBy("SoftGroup Perú")->setTitle("Reporte Evaluacion")->setSubject("Evaluacion Nutricional")->setDescription("Reporte Total de Evaluacion")->setKeywords("office 2007 openxml php")->setCategory("Reporte"); //agrego estilos $border_style = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000')))); $center_style = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $color1_style = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CEECF5'))); $this->phpexcel->setActiveSheetIndex(0)->setCellValue('B1', 'I.E.I. “DIVINO NIÑO JESÚS”')->setCellValue('B2', 'REPORTE DE LAS EVALUACIONES N°' . $num)->setCellValue('B3', '')->setCellValue('B4', ''); $fila = 4; // a partir de que fila empezara el listado $f2 = $fila + 1; $this->phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(7); $this->phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(25); $this->phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $this->phpexcel->getActiveSheet()->getColumnDimension('I')->setWidth(15); $this->phpexcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); $sheet = $this->phpexcel->getActiveSheet(); $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . $f2, '#')->setCellValue('B' . $f2, 'Aula')->setCellValue('C' . $f2, 'Normal')->setCellValue('D' . $f2, 'Obeso')->setCellValue('E' . $f2, 'Sobrepeso')->setCellValue('F' . $f2, 'D. Aguda')->setCellValue('G' . $f2, 'D. Severa')->setCellValue('H' . $f2, 'D. Crónica')->setCellValue('I' . $f2, 'Sin Diagnóstico')->setCellValue('J' . $f2, 'Total'); $con = 1; $t_normales = 0; $t_obesos = 0; $t_sobrepesos = 0; $t_agudas = 0; $t_severos = 0; $t_cronicos = 0; $t_sindiag = 0; $t_totales = 0; foreach ($aulas as $key) { //fila + 1 foreach ($key as $v) { $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . ($f2 + $con), $con)->setCellValue('B' . ($f2 + $con), $v->aula)->setCellValue('C' . ($f2 + $con), $v->normales)->setCellValue('D' . ($f2 + $con), $v->obesos)->setCellValue('E' . ($f2 + $con), $v->sobrepesos)->setCellValue('F' . ($f2 + $con), $v->agudas)->setCellValue('G' . ($f2 + $con), $v->severos)->setCellValue('H' . ($f2 + $con), $v->cronicos)->setCellValue('I' . ($f2 + $con), $v->sindiag)->setCellValue('J' . ($f2 + $con), $v->totales); $t_normales += $v->normales; $t_obesos += $v->obesos; $t_sobrepesos += $v->sobrepesos; $t_agudas += $v->agudas; $t_severos += $v->severos; $t_cronicos += $v->cronicos; $t_sindiag += $v->sindiag; $t_totales += $v->totales; } //end foreach $con++; } //end forreach $aula $f2 += $con; $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . $f2, ' ')->setCellValue('B' . $f2, 'Total')->setCellValue('C' . $f2, $t_normales)->setCellValue('D' . $f2, $t_obesos)->setCellValue('E' . $f2, $t_sobrepesos)->setCellValue('F' . $f2, $t_agudas)->setCellValue('G' . $f2, $t_severos)->setCellValue('H' . $f2, $t_cronicos)->setCellValue('I' . $f2, $t_sindiag)->setCellValue('J' . $f2, $t_totales); //GRAFICAS $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Reporte!$J$' . $f2, NULL, 1)); $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Reporte!$C$' . ($fila + 1) . ':$J$' . ($fila + 1), NULL, 5)); $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Reporte!$C$' . $f2 . ':$J$' . $f2, NULL, 5)); // Build the dataseries $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1); $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); // Set the series in the plot area $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series1)); // Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title = new PHPExcel_Chart_Title('Reporte de las Evaluaciones N°' . $num); // Create the chart $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, NULL, NULL); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition('A' . ($f2 + 2)); $chart->setBottomRightPosition('G' . ($f2 + 14)); // Add the chart to the worksheet $sheet->addChart($chart); //PIE CHART $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, null, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1); // Set up a layout object for the Pie chart $layout1 = new PHPExcel_Chart_Layout(); $layout1->setShowVal(TRUE); $layout1->setShowPercent(TRUE); // Set the series in the plot area $plotarea2 = new PHPExcel_Chart_PlotArea($layout1, array($series2)); // Set the chart legend $chart2 = new PHPExcel_Chart('chart2', $title, $legend, $plotarea2, true, 0, NULL, NULL); // Set the position where the chart should appear in the worksheet $chart2->setTopLeftPosition('A' . ($f2 + 15)); $chart2->setBottomRightPosition('G' . ($f2 + 35)); // Add the chart to the worksheet $sheet->addChart($chart2); // renombro la hoja de trabajo con el nombre del aula $this->phpexcel->getActiveSheet()->setTitle('Reporte'); $sheet->getStyle("A" . ($fila + 1) . ":J" . $f2)->applyFromArray($border_style); $sheet->getStyle("A" . ($fila + 1) . ":J" . ($fila + 1))->applyFromArray($center_style)->getFont()->setBold(true); $sheet->getStyle("A" . $f2 . ":J" . $f2)->applyFromArray($center_style)->getFont()->setBold(true); $sheet->getStyle("A" . ($fila + 1) . ":J" . ($fila + 1))->applyFromArray($color1_style); $sheet->getStyle("A" . $f2 . ":J" . $f2)->applyFromArray($color1_style); $this->phpexcel->setActiveSheetIndex(0); //redireccionamos la salida al navegador del cliente (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="Reporte Evaluacion ' . $num . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save('php://output'); }
public function excel_create_chartsheet_for_faults($charttype, $datasheetname, $dataendrow, $graphname) { // Set the Labels for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $datasheetname = $datasheetname; $dataseriesLabels = array(new PHPExcel_Chart_DataSeriesValues('String', $datasheetname . '!$B$2:$D$2', NULL, 1)); $xAxis_count = $dataendrow; // Set the X-Axis Labels // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', $datasheetname . '!$A$3:$C$' . $xAxis_count, NULL, $xAxis_count - 1)); // Set the Data values for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', $datasheetname . '!$D$3:$D$' . $xAxis_count, NULL, $xAxis_count - 1)); // Build the dataseries if ($charttype == "饼状图") { $series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues); // Set up a layout object for the Pie chart $layout1 = new PHPExcel_Chart_Layout(); $layout1->setShowVal(TRUE); $layout1->setShowPercent(TRUE); } /* else if($charttype == "曲线图"){ $series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_LINECHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_STACKED, // plotGrouping range(0, count($dataSeriesValues)-1), // plotOrder $dataseriesLabels, // plotLabel $xAxisTickValues, // plotCategory $dataSeriesValues // plotValues ); } else{ $series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping // PHPExcel_Chart_DataSeries::TYPE_LINECHART, // plotType // PHPExcel_Chart_DataSeries::GROUPING_STACKED, // plotGrouping range(0, count($dataSeriesValues)-1), // plotOrder $dataseriesLabels, // plotLabel $xAxisTickValues, // plotCategory $dataSeriesValues // plotValues ); }*/ $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); // Set the series in the plot area if ($charttype == "饼状图") { $plotarea = new PHPExcel_Chart_PlotArea($layout1, array($series)); } else { // $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series)); } // Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, NULL, false); // $title = new PHPExcel_Chart_Title('Test Stacked Line Chart'); // $yAxisLabel = new PHPExcel_Chart_Title('Value ($k)'); $title = new PHPExcel_Chart_Title($graphname); $yAxisLabel = new PHPExcel_Chart_Title(''); // Create the chart $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, NULL, $yAxisLabel); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition('F2'); $chart->setBottomRightPosition('S26'); return $chart; }
public function _makeChartFromSheetData($dataStartCol, $dataStartRow, $dataEndCol, $dataEndRow, $chartHeight, $chartWidth) { $worksheetName = $this->objWorksheet->getTitle(); $seriesStartRow = $dataStartRow + 1; $seriesTotalCols = $dataStartCol + ($dataEndCol - 1); $dataSeriesLabels = $xAxisTickValues = $dataSeriesValues = array(); $totalRows = $dataEndRow - 1; // Basic structure of sheet data /* $objWorksheet->fromArray( array( array('', 2010, 2011, 2012), array('Q1', 12, 15, 21), array('Q2', 56, 73, 86), array('Q3', 52, 61, 69), array('Q4', 30, 32, 0), ) ); */ // Labels for dataseries. Direction ===> for ($i = $dataStartCol; $i <= $seriesTotalCols; $i++) { array_push($dataSeriesLabels, new PHPExcel_Chart_DataSeriesValues('String', $worksheetName . '!$' . $this->alphabets[$dataStartCol] . '$' . $i, NULL, 1)); } for ($j = $dataStartRow; $j <= $totalRows; $j++) { array_push($xAxisTickValues, new PHPExcel_Chart_DataSeriesValues('String', $worksheetName . '!$' . $this->alphabets[$j] . '$' . $dataStartCol, NULL, 1)); } for ($i = $dataStart; $i <= $dataEnd; $i++) { array_push($dataSeriesValues, new PHPExcel_Chart_DataSeriesValues('Number', $worksheetName . '!$' . $this->alphabets[$col + 1] . '$' . $i . ':$' . $this->alphabets[$col + 3] . '$' . $i, NULL, 3)); } $series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataSeriesLabels, $xAxisTickValues, $dataSeriesValues); // new PHPExcel_Chart_DataSeries($plotType, $plotGrouping, $plotOrder, $plotLabel, $plotCategory, $plotValues, $smoothLine, $plotStyle); $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); // Set the series in the plot area $plotArea = new PHPExcel_Chart_PlotArea(NULL, array($series)); // Set the chart legend if ($type == 3) { $legend = null; } else { $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); } //$legend-> //$legend->setOverlay(true); $chrtTitle = ' '; $title = new PHPExcel_Chart_Title($chrtTitle); $yAxisLabel = new PHPExcel_Chart_Title(''); $chart = new PHPExcel_Chart(null, $title, $legend, $plotArea, true, 0, NULL, $yAxisLabel); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition($this->alphabets[$col] . $row); $chart->setBottomRightPosition($this->alphabets[$col + $width] . ($row + $chartHeight)); // // Add the chart to the worksheet $this->objWorksheet->addChart($chart); }
/** * Write drawings to XML format * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Chart $pChart * @param int $pRelationId * @throws PHPExcel_Writer_Exception */ public function writeChart(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Chart $pChart = null, $pRelationId = -1) { $tl = $pChart->getTopLeftPosition(); $tl['colRow'] = PHPExcel_Cell::coordinateFromString($tl['cell']); $br = $pChart->getBottomRightPosition(); $br['colRow'] = PHPExcel_Cell::coordinateFromString($br['cell']); $objWriter->startElement('xdr:twoCellAnchor'); $objWriter->startElement('xdr:from'); $objWriter->writeElement('xdr:col', PHPExcel_Cell::columnIndexFromString($tl['colRow'][0]) - 1); $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($tl['xOffset'])); $objWriter->writeElement('xdr:row', $tl['colRow'][1] - 1); $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($tl['yOffset'])); $objWriter->endElement(); $objWriter->startElement('xdr:to'); $objWriter->writeElement('xdr:col', PHPExcel_Cell::columnIndexFromString($br['colRow'][0]) - 1); $objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($br['xOffset'])); $objWriter->writeElement('xdr:row', $br['colRow'][1] - 1); $objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($br['yOffset'])); $objWriter->endElement(); $objWriter->startElement('xdr:graphicFrame'); $objWriter->writeAttribute('macro', ''); $objWriter->startElement('xdr:nvGraphicFramePr'); $objWriter->startElement('xdr:cNvPr'); $objWriter->writeAttribute('name', 'Chart ' . $pRelationId); $objWriter->writeAttribute('id', 1025 * $pRelationId); $objWriter->endElement(); $objWriter->startElement('xdr:cNvGraphicFramePr'); $objWriter->startElement('a:graphicFrameLocks'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->startElement('xdr:xfrm'); $objWriter->startElement('a:off'); $objWriter->writeAttribute('x', '0'); $objWriter->writeAttribute('y', '0'); $objWriter->endElement(); $objWriter->startElement('a:ext'); $objWriter->writeAttribute('cx', '0'); $objWriter->writeAttribute('cy', '0'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->startElement('a:graphic'); $objWriter->startElement('a:graphicData'); $objWriter->writeAttribute('uri', 'http://schemas.openxmlformats.org/drawingml/2006/chart'); $objWriter->startElement('c:chart'); $objWriter->writeAttribute('xmlns:c', 'http://schemas.openxmlformats.org/drawingml/2006/chart'); $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'); $objWriter->writeAttribute('r:id', 'rId' . $pRelationId); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->startElement('xdr:clientData'); $objWriter->endElement(); $objWriter->endElement(); }
/** * Write charts to XML format * * @param PHPExcel_Chart $pChart * @return string XML Output * @throws PHPExcel_Writer_Exception */ public function writeChart(PHPExcel_Chart $pChart = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY); } // Ensure that data series values are up-to-date before we save $pChart->refresh(); // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // c:chartSpace $objWriter->startElement('c:chartSpace'); $objWriter->writeAttribute('xmlns:c', 'http://schemas.openxmlformats.org/drawingml/2006/chart'); $objWriter->writeAttribute('xmlns:a', 'http://schemas.openxmlformats.org/drawingml/2006/main'); $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'); $objWriter->startElement('c:date1904'); $objWriter->writeAttribute('val', 0); $objWriter->endElement(); $objWriter->startElement('c:lang'); $objWriter->writeAttribute('val', "en-GB"); $objWriter->endElement(); $objWriter->startElement('c:roundedCorners'); $objWriter->writeAttribute('val', 0); $objWriter->endElement(); $this->_writeAlternateContent($objWriter); $objWriter->startElement('c:chart'); $this->_writeTitle($pChart->getTitle(), $objWriter); $objWriter->startElement('c:autoTitleDeleted'); $objWriter->writeAttribute('val', 0); $objWriter->endElement(); $this->_writePlotArea($pChart->getPlotArea(), $pChart->getXAxisLabel(), $pChart->getYAxisLabel(), $objWriter, $pChart->getWorksheet()); $this->_writeLegend($pChart->getLegend(), $objWriter); $objWriter->startElement('c:plotVisOnly'); $objWriter->writeAttribute('val', 1); $objWriter->endElement(); $objWriter->startElement('c:dispBlanksAs'); $objWriter->writeAttribute('val', "gap"); $objWriter->endElement(); $objWriter->startElement('c:showDLblsOverMax'); $objWriter->writeAttribute('val', 0); $objWriter->endElement(); $objWriter->endElement(); $this->_writePrintSettings($objWriter); $objWriter->endElement(); // Return return $objWriter->getData(); }
// Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $dataSeriesValues3 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$13', null, 12)); // Build the dataseries $series3 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_AREACHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels3, null, $dataSeriesValues3); // Set the series in the plot area $plotarea = new PHPExcel_Chart_PlotArea(null, array($series1, $series2, $series3)); // Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false); $title = new PHPExcel_Chart_Title('Average Weather Chart for Crete'); // Create the chart $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, null, null); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition('F2'); $chart->setBottomRightPosition('O16'); // Add the chart to the worksheet $objWorksheet->addChart($chart); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(true); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done echo date('H:i:s'), " Done writing file", EOL;
// Data values // Data Marker $dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', null, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', null, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', null, 4)); // Build the dataseries $series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues); // Set additional dataseries parameters // Make it a horizontal bar rather than a vertical column graph $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR); // Set the series in the plot area $plotarea = new PHPExcel_Chart_PlotArea(null, array($series)); // Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false); $title = new PHPExcel_Chart_Title('Test Chart'); $yAxisLabel = new PHPExcel_Chart_Title('Value ($k)'); // Create the chart $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, null, $yAxisLabel); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition('A7'); $chart->setBottomRightPosition('H20'); // Add the chart to the worksheet $objWorksheet->addChart($chart); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done echo date('H:i:s'), " Done writing file", EOL;
public function generate() { $input = Request::all(); $counter = 1; $reportType = $input['report_type']; $salesCounter = 0; $counterClientBreakdown = 0; $counterClientLabel = 0; $counterClientData = 0; if ($reportType == "sales") { $monthStart = $input['select_monthFrom']; $monthEnd = $input['select_monthTo']; $yearStart = $input['yearFrom']; $yearEnd = $input['yearTo']; $monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10)); $monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10)); $results = DB::select("SELECT SUM(total_amount) as 'total' FROM sales_invoices si\n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'"); $resultsClient = DB::select("SELECT name, SUM(total_amount) as 'total' FROM clients c\n JOIN sales_invoices si ON si.client_id = c.id\n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' GROUP BY c.id"); $resultsSales = DB::select("SELECT username, SUM(total_amount) as 'total' FROM users u\n JOIN clients c ON c.user_id = u.id\n JOIN sales_invoices si ON si.client_id = c.id \n WHERE MONTH(si.date) >= '{$monthStart}' AND MONTH(si.date) <= '{$monthEnd}'\n AND YEAR(si.date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' GROUP BY u.id"); Excel::create('Sales Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $resultsClient, $resultsSales, $counter, $monthStartName, $monthEndName, $salesCounter, $counterClientBreakdown, $counterClientLabel, $counterClientData, $yearStart, $yearEnd) { $excel->sheet('Data', function ($sheet) use($results, $resultsClient, $resultsSales, $counter, $monthEndName, $monthStartName, $salesCounter, $counterClientBreakdown, $counterClientLabel, $counterClientData, $yearStart, $yearEnd) { $sheet->row($counter, array('Sales Report')); $counter++; $sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd)); $counter++; $sheet->row($counter, array('Total Sales: ', 'P' . $results[0]->total)); $counter++; $counter++; $sheet->row($counter, array('Sale Employee Breakdown:')); //styling for 'Sales Employee Breakdown' $sheet->cells('A5', function ($cells) { $cells->setFontSize(14); $cells->setFontWeight('bold'); }); $counter++; $sheet->row($counter, array('Username', 'Total Amount')); $counter++; foreach ($resultsSales as $sales) { $sheet->row($counter, array($sales->username, $sales->total)); $counter++; } $salesCounter = $counter - 1; $counter += 10; $sheet->row($counter, array('Client Breakdown: ')); $counterClientBreakdown = $counter; $counter++; $sheet->row($counter, array('Client Name', 'Total Amount')); $counterClientLabel = $counter; //$counter++; $counterClientData = $counter + 1; foreach ($resultsClient as $client) { $counter++; $sheet->row($counter, array($client->name, $client->total)); //$counter++; } //styling 'Sales Report' -> bold $sheet->mergeCells('A1:B1'); $sheet->cells('A1', function ($cells) { $cells->setFontSize(16); $cells->setFontWeight('bold'); }); //styling for total sales $sheet->cells('A3:B3', function ($cells) { $cells->setFontSize(14); $cells->setFontWeight('bold'); }); //styling for 'Client Breakdown' $sheet->cells('A' . $counterClientBreakdown, function ($cells) { $cells->setFontSize(14); $cells->setFontWeight('bold'); }); //create Sales Employee chart if (Count($resultsSales) > 0) { //Data Series Label $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$6', NULL, 1)); $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$7:$A$' . $salesCounter, NULL, count($resultsSales))); $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$7:$B$' . $salesCounter, NULL, count($resultsSales))); $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv); $pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds)); $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title = new \PHPExcel_Chart_Title('Sales by Employee'); $chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL); $chart->setTopLeftPosition('E5'); $chart->setBottomRightPosition('L16'); $sheet->addChart($chart); } //create Client Breakdown chart if (Count($resultsClient) > 0) { //Data Series Label $dsl2 = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$' . $counterClientLabel, NULL, 1)); $xal2 = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$' . $counterClientData . ':$A$' . $counter, NULL, count($resultsClient))); $dsv2 = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$' . $counterClientData . ':$B$' . $counter, NULL, count($resultsClient))); $ds2 = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv2) - 1), $dsl2, $xal2, $dsv2); $pa2 = new \PHPExcel_Chart_PlotArea(NULL, array($ds2)); $legend2 = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title2 = new \PHPExcel_Chart_Title('Sales by Client'); $chart = new \PHPExcel_Chart('chart2', $title2, $legend2, $pa2, true, 0, NULL, NULL); $chart->setTopLeftPosition('E19'); $chart->setBottomRightPosition('L30'); $sheet->addChart($chart); } }); })->export('xlsx'); } else { if ($reportType == "collection") { $monthStart = $input['select_monthFrom']; $monthEnd = $input['select_monthTo']; $yearStart = $input['yearFrom']; $yearEnd = $input['yearTo']; $monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10)); $monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10)); $counter = 1; $results = DB::select("SELECT sum(total_amount) as 'total', count(*) as 'numCount' FROM sales_invoices si\n WHERE (si.status = 'delivered' OR si.status = 'overdue')\n AND MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'"); $resultsCollected = DB::select("SELECT sum(total_amount) as 'total', count(*) as 'numCount' FROM sales_invoices si\n WHERE si.status = 'collected'\n AND MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}'"); $resultsInvoices = DB::select("SELECT name, si_no, date, due_date, total_amount, si.status FROM sales_invoices si\n JOIN clients c ON si.client_id = c.id\n WHERE MONTH(si.due_date) >= '{$monthStart}' AND MONTH(si.due_date) <= '{$monthEnd}'\n AND YEAR(si.due_date) >= '{$yearStart}' AND YEAR(si.date) <= '{$yearEnd}' ORDER BY name, si.status"); Excel::create('Collection Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $monthStartName, $monthEndName, $yearStart, $yearEnd, $resultsCollected, $resultsInvoices) { $excel->sheet('Data', function ($sheet) use($results, $counter, $monthStartName, $monthEndName, $yearStart, $yearEnd, $resultsCollected, $resultsInvoices) { $sheet->row($counter, array('Collection Report')); $counter++; $sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd)); $counter++; $sheet->row($counter, array('Total Amount Collected: ', $resultsCollected[0]->total)); $counter++; $sheet->row($counter, array('Total Collectibles: ', $results[0]->total)); $counter += 2; $sheet->row($counter, array('List of Collectibles: ')); $counter++; $sheet->row($counter, array('Client Name', 'Sales Invoice Number', 'Date', 'Due Date', 'Total Amount', 'Status')); $counter++; foreach ($resultsInvoices as $result) { $sheet->row($counter, array($result->name, $result->si_no, $result->date, $result->due_date, $result->total_amount, $result->status)); $counter++; } // foreach($results as $result) // { // $sheet->row($counter, array('Total Collectibles: ', $result->total)); // } //style //style for item name $sheet->mergeCells('A1:B1'); $sheet->cells('A1', function ($cells) { $cells->setFontSize(14); $cells->setFontWeight('bold'); }); //style for total amount collected $sheet->cells('A3:B3', function ($cells) { $cells->setFontSize(12); $cells->setFontWeight('bold'); }); //style for total collectibles $sheet->cells('A4:B4', function ($cells) { $cells->setFontSize(12); $cells->setFontWeight('bold'); }); }); })->export('xlsx'); } else { if ($reportType == "item") { $itemID = $input['item']; $item = Item::find($itemID); $itemName = $item->name; $monthStart = $input['select_monthFrom']; $monthEnd = $input['select_monthTo']; $yearStart = $input['yearFrom']; $yearEnd = $input['yearTo']; $monthStartName = date("F", mktime(0, 0, 0, $monthStart, 10)); $monthEndName = date("F", mktime(0, 0, 0, $monthEnd, 10)); $counterLabel = 0; $counterData = 0; $results = DB::select("SELECT name, supplier_id FROM price_logs pl\n JOIN suppliers s ON pl.supplier_id = s.id \n WHERE item_id = '{$itemID}' AND MONTH(pl.date) >= '{$monthStart}' \n AND MONTH(pl.date) <= '{$monthEnd}' AND YEAR(pl.date) >= '{$yearStart}' AND YEAR(pl.date) <= '{$yearEnd}'\n GROUP BY pl.supplier_id"); $itemResults = DB::select("SELECT name, sum(quantity) as 'totalSold', sum(total_price) as 'totalAmount' FROM invoice_items ii\n JOIN items i ON ii.item_id = i.id\n WHERE ii.item_id = '{$itemID}'"); Excel::create('Item Report-' . $itemName . ' ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $itemID, $monthStart, $monthStartName, $monthEnd, $monthEndName, $yearStart, $yearEnd, $itemResults, $itemName, $counterLabel, $counterData) { $excel->sheet('Data', function ($sheet) use($results, $counter, $itemID, $monthStart, $monthStartName, $monthEnd, $monthEndName, $yearStart, $yearEnd, $itemResults, $itemName, $counterLabel, $counterData) { $sheet->row($counter, array('Item Report')); //style for Item Report Title $sheet->cells('A1', function ($cells) { $cells->setFontSize(16); $cells->setFontWeight('bold'); }); $counter++; $sheet->row($counter, array('For ' . $monthStartName . ' ' . $yearStart . ' To ' . $monthEndName . ' ' . $yearEnd)); $counter++; $sheet->row($counter, array('Item Name: ' . $itemName)); //style for item name $sheet->mergeCells('A1:B1'); $sheet->cells('A' . $counter, function ($cells) { $cells->setFontSize(14); $cells->setFontWeight('bold'); }); $counter++; $sheet->row($counter, array('Total Quantity Sold: ', $itemResults[0]->totalSold)); //style for quantity sold $sheet->cells('A' . $counter . ':B' . $counter, function ($cells) { $cells->setFontSize(12); $cells->setFontWeight('bold'); }); $counter++; $sheet->row($counter, array('Total Revenue Generated: ', 'P' . $itemResults[0]->totalAmount)); //style for revenue generated $sheet->cells('A' . $counter . ':B' . $counter, function ($cells) { $cells->setFontSize(12); $cells->setFontWeight('bold'); }); $counter += 2; foreach ($results as $result) { $sheet->row($counter, array($result->name)); //style for quantity sold $sheet->cells('A' . $counter, function ($cells) { $cells->setFontWeight('bold'); }); $counter++; $sheet->row($counter, array('Date', 'Unit Price')); $counterLabel = $counter; $counterData = $counter + 1; $logs = DB::select("SELECT date, price FROM price_logs pl \n WHERE item_id = '{$itemID}' AND supplier_id = '{$result->supplier_id}'\n AND MONTH(pl.date) >= '{$monthStart}' \n AND MONTH(pl.date) <= '{$monthEnd}' AND YEAR(pl.date) >= '{$yearStart}' AND YEAR(pl.date) <= '{$yearEnd}'"); foreach ($logs as $log) { $counter++; $sheet->row($counter, array($log->date, $log->price)); } //Create chart if (count($logs) > 0) { //Data Series Label $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$' . $counterLabel, NULL, 1)); $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$' . $counterData . ':$A$' . $counter, NULL, count($logs))); $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$' . $counterData . ':$B$' . $counter, NULL, count($logs))); $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv); $pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds)); $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title = new \PHPExcel_Chart_Title('Price History'); $chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL); $counter += 5; $chart->setTopLeftPosition('E' . $counterLabel); $chart->setBottomRightPosition('K' . $counter); $sheet->addChart($chart); } $counter += 2; } }); })->export('xlsx'); } else { if ($reportType == "client") { //$client = Client::all(); $clientid = $input['client']; $clientName = Client::find($clientid)->name; $results = DB::select("SELECT c.name AS 'clientName', i.name, SUM(quantity) AS 'total' FROM invoice_items ii\n JOIN items i on ii.item_id = i.id \n JOIN sales_invoices si ON ii.sales_invoice_id = si.id\n JOIN clients c ON si.client_id = c.id\n WHERE c.id = '{$clientid}'\n GROUP BY item_id ORDER BY sum(quantity) DESC LIMIT 3"); //$client = DB::table('clients')->get(); Excel::create('Client Report ' . \Carbon\Carbon::today()->format('m-d-y'), function ($excel) use($results, $counter, $clientName) { $excel->sheet('Data', function ($sheet) use($results, $counter, $clientName) { $sheet->row($counter, array('Client Report')); $counter++; //$sheet->row($counter, array('Client Name', $results[0]->clientName)); $sheet->row($counter, array('Client Name', $clientName)); $counter++; $counter++; //add space $sheet->row($counter, array('Most Bought Items:')); $counter++; $sheet->row($counter, array('Item Name', 'Total Quantity')); $counter++; foreach ($results as $result) { $sheet->row($counter, array($result->name, $result->total)); $counter++; } //Add the styling $sheet->mergeCells('A1:B1'); $sheet->cells('A1', function ($cells) { $cells->setFontSize(16); $cells->setFontWeight('bold'); }); $sheet->cells('A2:B2', function ($cells) { $cells->setFontSize(16); }); $sheet->cells('A4', function ($cells) { $cells->setFontWeight('bold'); }); //Create chart if (count($results) > 0) { //Data Series Label $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$B$5', NULL, 1)); $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$A$6:$A$' . $counter, NULL, 3)); $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$B$6:$B$' . $counter, NULL, 3)); $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv); $pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds)); $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title = new \PHPExcel_Chart_Title('Most Bought'); $chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL); $chart->setTopLeftPosition('A10'); $chart->setBottomRightPosition('C25'); $sheet->addChart($chart); } }); })->export('xlsx'); } } } } }
public function exportAnualFile() { $this->load->library('excel'); //get Info Evaluados $colaboradores = $this->evaluacion_model->getEvaluados(); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Portal de Evaluación Advanzer de México")->setLastModifiedBy("Portal de Evaluación")->setTitle("Reporte Anual de Evaluación")->setSubject("Reporte Anual de Evaluación")->setDescription("Concentrado de resultados de evaluación anual y proyectos durante el año en cuestión"); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objSheet = $objPHPExcel->getActiveSheet(0); $objSheet->setTitle('Junta_Anual'); $objSheet->getStyle('A1:Q1')->getFont()->setBold(true)->setName('Verdana')->setSize(11)->getColor()->setRGB('FFFFFF'); $objSheet->getStyle('A1:Q1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objSheet->getStyle('A1:Q1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objSheet->getStyle('A1:Q1')->getFill('')->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000A75'))); $objSheet->getRowDimension(1)->setRowHeight(50); // write header $objSheet->getCell('A1')->setValue('No.'); $objSheet->getCell('B1')->setValue('Colaborador'); $objSheet->getCell('C1')->setValue('Fecha de Ingreso'); $objSheet->getCell('D1')->setValue('Track'); $objSheet->getCell('E1')->setValue('Posición'); $objSheet->getCell('F1')->setValue('Evaluadores'); $objSheet->getCell('G1')->setValue('Competencias'); $objSheet->getCell('H1')->setValue('Responsabilidades'); $objSheet->getCell('I1')->setValue('Promedio'); $objSheet->getCell('J1')->setValue('Rating 2012'); $objSheet->getCell('K1')->setValue('Rating 2013'); $objSheet->getCell('L1')->setValue('Rating 2014'); $objSheet->getCell('M1')->setValue('Rating 2015'); $objSheet->getCell('N1')->setValue('Encargado de Feedback'); $objSheet->getCell('O1')->setValue('Comentarios de Desempeño'); $objSheet->getCell('P1')->setValue('Observaciones de la Junta'); $objSheet->getCell('Q1')->setValue('Acciones a Tomar'); $column = 1; foreach ($colaboradores as $colaborador) { $column++; $total_c = 0; $total_r = 0; if ($colaborador->nivel_posicion <= 5) { $total_c = ($colaborador->autoevaluacion + $colaborador->tres60 + $colaborador->competencias) / 3; } else { $total_c = ($colaborador->autoevaluacion + $colaborador->competencias) / 2; } if (isset($colaborador->proyectos)) { $total_r = ($colaborador->responsabilidades + $colaborador->proyectos) / 2; } else { $total_r = $colaborador->responsabilidades; } ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2012')) ? $colaborador->rating_2012 = $res->rating : ($colaborador->rating_2012 = null); ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2013')) ? $colaborador->rating_2013 = $res->rating : ($colaborador->rating_2013 = null); ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2014')) ? $colaborador->rating_2014 = $res->rating : ($colaborador->rating_2014 = null); $evaluadores = ""; $comentarios = ""; foreach ($colaborador->evaluadores as $evaluador) { $evaluadores .= $evaluador->nombre . "\n"; $comentarios .= $evaluador->comentarios . "\n"; } if (isset($colaborador->evaluadoresProyecto)) { foreach ($colaborador->evaluadoresProyecto as $evaluador) { $evaluadores .= $evaluador->nombre . "\n"; $comentarios .= $evaluador->comentarios . "\n"; } } if ($colaborador->nivel_posicion <= 5) { $cont = 0; if (isset($colaborador->evaluadores360)) { foreach ($colaborador->evaluadores360 as $evaluador) { $evaluadores .= $evaluador->nombre . "\n"; $comentarios .= $evaluador->comentarios . "\n"; $cont++; } } } $objSheet->getCell('A' . $column)->setValue($colaborador->nomina); $objSheet->getCell('B' . $column)->setValue($colaborador->nombre); $objSheet->getCell('C' . $column)->setValue($colaborador->fecha_ingreso); $objSheet->getCell('D' . $column)->setValue($colaborador->track); $objSheet->getCell('E' . $column)->setValue($colaborador->posicion); $objSheet->getCell('F' . $column)->setValue($evaluadores); $objSheet->getCell('G' . $column)->setValue(number_format(floor($total_c * 100) / 100, 2)); $objSheet->getCell('H' . $column)->setValue(number_format(floor($total_r * 100) / 100, 2)); $objSheet->getCell('I' . $column)->setValue(number_format(floor($colaborador->total * 100) / 100, 2)); $objSheet->getCell('J' . $column)->setValue($colaborador->rating_2012); $objSheet->getCell('K' . $column)->setValue($colaborador->rating_2013); $objSheet->getCell('L' . $column)->setValue($colaborador->rating_2014); $objSheet->getCell('O' . $column)->setValue($comentarios); $objSheet->getStyle('A' . $column . ':' . 'Q' . $column)->getAlignment()->setWrapText(true); $objSheet->getRowDimension($column)->setRowHeight(-1); } $objSheet->getStyle('C2:C' . $column)->getNumberFormat()->setFormatCode('yyyy-mm-dd'); $objSheet->getStyle('A2:Q' . $column)->getFont()->setSize(12); $objSheet->getStyle('G2:H' . $column)->getFont()->setSize(16); $objSheet->getStyle('I2:I' . $column)->getFont()->setSize(18); $objSheet->getStyle('M2:M' . $column)->getFont()->setSize(24); $objSheet->getStyle('A2:Q' . $column)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // create some borders // first, create the whole grid around the table $objSheet->getStyle('A1:Q' . $column)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // create medium border around the table $objSheet->getStyle('A1:Q' . $column)->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM); // create a medium border on the header line $objSheet->getStyle('A1:Q1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM); // autosize the columns $objSheet->getColumnDimension('A')->setAutoSize(true); $objSheet->getColumnDimension('B')->setAutoSize(true); $objSheet->getColumnDimension('C')->setAutoSize(true); $objSheet->getColumnDimension('D')->setAutoSize(true); $objSheet->getColumnDimension('E')->setAutoSize(true); $objSheet->getColumnDimension('F')->setWidth(40); $objSheet->getColumnDimension('G')->setAutoSize(true); $objSheet->getColumnDimension('H')->setAutoSize(true); $objSheet->getColumnDimension('I')->setAutoSize(true); $objSheet->getColumnDimension('J')->setAutoSize(true); $objSheet->getColumnDimension('K')->setAutoSize(true); $objSheet->getColumnDimension('L')->setAutoSize(true); $objSheet->getColumnDimension('M')->setAutoSize(true); $objSheet->getColumnDimension('N')->setWidth(20); $objSheet->getColumnDimension('O')->setWidth(40); $objSheet->getColumnDimension('P')->setWidth(40); $objSheet->getColumnDimension('Q')->setWidth(40); //tabla de resumen en hoja principal $column += 5; $objSheet->getStyle('G' . $column . ':J' . ($column + 5))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // create medium border around the table $objSheet->getStyle('G' . $column . ':J' . ($column + 5))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM); // create a medium border on the header line $objSheet->getStyle('G' . $column . ':J' . $column)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM); $objSheet->getStyle('G' . $column . ':J' . ($column + 6))->getFont()->setBold(true)->setName('Verdana')->setSize(10); $objSheet->getStyle('I' . ($column + 1) . ':I' . ($column + 6))->getFont()->setBold(true)->setName('Verdana')->setSize(10)->getColor()->setRGB('FF0000'); $objSheet->getCell('G' . $column)->setValue('Rating'); $objSheet->getCell('H' . $column)->setValue('Conteo'); $objSheet->getCell('I' . $column)->setValue('% Real'); $objSheet->getCell('J' . $column)->setValue('% Requerida'); $objSheet->getStyle('I' . ($column + 1) . ':J' . ($column + 6))->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00)); $letras = array('A', 'B', 'C', 'D', 'E', '.05', '.15', '.65', '.10', '.05'); for ($i = 1; $i <= 5; $i++) { $objSheet->getCell('G' . ($column + $i))->setValue($letras[$i - 1]); $objSheet->getCell('H' . ($column + $i))->setValue('=COUNTIF($M$2:$M$' . ($column - 5) . ',"' . $letras[$i - 1] . '")'); $objSheet->getCell('I' . ($column + $i))->setValue('=H' . ($column + $i) . '/$H$' . ($column + 6)); $objSheet->getCell('J' . ($column + $i))->setValue($letras[$i + 4]); } $column += 6; $objSheet->getCell('H' . $column)->setValue('=SUM(H' . ($column - 5) . ':H' . ($column - 1) . ')'); $objSheet->getCell('I' . $column)->setValue('=SUBTOTAL(9,I' . ($column - 5) . ':I' . ($column - 1) . ')'); $objSheet->getCell('J' . $column)->setValue('=SUBTOTAL(9,J' . ($column - 5) . ':J' . ($column - 1) . ')'); //line chart $objSheet = $objPHPExcel->createSheet(1); $objSheet->setTitle('Resumen'); //data series label $dsl = array(new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!I' . ($column - 6), null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!J' . ($column - 6), null, 1)); //X axis value label $xal = array(new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!$G$' . ($column - 5) . ':$G$' . ($column - 1), NULL, 5)); //data series values $dsv = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Junta_Anual!$I$' . ($column - 5) . ':$I$' . ($column - 1), NULL, 5), new PHPExcel_Chart_DataSeriesValues('Number', 'Junta_Anual!$J$' . ($column - 5) . ':$J$' . ($column - 1), NULL, 5)); //data series values $ds = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv, PHPExcel_Chart_DataSeries::STYLE_SMOOTHMARKER); $layout = new PHPExcel_Chart_Layout(); $ds->setSmoothLine(PHPExcel_Chart_DataSeries::STYLE_SMOOTHMARKER); $layout->setShowPercent(TRUE); //plot area & legend $pa = new PHPExcel_Chart_PlotArea($layout, array($ds)); $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); //title of chart $title = new PHPExcel_Chart_Title('Curva de Desempeño'); //chart $chart = new PHPExcel_Chart('chart1', $title, $legend, $pa, null, 0, NULL, NULL); $chart->setTopLeftPosition('B3'); $chart->setBottomRightPosition('K25'); $objSheet->addChart($chart); $file_name = "Reporte_Anual.xlsx"; $objWriter->setPreCalculateFormulas(true); $objWriter->setIncludeCharts(true); /*//output to browser header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$file_name.'"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output');*/ //output to server $objWriter->save(getcwd() . "/assets/docs/{$file_name}"); $this->load->library("email"); //configuracion para gmail $config = array('protocol' => 'smtp', 'smtp_host' => 'ssl://smtp.gmail.com', 'smtp_port' => 465, 'smtp_user' => '*****@*****.**', 'smtp_pass' => 'CapitalAdv16', 'mailtype' => 'html', 'charset' => 'utf-8', 'newline' => "\r\n"); $this->email->initialize($config); $this->email->clear(TRUE); $this->email->from('*****@*****.**', 'Portal de Evaluación Advanzer-Entuizer'); $this->email->to("*****@*****.**"); /*$this->email->to("*****@*****.**"); $this->email->bcc(array('*****@*****.**', '*****@*****.**')); $this->email->to("*****@*****.**");*/ $this->email->subject('Reporte de Evaluación para Junta Anual'); $this->email->message('<h2>Se ha generado el archivo de Reporte de Evaluación para la Junta Anual</h2><hr>'); $this->email->attach(base_url("assets/docs/{$file_name}")); if (!$this->email->send()) { var_dump($this->email->print_debugger()); } else { return "Se ha enviado reporte anual"; } }
$labels, $xLabels, $datas ) );//根据取得的东西做出一个图表的框架 $layout=new PHPExcel_Chart_Layout(); $layout->setShowVal(true); $areas=new PHPExcel_Chart_PlotArea($layout,$series); $legend=new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false); $title=new PHPExcel_Chart_Title("高一学生成绩分布"); $ytitle=new PHPExcel_Chart_Title("value(人数)"); $chart=new PHPExcel_Chart( 'line_chart', $title, $legend, $areas, true, false, null, $ytitle );//生成一个图标 $chart->setTopLeftPosition("A7")->setBottomRightPosition("K25");//给定图表所在表格中的位置 $objSheet->addChart($chart);//将chart添加到表格中 $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件 $objWriter->setIncludeCharts(true); //$objWriter->save($dir."/export_1.xls");//保存文件 browser_export('Excel2007','browser_chart.xlsx');//输出到浏览器
$line = 2; foreach ($rows as $row) { $sheet->setCellValue('A' . $line, $row['type_name']); $sheet->setCellValue('B' . $line, $row['number']); $line++; } //Autofit foreach (range('A', 'B') as $colD) { $sheet->getColumnDimension($colD)->setAutoSize(TRUE); } $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', $title . '!$A$1', null, 1)); $xAxisTickValues1 = array(new PHPExcel_Chart_DataSeriesValues('String', $title . '!$A$2:$A$' . $line, null, 4)); $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', $title . '!$B$2:$B$' . $line, null, 4)); $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues1, $dataSeriesValues1); $layout1 = new PHPExcel_Chart_Layout(); $layout1->setShowVal(TRUE); $layout1->setShowPercent(TRUE); $plotarea1 = new PHPExcel_Chart_PlotArea($layout1, array($series1)); $legend1 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false); $title1 = new PHPExcel_Chart_Title($chartTitle); $chart1 = new PHPExcel_Chart('chart1', $title1, $legend1, $plotarea1, true, 0, null, null); $chart1->setTopLeftPosition('E3'); $chart1->setBottomRightPosition('K20'); $sheet->addChart($chart1); $filename = 'excel-export.xlsx'; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($ci->excel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save('php://output');
$objDrawing->setCoordinates('L1'); $objDrawing->setWidth(300); $objDrawing->setHeight(38); $objDrawing->setOffsetX(20); $objDrawing->setOffsetY(25); $objDrawing->setWorksheet($ews2); $dsl2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$B$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$C$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$D$1', NULL, 1)); $xal2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$A$2:$A$' . $ca, NULL, $ca)); $dsv2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$B$2:$B$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$C$2:$C$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$D$2:$D$' . $ca, NULL, $ca)); $dsv2[0]->setPointMarker('none'); $dsv2[1]->setPointMarker('none'); $dsv2[2]->setPointMarker('none'); $ds2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv2) - 1), $dsl2, $xal2, $dsv2); $pa2 = new PHPExcel_Chart_PlotArea(NULL, array($ds2)); $legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $chart2 = new PHPExcel_Chart('chart2', NULL, $legend2, $pa2, true, 0, NULL, NULL); $chart2->setTopLeftPosition('F3'); $chart2->setBottomRightPosition('Z22'); $ews2->addChart($chart2); $ea->setActiveSheetIndex(0); $fileName = "temp_xls/PL_Data_" . uniqid() . ".xlsx"; $title = new PHPExcel_Chart_Title('PhoneLabs Data'); $writer = PHPExcel_IOFactory::createWriter($ea, 'Excel2007'); $writer->setIncludeCharts(true); $writer->save($fileName); if ($writer) { $mail = new PHPMailer(true); // the true param means it will throw exceptions on errors, which we need to catch $mail->IsSMTP(); // telling the class to use SMTP //$mail->SMTPDebug = 2; // enables SMTP debug information (for testing)
private function addChart2(\PHPExcel $ea, \PHPExcel_Worksheet $ews) { $title = new \PHPExcel_Chart_Title($ews->getTitle()); $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$D$1', NULL, 1), new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$E$1', NULL, 1)); $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$F$2:$F$91', NULL, 90)); $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$D$2:$D$91', NULL, 90), new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$E$2:$E$91', NULL, 90)); $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_LINECHART, \PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv); $pa = new \PHPExcel_Chart_PlotArea(NULL, array($ds)); // Set legend $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $chart = new \PHPExcel_Chart('Chart1', $title, $legend, $pa, true, 0, NULL, NULL); $chart->setTopLeftPosition('I1'); $chart->setBottomRightPosition('AA21'); $ews->addChart($chart); return $chart; }
// Data values // Data Marker $dataSeriesValues2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', NULL, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', NULL, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', NULL, 4)); // Build the dataseries $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels2, $xAxisTickValues2, $dataSeriesValues2); // Set additional dataseries parameters // Make it a vertical column rather than a horizontal bar graph $series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL); // Set the series in the plot area $plotarea2 = new PHPExcel_Chart_PlotArea(NULL, array($series2)); // Set the chart legend $legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title2 = new PHPExcel_Chart_Title('Test Column Chart'); $yAxisLabel2 = new PHPExcel_Chart_Title('Value ($k)'); // Create the chart $chart2 = new PHPExcel_Chart('chart2', $title2, $legend2, $plotarea2, true, 0, NULL, $yAxisLabel2); // Set the position where the chart should appear in the worksheet $chart2->setTopLeftPosition('I7'); $chart2->setBottomRightPosition('P20'); // Add the chart to the worksheet $objWorksheet->addChart($chart2); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done echo date('H:i:s'), " Done writing file", EOL;
/** * Add chart * * @param PHPExcel_Chart $pChart * @param int|null $iChartIndex Index where chart should go (0,1,..., or null for last) * @return PHPExcel_Chart */ public function addChart(PHPExcel_Chart $pChart = null, $iChartIndex = null) { $pChart->setWorksheet($this); if (is_null($iChartIndex)) { $this->_chartCollection[] = $pChart; } else { // Insert the chart at the requested index array_splice($this->_chartCollection, $iChartIndex, 0, array($pChart)); } return $pChart; }
* start 图表代码的编写 */ $labels = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', null, 1)); //取得绘制图表的标签 $xLabels = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$4', null, 3)); $datas = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$4', null, 3), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$4', null, 3), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$4', null, 3)); //取得绘图所需的数据 $series = array(new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($labels) - 1), $labels, $xLabels, $datas)); //根据取得的东西做出一个图表的框架 $layout = new PHPExcel_Chart_Layout(); $layout->setShowVal(true); $areas = new PHPExcel_Chart_PlotArea($layout, $series); $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, $layout, false); $title = new PHPExcel_Chart_Title("高一学生成绩分布"); $ytitle = new PHPExcel_Chart_Title("value(人数)"); $chart = new PHPExcel_Chart('line_chart', $title, $legend, $areas, true, false, null, $ytitle); //生成一个图标 $chart->setTopLeftPosition("A7")->setBottomRightPosition("K25"); //给定图表所在表格中的位置 $objSheet->addChart($chart); //将chart添加到表格中 /** * end 图表代码的编写 */ /** * start 输出到浏览器的操作 */ $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //生成excel文件 $objWriter->setIncludeCharts(true); //$objWriter->save($dir.'/file/demo3.xlsx'); //保存文件