JAVAWeb SSH框架 利用POI 导出EXCEL，弹出保存框

导入包这一些不多说，直接贴出关键代码，jsp只要点一个Action链接就行。

poi包我是用：poi-3.11-20141221.jar

亲测有效：

效果：

Action 类代码：

PRivate InputStream inputStream; //（get,set方法省略）定义一个输入流，用于接住在Service类生成的含有EXCEL的输入流

public String exportNetworkDeviceList() throws Exception { setInputStream(networkDeviceService.exportNetworkDeviceList(NET_STATUS, NET_MODEL_NUMBER, NET_BUILDING, NET_FLOOR, NET_LOCATION)); return "getNetworkDeviceExportList"; }

Service类代码：（生成EXCEL表格代码在Service类写）

public InputStream exportNetworkDeviceList(String netStatus, String netModelNumber, String netBuilding, String netFloor, String netLocation) { HSSFWorkbook wb = new HSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("表一"); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

　　//写列名，视自己的需求而定 HSSFCell cell = row.createCell(0); cell.setCellValue("设备型号"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("端口数"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("设备名称"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("状态"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("楼宇"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("楼层"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("位置"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("接口"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("ip地址"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("网关"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("备注"); cell.setCellStyle(style); //构造数据库查询语句，待会我用与从DAO类取数据，视自己的需求而定，个人建议将这一部分写在另一个方法里面 String hql = "from NetworkDevice "; if (netStatus == null) { } else { if (netStatus.equalsIgnoreCase("00")) { hql += "n where n.NET_STATUS!=null "; } else { hql += "n where n.NET_STATUS='" + netStatus + "' "; } ; if (!netModelNumber.isEmpty()) { hql += "AND n.NET_MODEL_NUMBER = '" + netModelNumber + "' "; } ; if (!netBuilding.isEmpty()) { hql += "AND n.NET_BUILDING = '" + netBuilding + "' "; } ; if (!netFloor.isEmpty()) { hql += "AND n.NET_FLOOR = '" + netFloor + "' "; } ; if (!netLocation.isEmpty()) { hql += "AND n.NET_LOCATION = '" + netLocation + "' "; } ; } hql += "order by 1 DESC"; // 第五步，写入实体数据 实际应用中这些数据从数据库得到， List<NetworkDevice> exportList = networkDeviceDaoImpl.exportNetworkDeviceList(hql); for (int i = 0; i < exportList.size(); i++) { row = sheet.createRow((int) i + 1); NetworkDevice netDevice = exportList.get(i); // 第四步，创建单元格，并设置值 row.createCell(0).setCellValue(netDevice.getNET_MODEL_NUMBER()); row.createCell(1).setCellValue(netDevice.getNET_DEVICE_PORT()); row.createCell(2).setCellValue(netDevice.getNET_DEVICE_NAME()); row.createCell(3).setCellValue(netDevice.getNET_STATUS()); row.createCell(4).setCellValue(netDevice.getNET_BUILDING()); row.createCell(5).setCellValue(netDevice.getNET_FLOOR()); row.createCell(6).setCellValue(netDevice.getNET_LOCATION()); row.createCell(7).setCellValue(netDevice.getNET_INTERFACE()); row.createCell(8).setCellValue(netDevice.getNET_IP()); row.createCell(9).setCellValue(netDevice.getNET_GATEWAY()); row.createCell(10).setCellValue(netDevice.getNET_REMARK()); } //自动设置EXCEL的列宽，视自己的需求而定，也可以用sheet.setDefaultColumnWidth(13);为全部列的列宽设置默认值 sheet.autoSizeColumn((short)0); sheet.autoSizeColumn((short)2); sheet.autoSizeColumn((short)6); sheet.autoSizeColumn((short)7); sheet.autoSizeColumn((short)8); sheet.autoSizeColumn((short)9); sheet.autoSizeColumn((short)10);

　　//设置文件名，用格式化日期来生成一个ID String filePath=""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "NetDevice" + date + ".xls"; File file=new File(filePath); try{ OutputStream out=new FileOutputStream(file); wb.write(out); out.close(); }catch(Exception e){ e.printStackTrace(); } InputStream in=null; try{ in=new FileInputStream(file); }catch(Exception e) { e.printStackTrace(); } return in; }

strust2代码：

<action name="ExportNetworkDeviceList" class="com.javaweb.action.NetworkDeviceAction" method="exportNetworkDeviceList"> <result name="getNetworkDeviceExportList" type="stream"> <param name="inputStream">excelStream</param> <param name="ContentType">application/vnd.ms-excel</param> <param name="contentDisposition">filename="NetDevice.xls"</param> </result> </action>

PS：据网友@puyans反馈，若strust2的代码写：<param name="inputStream">excelStream</param> 控制台会报：

Cannot create type class java.io.InputStream from value excelStream - [unknown location]

若出现这种情况，请改为：

<param name="inputStream">inputStream</param>