Is it possible to create a bar and line graph chart , or is it possible to set graph background to transparent

Topics: Developer Forum
Feb 18, 2013 at 7:56 PM
Is it possible to create a graph with both bar and line data with two Y axis like the picture below in PHPExcel.
Image

Also is it possible to set the graph backround to transparent?

Thanks
Coordinator
Feb 19, 2013 at 12:39 PM
I had to make a minor tweak to the PHPExcel code to get it working correctly, just pushed it to github; together with 33chartcreate-composite-chart.php in /Examples.
    $objPHPExcel = new PHPExcel();
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $objWorksheet->fromArray(
        array(
            array('',       'Rainfall (mm)',    'Temperature (°F)', 'Humidity (%)'),
            array('Jan',        78,                 52,                 61),
            array('Feb',        64,                 54,                 62),
            array('Mar',        62,                 57,                 63),
            array('Apr',        21,                 62,                 59),
            array('May',        11,                 75,                 60),
            array('Jun',        1,                  75,                 57),
            array('Jul',        1,                  79,                 56),
            array('Aug',        1,                  79,                 59),
            array('Sep',        10,                 75,                 60),
            array('Oct',        40,                 68,                 63),
            array('Nov',        69,                 62,                 64),
            array('Dec',        89,                 57,                 66),
        )
    );
    
    
    //  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
    $dataseriesLabels1 = array(
        new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1),   //  Temperature
    );
    $dataseriesLabels2 = array(
        new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1),   //  Rainfall
    );
    $dataseriesLabels3 = array(
        new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1),   //  Humidity
    );
    
    //  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', 'Worksheet!$A$2:$A$13', NULL, 12),    //  Jan to Dec
    );
    
    
    //  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
    $dataSeriesValues1 = array(
        new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$13', NULL, 12),
    );
    
    //  Build the dataseries
    $series1 = new PHPExcel_Chart_DataSeries(
        PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
        PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
        range(0, count($dataSeriesValues1)-1),          // plotOrder
        $dataseriesLabels1,                             // plotLabel
        $xAxisTickValues,                               // plotCategory
        $dataSeriesValues1                              // plotValues
    );
    //  Set additional dataseries parameters
    //      Make it a vertical column rather than a horizontal bar graph
    $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
    
    
    //  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
    $dataSeriesValues2 = array(
        new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$13', NULL, 12),
    );
    
    //  Build the dataseries
    $series2 = new PHPExcel_Chart_DataSeries(
        PHPExcel_Chart_DataSeries::TYPE_LINECHART,      // plotType
        PHPExcel_Chart_DataSeries::GROUPING_STANDARD,   // plotGrouping
        range(0, count($dataSeriesValues2)-1),          // plotOrder
        $dataseriesLabels2,                             // plotLabel
        NULL,                                           // plotCategory
        $dataSeriesValues2                              // plotValues
    );
    
    
    //  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
    $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,      // plotType
        PHPExcel_Chart_DataSeries::GROUPING_STANDARD,   // plotGrouping
        range(0, count($dataSeriesValues2)-1),          // plotOrder
        $dataseriesLabels3,                             // plotLabel
        NULL,                                           // plotCategory
        $dataSeriesValues3                              // plotValues
    );
    
    
    //  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',       // name
        $title,         // title
        $legend,        // legend
        $plotarea,      // plotArea
        true,           // plotVisibleOnly
        0,              // displayBlanksAs
        NULL,           // xAxisLabel
        NULL            // yAxisLabel
    );
    
    //  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);
Feb 7, 2014 at 9:37 AM
Edited Feb 7, 2014 at 9:54 AM
Hi! Is there a possibility:

1) to add 2nd Y-axis for the data with different scale?
2) make X-axis labels vertically aligned?

Image

Regards
Evghenii