PHPexcel实现批量导出表格

ThinkPHP  /  管理员 发布于 5年前   866

1. 下载这个类放到verdor中phpexcel.7z  （这里我就以tp3为例 ）

2. 在公共函数库里面加入

/**
 * Excel导出
 * @param data 导出数据
 * @param title 表格的字段名 字段长度有限制，一般都够用，可以改变 $length1和$length2来增长
 * @return subject 表格主题 命名为主题+导出日期
 */
function exportExcel($data,$title,$subject){  
    Vendor('phpexcel.PHPExcel');
    Vendor('phpexcel.PHPExcel.IOFactory');
    Vendor('phpexcel.PHPExcel.Reader.Excel5');
    // Create new PHPExcel object  
    $objPHPExcel = new PHPExcel();  
    // Set properties  
    $objPHPExcel->getProperties()->setCreator("ctos")  
        ->setLastModifiedBy("ctos")  
        ->setTitle("Office 2007 XLSX Test Document")  
        ->setSubject("Office 2007 XLSX Test Document")  
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")  
        ->setKeywords("office 2007 openxml php")  
        ->setCategory("Test result file");  
    $length1=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y'
                ,'Z','AA','AB','AC','AD');
    $length2=array('A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1'
                ,'U1','V1','W1','X1','Y1','Z1','AA1','AB1','AC1','AD1');
    //set width  
    for($a=0;$a<count($title);$a++){
         $objPHPExcel->getActiveSheet()->getColumnDimension($length1[$a])->setWidth(20); 
    }
    //set font size bold  
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);  
    $objPHPExcel->getActiveSheet()->getStyle($length2[0].':'.$length2[count($title)-1])->getFont()->setBold(true); 
    $objPHPExcel->getActiveSheet()->getStyle($length2[0].':'.$length2[count($title)-1])->getFont()->setBold(true);    
    $objPHPExcel->getActiveSheet()->getStyle($length2[0].':'.$length2[count($title)-1])->getBorders()->getAllBorders()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
    
    // set table header content  
    for($a=0;$a<count($title);$a++){
          $objPHPExcel->setActiveSheetIndex(0)->setCellValue($length2[$a], $title[$a]); 
    }
    for($i=0;$i<count($data);$i++){ 
        $buffer=$data[$i];
        $number=0;
        foreach ($buffer as $value) {
            $objPHPExcel->getActiveSheet(0)->setCellValueExplicit($length1[$number].
                ($i+2),$value,PHPExcel_Cell_DataType::TYPE_STRING);//设置单元格为文本格式
            $number++;
        }
        unset($value);
        $objPHPExcel->getActiveSheet()->getStyle($length1[0].($i+2).':'.$length1[$number-1].
            ($i+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objPHPExcel->getActiveSheet()->getStyle($length1[0].($i+2).':'.$length1[$number-1].($i+2))->getBorders()
            ->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objPHPExcel->getActiveSheet()->getRowDimension($i+2)->setRowHeight(16);  
    }  
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet  
    $objPHPExcel->setActiveSheetIndex(0);  

    ob_end_clean();//清除缓冲区,避免乱码
    // Redirect output to a client’s web browser (Excel5)  
    header('Content-Type: application/vnd.ms-excel');  
    header('Content-Disposition: attachment;filename='.$subject.'('.date('Y-m-d').').xls');  
    header('Cache-Control: max-age=0');  

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
    $objWriter->save('php://output');  
}  

    3.控制器方法

    /**
     * 导出数据库表 生成excel
     */
    public function _export(){
        $nm_list = M('表')->select();//数据 如果按条件导入 只需传入叁数
        $subject="Excel导出-表"; //名字
        $title=array("id","字段","字段","字段","字段");//往下加
        exportExcel($nm_list,$title,$subject); 
    }

    4.前端用一个正常的表单提交就OK