KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Excel problem memory #247

Open arthur opened this topic on on Apr 16, 2018 - 22 comments

arthur commented on Apr 16, 2018

Hi,

First thank you for this great tool. I hesitate to take a pro version for my enterprise.

I succeed to read excel file to make nice charts, however I got a problem : When I load the index page, it's very slow. And sometimes I got this error -> "Fatal error: Allowed memory size of exhausted " whereas I got just 2 tables and a chart...

What could be the problem (I'm working locally with MAMP on mac) ?

Thanks a lot

David Winterburn commented on Apr 17, 2018

Hi Arthur,

Would you please show us your code as well as the estimation of your data size? We might be able to help you optimize the process to avoid memory problem. Thanks!

arthur commented on Apr 17, 2018

Hi,

Thanks for your answer. So my excel file is 999 Ko (621 rows / 33 columns).

Here is my code :

<?php
require_once "../koolreport/autoload.php";
use \koolreport\processes\Group;
use \koolreport\processes\Sort;
use \koolreport\processes\Limit;
use \koolreport\processes\CopyColumn;
use \koolreport\processes\OnlyColumn;
    error_reporting(E_ALL);
ini_set('display_errors', 'on');

class SalesByCustomer extends \koolreport\KoolReport
{
  
  function settings()
  {
    return array(
      "dataSources"=>array(
        "sales"=>array(
            "class"=>'\koolreport\excel\ExcelDataSource',
            "filePath"=>"../databases/tableau.xlsx",
            "charset"=>"utf8",
            "firstRowData"=>false,
        ),        
      )
    );
  }
  
  function setup()
  {
        $this->src('sales')
        ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Controlée"
        )))
        ->pipe(new Sort(array(
            "Marge_FAS_Controlée"=>"desc"
        )))
        ->pipe(new Limit(array(10)))
        ->pipe($this->dataStore('sales'));

        $this->src('sales')
        ->pipe(new Group(array(
            "by"=>"Famille",
            "sum"=>"Marge_FAS_Controlée"
        )))
        ->pipe(new Limit(array(10)))
         ->pipe($this->dataStore("produits"));
  }
}

arthur commented on Apr 17, 2018

Ok, I may have found the problem : the xlsx file contains a lot of tabs. Is it possible to open a specific tab only ?

David Winterburn commented on Apr 17, 2018

Hi Arthur,

Multi tabs should not be a problem becaue the ExcelDataSource only retrieves data from the active tab (often the 1st tab). Please try the following setup and let us know if it runs ok:

 function setup()
  {
        $this->src('sales')
        ->pipe(new Limit(array(10)))
        ->pipe($this->dataStore('sales'));

  }
}

If there's no error with this setup we will find a way to optimize for you. Thanks!

arthur commented on Apr 17, 2018

Yes it's better.

But everything is working fine when I delete the tabs I dont use so the tab numbers seem to be a problem

David Winterburn commented on Apr 17, 2018

It's great to hear your problem is solved. It seems PHPExcel is not very optimized when loading a multi-tab file. We are planning to replace it with newer libraries for better processes. If you have any problem, don't forget to contact us for help. Thanks!

arthur commented on Apr 17, 2018

Thanks. The problem is I have no choice, I must keep all the tabs. That's why I would know if possible to select just a specific tab, if not I will have to find an other solution...

David Winterburn commented on Apr 17, 2018

Hi Arthur,

Please open the file ExcelDataSource.php in Excel package and replace its content with the following code:

<?php
/**
 * This file contains class to pull data from Microsoft Excel
 *
 * @author KoolPHP Inc (support@koolphp.net)
 * @link https://www.koolphp.net
 * @copyright KoolPHP Inc
 * @license https://www.koolreport.com/license#mit-license
 */

/*
 * The ExcelDataSource will load the Excel data, breaking down to columns and try to determine
 * the type for the columns, the precision contain number of rows to run to determine
 * the meta data for columns.
 * 
 * $firstRowData: is the first row data, usually is false, first row is column name
 * if the firstRowData is true, name column as column 1, column 2
 * 
 */
namespace koolreport\excel;
use \koolreport\core\DataSource;
use \koolreport\core\Utility;

class ExcelDataSource extends DataSource
{
	protected $filePath;
	protected $charset;
	protected $firstRowData;
	protected $sheetName;
	protected $sheetIndex;
	
