codeigniter - 如何在 CODEIGNITER 中使用 phpExcel 将 mysql 表导出到 csv 或 excel 文件

Question

我一直在寻找如何将 mysql 表导出到 csv 或 excel 文件。我已经看到了一些步骤并且我遵循了它们。有没有办法使用 codeigniter 将 mysql 表导出到 csv 或 excel 文件？

我试过这个 PHPExcel。但它似乎对我不起作用。

function index()
{
    $query = $this->db->get('filter_result');

    if(!$query)
        return false;

    // Starting the PHPExcel library
    $this->load->library('PHPExcel');
    $this->load->library('PHPExcel/IOFactory');

    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

    $objPHPExcel->setActiveSheetIndex(0);

    // Field names in the first row
    $fields = $query->list_fields();
    $col = 0;
    foreach ($fields as $field)
    {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
        $col++;
    }

    // Fetching the table data
    $row = 2;
    foreach($query->result() as $data)
    {
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
            $col++;
        }

        $row++;
    }

    $objPHPExcel->setActiveSheetIndex(0);

    $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

    // Sending headers to force the user to download the file
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
    header('Cache-Control: max-age=0');

    $objWriter->save('php://output');
}

Answer 1

这是我使用的代码。

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');  
class excel{

function to_excel($array, $filename) {
    header('Content-Disposition: attachment; filename='.$filename.'.xls');
    header('Content-type: application/force-download');
    header('Content-Transfer-Encoding: binary');
    header('Pragma: public');
    print "\xEF\xBB\xBF"; // UTF-8 BOM
    $h = array();
    foreach($array->result_array() as $row){
        foreach($row as $key=>$val){
            if(!in_array($key, $h)){
                $h[] = $key;   
            }
        }
    }
    echo '<table><tr>';
    foreach($h as $key) {
        $key = ucwords($key);
        echo '<th>'.$key.'</th>';
    }
    echo '</tr>';

    foreach($array->result_array() as $row){
        echo '<tr>';
        foreach($row as $val)
            $this->writeRow($val);   
    }
    echo '</tr>';
    echo '</table>';


}

function writeRow($val) {
    echo '<td>'.$val.'</td>';              
}

}
?>

使用此代码创建一个库并将其命名为:

public function brandExcel() {
    $this->load->library('excel');
    $result = $this->config_model->getBrandsForExcel();
    $this->excel->to_excel($result, 'brands-excel'); 
}