导出Excel

本次总结了两种导出excel的方式，均在项目中测试可行。

第一种 （比较常用）

jsp页面就不介绍了。

js页面导出的方法

/** * 导出Excel */UserFeedBackSearch.prototype.exportExcel=function(){var offerTotalAcount=$("#offerTotalAcount").val();if(offerTotalAcount>5000){alert("记录数("+offerTotalAcount+")大于5000，请缩小搜索范围!");return;}var  url = constants.CTX + "/userFeedBackManage!exportExcel.action";$("#form1")[0].action = url;$("#form1")[0].submit();}

注意：你用其他方法提交的时候，要保证提交方法的返回值支持流的方式传回。

action页面

/**     * 导出Excel     * <功能详细描述>     * @return [参数说明]     * @return String [返回类型说明]     * @exception throws [违例类型] [违例说明]     * @see [类、类#方法、类#成员]     */    public void exportExcel(){        log.info("UserFeedBackManageAction.exportExcel");        try{            int count=userFeedBackService.queryUserFeedbackCnt(getAuthLatnCd(), userFeedBack);             if(count>5000){                super.getRequest().setAttribute("errorFlag", "1"); //errorFlag 超过5000赋值1，用来界面提示                if (AAUtils.isAjaxRequest(super.getRequest())) {                    AAUtils.addZonesToRefresh(super.getRequest(), "ErrorContentZone");                }            }else{                Pagination pagination = initPagination(getRequest(), 5000,count);                List<UserFeedBack> list=userFeedBackService.queryUserFeedBack(getAuthLatnCd(), userFeedBack, pagination);                if(null!=list&&list.size()>0){                    String[] title=new String[]{"记录ID","反馈标题","反馈人","本地网","反馈时间","状态"};                    List<String[]> resultList = new ArrayList<String[]>();                    String[] obj = null;                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                    for (UserFeedBack vo : list) {                        int i=0;                        obj = new String[6];                        obj[i++] = vo.getFbId()+"";                        obj[i++] = vo.getFbTitle();                        obj[i++] = vo.getFbPeople();                        obj[i++] = vo.getLatnName();                        obj[i++] = vo.getFbDate()+"";                        obj[i++] = vo.getStatusCdName();                        resultList.add(obj);                    }                    File file = ExcelUtils.writeExecl("用户改善意见导出_" +sdf.format(Calendar.getInstance().getTime())+ ".xls", title, resultList);                    ResponseDownloadUtils.download(super.getResponse(), file);                }            }        }catch(Exception e){            log.error("UserFeedBackManageAction.exportExcel"+e);        }    }

/**     * 销售品产品导出     *      * @param title     * @param columnTitles     * @param data     * @return     */    @SuppressWarnings("unchecked")    public static File writeExecl(String title, String[] columnTitles, Collection data) {        File file = null;        WritableWorkbook book = null;        try {                        file = new File(title);            book = Workbook.createWorkbook(file);                        WritableSheet sheet = book.createSheet(title, 0);                        int rowIndex = 0;                        WritableCellFormat titleFormat = getDefaultTitleFormat();                        // 如果表头不为空，则写入表头            if (columnTitles != null) {                for (int i = 0; i < columnTitles.length; i++) {                    WritableCell label = createCell(i, rowIndex, columnTitles[i], titleFormat);                    sheet.addCell(label);                    int newLength = columnTitles[i] == null ? 0 : columnTitles[i].getBytes().length;                    sheet.setColumnView(i, newLength);                    // CellView cellView = new CellView();                    // cellView.setAutosize(true);                    // sheet.setColumnView(i, cellView);                }                rowIndex++;            }            // 写入数据            Iterator it = data.iterator();            while (it.hasNext()) {                Object[] row = (Object[])it.next();                int colIndex = 0;                for (Object cell : row) {                    WritableCell label = createCell(colIndex, rowIndex, cell);                                        // 将定义好的单元格添加到工作表中                    sheet.addCell(label);                    int maxLength = sheet.getColumnWidth(colIndex);//                  int newLength = ((String)cell) == null ? 0 : ((String)cell).getBytes().length;                    double newLength = 0;                    if (cell != null) {                        char[] ch = ((String)cell).toCharArray();                        for (int i = 0; i < ch.length; i++) {                            char c = ch[i];                            if(isChinese(c)){                                newLength = newLength+2;                            }else {                                newLength+=1;                            }                        }                    }                    if (maxLength < newLength) {                        sheet.setColumnView(colIndex, ((int)newLength+1)>56?56:(int)newLength+1);                    }                    colIndex++;                }                rowIndex++;            }            return file;        }        catch (Exception e) {            log.error(e);        }        finally {            try {                if (book != null) {                    book.write();                    book.close();                }            }            catch (IOException e) {                log.error(e);            }            catch (WriteException e) {                log.error(e);            }        }                return file;    }

public static void download(HttpServletResponse response, File file) {BufferedInputStream br = null;OutputStream out = null;try {File f = file;if (!f.exists()) {response.sendError(404, "File not found!");return;}br = new BufferedInputStream(new FileInputStream(f));byte[] buf = new byte[1024];int len = 0;response.reset();String fileName = new String(f.getName().getBytes("GBK"), "ISO8859-1");response.setContentType("application/x-msdownload");response.setHeader("Content-Disposition", "attachment; filename=" + fileName);out = response.getOutputStream();// out.flush();while ((len = br.read(buf)) > 0)out.write(buf, 0, len);br.close();out.close();} catch (Exception ex) {ex.printStackTrace();} finally {try {if (br != null)br.close();if (out != null)out.close();} catch (IOException e) {log.error(e);}}}

上两个方法封装成了共用方法，直接可以用。

第二种方法（这种方法需要结合Struts框架）该导出的好处是可以很方便调整excel文档的样式

jsp和js文件和上面的类似，就不介绍了。

struts层

<action name="exportExcelAction" class="exportExcelAction"><!-- 下载导出excel --><result name="success" type="stream"><!-- 文件类型及编码设置 --><param name="contentType">application/vnd.ms-excel,charset=ISO8859-1</param><!-- contentDisposition:文件下载的处理方式，包括内联(inline)和附件(attachment)两种方式，而附件方式会弹出文件保存对话框，否则浏览器会尝试直接显示文件。--><!-- fileName下载文件名称--><param name="contentDisposition">attachment;filename="${fileName}"</param><!-- 下载缓冲区的大小--><param name="bufferSize">4096</param><!-- 下载文件的来源流--><param name="inputName">excelFile</param></result></action>

action层（这里方法比较多，一步步来）

私有属性

private static final long serialVersionUID = 1L;private static final SimpleDateFormat yyyy_MM_dd = new SimpleDateFormat("yyyy-MM-dd");private static final String Explain =    "说明：注册来源（0代表App端，1代表Pc端);性别（0代表女，1代表男）;状态（0代表未激活，1代表正常，2代表警告，3代表冻结）;用户类型（0代表用户，1代表商家，2代表其它）";private User user;private InputStream excelStream; // 这个输入流对应上面struts.xml中配置的那个excelStream，两者必须一致private String fileName; // 这个名称就是用来传给上面struts.xml中的${fileName}的private UserQuery userQuery;

Explain是excel文件第一行的说明（根据情况取舍）

public String exportExcel() throws Exception{HttpServletRequest request = ServletActionContext.getRequest();// 获取每页展示数据量int pageSize = UsualTools.getInt(request.getParameter("rows"));// 获取当前页int pageNo = UsualTools.getInt(request.getParameter("page"));String ids = request.getParameter("idss");PageControl<User> pageControl = new PageControl<User>();pageControl.setCurrPage(pageNo);pageControl.setPerPageNum(pageSize);// 查询分页数据this.userService.findUserAll(pageControl, this.user, userQuery);List<User> list = pageControl.getObjectList();List<User> userList = new ArrayList<>();if (ids != null && !"".equals(ids)){userList = userService.findByIds(ids);}if (userList != null && userList.size() > 0){return this.createBookName(userList);}else if (list == null || list.size() == 0){request.setAttribute("message", "查无此数据!");return "error";}else{return this.createBookName(list);}}

/** * 生成表名 *  * @param list * @return * @throws Exception *             lishun */public String createBookName(List<User> list) throws Exception{HSSFWorkbook workbook = getWorkbook(list);try{if (workbook != null){Calendar c = Calendar.getInstance();int year = c.get(Calendar.YEAR);int month = c.get(Calendar.MONTH) + 1;String month_ = new String("" + month);if (month < 10){month_ = "0" + month;}int day = c.get(Calendar.DAY_OF_MONTH);String day_ = new String("" + day);if (day < 10){day_ = "0" + day;}// 第四步：将工作簿写入最上面定义的InputStream流——名称为excelStream，这个名字对应struts.xml中配置的inputName参数this.workbook2InputStream(workbook, "user_list_" + year + "-" + month_ + "-" + day_    + "");return "success";}}catch (IOException e){return "error";}return "error";}

/** * 将Workbook写入到InputStream *  * @param workbook * @param fileName * @throws Exception * lishun */public void workbook2InputStream(HSSFWorkbook workbook, String fileName) throws Exception{this.fileName = fileName; // 设置fileNameByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);baos.flush();byte[] aa = baos.toByteArray();excelStream = new ByteArrayInputStream(aa, 0, aa.length);baos.close();}

/** * 将list转化为workbook输出 *  * @param list * @return * @throws Exception *             lishun */public HSSFWorkbook getWorkbook(List<User> list) throws Exception{HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("用户信息列表");sheet.setDefaultColumnWidth(14);sheet.setDefaultRowHeightInPoints(5);// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));//单元格合并**// 这里的columnMethods中的值就是pojo里面的getter方法名,是用来取值String[] columnMethods =    new String[] { "getId", "getName", "getNickname", "getSex", "getMobile", "getAddress",        "getRegSource", "getStatus", "getLoginTime", "getLoginAddr", "getImage",        "getIdcard", "getLevel", "getScore", "getInviter", "getLongitude", "getLatitude",        "getMoney", "getStyle", "getIp" };// 这里的columnNames 中的值就是导出的excel里面的标题String[] columnNames =    new String[] { "用户ID", "姓名", "昵称", "性别", "手机", "地址", "注册来源", "状态 ", "最后登录时间", "登录地址",        "头像", "身份证", "等级", "积分", "邀请人", "经度", "纬度", "金额", "用户类型", "最后登录IP" };// 首先，我们读取list中的第一个元素，根据它来确定工作表的列名，以及输出数据所对应的方法数组User user = list.get(0);HSSFRow row0 = sheet.createRow(0); // 创建第1行，也就是输出说明 **HSSFCell cell0 = row0.createCell(0);HSSFCellStyle cellStyle = cell0.getCellStyle();cellStyle.setWrapText(true);cell0.setCellValue(new HSSFRichTextString(Explain));HSSFRow row = sheet.createRow(1); // 创建第1行，也就是输出表头HSSFCell cell;for (int i = 0; i < columnNames.length; i++){cell = row.createCell(i); // 创建第i列cell.setCellValue(new HSSFRichTextString(columnNames[i]));}// 下面是输出各行的数据for (int i = 0; i < list.size(); i++){user = (User) list.get(i);row = sheet.createRow(i + 2);// 创建第i+1行**for (int j = 0; j < columnMethods.length; j++){cell = row.createCell(j);// 创建第j列Method method;method = user.getClass().getMethod(columnMethods[j]); // 这里用到了反射机制，通过方法名来取得对应方法返回的结果对象if (method.toString().indexOf("getInviter") > 0){Object obj = method.invoke(user);if (obj == null){cell.setCellValue("无邀请人");}else{User u = userService.findById(obj.toString());cell.setCellValue(u == null ? "无邀请人" : u.getName() == null ? "邀请人无更新名字" : u    .getName());;}}else{Object obj = method.invoke(user);cell.setCellValue(obj != null ? obj.toString() : ""); // 往excel写数据}}}sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnMethods.length - 1));// 单元格合并**return workbook;}