Apache POI 第五讲之利用POI 实现数据的批量导出

╰+攻爆jí腚メ 2022-09-23 08:12 175阅读 0赞

> 有时候我们在做项目时，有些项目需要生成Microsoft Excel文件格式的报告。有时，甚至希望将Excel文件作为输入数据。这是我们需要用到Apache POI 。例如，一个公司开发的应用程序将财务部门需要所有输出生成自己的Excel。

#### **利用 POI 实现数据的批量导出** ####

##### **1.编写导出工具类** #####

public class ExcelUtil {
    
        public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
            int rowIndex=0;
            Sheet sheet=wb.createSheet();
            Row row=sheet.createRow(rowIndex++);
            for(int i=0;i<headers.length;i++){
                row.createCell(i).setCellValue(headers[i]);
            }
            while(rs.next()){
                row=sheet.createRow(rowIndex++);
                for(int i=0;i<headers.length;i++){
                    row.createCell(i).setCellValue(rs.getObject(i+1).toString());
                }
            }
        }
    }

public class ResponseUtil {
    
        public static void write(HttpServletResponse response,Object o)throws Exception{
            response.setContentType("text/html;charset=utf-8");
            PrintWriter out=response.getWriter();
            out.print(o.toString());
            out.flush();
            out.close();
        }
    
        public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
            response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
            response.setContentType("application/ynd.ms-excel;charset=UTF-8");
            OutputStream out=response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        }
    
    }

##### **2.编写action类导出方法** #####

public String export()throws Exception{
            Connection con=null;
            try {
                con=dbUtil.getCon();
                Workbook wb=new HSSFWorkbook();
                String headers[]={
       "编号","姓名","电话","Email","QQ"};
                ResultSet rs=userDao.userList(con, null);
                ExcelUtil.fillExcelData(rs, wb, headers);
                ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                try {
                    dbUtil.closeCon(con);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            return null;
        }

##### **3.编写页面** #####

function exportUser(){ 
        window.open('user!export') ;
    }

##### **5.查看结果** #####

![这里写图片描述][20160706203533351]

![这里写图片描述][20160706203543385]

![这里写图片描述][20160706203551995]

> 本实例用了easyui 与 struts2 ，无关紧要，简单的把相关POI代码粘贴出来，仅供参考。。

[20160706203533351]: /images/20220721/273f4fda45734c33ab254586693dabef.png
[20160706203543385]: /images/20220721/3724e15e80224da589ac86bdb0aa90b4.png
[20160706203551995]: /images/20220721/e0183a038f6b40bc891af63697c22a01.png