一.导出过程
1.用户请求导出
2.先访问数据库，查询需要导出的结果集
3.创建导出的Excel工作簿
4.遍历结果集，写入工作簿
5.将Excel已文件下载的形式回复给请求客户端
二.具体实现（截取关键代码）
1.
public ResultSet userList(Connection con,PageBean pageBean)throws Exception{
StringBuffer sb=new StringBuffer("select * from t_user");
if(pageBean!=null){
sb.append(" limit ?,?");
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
if(pageBean!=null){
pstmt.setInt(, pageBean.getStart());
pstmt.setInt(, pageBean.getRows());
}
return pstmt.executeQuery();
}
2.export1代表基本导出,export2代表模板导出
@RequestMapping("/export")
public void export(HttpServletResponse response)throws Exception{
Connection con=null;
con=dbUtil.getCon();
Workbook wb=new HSSFWorkbook();
String headers[]={"编号","姓名","电话","Email","QQ"};
ResultSet rs=userDao.userList(con, null);
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(response, wb, "导出excel.xls");
dbUtil.closeCon(con);
}
@RequestMapping("/export2")
public void export2(HttpServletResponse response)throws Exception{
Connection con=null;
con=dbUtil.getCon();
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
ResponseUtil.export(response, wb, "利用模版导出excel.xls");
dbUtil.closeCon(con);
}
3.
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
int rowIndex=;
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(rowIndex++);
for(int i=;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=;i<headers.length;i++){
row.createCell(i).setCellValue(rs.getObject(i+).toString());
}
}
}
public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/qgx/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt();
// 获取列数
int cellNums=sheet.getRow().getLastCellNum();
int rowIndex=;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+).toString());
}
}
return wb;
}
4.
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();
}