语言:java
框架:SSM
工程:maven
工具类:ExcelUtils.java
工具类下载地址:https://download.csdn.net/download/ledzcl/10234291
备注:本下载地址来源CSDN的dedzcl的博客(连接:https://blog.csdn.net/ledzcl/article/details/79222737)
仅此备忘
1、工具类下载完成后直接放到maven工程里,我放到了util包下
2、pom中添加jar包依赖关系
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
jar包下载地址:https://mvnrepository.com/artifact/org.apache.poi/poi
3、controller层添加requestMapper
@ResponseBody
@RequestMapping("/allExcel")
public void AllExcel(HttpServletResponse res) {
try {
ServletOutputStream out = res.getOutputStream();
res.setContentType("application/vnd.ms-excel");
res.setHeader("Content-disposition", "attachment;filename=" + new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+URLEncoder.encode("所有", "UTF-8") + ".xls");
Collection<Object[]> collection=new ArrayList<>();
//
List<Mess> allList = mService.getDownloadAll();
// System.out.println(allList);
for(Mess mess:allList) {
Object[] os=new Object[20];
os[0]=mess.getNumber();
os[1]=mess.getSname();
os[2]=mess.getIdcard();
os[3]=mess.getSex();
os[4]=mess.getBirthdate();
os[5]=mess.getNation();
os[6]=mess.getNativeplace();
os[7]=mess.getHomeadress();
os[8]=mess.getSgs();
os[9]=mess.getSgc();
os[10]=mess.getScadre();
os[11]=mess.getDsfirst();
os[12]=mess.getDssecound();
os[13]=mess.getDsthird();
os[14]=mess.getPname();
os[15]=mess.getRelationship();
os[16]=mess.getUnit();
os[17]=mess.getDuty();
os[18]=mess.getPhonenumber();
os[19]=mess.getPaiwei();
collection.add(os);
}
//
String[] columnNames=new String[] {
"编号","姓名","身份证号","性别","出身日期","民族","户口所在地","家庭住址","毕业学校","毕业班级",
"曾任课代表","第一心仪学校","第二心仪学校","第三心仪学校","家长姓名","与学生关系","单位","职务","联系方式","是否参加派位"
};
int[] columnIndexs=new int[] {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19};
ExcelUtils.export(collection, "所有学生", columnNames, columnIndexs, 100, out);
out.flush();
out.close();
}catch(Exception e) {
e.printStackTrace();
}
}
解释:
(1)调用工具类中的ExcelUtils.export();即可完成Excel的生成及下载
(2)HttpServletResponse需要设置content type为res.setContentType("application/vnd.ms-excel");
(3)防止中文乱码URLEncoder.encode("所有", "UTF-8")
(4)Collection<Object[]>故需要把service层返回的list转成object放入到Collection<Object[]>中
(5)columnNames和columnIndexs及Collection<Object[]>中的Object[]三者为一一对应关系
(6)最后别忘了out.flush();和out.close();
结束...