今天公司项目中要把用户的数据导出成Excel格式的文件,费了好大劲也没有弄出来,最后在吴大哥的帮助下,算是弄好。但任然还有问题,就是当数据达到上万行,或者更多是会有问题,这个以后再慢慢考虑吧,先把这个记下。
代码如下:
@RequestMapping(params="action=export")
public ModelAndView export(HttpServletRequest request,HttpServletResponse response) throws Exception {
QueryCondition conds = new QueryCondition(new Pager());
conds.getPager().setPageSize(0);
conds.getPager().setCurrentPage(0);
conds.put("zuzhiId", request.getParameter("zuzhiId"));
String queryDate = request.getParameter("queryDate");
if(UtilValidate.isNotEmpty(queryDate)){
conds.put("queryDate",UtilDateTime.date2Str(UtilDateTime.getDayStart(new Date(), -Integer.parseInt(queryDate)),Constants.TimePattern.DATE));
}
conds.put("viewList", new String[]{TypeEntity.User.toString(),TypeEntity.Zuzhi.toString(),TypeEntity.UserProfile.toString() });
DataModelList list = zuzhiService.findLinkUserZuzhiRole(conds);
String zuzhiName = "";
HttpSession session = request.getSession();
for(int i=0;i<list.size();i++){
LinkUserZuzhiRoleVO userZuzhiRoleVO = (LinkUserZuzhiRoleVO)list.get(i);
zuzhiName = userZuzhiRoleVO.getZuzhiVO().getZuzhiName();
if(userZuzhiRoleVO.getUserVO()!=null&&userZuzhiRoleVO.getUserVO().getUserProfileVO()!=null){
userZuzhiRoleVO.getUserVO().getUserProfileVO().setCurrentCityName(
AddressUtil.getCityName(
(List<?>)((Map<?, ?>)session.getServletContext().getAttribute(Constants.METADATA)).get(Constants.MetaData.USER_CITY)
,userZuzhiRoleVO.getUserVO().getUserProfileVO().getCurrentCityId())
);
userZuzhiRoleVO.getUserVO().getUserProfileVO().setCurrentStateName(AddressUtil.getStateName((List<?>)((Map<?, ?>)session.getServletContext().getAttribute(Constants.METADATA)).get(Constants.MetaData.USER_PROVINCE),userZuzhiRoleVO.getUserVO().getUserProfileVO().getCurrentStateId()));
}
if(userZuzhiRoleVO.getUserId()!=this.getSessionWrapper(request).getUserId()){
Map<String, String> map = privacyService.findPrivacy(userZuzhiRoleVO.getUserId(),TypeObject.User.toString());
if(map==null||map.get("infoMember")==null||"1".equals(map.get("infoMember"))){
userZuzhiRoleVO.setFlagExport(true);
}else{
userZuzhiRoleVO.setFlagExport(false);
}
}else{
userZuzhiRoleVO.setFlagExport(true);
}
}
response.setCharacterEncoding("gbk");
response.addHeader("Content-type","application/octet-stream");
response.addHeader("Accept-Ranges","bytes");
response.setHeader("Content-disposition","attachment; filename=" +new String((zuzhiName+"成员列表").getBytes("gbk"),"iso8859-1")+ ".xls");
OutlookUtil.exportExcel(list,response,zuzhiName);
return null;
}
主要核心代码:
public static String exportExcel(DataModelList list,HttpServletResponse response,String zuzhiName){
StringWriter sw = new StringWriter();
try {
WritableWorkbook wbook = Workbook.createWorkbook(response.getOutputStream() ) ; //建立excel文件 当时主要是这个参数没有传对,IO的好好补补了
WritableSheet wsheet = wbook.createSheet(zuzhiName+"成员列表", 0); //工作表名称
WritableFont topFont = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat topFormat = new WritableCellFormat(topFont);
topFormat.setAlignment(jxl.format.Alignment.CENTRE);
topFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label topTitle = new Label(0, 0, zuzhiName+"成员列表", topFormat);
wsheet.addCell(topTitle);
wsheet.mergeCells(0, 0, 5, 0);
wsheet.setRowView(0,500);
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
String[] title = { "姓名","邮箱","电话","性别","所在地","家乡"};
wsheet.setColumnView( 0 ,20);
wsheet.setColumnView( 1 ,40);
wsheet.setColumnView( 2 ,20);
wsheet.setColumnView( 3 ,5);
wsheet.setColumnView( 4 ,40);
wsheet.setColumnView( 5 ,40);
for (int i = 0; i < title.length; i++) {
Label excelTitle = new Label(i, 1, title[i], titleFormat);
wsheet.addCell(excelTitle);
}
for (int i = 0; i < list.size(); i++) {
LinkUserZuzhiRoleVO linkUserZuzhiRoleVO = (LinkUserZuzhiRoleVO)list.get(i);
int j = i+2 ;
wsheet.setRowView(j,300);
if( linkUserZuzhiRoleVO.getUserVO().getUserProfileVO() == null ){
continue;
}
String userName="";
String email="";
String mobile="";
String gender="";
String CurrentHome="";
String home="";
userName = linkUserZuzhiRoleVO.getUserVO().getUserName();
if( UtilValidate.isEmpty(userName)){
userName = "";
}
if(linkUserZuzhiRoleVO.isFlagExport()){
email = linkUserZuzhiRoleVO.getUserVO().getLoginEmail();
if( UtilValidate.isEmpty(email)){
email = "";
}
mobile = linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getMobile();
if( UtilValidate.isEmpty(mobile)){
mobile = "";
}
gender = linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getGender();
if( UtilValidate.isEmpty(gender)){
gender = "";
}else{
gender = "male".equals(gender) ? "男" :"女" ;
}
CurrentHome = getAddress(linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getCurrentStateName(),linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getCurrentCityName());
home = getAddress(linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getStateName(),linkUserZuzhiRoleVO.getUserVO().getUserProfileVO().getCityName());
}
if(CurrentHome.equals(home)){ home = "";}
Label content1 = new Label(0, j, userName);
Label content2 = new Label(1, j, email);
Label content3 = new Label(2, j, mobile);
Label content4 = new Label(3, j, gender);
Label content5 = new Label(4, j, CurrentHome);
Label content6 = new Label(5, j, home);
wsheet.addCell(content1);
wsheet.addCell(content2);
wsheet.addCell(content3);
wsheet.addCell(content4);
wsheet.addCell(content5);
wsheet.addCell(content6);
}
wbook.write();
wbook.close();
} catch (Exception e) {
Debug.logInfo("导出文件出错");
}
return sw.toString();
}