主要分为两种:普通导出和按模板导出。
基本步骤:1、将数据放入workbook中
2、利用参数 workbook和导出后文件名,导出文件
两者区别在于第1步,列名是如何放入workbook的,普通导入是将列名放入数组,再将数组放入workbook;按模板导入则是通过输入流读取xls文件获得。
一、普通导出
控制层:
public String export()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
Workbook wb=new HSSFWorkbook();//创建workbook
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;
}
把数据和列名放入workbook中
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());
}
}
}
导出文件:需要workBook和导出后的文件名
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();
}
二、按模板导入
控制层:
public String export2()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
ResultSet rs=userDao.userList(con, null);//获取数据
//将数据通过模板放入workbook中,返回workbook
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
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;
}
利用模板将数据放入workbook
public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
//读取模板
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
// 获取列数
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
//放入数据
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}
}