Servlet导出Excel
-
导入org.apache.poi包
-
创建一个HSSWorkbook 对象,对应一个Excel文件
-
在对象添加sheet(表),对应Excel文件里的sheet
-
在sheet中添加表头第0行,老版本poi对excel的行数和列数有限制short
-
然后创建单元格(cerateCell),设置表头和格式
-
写入实体数据(从数据库读出数据,循环创建单元格并赋值)
-
下载Excel 函数
response.setContentType(application/x-msdownload);
//下载弹出的确定保存提示框
response.setHeader("Content- Disposition","attachment;filename="+URLEncoder.encod("filename","utf-8"));
response.setHeader("Content-disposition", "attachment;filename=" +filename);
Content-disposition:属性名
attachment:表示以附件方式下载,如果要在页面中打开,可以改为inline.
注意:filename如果是中文会出现乱码:解决办法:
1、将filename 替换为 new String(filename.getBytes(), “ISO8859-1”);
2、将filename 替换为 URLEncoder.encode(filename, “utf-8”);
OutputStream out = response.getOutputSteam();
wb.write(out);
示例:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet hs=wb.createSheet("员工数据表");
HSSFRow row = hs.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("员工id");
cell = row.createCell(1);
cell.setCellValue("员工姓名");
cell = row.createCell(2);
cell.setCellValue("年龄");
UserDao userDao = new UserDao();
List<User> list = userDao.findAll();//重数据库查找所有的员工用户
for(int i = 0 ;i<list.size();i++){
row = hs.createRow(i+1);
User user = list.get(i);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getAge());
}
OutputStream out = resp.getOutputStream();
String fileName = "员工信息表.xls";
resp.setContentType("application/x-msdownload");
resp.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(fileName, "UTF-8"));
wb.write(out);