Graph disapear read and write excel file using PHPExcel

Topics: Developer Forum, Project Management Forum, User Forum
Feb 7, 2013 at 5:58 AM
Hi I am using http://phpexcel.codeplex.com for read and write of excel file. I have graph in excel file when i read it and write new data graph disapear. This the example no 7 i.e 07reader.php but change include file from "05featuredemon.php" to "33chartcreate-bar.php" because i need graph

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/* Include PHPExcel_IOFactory /
require_once '../Classes/PHPExcel/IOFactory.php';


if (!file_exists("33chartcreate-bar.xlsx")) {
exit("Please run 33chartcreate-bar.php first." . EOL);
}

echo date('H:i:s') , " Load from Excel2007 file" , EOL;
$objPHPExcel = PHPExcel_IOFactory::load("05featuredemo.xlsx");

echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$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;
Coordinator
Feb 7, 2013 at 7:32 AM
You need to explicitly tell PHPExcel to read and to write charts
$objReader->setIncludeCharts(TRUE);
and
$objWriter->setIncludeCharts(TRUE);
as shown in the examples
Jan 12, 2015 at 3:04 PM
I have the same problem as well.
I read and write multiples sheets, but when I write first sheet, everything is fine, after sheet 2, the chart is gone from the first one and so on..

I am including charts in both reading and writing, what can be causing this?
Coordinator
Jan 12, 2015 at 4:46 PM
Explain what you mean by I read and write multiples sheets.... you don't read/write sheets, you read/write workbooks (PHPExcel objects)
Jan 12, 2015 at 6:05 PM
Edited Jan 12, 2015 at 6:38 PM
Sorry, my english isn't that great...
I have a excel file with 6 worksheets; I also have several functions that write tables and chart in them.
The thing is, when I write the first one, no problem. The charts and table are there.
When I write a second worksheet, the table and charts are there for the current worksheet only, but in the first one, just the table remains and the chart is gone.
Even though I'm using this:
$objReader->setIncludeCharts(TRUE);
Here is my code:

<?php
require_once'PHPExcel.php';
require_once 'ejemplo.php';
include 'PHPExcel/IOFactory.php';


$inputFileType = 'Excel5'; 
$inputFileName = 'Create_Sheet.xlsx'; 

$sheetname = 'Sistemas Operativos'; 


$objReader = PHPExcel_IOFactory::createReaderForFile($inputFileName); 
$objReader ->setReadDataOnly(true);


$objPHPExcel = $objReader->load($inputFileName); 
$objReader->setLoadSheetsOnly($sheetname); 
$objReader->setIncludeCharts(TRUE);

$sheet = $objPHPExcel->setActiveSheetIndexByName($sheetname);



$sheet ->setCellValue('A1', 'Informe de Inventario');       
        $sheet->getStyle('A1')->getFont()->setBold(false)->setSize(18); 
    
        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getDefaultRowDimension()->setRowHeight(-1);

        $sheet ->setCellValue('A2', 'Sistemas Operativos en la Unidad');
        $sheet->getStyle('A2')->getFont()->setBold(false)->setSize(11);
        $sheet ->setCellValue('A3', 'Sistemas Operativos');
        $sheet ->setCellValue('B3', 'U');
        $sheet ->setCellValue('C3', 'Bibliotecas ');    



$sheet->fromArray($array, null, 'A4');

$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);


$cell = count($array, COUNT_RECURSIVE);
$cell = (($cell/3)-3);
$num = ceil($cell);

$labels = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$4', null, 1),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$4', null, 1),
);
$categories = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
);
$values = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$B'.($num + 5), null, ($num - 1)),
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$5:$C'.($num + 5), null, ($num - 1)),
);
$series = new PHPExcel_Chart_DataSeries(
  PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
  PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
  array(0, 1),                                    // plotOrder
  $labels,                                        // plotLabel
  $categories,                                    // plotCategory
  $values                                         // plotValues
);


$title1 = new PHPExcel_Chart_Title('Total U');

$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',                                       // name
  $title1,                                           // title
  $legend,                                        // legend
  $plotarea,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);



/*-----------------------------Full Graph-------------------------------*/

