Hi,

I have a datatable report that I want to be able to export to excel. please note the the datatable includes a merged columns and rows.

This is the code of the report

DataTables::create(array(
            "dataSource"=>$this->dataStore("st"),
            "options"=>array(
                "searching"=>false,
                "paging"=>false,
                'columnDefs' => array(
        			array(
        				'visible' => false,
        				'targets' => [$this->monthDays+6], //hide the first column
        			)
        		),
        		'rowGroup' => [
        		     //'dataSrc' => [0], //10 is the order of the column you want to group
        		     'endRender' => "function ( rows, group ) {
                         var totalA = rows
                            .data()
                            .pluck($this->monthDays+7)
                            .reduce( function (a, b) {
                                return a + b.replace(/[^\d]/g,'')*1;
                            }, 0);

                        var totalB = rows
                           .data()
                           .pluck($this->monthDays+8)
                           .reduce( function (a, b) {
                               return a + b.replace(/[^\d]/g,'')*1;;
                           }, 0);

                        var totalTravelCost = rows
                            .data()
                            .pluck($this->monthDays+10)
                            .reduce( function (a, b) {
                                return a + b*1;
                            }, 0);
                        totalTravelCost = $.fn.dataTable.render.number(',', '.', 2).display( totalTravelCost );

                        return $('<tr/>')
                            .append( '<td colspan='+($this->monthDays+6)+'>סה״כ שעות מילוי מקום למורה '+group+'</td>' )
                            .append( '<td class=text-center>'+totalA+'</td>' )
                            .append( '<td class=text-center>'+totalB+'</td>' )
                            .append( '<td></td>' )
                            .append( '<td class=text-center>'+totalTravelCost+'</td>' );
                    }"
        		],
            ),
            'complexHeaders' => true,
            'headerSeparator' => ' - ',
            'cssClass'=>array(
                'table'=>'table table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'text-right' : 'text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'table-dark text-right' : 'table-dark text-center';
                },
            ),
        ));
        echo '<div class="footer print-only">Footer</div>';
        ?>

I don't think that would help. I am using dataTable widget and not table.

its not so understood in the link that you supplied I do i make the shift from datatable web rendering to table/excel export. I am adding the report setup function beside the report view that we already have above. can you please assist me in converting the code that it will export to excel:

function setup()
    {
        $this->month = date('m', strtotime($this->params["date"]));
        $this->year = date('Y', strtotime($this->params["date"]));
        $this->monthDays = cal_days_in_month(CAL_GREGORIAN, $this->month, $this->year);
        $this->src('tts')
        ->query('CALL getSubstituteTeacherHourListPerMonth(:year, :date)')
        ->params(array(
            ":year"=>$this->params["year"],
            ":date"=>$this->params["date"]
        ))
        ->pipe(new \koolreport\processes\Map(array(
            "{value}" => function($row, $metaData, $index, $mapState) {
                $days = array('א','ב','ג','ד','ה','ו','ש');
                //$month = date('m', strtotime($this->params["date"]));
                //$year = date('Y', strtotime($this->params["date"]));
                $newRow = [];
                foreach ($row as $k => $v) {
                    if (is_numeric($k))
                        $k = 1*$k;
                        if ($k >= 1 && $k <= 31) { //i.e $k is month day
                            $weekday = date('w', strtotime($this->year.'-'.$this->month.'-'.$k));
            		        $newRow[$k. " - " . $days[$weekday]] = $row[$k];
                        } else {
                            //echo $k."<br>";
                            $originalK = $k;
                            if ( in_array($k, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','hour_group_id', 'cost', 'total_travel_cost') ) ) {
                                $k = lang('tts.'.$k);
                            }
                            $newRow[$k] = $row[$originalK];
                        }
                    }
                    return $newRow;
                }
        )))
        ->pipe($this->dataStore("st"));
    }

Hi David,

above i posted the code of the view. you can see that there are several totals are calculated. I have issues with that. the calculation are not done in the right place. please see attached the report output and problems.

Hi David, This report is not exporting to excel yet. it is DataTable widget.

DataTables::create(array(
            "dataSource"=>$this->dataStore("st"),
            "options"=>array(
                "searching"=>false,
                "paging"=>false,
                'columnDefs' => array(
        			array(
        				'visible' => false,
        				'targets' => [$this->monthDays+6], //hide the first column
        			)
        		),
        		'rowGroup' => [
        		     //'dataSrc' => [0], //10 is the order of the column you want to group
        		     'endRender' => "function ( rows, group ) {
                         var totalA = rows
                            .data()
                            .pluck($this->monthDays+7)
                            .reduce( function (a, b) {
                                return a + b.replace(/[^\d]/g,'')*1;
                            }, 0);

                        var totalB = rows
                           .data()
                           .pluck($this->monthDays+8)
                           .reduce( function (a, b) {
                               return a + b.replace(/[^\d]/g,'')*1;;
                           }, 0);

                        var totalTravelCost = rows
                            .data()
                            .pluck($this->monthDays+10)
                            .reduce( function (a, b) {
                                return a + b*1;
                            }, 0);
                        totalTravelCost = $.fn.dataTable.render.number(',', '.', 2).display( totalTravelCost );

                        return $('<tr/>')
                            .append( '<td colspan='+($this->monthDays+6)+'>סה״כ שעות מילוי מקום למורה '+group+'</td>' )
                            .append( '<td class=text-center>'+totalA+'</td>' )
                            .append( '<td class=text-center>'+totalB+'</td>' )
                            .append( '<td></td>' )
                            .append( '<td class=text-center>'+totalTravelCost+'</td>' );
                    }"
        		],
            ),
            'complexHeaders' => true,
            'headerSeparator' => ' - ',
            'cssClass'=>array(
                'table'=>'table table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'text-right' : 'text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'table-dark text-right' : 'table-dark text-center';
                },
            ),
        ));
        echo '<div class="footer print-only">Footer</div>';
        ?>

