创建一个servlet:代码如下
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
List<User> dataset = new ArrayList<User>();
User u = new User();
u.setId("aaa");
u.setRealname("aaaadddd");
u.setPassword("123456");
User u2 = new User();
u2.setId("aaa555555555");
u2.setRealname("aaaaddddf");
u2.setPassword("123456566");
dataset.add(u);
dataset.add(u2);
// List<Map<String,String>> dataset = new ArrayList<Map<String,String>>();
// Map<String,String> m1 = new HashMap<String, String>();
// m1.put("id","aaaa");
// m1.put("realname", "aaaadddd");
// m1.put("password", "ddddd");
//
// Map<String,String> m2 = new HashMap<String, String>();
// m2.put("id","aaa555555555");
// m2.put("realname", "123456566");
// m2.put("password", "aaaaddddf");
//
// userBankCardList.add(m1);
// userBankCardList.add(m2);
HSSFWorkbook workbook = genHSSFWorkBook(genTableHead(),dataset);
ServletOutputStream sos = null;
try{
response.reset();
sos = response.getOutputStream();
workbook.write(sos);
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition","attachment; filename="+ URLEncoder.encode("测试导出.xlsx", "utf-8"));
// response.setHeader("Pragma", "public");
// response.setHeader("Cache-Control","public");
}catch(Exception e){
e.printStackTrace();
}finally{
sos.flush();
sos.close();
}
}
//生成Excel
// public HSSFWorkbook genHSSFWorkBook(Map<String, String> map,List<Map<String,String>> userBankCardList) {
public HSSFWorkbook genHSSFWorkBook(Map<String, String> map,List<User> dataset) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格 - sheet页的名称
HSSFSheet sheet = workbook.createSheet("用户银行卡导出");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(15);
// 产生表格标题行 下标从0开始
HSSFRow row = sheet.createRow(0);
List<String> list = Arrays.asList(map.keySet().toArray(new String[map.keySet().size()]));
for (int i = 0; i <list.size(); i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(list.get(i));
}
for(int i=0;i<dataset.size();i++){
row = sheet.createRow(i+1);
List<String> fileNameList = Arrays.asList(map.values().toArray(new String[map.values().size()]));
for (int j = 0; j < fileNameList.size(); j++){
HSSFCell cell = row.createCell(j);
String value = null;
try{
value = BeanUtils.getProperty(dataset.get(i), fileNameList.get(j));
//value = dataset.get(i).get(fileNameList.get(j));
}catch (Exception e){
e.printStackTrace();
value=null;
System.out.println("++++++++++++值获取失败字段+++++++++++" + fileNameList.get(j));
}
cell.setCellValue(value);
}
}
System.out.println("++++++++++++写入成功+++++++++++" + dataset.size());
return workbook;
}
//自定义的表头和字段必须与一一对应
private Map<String, String> genTableHead() {
Map<String,String> map = new LinkedHashMap<String,String>();
map.put("用户ID","id");
map.put("真实姓名", "realname");
map.put("身份证号", "password");
return map;
}
//内部类User
class User implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private String realname;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
}
使用jar包:pio-3.8-20120326.jar
参考地址:
*** https://blog.csdn.net/jimmy609/article/details/41038537
http://blog.csdn.net/evangel_z/article/details/7332535
http://download.csdn.net/detail/evangel_z/3895051
http://www.open-open.com/lib/view/open1429847388213.html