/**
*Controller层
*/
@RequestMapping("/downUserInfo")
public void downUserInfo(HttpServletRequest request,
HttpServletResponse response) {
try {
djsService.downUserInfo(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* service层
* 下载所有报名学员信息
* @throws Exception
*/
public void downUserInfo(HttpServletRequest request,HttpServletResponse response) throws Exception {
List<User> users = djsDao.findAllUser(); //查询所有的报名学员
File file = UserInfoPoiUtil.write2003(users, request); //将学员信息写入到excel中
DownLoadUtil.downLoad(request, response, file); //下载文件
}
/**
* 将学员信息写入到excel中
*/
public static File write2003(List<User> users,HttpServletRequest request)throws Exception{
File src=new File(request.getSession().getServletContext().getRealPath("/poi/userInfo.xls"));
File dest=new File(request.getSession().getServletContext().getRealPath("/temp/userInfo.xls"));
FileUtil fileUtil=FileUtil.getInstance();
fileUtil.copy(src, dest);
FileInputStream in = new FileInputStream(dest);
HSSFWorkbook wb = new HSSFWorkbook(in);
//获得表单
HSSFSheet sheet = wb.getSheetAt(0);
// 获取字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体增粗
for(int i=0;i<users.size();i++){
User u=users.get(i);
HSSFRow row = sheet.getRow(3+i);
if(row==null){
row=sheet.createRow(3+i);
}
for (int j = 0; j < 8; j++) {
HSSFCell cell=row.getCell(j);
if(cell==null){
cell=row.createCell(j);
}
switch (j) {
case 0:
cell.setCellValue(u.getEmail());
break;
case 1:
cell.setCellValue(u.getTelephone());
break;
case 2:
Integer discipline = u.getDiscipline();
if(discipline == 1){
cell.setCellValue("APP开发与设计");
}else{
cell.setCellValue("网络营销技能");
}
break;
case 3:
cell.setCellValue(u.getQq());
break;
case 4:
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(u.getAdminssionDate()));
break;
case 5:
cell.setCellValue(GetCityValue.getCityValue(Integer.toString(u.getProvince())));
break;
case 6:
String school = GetCityValue.getSchoolValue(u);
cell.setCellValue(school);
break;
case 7:
String major = GetCityValue.getMajorValue(u);
cell.setCellValue(major);
default:
break;
}
}
}
// 写入Excel
FileOutputStream out = new FileOutputStream(dest);
wb.write(out);
out.close();
return dest;
}
/**
* 文件下载
*/
public static void downLoad(HttpServletRequest request,HttpServletResponse response,File file)throws Exception{
//下载文件
boolean isOnLine = false;
response.reset(); // 非常重要
if (isOnLine) { // 在线打开方式
URL u = new URL("file:///" + file.getPath());
response.setContentType(u.openConnection().getContentType());
response.setHeader("Content-Disposition", "inline; filename="+file.getName());
} else { // 纯下载方式
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "p_w_upload; filename="+file.getName());
}
byte[] buf = new byte[1024];
int len = 0;
BufferedInputStream br = null;
OutputStream fileOut = null;
try {
br = new BufferedInputStream(new FileInputStream(file));
fileOut = response.getOutputStream();
while ((len = br.read(buf)) > 0) {
fileOut.write(buf, 0, len);
}
fileOut.flush();
} catch (Exception e) {
} finally {
if (br != null) {
br.close();
br = null;
}
if (fileOut != null) {
fileOut.close();
fileOut = null;
}
}
if(file.exists()){
file.delete();
}
}
需要注意的问题:
1、excel2003和excel2007是不同的。详细区别自己查
2、excel表格的类型,一般设置为text文本类型即可。
3、excel表格中数据对应。
4、记得写操作以及关闭流的操作。
5、excel文件命名一般不要出现中文名,使用英文名,再不济汉语拼音也可以。(如果是中文名的话,需要做相应的修改。例如将tomcat的编码格式配置为utf8)
转载于:https://blog.51cto.com/fengcl/1704191