java实现导出excel功能

直接贴代码：

controller层

  /**     * 会员列表-导出会员列表     *      * @author Mokuran     * @param request     * @param     * @return     */    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)    public void exportMemberList(HttpServletRequest request, HttpServletResponse response)            throws WriteException, IOException {        String sessionId = HttpUtils.getJSessionId(request);        StaffInfoBean staff = RedisUtils.getObject(sessionId + "staffInfo", StaffInfoBean.class);        String merchNo = storeService.findByStoreno(staff.getStoreNo()).getMerchno();        MemberListBean memberListBean = new MemberListBean();        memberListBean.setMerchNo(merchNo);        if (!request.getParameter("levelName").equals("null")) {            memberListBean.setLevelName(request.getParameter("levelName"));        }        if (!request.getParameter("channel").equals("null")) {            memberListBean.setChannel(Integer.valueOf(request.getParameter("channel")));        }        if (!request.getParameter("status").equals("null")) {            memberListBean.setStatus(Integer.valueOf(request.getParameter("status")));        }        if (!request.getParameter("startTime").equals("null")                && !StringUtil.isEmpty(request.getParameter("startTime"))) {            memberListBean.setStartTime(request.getParameter("startTime"));        }        if (!request.getParameter("endTime").equals("null") && !StringUtil.isEmpty(request.getParameter("endTime"))) {            memberListBean.setEndTime(request.getParameter("endTime"));        }        if (!request.getParameter("phoneOrNumber").equals("null")) {            memberListBean.setPhoneOrNumber(request.getParameter("phoneOrNumber"));        }        logger.info("开始获取会员列表" + memberListBean);        List<MemberListBean> memberList = memberService.getMemberListForExport(memberListBean);        String fileName = "会员列表.xls";        // 导出参数        Map<String, String> exportMap = new LinkedHashMap<String, String>();        exportMap.put("会员卡号", "memberNo");        exportMap.put("姓名", "memberName");        exportMap.put("手机号", "phone");        exportMap.put("来源渠道", "startTime");        exportMap.put("门店名称", "storeName");        exportMap.put("会员卡类别", "levelName");        exportMap.put("生日", "birthday");        exportMap.put("储值余额", "availableAmount");        exportMap.put("剩余积分", "availablePoint");        exportMap.put("开卡时间", "registrationTime");        exportMap.put("状态", "endTime");        ExportExcelUtil util = new ExportExcelUtil();        util.exportExcel(fileName, exportMap, memberList, response);    }

util方法：

package anmav.boss.utils;import java.io.IOException;import java.io.OutputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import jxl.Cell;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.VerticalAlignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import org.apache.log4j.Logger;import org.springframework.util.StringUtils;public class ExportExcelUtil {private final static Logger logger = Logger.getLogger(ExportExcelUtil.class);/** * 导出excel *  * @param fileName * @param exportMap * @param listContent * @param response * @return * @throws IOException * @throws WriteException */public void  exportExcel(String fileName,Map<String, String> exportMap, List<? extends Object> listContent, HttpServletResponse response ) throws WriteException, IOException {WritableWorkbook workbook = null; String str = exportMap.get("record");// 以下开始输出到EXCELtry {// 定义输出流，以便打开保存对话框______________________begin// HttpServletResponse response=ServletActionContext.getResponse();OutputStream os = response.getOutputStream();// 取得输出流response.reset();// 清空输出流response.setHeader("Content-disposition","attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));// 设定输出文件头response.setContentType("application/msexcel");// 定义输出类型// 定义输出流，以便打开保存对话框______________________begin// HttpServletResponse response=ServletActionContext.getResponse();// 定义输出流，以便打开保存对话框_______________________end/** **********创建工作簿************ */workbook = Workbook.createWorkbook(os);/** **********创建工作表************ */WritableSheet sheet = workbook.createSheet("Sheet1", 0);/** **********设置纵横打印（默认为纵打）、打印纸***************** *//* * jxl.SheetSettings sheetset = sheet.getSettings(); * sheetset.setProtected(false); *//** ************设置单元格字体************** */WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);/** ************以下设置三种单元格样式，灵活备用************ */// 用于标题居中WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐wcf_center.setWrap(false); // 文字是否换行// 用于正文居左WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐wcf_left.setWrap(false); // 文字是否换行int k = 0;/** ***************以下是EXCEL开头大标题，暂时省略********************* */if(!StringUtils.isEmpty(str)){ sheet.mergeCells(0, k, exportMap.size() - 1, 2); sheet.addCell(new Label(0, k, str, wcf_center)); k = 3; exportMap.remove("record");}/** ***************以下是EXCEL第一行列标题********************* */Object[] columArr = exportMap.keySet().toArray();Object[] filedNameArr = exportMap.values().toArray();sheet.addCell(new Label(0, k, "序号", wcf_center));for (int i = 0; i < columArr.length; i++) {sheet.setColumnView(i, 20);sheet.addCell(new Label(i + 1, k, columArr[i].toString(),wcf_center));}/** ***************以下是EXCEL正文数据********************* */k++;int c = 1;//设置第一列的序号for (Object obj : listContent) {int j = 0;Object value = null;sheet.addCell(new Label(j, k, String.valueOf(c), wcf_left));j++;for (Object fieldName : filedNameArr) {//logger.info("obj:" + obj);//logger.info("fieldName:"+fieldName);value =((HashMap)obj).get(fieldName);logger.info("value:"+value);if (value == null) {value = "";}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     sheet.addCell(new Label(j, k, value.toString(), wcf_left));j++;}k++;c++;}/** **********将以上缓存中的内容写到EXCEL文件中******** */workbook.write();/** *********关闭文件************* *//* * workbook.close(); logger.info("导出excel成功"); */logger.info("导出excel成功");} catch (Exception e) {logger.error("导出excel失败", e);} finally {if (workbook != null)workbook.close();}}/** * 获取EXCEL单元格的内容 *  * @param cell * @return */public static String getContent(Cell cell) {if (cell == null) {return "";}String str = cell.getContents();if (str == null) {return "";}return str.trim();}}

导出结果如下：