Thanks david. It helped me very much. Now regards the export to Excel. I want to have a button like "Export to Excel" after I rendered the report and once the user clicks on it it will export the entire report to excel file. I need your guidance how to do it. using you documentation is very difficult to figure out what exactly I need to do. can I simply post both report files and get your explanation?

I am getting the following error after file is generated

Excel cannot open the file 'MyReport.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

this is the view page code

<?php
    //MyReport.view.php
    use \koolreport\datagrid\DataTables;
?>
<html>
    <head>
        <title><?php echo lang('tts.report_substitute_teachers'); ?></title>
        <link rel="stylesheet" href="<?php echo site_url('../assets/css/print.css')?>">
        <style>
            @media print { @page {size: landscape;} body { writing-mode: tb-rl;} }
            table.table-bordered{
                border:1px solid black!important;
                margin-top:20px;
            }
            table.table-bordered > thead > tr > th{
                border:1px solid black!important;
            }
            table.table-bordered > tbody > tr > td{
                border:1px solid black!important;
            }
            @media print {
                .hidden-print {
                    display: none !important;
                }
            }
        </style>
    </head>
    <body DIR="RTL" >
        <div class="row d-print-none">
            <div class="col text-center">
                <button id="print" class="btn btn-success" href="<?php echo site_url('reports'); ?>" onclick="javascript: window.print()">
                    <?php echo lang('tts.print'); ?>
                </button>
                <a class="btn btn-secondary mr-2" href="<?php echo site_url('reports'); ?>">
                    <?php echo lang('tts.back'); ?>
                </a>
            </div>
        </div>
        <?php
        echo '<div class="header print-only"><img src="'.site_url('../assets/img/'.(isset($_SESSION['institute_number']) ? $_SESSION['institute_number'].'.png' : "logo.svg")).'" height="38" /></div>';
        ?>
        <h1 class="text-center"><?php echo lang('tts.report_substitute_teachers'); ?></h1>
        <h3 class="text-center"><u><?php echo lang('tts.for_month').' '.lang('tts.'.date('F', mktime(0, 0, 0, $this->month, 10))).' '.$this->year;  ?></u></h3>
        <?php
        DataTables::create(array(
            "dataSource"=>$this->dataStore("st"),
            "options"=>array(
                "searching"=>true,
                "paging"=>true,
                'columnDefs' => array(
        			array(
        				'visible' => false,
        				'targets' => [$this->monthDays+6,2,3], //hide the first column
        			)
        		),
        		'rowGroup' => [
        		     //'dataSrc' => [0], //10 is the order of the column you want to group
        		     'endRender' => "function ( rows, group ) {
                         var totalA = rows
                            .data()
                            .pluck($this->monthDays+7)
                            .reduce( function (a, b) {
                                return a + b.replace(/[^\d]/g,'')*1;
                            }, 0);

                        var totalB = rows
                           .data()
                           .pluck($this->monthDays+8)
                           .reduce( function (a, b) {
                               return a + b.replace(/[^\d]/g,'')*1;
                           }, 0);

                        var totalTravelCost = rows
                            .data()
                            .pluck($this->monthDays+10)
                            .reduce( function (a, b) {
                                return a + b*1;
                            }, 0);
                        totalTravelCost = $.fn.dataTable.render.number(',', '.', 2).display( totalTravelCost );

                        return $('<tr/>')
                            .append( '<td colspan='+($this->monthDays+4)+'><b>סה״כ שעות מילוי מקום למורה <i>'+group+'</i><b></td>' )
                            .append( '<td class=text-center><b>'+totalA+'</b></td>' )
                            .append( '<td class=text-center><b>'+totalB+'</b></td>' )
                            .append( '<td></td>' )
                            .append( '<td class=text-center bg-success><b>'+totalTravelCost+'</b></td>' );
                    }"
        		],
            ),
            'complexHeaders' => true,
            'headerSeparator' => ' - ',
            'cssClass'=>array(
                'table'=>'table table-bordered',
                'tr'=>'cssItem',
                'td'=>function($row,$colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'text-right' : 'text-center';
                },
                'th'=>function($colName)
                {
                    return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'table-dark text-right' : 'table-dark text-center';
                },
            ),
        ));
        echo '<div class="footer print-only">Footer</div>';
        ?>
    </body>
</html>