importing data from excel

hayu commented on Dec 1, 2021

Hi, I want to create an API to processing data from "importing data from excel koolreport" into data array. I have used koolreport pro. what I want to ask:

Can koolreport read more than 2 sheets in 1 excel file? if so what method should be used?
how to retrieve/get data after pipe->datastore() to make it an array?

this is my script

require ROOT_PATH.'\gsapi\vendor\autoload.php';

use \koolreport\excel\ExcelDataSource;

class tes extends \koolreport\KoolReport{
    public $cls;
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "read_excel"=>array(
                    "class"=>ExcelDataSource::class,
                    "filePath"=>dirname(__FILE__)."\\"."temp\FF CTP DELTA 08W21  - OUT PORT.xls",
                    "sheetIndex"=>2,
                    )
            )
        );
    }
    public function setup()
    {
        $result = $this->src('read_excel')
        ->pipe($this->dataStore("tes"));
        // $res = $this->dataStore("tes")->all();
        
    }
    public function handler(){
        $cls = new tes;
        $cls->run();
        
        $res = $this->dataStore("tes")->count();
        echo var_dump($cls);die;
    }
}
    $param = file_get_contents("php://input");
    $cls_tes = new tes;
    $start = $cls_tes->handler();

Sebastian Morales commented on Dec 2, 2021

Hi, regarding your points:

1 - It's unfortunate at the moment the ExcelDataSource can only read from one sheet per file. But per your suggestion we would soon make it be able to read multiple sheets per file. A work around is to use multiple sources, each using the same file but with a different "sheetIndex":

            "dataSources"=>array(
                "read_excel2"=>array(
                    "class"=>ExcelDataSource::class,
                    "filePath"=>dirname(__FILE__)."\\"."temp\FF CTP DELTA 08W21  - OUT PORT.xls",
                    "sheetIndex"=>2,
                ),
                "read_excel4"=>array(
                    "class"=>ExcelDataSource::class,
                    "filePath"=>dirname(__FILE__)."\\"."temp\FF CTP DELTA 08W21  - OUT PORT.xls",
                    "sheetIndex"=>4,
                ),
            )

2 - With any datasource you can call ->requestDataSending() right after piping to a datastore for the data pipe to start running right away so you could get the datastore's data as an array:

$this->src(...)
->pipe(...)
->pipe($this->dataStore(...))
->requestDataSending(); //pull data right away

$data = $this->dataStore(...)->data(); //if requestDataSending is not called, a datastore's data is only available after $report->run(), i.e inside the report's view

Let us know if these work for you. Tks,

hayu commented on Dec 2, 2021

i have tried to apply these and these works for me. Thank you

KoolReport's Forum

Importing data from excel #2468

Build Your Excellent Data Report