mysql - 自定义过滤器并将过滤后的数据导出到 Excel 工作表中，在 codeigniter 中不起作用

Question

我有带有搜索字段的数据表。我正在尝试使用数据范围和订单状态过滤表。我还想使用范围和状态过滤表，还想导出到 Excel 表中。代码仅适用于搜索和简单导出（未过滤数据）。

谢谢

这是我的文件（视图、控制器和模型）

看法：-

<a class="pull-right btn btn-primary btn-xs" href="<?php echo base_url()?>order/createXLS"><i class="fa fa-file-excel-o"></i> Export Data</a>
<div class="panel panel-default">
              <div class="panel-heading">
                <h3 class="panel-title" >Custom Filter : </h3>
            </div>
            <div class="panel-body">
                <form id="form-filter" method="POST" action="order/ajax_list" class="form-horizontal">

                    <div class="form-group">
                        <label for="FirstName" class="col-sm-2 control-label">From</label>
                        <div class="col-sm-4">
                            <input type="date" class="form-control" id="from">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="LastName" class="col-sm-2 control-label">To</label>
                        <div class="col-sm-4">
                            <input type="date" class="form-control" id="to">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="LastName" class="col-sm-2 control-label">Status</label>
                        <div class="col-sm-4">
                            <input type="text" class="form-control" id="status">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="LastName" class="col-sm-2 control-label"></label>
                        <div class="col-sm-4">
                            <button type="button" id="btn-filter" class="btn btn-primary">Filter</button>
                            <button type="button" id="btn-reset" class="btn btn-default">Reset</button>
                        </div>
                    </div>
                </form>
            </div>  
        </div> 
   <table class="table table-bordered table-striped" id="mytable"> 
        <thead> 
            <tr> 
                <th width="80px">No</th> 
                <th>Order Number</th> 
                <th>Service Date</th>
                <th>Service Location</th> 
                <th>Customer Name</th> 
                <th>Contact Number</th> 
                <th>Order Status</th>
                <th>Payment Status</th> 
                <th>Action</th> 
            </tr> 
        </thead> 
        <tbody> 
            <?php 
            $start = 0; 
            foreach ($job_positions_data as $job_positions) 
            { 
                ?> 
                <tr> 
                    <td> 
                        <?php echo ++$start ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->order_number ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->service_date ?> 
                    </td>
                    <td> 
                        <?php if($job_positions->service_location == 1)
                        {
                            echo 'vkool_branch';
                        }
                        else
                        {
                            echo 'customer_location' ;
                        }
                        ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->firstname ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->contact_number ?> 
                    </td> 
                    <td> 
                        <?php if($job_positions->order_status == 0)
                        {
                            echo 'Action Pending';
                        }
                        elseif($job_positions->order_status == 1)
                        {
                            echo 'Under Process' ;
                        }
                        elseif($job_positions->order_status == 2)
                        {
                            echo 'Cancelled' ;
                        }
                        elseif($job_positions->order_status == 3)
                        {
                            echo 'Completed' ;
                        }
                        elseif($job_positions->order_status == 4)
                        {
                            echo 'Refund' ;
                        }
                        ?>  
                    </td> 
                    <td> 
                        <?php if( $job_positions->payment_status == 1) 
                        {
                            echo 'Pending (Pay at Store)';
                        }
                        elseif($job_positions->payment_status == 0)
                        {
                            echo 'Pending (Pay Online)';
                        }
                        elseif($job_positions->payment_status == 14)
                        {
                           echo 'Paid';
                        }?> 
                    </td> 
                    <td style="text-align:center" width="200px"> 
 <a href="javascript:view_order(<?php echo $job_positions->id; ?>)"><i class="fa fa-search-plus"></i></a>
                    </td> 
                </tr> 
                <?php 
            } 
            ?> 
        </tbody> 
    </table> 

    <script type="text/javascript"> 
        $(document).ready(function() { 
            $("#mytable").dataTable(); 
        }); 
    </script>  

控制器 ： -

public function createXLS() {

    $fileName = 'data-'.time().'.xlsx';  
    require_once APPPATH . "/third_party/PHPExcel/Classes/PHPExcel.php";
    $orderInfo = $this->Order_model->get_For_Export();
    $ordervalue = json_encode($orderInfo, true);

    /*echo '<pre>';
    print_r($orderInfo);
    echo '</pre>';
    exit;*/
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    // set Header
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'S.No');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Order Number');
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Service Location');
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'First Name');
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Last Name');
    $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Contact_No');    
    $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Order Status');
    $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Payment Status');

    // Style
    $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '2F4F4F'),
            'size' => 20,
        ),
    );
    $styleHeading = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'font' => array(
            'bold' => true,
            'size' => 15,
        ),
    );






    // set Row
    $rowCount = 2;
    foreach ($orderInfo as $element) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['id']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['order_number']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['service_location']);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['firstname']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['lastname']);
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element['contact_number']);
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['order_status']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['payment_status']);
        $rowCount++;
    }

    // download file
    $filename = "jobSummery". date("Y-m-d-H-i-s").".xls";
    header('Content-Type: application/vnd.ms-excel'); 
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0'); 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
    $objWriter->save('php://output');  
}


public function list_view()
{
    $job_positions = $this->Order_model->get_all(); 
    $data = array( 
        'job_positions_data' => $job_positions 
    ); 
    $this->load->view('order/index', $data);
}

模型 ：-

public function get_all() 
{ 
    $this->db->order_by('id', 'DESC');
    $this->db->from('tbl_order');

    $query = $this->db->get();
    $result = $query->result();

    return $result; 
}
public function get_For_Export() 
{ 
    $this->db->order_by('id', 'DESC');
    $this->db->from('tbl_order');

    $query = $this->db->get();
    $result = $query->result_array();

    return $result; 
}

标签： mysqlexcelajaxdatatables