struts2结合poi导出excel（二）

先看效果图：

第一步：

第二步：点击导出内容到excel

第三步：点击上图的“选择”按钮

最后可以选择打开或者保存了。

实现方式如下：

页面设计：

<td width=”100″ align=”center”><a href=”#” onclick=”doExport();return false;”>导出内容到excel</a></td>

function doExport(){

var dataV=window.showModalDialog(“<c:out value=’${ctx}’/>/task/dialog.jsp”,”",”dialogWidth=250px;dialogHeight=220px”); if(dataV !=null && dataV != “”){ if(dataV == ’1′){ var eleForm = $(“pageQueryForm”); var lastAction = eleForm.action; var ExportAll = $(“ExportAll”); if(ExportAll != null){ eleForm.removeChild(ExportAll); } eleForm.action = “doExportExcel.action”; eleForm.submit(); eleForm.action = lastAction; return; }else if(dataV == ’2′){ var eleForm = $(“pageQueryForm”); var lastAction = eleForm.action; var eleHiddenExportExcel = new Element(“input”,{type:”hidden”,name:”ExportAll”}); eleHiddenExportExcel.wrap(eleForm); eleForm.action = “doExportExcel.action”; eleForm.submit(); eleForm.action = lastAction; return; } } }

Action：

两个属性：

String fileNames; InputStream excelStream;

提供get和set方法

public String doExport() throws TaException{

String result = “”; User user = (User) this.getSession().get(“user”);

if(user != null){

String ExportAll = this.getHttpServletRequest().getParameter(“ExportAll”);

List dataList = new ArrayList(); if(ExportAll == null){ log.debug(“导出当前页面内容到excel”); this.projectService.queryPageProject(user, pageQueryForm,customDisplayWhere,orderQueryString,orgList); dataList = pageQueryForm.getPageRecordList(); }else{ log.debug(“导出所有容到excel”); dataList = this.projectService.queryAllProject(user,pageQueryForm,customDisplayWhere,orderQueryString,orgList); }

HSSFWorkbook workbook = printExcel(tmpContent,tmpContentCn,dataList); if(workbook != null){ try{ 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; } this.exportExcel(workbook,year+month_+”"+day_+”sxta.xls”); result = “outExcel”; }catch(IOException e){ e.printStackTrace(); message = “出错了！”; redirectTo = this.getHttpServletRequest().getContextPath()+”/task/listProject.action”; return SUCCESS; } }

}else{

log.debug(“user is null”); message = “出错了！”; redirectTo = this.getHttpServletRequest().getContextPath()+”/task/listProject.action”; return SUCCESS;

}

return result;

}

private HSSFWorkbook printExcel(String tmpContent,String tmpContentCn,List dataList){ HSSFWorkbook workbook = null; String[] titles_CN = tmpContentCn.split(“,”); String[] titles_EN = tmpContent.split(“,”); try{ 创建工作簿实例 workbook = new HSSFWorkbook(); 创建工作表实例 HSSFSheet sheet = workbook.createSheet(“sxtaExcel”); 设置列宽 this.setSheetColumnWidth(titles_CN,sheet); 获取样式 HSSFCellStyle style = this.createTitleStyle(workbook); if(dataList != null){ 创建第一行标题 HSSFRow row = sheet.createRow((short)0);// 建立新行 for(int i=0;i<titles_CN.length;i++){ this.createCell(row, i, null, HSSFCell.CELL_TYPE_STRING, this.getText(titles_CN[i])); } 给excel填充数据 for(int i=0;i<dataList.size();i++){ 将dataList里面的数据取出来 Project project= (Project)dataList.get(i); HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行 boolean isOverTime = false; for(int j=0;j<titles_EN.length;j++){ String tmpstr = “”; if (titles_EN[j].equals(“name”)){ this.createCell(row1, j, style, HSSFCell.CELL_TYPE_STRING, project.getName()); }

……

} } }else{ this.createCell(sheet.createRow(0), 0, style,HSSFCell.CELL_TYPE_STRING, “查无资料”); } }catch(Exception e){ e.printStackTrace(); } return workbook; }

//设置列宽 private void setSheetColumnWidth(String[] titles_CN,HSSFSheet sheet){ 根据你数据里面的记录有多少列，就设置多少列 for(int i=0;i<titles_CN.length;i++){ sheet.setColumnWidth((short)i, (short) 3000); }

}
//设置excel的title样式 private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) { HSSFFont boldFont = wb.createFont(); boldFont.setFontHeight((short) 200); HSSFCellStyle style = wb.createCellStyle(); style.setFont(boldFont); style.setDataFormat(HSSFDataFormat.getBuiltinFormat(“###,##0.00″)); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillBackgroundColor(HSSFColor.LIGHT_ORANGE.index); return style; } //创建Excel单元格 private void createCell(HSSFRow row, int column, HSSFCellStyle style,int cellType,Object value) { HSSFCell cell = row.createCell((short) column); cell.setEncoding(HSSFCell.ENCODING_UTF_16); if (style != null) { cell.setCellStyle(style); } switch(cellType){ case HSSFCell.CELL_TYPE_BLANK: {} break; case HSSFCell.CELL_TYPE_STRING: {cell.setCellValue(value.toString()+”");} break; case HSSFCell.CELL_TYPE_NUMERIC: { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(value.toString()));}break; default: break; }

}

//写入输入流中

private void exportExcel(HSSFWorkbook workbook,String fileName) throws IOException{ fileNames =
fileName; ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); baos.flush(); byte[] aa = baos.toByteArray(); excelStream = new ByteArrayInputStream(aa, 0, aa.length); baos.close(); }

接下来，struts.xml中的配置：

<result name=”outExcel” type=”stream”> <param name=”contentType”>application/vnd.ms-excel</param> <!– 注意这里的ContentType –> <param name=”inputName”>excelStream</param> <param name=”contentDisposition”>attachment;filename=”${fileNames}”</param> <param name=”bufferSize”>1024</param> </result>

 

三个jar包：

OK