Created chart incompatible with MS Excel 2007

Topics: Developer Forum, User Forum
Jun 20, 2012 at 2:03 PM

I've used the latest develop branch Tests/33createchart example to create a phpexcel bar chart.

My xlsx file is being opened successfully with MS Excel 2010, but with MS Excel 2007, it complains about it and does recover the file and finally removes the drawing!!!

 

Have you tested phpexcel created charts in MS Excel 2007?

Coordinator
Jun 20, 2012 at 5:01 PM

Yes, but and while there are always discrepancies any Excel2007 written file should open in both Excel2007 and in Excel2010. It could be that Excel2010 is being more forgiving than Excel2007 about something in the generated file. But without specifics, you've given me a question that's almost impossible to answer.

Jun 20, 2012 at 5:26 PM

Thank you Mark, I will send you the code and a sample generated file on saturday.

Jun 23, 2012 at 7:36 AM
Edited Jun 23, 2012 at 7:43 AM

This is the code that the chart.xls file is generated by:

$objPHPExcel = new PHPExcel();
            $objPHPExcel->getProperties()->setCreator("AC&C Basamad Co.")
                ->setLastModifiedBy("AC&C Basamad Co.")
                ->setTitle("Office 2007 XLSX")
                ->setSubject("Office 2007 XLSX")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

            //        $objPHPExcel->setActiveSheetIndex( 0 )->setRightToLeft( true );
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'Media Type')
                ->setCellValue('B1', 'Success')
                ->setCellValue('C1', 'Notice')
                ->setCellValue('D1', 'Inprogress')
                ->setCellValue('E1', 'Error')
                ->setCellValue('F1', 'Failed')
                ->setCellValue('G1', 'Undefined');
            $i = 2;
            foreach( $list as $statuses ) {
                $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i, $statuses->media )
                    ->setCellValue('B'.$i, $statuses->success )
                    ->setCellValue('C'.$i, $statuses->notice )
                    ->setCellValue('D'.$i, $statuses->inprogress )
                    ->setCellValue('E'.$i, $statuses->error )
                    ->setCellValue('F'.$i, $statuses->failed )
                    ->setCellValue('G'.$i, $statuses->undefined );
                $i++;
            }
            $i = $i-1;
            //	Set the Labels for each dataset we want to plot
            $labels = array(
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', null, 1),	//	Success
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', null, 1),	//	Notice
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', null, 1),	//	Inprogress
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$1', null, 1),	//	Error
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$F$1', null, 1),	//	Failed
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$G$1', null, 1),	//	Undefined
            );
            //	Set the X-Axis Labels
            $categories = array(
                new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$'.$i, null, 2),	//	media types
            );
            //	Set the Data values for each dataset we want to plot
            $values = array(
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$'.$i, null, 2),
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$'.$i, null, 2),
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$'.$i, null, 2),
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$2:$E$'.$i, null, 2),
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$F$2:$F$'.$i, null, 2),
                new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$G$2:$G$'.$i, null, 2),
            );

            //	Build the dataseries
            $series = new PHPExcel_Chart_DataSeries(
                PHPExcel_Chart_DataSeries::TYPE_BARCHART,		// plotType
                PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,	// plotGrouping
                array(0, 1, 2, 3, 4, 5),						// plotOrder
                $labels,										// plotLabel
                $categories,									// plotCategory
                $values											// plotValues
            );
            //	Set additional dataseries parameters
            $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
            $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);

            $title = new PHPExcel_Chart_Title( $value  . ' Messages' );


            //	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('A7');
            $chart->setBottomRightPosition('H20');

            //	Add the chart to the worksheet
            $objPHPExcel->setActiveSheetIndex(0)->addChart($chart);

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="chart.xlsx"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->setIncludeCharts(TRUE);
            $objWriter->save('php://output');