poi之excel基本导出

此excel导出为基本的导出,

• 优点: 通用方法,我们需要修改获取的结果集方法,返回的数据封装
• 缺点: 通用导出的excel表格的样式需要自己定义,不然excel的格式很混乱

1.请求excel导出数据

    Workbook wb=new HSSFWorkbook();
            String headers[]={"编号","姓名","电话","Email","QQ"};//这是拼接datalist表头
            ResultSet rs=userDao.userList(con, null);//获取结果集
            ExcelUtil.fillExcelData(rs, wb, headers);
            ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");

2.excelUtil中的数据遍历赋值

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

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());//注意jdbc的行号是0开始要加1
            }
        }
    }
}

3.输出的格式重新封装

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();
    }

}