本文整理汇总了PHP中PHPExcel_Chart::setBottomRightPosition方法的典型用法代码示例。如果您正苦于以下问题：PHP PHPExcel_Chart::setBottomRightPosition方法的具体用法？PHP PHPExcel_Chart::setBottomRightPosition怎么用？PHP PHPExcel_Chart::setBottomRightPosition使用的例子？那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类PHPExcel_Chart的用法示例。
在下文中一共展示了PHPExcel_Chart::setBottomRightPosition方法的14个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: getChart
/**
*
* @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;
}示例2: reporteEvaluacion
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);
//.........这里部分代码省略.........
示例3: getExportMintic
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++;
//.........这里部分代码省略.........
示例4: excel_create_chartsheet_for_faults
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;
}示例5: _makeChartFromSheetData
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);
}示例6: date
// 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;
echo 'File has been created in ', getcwd(), EOL;示例7: date
// 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;
echo 'File has been created in ', getcwd(), EOL;示例8: date
// 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;
echo 'File has been created in ', getcwd(), EOL;示例9: date
// Number of datapoints in series
// Data values
// Data Marker
$dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$13', null, 12), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$13', null, 12));
// Build the dataseries
$series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_RADARCHART, null, range(0, count($dataSeriesValues) - 1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues, null, PHPExcel_Chart_DataSeries::STYLE_MARKER);
// Set up a layout object for the Pie chart
$layout = new PHPExcel_Chart_Layout();
// Set the series in the plot area
$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
// Set the chart legend
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$title = new PHPExcel_Chart_Title('Test Radar Chart');
// 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('M15');
// 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;
echo 'File has been created in ', getcwd(), EOL;示例10: generate
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);
//.........这里部分代码省略.........
示例11: exportAnualFile
//.........这里部分代码省略.........
// 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' => 'notificaciones.ch@advanzer.com', 'smtp_pass' => 'CapitalAdv16', 'mailtype' => 'html', 'charset' => 'utf-8', 'newline' => "\r\n");
$this->email->initialize($config);
$this->email->clear(TRUE);
$this->email->from('notificaciones.ch@advanzer.com', 'Portal de Evaluación Advanzer-Entuizer');
$this->email->to("micaela.llano@advanzer.com");
/*$this->email->to("micaela.llano@advanzer.com");
$this->email->bcc(array('jesus.salas@advanzer.com', 'enrique.bernal@advanzer.com'));
$this->email->to("antonio.baez@advanzer.com");*/
$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";
}
}示例12: array
$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');示例13: array
$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)
$mail->SMTPAuth = true;
// enable SMTP authentication示例14: addChart2
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;
}