如何在CodeIgniter中整合脚本实现按日期范围导出过滤数据到Excel

Hey there! Let's put together those two CodeIgniter functionalities—server-side datatable with custom date range search and Excel export—into one seamless workflow. Below is a structured implementation based on your modified Order.php controller, plus the necessary frontend tweaks.

# 1. Refine the Order.php Controller #

First, let's ensure your controller handles both server-side datatable processing and Excel export, with shared logic for date range filtering. Here's how the modified controller might look:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Order extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('Order_model'); 
        // Load your Excel library (adjust based on your setup: PHPExcel/PhpSpreadsheet)
        $this->load->library('excel');
    }

    // Server-side datatable with date range filter
    public function datatable() {
        // Pull date range from AJAX request
        $start_date = $this->input->get('start_date');
        $end_date = $this->input->get('end_date');

        // Fetch filtered data from model
        $filtered_data = $this->Order_model->get_filtered_orders($start_date, $end_date);
        
        // Format response for datatable
        $output = [
            "draw" => intval($this->input->get('draw')),
            "recordsTotal" => $this->Order_model->get_total_orders(),
            "recordsFiltered" => count($filtered_data),
            "data" => $filtered_data
        ];
        echo json_encode($output);
    }

    // Excel export with applied date filter
    public function export_excel() {
        // Get date range parameters
        $start_date = $this->input->get('start_date');
        $end_date = $this->input->get('end_date');

        // Fetch filtered orders
        $orders = $this->Order_model->get_filtered_orders($start_date, $end_date);

        // Initialize Excel object
        $objExcel = new PHPExcel();
        // Set document properties
        $objExcel->getProperties()->setCreator("Your Team")
                                 ->setTitle("Filtered Orders Export")
                                 ->setDescription("Orders filtered by date range: $start_date to $end_date");

        // Add header row
        $objExcel->setActiveSheetIndex(0)
                 ->setCellValue('A1', 'Order ID')
                 ->setCellValue('B1', 'Customer Name')
                 ->setCellValue('C1', 'Order Date')
                 ->setCellValue('D1', 'Total Amount');

        // Populate data rows
        $row = 2;
        foreach($orders as $order) {
            $objExcel->setActiveSheetIndex(0)
                     ->setCellValue('A'.$row, $order['id'])
                     ->setCellValue('B'.$row, $order['customer_name'])
                     ->setCellValue('C'.$row, $order['order_date'])
                     ->setCellValue('D'.$row, $order['total_amount']);
            $row++;
        }

        // Configure download headers
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Filtered_Orders_'.date('Y-m-d').'.xls"');
        header('Cache-Control: max-age=0');

        // Generate and export file
        $writer = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $writer->save('php://output');
        exit;
    }
}
?>

# 2. Build the Frontend View #

Create a view file (e.g., orders_view.php) that includes the date filter form, server-side datatable, and export button:

<!DOCTYPE html>
<html>
<head>
    <title>Filterable Orders with Excel Export</title>
    <!-- Datatable CSS -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <!-- jQuery -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- Datatable JS -->
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
</head>
<body>
    <h1>Orders Management</h1>

    <!-- Date Range Filter Form -->
    <div class="filter-controls">
        <label>Start Date:</label>
        <input type="date" id="start_date" name="start_date">
        <label>End Date:</label>
        <input type="date" id="end_date" name="end_date">
        <button type="button" id="filter-btn">Apply Filter</button>
        <button type="button" id="export-btn">Export to Excel</button>
    </div>

    <!-- Server-Side Datatable -->
    <table id="orders-table" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Order ID</th>
                <th>Customer Name</th>
                <th>Order Date</th>
                <th>Total Amount</th>
            </tr>
        </thead>
        <tbody></tbody>
    </table>

    <script>
        // Initialize datatable with server-side processing
        const table = $('#orders-table').DataTable({
            processing: true,
            serverSide: true,
            ajax: {
                url: "<?php echo base_url('order/datatable'); ?>",
                type: "GET",
                data: function(d) {
                    // Pass date filters to server
                    d.start_date = $('#start_date').val();
                    d.end_date = $('#end_date').val();
                }
            }
        });

        // Refresh table when filter button is clicked
        $('#filter-btn').click(() => table.draw());

        // Trigger export with current date filters
        $('#export-btn').click(() => {
            const start = $('#start_date').val();
            const end = $('#end_date').val();
            window.location.href = `<?php echo base_url('order/export_excel'); ?>?start_date=${start}&end_date=${end}`;
        });
    </script>
</body>
</html>

# 3. Update the Model (Order_model.php) #

Add methods to fetch filtered and total orders from the database:

<?php
class Order_model extends CI_Model {

    // Fetch orders filtered by date range
    public function get_filtered_orders($start_date = null, $end_date = null) {
        $this->db->select('*');
        $this->db->from('orders');
        
        // Apply date filter if both dates are provided
        if($start_date && $end_date) {
            $this->db->where('order_date >=', $start_date);
            $this->db->where('order_date <=', $end_date);
        }
        
        return $this->db->get()->result_array();
    }

    // Get total number of orders (for datatable recordsTotal)
    public function get_total_orders() {
        return $this->db->count_all('orders');
    }
}
?>

# Key Tips for Smooth Implementation #

• Excel Library: Use PhpSpreadsheet (the successor to PHPExcel) for newer CodeIgniter versions—install it via Composer for easier management.
• Date Validation: Add server-side validation in the controller to ensure the date range is valid (e.g., start date doesn’t come after end date).
• Database Date Format: Ensure the frontend date input format matches your database’s order_date column format (usually YYYY-MM-DD).

内容的提问来源于stack exchange，提问作者Newsboy