KoolReport's Forum

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

Excel has no values when using params for creating datastore. #1288

Closed Identity Works opened this topic on on Feb 6, 2020 - 21 comments

Identity Works commented on Feb 6, 2020

Hi,

When I am trying to export excel of a table that is generated based on the year selected, I don't see any values in the excel sheet.

When i manually enter the year and generate the datastore, I am able to see the values in the exported excel.

Am I missing something? Please help.

KoolReport commented on Feb 6, 2020

Please check your query with given year if it returns correct data. I suspect that the query return no data. If it return data, please let me know the number of rows.

Identity Works commented on Feb 6, 2020

Hi, The query returns data. I can confirm this because I am using the same datastore in both view and excel.view, the data is displayed in view. Anyway the number of rows is around 12.

KoolReport commented on Feb 6, 2020

May be you could post some of your code for me.

Identity Works commented on Feb 6, 2020

in main reportfile

 use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;  
protected function defaultParamValues()
    {
        return array(
            "years"=> date("Y",strtotime("-1 year"))
        );
    }
    protected function bindParamsToInputs()
    {
        return array(
            "years",
        );
    }

protected function setup(){
$this->src('quickbrand')
        ->query("
        SELECT monthname(OrderTS)  as month,count(Order) as 'order'   
        from orders					  
        where YEAR(OrderTS) = :years and website=:siteid
        group by MONTH(OrderTS)
        ")
        ->params(array(
            ":years"=>$this->params["years"],
            ":siteid"=>$this->params["siteId"]
        ))
        ->pipe($this->dataStore("orderspermonth"));

$this->src('quickbrand')
        ->query("
        SELECT year(OrderTS) as years  
        from orders					  
        where  website=:siteid 
        group by year(OrderTS)
        ")
        ->params(array(
            ":siteid"=>$this->params["siteId"]
        ))
        ->pipe($this->dataStore("years"));

in excel.view

<div sheet-name="Order Report">
    <div>
        <?php 
        \koolreport\excel\Table::create(array(
            "dataSource"=>$this->dataStore("orderspermonth"),            
        ))
        ?>
        </div>
</div>

in view

<form class="d-print-none">
        <button type="submit" class="btn btn-primary" formaction="/export.php">Download Excel</button>
    </form>

<form method="post"> 
<?php Select::create(array(
            "name"=>"years",
            "dataStore"=>$this->dataStore("years"),    
            "dataBind"=>"years",
            "attributes"=>array(
                "class"=>"form-control",
                )
            ));
        ?>

  <div class="form-group text-center m-2">
            <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load </button>
        </div>
 </form>
    <?php 
        Table::create(array(
            "dataSource"=>$this->dataStore("orderspermonth"),            
            "cssClass"=>array(
                "table"=>"table-bordered table-striped table-hover"
            ),
            "columns"=>array(
                "month"=>array(
                    "label"=>"Month"
                ),
                "order"=>array(
                    "label"=>"Order",
                    "type"=>"number",                
                )
            )
        ))
    ?>

I hope this makes sense, please let me know if you need more info. Thanks

KoolReport commented on Feb 6, 2020

Your code seems fine, I have not spotted anything yet. Please send me the export code as well. I will ask the dev.team to look at your code tomorrow morning.

Identity Works commented on Feb 6, 2020

export.php

<?php

include "OrderPerMonth.php";
$report = new OrderPerMonth;

// //excel generator
$report->run();
$report->exportToExcel('OrderPerMonthExcel')->toBrowser("MyOrdersReport.xlsx");

Thanks for looking into it.

David Winterburn commented on Feb 7, 2020

Hi,

It's advisable to use only 1 <form> tag in your page and put all your select/text/etc inputs and submit/export buttons inside it.

Identity Works commented on Feb 7, 2020

Hi David, Thanks for the advise. Duly noted and made changes.

Do you guys ever run into an issue like this where using params while generating excel report shows no data? I haven't found any of your examples generating excel output with params, so i am actually curious to know how to make it work.

Identity Works commented on Feb 7, 2020

Hi Guys,

Any update?

Thanks.

KoolReport commented on Feb 7, 2020

You just need to remove the first form

<form class="d-print-none">
...        
</form>

and then move the

<button type="submit" class="btn btn-primary" formaction="/export.php">Download Excel</button>

to the second form near the Load button.

Identity Works commented on Feb 7, 2020

I did that.

That doesn't solve the issue. The excel generated still has no data inside it.

Identity Works commented on Feb 8, 2020

Hi Team,

Any solution for this? It would be greatly appreciated.

Identity Works commented on Feb 10, 2020

Hi Guys,

Just checking in to know if you guys had any update on this issue.

Thanks.

KoolReport commented on Feb 10, 2020

That's strange. It should work.. Anyway could you please your form here now. By the way, is the export.php in the same folder with index.php where you initiate the report.

Identity Works commented on Feb 10, 2020

The export.php is in the same folder as index.php. The other thing I noticed was when I removed the params and placed direct values and then generated the excel sheet, the values are generated in the excel sheet but once I go back to the params, the values are not generated in excel(they show up in the view though). I had placed the download excel inside the same <form> tag as suggested earlier, Apart from that my code has not changed.

KoolReport commented on Feb 10, 2020

It seems to me that the form (include years paramers ) does not post to export.php that why I told you to post the form again. Does it works when you use the Load button to post back form to index.php?

Identity Works commented on Feb 10, 2020

Hi,

Thanks for pointing out that values were not being posted to export.php

here is the index.php

<?php

require_once "OrderPerMonth.php";

$report= new OrderPerMonth(array(
    "siteId"=>$_SESSION["QB_SITEID"]
));

$report->run()->render();

here is the export.php

<?php
include "OrderPerMonth.php";
$report = new OrderPerMonth;

// //excel generator
$report->run();
$report->exportToExcel('OrderPerMonthExcel')->toBrowser("MyOrdersReport.xlsx");

here is the OrderPerMonthExcel.view.php

<div sheet-name="Order Report">
    <div>
        <?php 
        \koolreport\excel\Table::create(array(
            "dataSource"=>$this->dataStore("orderspermonth"),            
        ))
        ?>
        </div>
</div>

view.php


<form method="post"> 
    
        <?php Select::create(array(           
            "name"=>"years",
            "dataStore"=>$this->dataStore("years"),    
            "dataBind"=>"years",            
            "attributes"=>array(
                "class"=>"form-control",
                )
            ));
        ?>
        <div class="form-group text-center m-2">
            <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load </button>
            <button type="submit" class="btn btn-primary" formaction="/export.php">Download Excel</button>
        </div>
</form>
    <?php 
        Table::create(array(
            "dataSource"=>$this->dataStore("orderspermonth"),            
            "cssClass"=>array(
                "table"=>"table-bordered table-striped table-hover"
            ),
            "columns"=>array(
                "month"=>array(
                    "label"=>"Month"
                ),
                "order"=>array(
                    "label"=>"Order",
                    "type"=>"number",                
                )
            )
        ))
    ?>

main.php

use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;  
protected function defaultParamValues()
    {
        return array(
            "years"=> date("Y",strtotime("-1 year"))
        );
    }
    protected function bindParamsToInputs()
    {
        return array(
            "years",
        );
    }

protected function setup(){
$this->src('quickbrand')
        ->query("
        SELECT monthname(OrderTS)  as month,count(Order) as 'order'   
        from orders					  
        where YEAR(OrderTS) = :years and website=:siteid
        group by MONTH(OrderTS)
        ")
        ->params(array(
            ":years"=>$this->params["years"],
            ":siteid"=>$this->params["siteId"]
        ))
        ->pipe($this->dataStore("orderspermonth"));

$this->src('quickbrand')
        ->query("
        SELECT year(OrderTS) as years  
        from orders					  
        where  website=:siteid 
        group by year(OrderTS)
        ")
        ->params(array(
            ":siteid"=>$this->params["siteId"]
        ))
        ->pipe($this->dataStore("years"));

I checked the headers that was being sent to export.php and realized that siteid parameter is not being sent. How can that be done?

Identity Works commented on Feb 10, 2020

Does it works when you use the Load button to post back form to index.php? Yes.

KoolReport commented on Feb 10, 2020

I see, I think probably the form post is working well but you are missing the siteId parameter when initiate the report inside export.php (in comparing to index.php) and I think this parameter is important for your query. Because this parameter was missing, query did not work.

Identity Works commented on Feb 10, 2020

Hi,

I can confirm this is the issue because when I manually enter siteid, it generates values in the excel sheet.

Any idea as to how we can solve this?

Thanks.

Identity Works commented on Feb 10, 2020

Hi,

Thanks for helping me out. I realized adding this to export.php fixed the issue for me.

$report= new OrderPerMonth(array(
    "siteId"=>$_SESSION["QB_SITEID"]
));

Thanks for all the help again. Cheers.

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
help needed

Excel