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!

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!

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!

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!

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!

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;
        }
    }
)))

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.

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.

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.

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

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!