/*-----------------------------Small Graph-------------------------------*/
$label = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$4', null, 1),
  
);
$category = array(
  new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5:$A'.($num + 5), null, ($num - 1)),
);
$values2 = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$B'.($num + 5), null, ($num - 1)),

);
$series2 = new PHPExcel_Chart_DataSeries(
  PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
  PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
  array(0),                                    // plotOrder
  $label,                                        // plotLabel
  $category,                                    // plotCategory
  $values2                                         // plotValues
);

//$title2 = new PHPExcel_Chart_Title('Total  ' .($dep));
$title2 = new PHPExcel_Chart_Title('Total  BIB');

$series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$plotarea2 = new PHPExcel_Chart_PlotArea(null, array($series2));
$legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$chart2 = new PHPExcel_Chart(
  'chart2',                                       // name
   $title2,                                           // title
  $legend2,                                        // legend
  $plotarea2,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);




/*-----------------------------Small Graph-------------------------------*/


$chart->setTopLeftPosition('A'.($num+10));
$chart2->setTopLeftPosition('K'.($num+10));
$chart->setBottomRightPosition('I30');
$chart2->setBottomRightPosition('X30');
$sheet->addChart($chart);
$sheet->addChart($chart2);
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$writer->setIncludeCharts(TRUE);
$writer->save('Create_Sheet.xlsx');

$objPHPExcel->disconnectWorksheets();


?>
Coordinator
Jan 12, 2015 at 7:13 PM
Edited Jan 12, 2015 at 7:18 PM
So you're loading a single sheet from an xls file, adding charts to that sheet, and saving as an xlsx.... I don't see anything in this code to indicate that there should be more than one worksheet in your saved file.

However, when creating your charts you make reference to a worksheet called Worksheet, e.g
$values2 = array(
  new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$B'.($num + 5), null, ($num - 1)),

);
but the only worksheet that you're loading is called Sistemas Operativos, so that chart won't be able to access any data on the non-existent worksheet
Jan 12, 2015 at 7:17 PM
Edited Jan 12, 2015 at 7:20 PM
This is for one sheet only, the others functions are very similar, (that's why I only posted one) the thing is that it erases the chart of the previous sheet.

Each time I only need to access one sheet to write the data and charts.
Like I said before, the tables that were written before stay in the file, it is only the previous charts that are erased.
Coordinator
Jan 12, 2015 at 7:32 PM
So you're loading a worksheet each time, adding charts, then saving the file? You're not saving to the same filename each time are you?
Jan 12, 2015 at 7:34 PM
Edited Jan 12, 2015 at 7:42 PM
Yes! That's how I have to do it actually.
But, even if it was the same filename shouldn't erase the work done before.. is it?

(btw thank you so much for replying)


edit. tried with different file names, same thing.
Coordinator
Jan 12, 2015 at 7:50 PM
Edited Jan 12, 2015 at 7:51 PM
If you save several files with the same filename, then each save will overwrite the previous file with that filename.

If you save with different filenames, then it won't overwrite, because it's a different filename.
Jan 12, 2015 at 7:53 PM
Ok, I know it will overwrite the existing file, but the only thing that it's missing it's the charts, the data written before is fine.
Jan 12, 2015 at 7:56 PM
Edited Jan 12, 2015 at 7:56 PM
This is what I'm doing:

1- Open the file-> write a single sheet (charts and tables)->save
2- same thing, but now in the "second save", only charts are missing


Using same and different filenames
Apr 15, 2015 at 10:54 AM
You can use below code to read and write graph on excel :-

error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
require_once '../Classes/PHPExcel/IOFactory.php';
require_once '../Classes/PHPExcel.php';
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');

/Enable chart read on excel/
$excel2->setIncludeCharts(TRUE);
/Enable chart read on excel/

$excel2 = $excel2->load('excelname.xlsx'); // Empty Sheet

/update cell data if you required /
$excel2->getActiveSheet()->setCellValue('B6', '2');
$excel2->getActiveSheet()->setCellValue('B7', '1');
$excel2->getActiveSheet()->setCellValue('B8', '3');
/-----------------------------/

$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');
/Enable chart write on excel/
$objWriter->setIncludeCharts(TRUE);
/Enable chart write on excel/
$objWriter->save('excelout.xlsx');