	protected function onInit()
	{
		$this->filePath = Utility::get($this->params,"filePath");
		$this->charset = Utility::get($this->params,"charset","utf8");
		$this->firstRowData = Utility::get($this->params,"firstRowData",false);
		$this->sheetName = Utility::get($this->params,"sheetName",null);
		$this->sheetIndex = Utility::get($this->params,"sheetIndex",null);
	}
  
  protected function guessType($value)
	{
		$map = array(
			"float"=>"number",
			"double"=>"number",
			"int"=>"number",
			"integer"=>"number",
			"bool"=>"number",
			"numeric"=>"number",
			"string"=>"string",
		);

		$type = strtolower(gettype($value));
		foreach($map as $key=>$value)
		{
			if(strpos($type,$key)!==false)
			{
				return $value;
			}			
		}
		return "unknown";
	}
	
	public function start()
	{
    $excelReader = \PHPExcel_IOFactory::createReaderForFile($this->filePath);
    if (isset($this->sheetName))
      $excelReader->setLoadSheetsOnly($this->sheetName);
    else if (isset($this->sheetIndex)) {
      $sheetNames = $excelReader->listWorksheetNames($this->filePath);
      $excelReader->setLoadSheetsOnly($sheetNames[$this->sheetIndex]);
    }
    $excelObj = $excelReader->load($this->filePath);
    
    $sheet = $excelObj->getSheet(0);
    $highestRow = $sheet->getHighestRow(); 
    $highestColumn = $sheet->getHighestColumn();
    
    $firstRow = $sheet->rangeToArray(
        'A1:' . $highestColumn . '1',
        NULL,TRUE,FALSE
    )[0];
    $colNum = 0;
    foreach ($firstRow as $col => $text)
      if (empty($text)) {
        $colNum = $col;
        break;
      }
    $colNum = \PHPExcel_Cell::stringFromColumnIndex($colNum - 1);
    $rowNum = $highestRow;
    
    $i = 1;
    $row = $sheet->rangeToArray(
      "A1:" . $colNum . "1", NULL,TRUE,FALSE
    )[0];
    if (is_array($row)) {
      if (! $this->firstRowData) {
        $columnNames = $row;
        $row = $sheet->rangeToArray(
          "A2:" . $colNum . "2", NULL,TRUE,FALSE
        )[0];
      }
      else {
        $columnNames = array();
        for ($i=0; $i<count($row); $i++)
          array_push($columnNames, 'Column ' . $i);
      }
      
      $metaData = array("columns"=>array());
      for($i=0;$i<count($columnNames);$i++) {						
        $metaData["columns"][$columnNames[$i]] = array(
          "type"=>(isset($row)) ? $this->guessType($row[$i]) : "unknown");
      }
      $this->sendMeta($metaData,$this);
      $this->startInput(null);
      
      if ($this->firstRowData)
        $this->next(array_combine($columnNames, $row), $this);
    }
    
    for($i=2; $i<$rowNum+1; $i++) {
      $row = $sheet->rangeToArray(
        "A$i:" . $colNum . $i, NULL,TRUE,FALSE
      )[0];
      $this->next(array_combine($columnNames, $row), $this);	
    }
    $this->endInput(null);
	}
}

Then in your report's setting, add either sheetName or sheetIndex property (if both set, sheetName would supersede sheetIndex):

function settings()
  {
    return array(
      "dataSources"=>array(
        "sales"=>array(
            "class"=>'\koolreport\excel\ExcelDataSource',
            "filePath"=>"../databases/tableau.xlsx",
            "charset"=>"utf8",
            "firstRowData"=>false,
            "sheetName"=>"sheet1",
            "sheetIndex"=>0,
        ),        
      )
    );
  }

Please try this and let us know if the new data source solves your problem. Thanks!

arthur commented on Apr 17, 2018

Thanks a lot!!! It works like a charm !

Just a last question : I got a column name date which is the month of the sell as string (january, february etc.). When I apply a group to find the numbers / seller, I would like to filter a quarter. I did this but doesnt seem to work

        ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"MARGE_DELTA_REC",
        )))
        ->pipe(new Sort(array(
            "MARGE_DELTA_REC"=>"desc"
        )))
->pipe(new Filter(array(
            array("Date","=","JANVIER")
        )))
->pipe(new Filter(array(
            array("Date","=","FEVRIER")
        )))
        ->pipe(new Filter(array(
            array("Date","=","MARS")
        )))
        ->pipe(new Limit(array(10)))
        ->pipe($this->dataStore('sales'));

Thanks a lot

David Winterburn commented on Apr 17, 2018

Nice to know the sheetName and sheetIndex properties work for you! Regarding the filter question, please try the following code:

->pipe(new Filter(array(
            "or",
            array("Date","=","JANVIER"),
            array("Date","=","FEVRIER"),
            array("Date","=","MARS")
        )))

Be default, the filter process use logical "and" for multiple conditions. But a month could not be all three names at once so we change the operator to "or" to suit your case. Please test it and let us know if it works. Thanks!

arthur commented on Apr 17, 2018

Ok Perfect, thx a lot. And for the CalculatedColumn, I did this :

->pipe(new CalculatedColumn(array(
            "PO"=>"{CA_Delta_REC}*{CA_Delta_REC}",
        )))
        
        ->pipe($this->dataStore("PO"));

But I have this error : Parse error: syntax error, unexpected '*' in /Applications/MAMP/htdocs/tutorials/koolreport/processes/CalculatedColumn.php(67) : eval()'d code on line 1

KoolReport commented on Apr 17, 2018

Please check your data, if your data contains null value, it will cause this issue. To overcome this you should do:

->pipe(new CalculatedColumn(array(
    "PO"=>function($data){
        if($data["CA_Delta_REC"]!=null)
        {
            return $data["CA_Delta_REC"]* $data["CA_Delta_REC"]; 
        }
        else
        {
            return 0;
        }
    }
)))
arthur commented on Apr 17, 2018

ok. The exact equation is {CA_FAS} + 12*{CA_Delta_REC} and it may happens that one value is equal 0 sometimes, even that CA_FAS is negative but it may need to calculate. It's a commercial challenge and we can't return 0 if one is negative and the other not. Is it possible ?

KoolReport commented on Apr 17, 2018

The above code is just an example of usage. What I show you is you can define a new column from a php function. The function received a row data as parameters and what you need to do is to apply all logic or calculation so that at the end you return a value for that column.

arthur commented on Apr 18, 2018

Thanks very much ! For the group, if I want to include 2 separate columns ? For now it's

 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))

And I would like to be

 ->pipe(new Group(array(
            "by"=>"Ccial",
"by" => "ACC"
            "sum"=>"Marge_FAS_Déclarée",
        )))

Thxs

PS : we will buy the pro version. I will ask you some help to include some select on front to filter dynamicly

KoolReport commented on Apr 18, 2018

Hi, you do:

->pipe(new Group(array(
        "by"=>array("Ccial","ACC"),
        "sum"=>"Marge_FAS_Déclarée",
)))

or simply:

->pipe(new Group(array(
        "by"=>"Ccial, ACC",
        "sum"=>"Marge_FAS_Déclarée",
)))

Hope that helps. Looking forward to serving you as our customer.

arthur commented on Apr 18, 2018

Thx, but on some cases Ccial names appear twice. Maybe because my file view is wrong. here is the code

 "dataStore"=>$this->dataStore('CA_Delta_REC'),
        "width"=>"100%",
        "height"=>"500px",
        "columns"=>array(
            "Ccial"=>array(
                "label"=>"ccial"
            ),
KoolReport commented on Apr 18, 2018

Yes, because you group by both Ccial and ACC. By this grouping, result will be unqiue combination of Ccial and ACC but not unqiue for each column separately. Please let me know if my explanation still seems blur to you.

arthur commented on Apr 18, 2018

Ok, sounds clear. Here is the situation :

On a row, we can have a commercial and ACC (who can be different or the same) or just a commercia. The rule is :

if commercial and ACC are different, count the value for each, if they are the same just count the value once, else the value for the commercial.

Is that clear ?

KoolReport commented on Apr 18, 2018

I have not understood yet. Could you show the the result that you want from above data tables.

arthur commented on Apr 19, 2018

Sorry. So let's take this line (see picture). So LBenoit, and ADV, both made the sale. So I would like, when sum function is proceeding, that both have the 399,96€ amount. And I dont understand, for example when I group ACC and commercial, I can have a commercial 2 times on the graph, whereas he just appears on the commercial column...

Thanks !

David Winterburn commented on Apr 19, 2018

Hi Arthur,

If I understood correctly, you wanted to group by Commercial and ACC by sales and union them into one table only. To do that please try the following code:

$this->src('sales')
 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
->pipe($this->dataStore("salesSum"));

$this->src('sales')
 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"Marge_FAS_Déclarée",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))
->pipe($this->dataStore("salesSum"));

Please try this and let us know if it works for you. May I also suggest you open a new thread for each new problem because single-issue threads are clearer for users of the forum to follow through. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
solved

Excel