java导出excel表格_java导出Excel表格

packagecom.lin.controller;importjava.io.BufferedInputStream;importjava.io.BufferedOutputStream;importjava.io.ByteArrayInputStream;importjava.io.ByteArrayOutputStream;importjava.io.IOException;importjava.io.InputStream;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importjavax.annotation.Resource;importjavax.servlet.ServletContext;importjavax.servlet.ServletOutputStream;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.springframework.beans.factory.annotation.Value;importorg.springframework.stereotype.Controller;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RequestMethod;importorg.springframework.web.bind.annotation.RequestParam;importorg.springframework.web.bind.annotation.ResponseBody;importorg.springframework.web.multipart.MultipartFile;importorg.springframework.web.multipart.MultipartHttpServletRequest;importorg.springframework.web.multipart.MultipartResolver;importorg.springframework.web.multipart.commons.CommonsMultipartResolver;importcom.lin.domain.data.ResData;importcom.lin.domain.data.ResListData;importcom.lin.domain.error.Error;importcom.lin.domain.sysUser.SysUser;importcom.lin.service.RoleService;importcom.lin.service.SysUserService;importcom.lin.utils.Aes;importcom.lin.utils.DateTimeUtils;importcom.lin.utils.ExcelUtil;importcom.lin.utils.ResponseUtils;importcom.lin.utils.Upload;importcom.lin.utils.Utils;importcom.sun.jersey.api.client.Client;importnet.sf.json.JSONObject;/*** 后台用户-controller

*@authorlibo*/@Controller

@RequestMapping("/sysUser")public classSysUserController {

@ResourceprivateSysUserService sysUserService;

@ResourceprivateRoleService rService;

@Value(value="${headImgPath}") //后台图片保存地址

privateString headImgPath;

@Value(value="${uploadHost}")private String uploadHost; //项目host路径

@Value(value="${sysUserDefImg}")private String sysUserDefImg; //系统用户默认头像

/*** 导出系统用户数据

*@paramreq

*@paramres

*@paramname

*@paramphone

*@paramemail

*@paramroleId

*@paramcreateTimeStart

*@paramcreateTimeEnd

*@paramstatus

*@paramdepartmentId

*@throwsIOException*/@ResponseBody

@RequestMapping(value="/exportSysUsers.do", method=RequestMethod.GET)public voidexportSysUsers(HttpServletRequest req,HttpServletResponse res,

String name, String phone, String email, Integer roleId, String createTimeStart, String createTimeEnd,

Integer status, Integer departmentId)throwsIOException{

Map params = new HashMap();

params.put("name", "".equals(name) || null == name ? null: name);

params.put("phone", "".equals(phone) || null == phone ? null: phone);

params.put("email", "".equals(email) || null == email ? null: email);

params.put("roleId", "".equals(roleId) || null == roleId ? null: roleId);

params.put("createTimeStart", "".equals(createTimeStart) || null == createTimeStart ? null : createTimeStart+" 00:00:00");

params.put("createTimeEnd", "".equals(createTimeEnd) || null == createTimeEnd ? null : createTimeEnd+" 23:59:59");

params.put("status", "".equals(status) || null == status ? null: status);

params.put("departmentId", "".equals(departmentId) || null == departmentId ? null: departmentId);

Date d= newDate();

SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMddHHmmss");

String formatTime=sdf.format(d);

String fileName="系统用户表-"+formatTime;//填充projects数据

List userList =sysUserService.getUserList(params);

List> list=createExcelRecord(userList);

String columnNames[]={"姓名", "性别", "邮箱", "电话", "部门", "角色", "状态", "创建时间"};//列名

String keys[] = {"name", "gender", "email", "phone", "department", "role", "status", "createTime"};//map中的key

ByteArrayOutputStream os = newByteArrayOutputStream();try{

ExcelUtil.createWorkBook(list,keys,columnNames).write(os);

}catch(IOException e) {

e.printStackTrace();

}byte[] content =os.toByteArray();

InputStream is= newByteArrayInputStream(content);//设置response参数，可以打开下载页面

res.reset();

res.setContentType("application/vnd.ms-excel;charset=utf-8");

res.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));

ServletOutputStream out=res.getOutputStream();

BufferedInputStream bis= null;

BufferedOutputStream bos= null;try{

bis= newBufferedInputStream(is);

bos= newBufferedOutputStream(out);byte[] buff = new byte[2048];intbytesRead;//Simple read/write loop.

while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

bos.write(buff,0, bytesRead);

}

}catch (finalIOException e) {throwe;

}finally{if (bis != null)

bis.close();if (bos != null)

bos.close();

}

}/*** 生成Excel数据

*@paramuserList

*@return

*/

private List> createExcelRecord(ListuserList) {

List> listmap = new ArrayList>();

Map map = new HashMap();

map.put("sheetName", "sheet1");

listmap.add(map);

SysUser user= null;for (int j = 0; j < userList.size(); j++) {

user=userList.get(j);

Map mapValue = new HashMap();

mapValue.put("name", user.getName());

mapValue.put("gender", (user.getGender()==1) ? "男":((user.getGender()==0) ? "女" : "保密"));

mapValue.put("email", user.getEmail());

mapValue.put("phone", user.getPhone());

mapValue.put("department", user.getDepartment().getName());

mapValue.put("role", user.getRole().getName());

mapValue.put("status", user.getStatus()==1 ? "启用" : "禁用");

mapValue.put("createTime", user.getCreateTime().substring(0, 19));

listmap.add(mapValue);

}returnlistmap;

}

}