PHP导出生成excel文件

composer包管理工具还是非常好用的 下载安装的扩展比较靠谱 无需自己解决扩展BUG 省时省力提高效率

1.下载安装composer自行百度

2.通过composer下载安装office

3.不在废话直接代码

/**
 * @param $file_name String 文件名称
 * @param $title String 标题
 * @param $order String 中文的表格序以逗号方式连接
 * @param $data array 数据集
 * @param $data_order　String　数据集合key以逗号的方式连接
 */
function export($file_name,$title,$order,$data,$data_order) {
    
    $objPHPExcel = new PHPExcel();
    $letter = array();
    for($j = 65;$j < 91;$j++) {
        $letter[] = chr($j);
    }
    /**
     * setCreator 作者
     * setTitle　标题
     * setSubject　主题
     * setDescription　备注
     * setKeywords　关键字
     * setCategory　类别
     */
    $objPHPExcel->getProperties()->setCreator("alading")
        ->setLastModifiedBy("alading")
        ->setTitle("Office 2007 XLSX")
        ->setSubject("Office 2007 XLSX")
        ->setDescription("Office 2007 XLSX")
        ->setKeywords("office 2007")
        ->setCategory("alading file");

    $order_arr = explode(',',$order);

    foreach ($order_arr as $key => $value) {
        // 设置宽
        $objPHPExcel->getActiveSheet()->getColumnDimension($letter[$key])->setWidth(20);
        // 设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle($letter[$key])->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    }

    // 设置行高度
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
    $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
    $data_order = explode(',',$data_order);
    //设置最后的样式
    end($data_order);
    $end_key = ':' . $letter[key($data_order)];

    // 字体和样式
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
    $objPHPExcel->getActiveSheet()->getStyle('A2'.$end_key.'2')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A2'.$end_key.'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A2'.$end_key.'2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

    //  合并
    $objPHPExcel->getActiveSheet()->mergeCells('A1'.$end_key.'1');

    // 表头
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $title);
    foreach ($order_arr as $k => $v) {
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($letter[$k].'2', $v);
    }

    for ($i = 0, $len = count($data); $i < $len; $i++) {
        foreach ($data_order as $da => $vo) {
            $objPHPExcel->getActiveSheet(0)->setCellValue($letter[$da]. ($i + 3), $data[$i][$vo]);
        }
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . $end_key . ($i + 3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . $end_key . ($i + 3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);
    }
    //　重命名表名称
    $objPHPExcel->getActiveSheet()->setTitle($title);
    // 表索引设置为第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    // 输出
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $file_name . '.xls"');
    header('Cache-Control: max-age=0');

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

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