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