Apache POI -- Java 导出Excel文档（笔记）
一、Util类
import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.List; import javax.swing.JOptionPane; import net.evecom.fjzw.vo.report.export.AreaDeviceFlowStatistical; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.hssf.util.Region; /** * Excel导出通用工具类 * * @author Miles Ma * @version 1.0 */ public class ExportExcelTool<T> { public HSSFWorkbook getWorkBook(String title, String subTitle, String[] headers, Collection<T> dataset) { // 创建一个工作薄和一个工作表 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("工作表"); sheet.setDefaultColumnWidth((short) 20); // 生成并设置样式 HSSFCellStyle titleStyle = setTitleStyle(workbook);// 标题单元格样式 HSSFFont subTitleFont = setSubTitleFont(workbook);// 子标题字体 HSSFCellStyle headerStyle = setHeaderStyle(workbook);// 列名单元格样式 HSSFCellStyle otherStyle = setOtherStyle(workbook);// 其他单元格样式 // 生成报表名称 HSSFRow titleRow = sheet.createRow(0); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));// 指定合并区域 HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints((short) 42);// 行高 titleCell.setCellStyle(titleStyle);// 设置样式 HSSFRichTextString titleText = new HSSFRichTextString(title + "\r\n" + subTitle); titleText.applyFont(title.length(), (title + "\r\n" + subTitle).length(), subTitleFont); titleCell.setCellValue(titleText);// 设置内容 // 产生表格标题行 HSSFRow headerRow = sheet.createRow(1); for (short i = 0; i < headers.length; i++) { HSSFCell cell = headerRow.createCell(i); cell.setCellStyle(headerStyle); HSSFRichTextString headerText = new HSSFRichTextString(headers[i]); cell.setCellValue(headerText); } HSSFRow dataRow = null; // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 1;// 从第三行开始 while (it.hasNext()) { index++; dataRow = sheet.createRow(index); T t = (T) it.next(); // 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) { HSSFCell cell = dataRow.createCell(i); cell.setCellStyle(otherStyle); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Method getMethod = t.getClass().getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); String textValue = null; if (value != null) { if (value instanceof Double) {// 判断值的类型后进行强制类型转换 double doubleValue = (Double) value; cell.setCellValue(doubleValue); } else { textValue = value.toString();// 其它数据类型都当作字符串简单处理 } if (textValue != null) { HSSFRichTextString richString = new HSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } return workbook; } // 标题单元格样式 private HSSFCellStyle setTitleStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); // 字体 HSSFFont font = workbook.createFont(); font.setFontName("宋体");// 字体名称 font.setFontHeightInPoints((short) 18);// 字体大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 style.setFont(font); // 样式 style.setWrapText(true);// 自动换行 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } // 子标题字体 private HSSFFont setSubTitleFont(HSSFWorkbook workbook) { HSSFFont font = workbook.createFont(); font.setFontName("Times New Roman");// 字体名称 font.setFontHeightInPoints((short) 14);// 字体大小 return font; } // 列名单元格样式 private HSSFCellStyle setHeaderStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); // 字体 HSSFFont font = workbook.createFont(); font.setFontName("Times New Roman");// 字体名称 font.setFontHeightInPoints((short) 11);// 字体大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 style.setFont(font); // 样式 style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } // 其他单元格样式 private HSSFCellStyle setOtherStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); // 字体 HSSFFont font = workbook.createFont(); font.setFontName("Times New Roman");// 字体名称 font.setFontHeightInPoints((short) 11);// 字体大小 style.setFont(font); // 样式 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } public static void main(String[] args) { ExportExcelTool<AreaDeviceFlowStatistical> tool = new ExportExcelTool<AreaDeviceFlowStatistical>(); String[] headers = { "区域名称", "设备名称", "主机名", "总流入流量", "总流出流量", "平均流入流量", "平均流出流量", "最大流入流量", "最大流出流量" }; List<AreaDeviceFlowStatistical> dataset = new ArrayList<AreaDeviceFlowStatistical>(); // dataset.add(new AreaDeviceFlowStatistical("福州市", "", "10.1.6.21", // null, 25.21, 25.21, 25.21, 25.21, 25.21)); // dataset.add(new AreaDeviceFlowStatistical("厦门市", "李四", "10.1.6.23", // 25.23, 25.23, 25.23, 25.23, 25.23, 25.23)); // dataset.add(new AreaDeviceFlowStatistical("泉州市", "王五", "10.1.6.25", // 25.25, 25.25, 25.25, 25.25, 25.25, 25.25)); OutputStream out = null; try { out = new FileOutputStream("E://流量.xls"); String title = "省级设备流量报表"; String subTitle = "2015年12月1日-2015年12月31日"; HSSFWorkbook workBook = tool.getWorkBook(title, subTitle, headers, dataset); try { workBook.write(out);// 将workBook写入输出流 } catch (IOException e) { e.printStackTrace(); } try { out.close(); } catch (IOException e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } JOptionPane.showMessageDialog(null, "导出成功!"); System.out.println("excel导出成功！"); } }
二、Action类
/** * 导出Excel文件 * * @author Miles Ma * @version 1.0 */ public class ExportExcelAction extends DispatchAction { /** 日志 */ Logger log = Logger.getLogger(this.getClass()); /** NodeDao */ private NodeDao nodeDao = ConfigurationDaoFactory.getNodeDao(); /** InterfaceDao */ private InterfaceDao interfaceDao = ConfigurationDaoFactory.getInterfaceDao(); /** 日期格式 */ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); public ActionForward exportCityFlow(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { String startTime = RequestUtils.getStringParameter(request, "startTime_export", null); String endTime = RequestUtils.getStringParameter(request, "endTime_export", null); List<AreaDeviceFlowStatistical> resultlist = new ArrayList<AreaDeviceFlowStatistical>(); try { if (startTime == null || endTime == null) { endTime = sdf.format(new Date()); startTime = sdf.format(new Date(System.currentTimeMillis() - (86400000 * 7))); } StringBuilder nodeSql = new StringBuilder(); nodeSql.append(" select r.* from ( "); nodeSql.append(" select n.*, g.name area_name "); nodeSql.append(" from sv_nodes n left join sv_groups g on n.area_id = g.id "); nodeSql.append(" where g.parent_id = 1 or g.name like '%平潭%' "); nodeSql.append(" ) r where r.node_type = 1 "); nodeSql.append(" order by r.area_id "); List<VoNode> nodeList = nodeDao.getNodesByNativeSql(nodeSql.toString()); List<NodeInterface> ifList = new ArrayList<NodeInterface>(); AreaDeviceFlowStatistical deviceFlow = null; String snmpifalias = null; for (int i = 0; i < nodeList.size(); i++) { snmpifalias = "";// 重置 deviceFlow = new AreaDeviceFlowStatistical(); String ifSql = " select * from sv_node_interfaces where up_linked = 1 and node_id = " + nodeList.get(i).getId() + " order by ifnumber "; ifList = interfaceDao.getIfByNativeSql(ifSql); for (int j = 0; j < (ifList.size() - 1); j++) { snmpifalias += ("'" + ifList.get(j).getSnmpIfalias() + "'") + ","; } if (ifList.size() > 0) { snmpifalias += "'" + ifList.get(ifList.size() - 1).getSnmpIfalias() + "'"; } FjzwReportMgr reportMgr = new FjzwReportMgr(); Map<String, Double> nodeTraffic = reportMgr.getTotalNodeTraffic(nodeList.get(i).getId(), snmpifalias, ifList.size(), FormatUtil.parseDate(startTime), FormatUtil.parseDate(endTime)); deviceFlow.setAreaName(nodeList.get(i).getAreaName()); deviceFlow.setDeviceName(nodeList.get(i).getNodeName()); deviceFlow.setHostName(nodeList.get(i).getIpHostName()); deviceFlow.setTotalNodeInOctet(nodeTraffic.get("IFINOCTET")); deviceFlow.setTotalNodeOutOctet(nodeTraffic.get("IFOUTOCTET")); deviceFlow.setAvgNodeInOctet(nodeTraffic.get("AVG_IFINOCTET")); deviceFlow.setAvgNodeOutOctet(nodeTraffic.get("AVG_IFOUTOCTET")); deviceFlow.setMaxNodeInOctet(nodeTraffic.get("MAX_IFINOCTET")); deviceFlow.setMaxNodeOutOctet(nodeTraffic.get("MAX_IFOUTOCTET")); resultlist.add(deviceFlow); } } catch (Exception e) { log.error("export excel error !", e); request.setAttribute("message", "对不起，没有获取任何有效的采样数据"); return mapping.findForward("errMsg"); } /** 将设置好的workBook对象写入输出流（生成Excel文档） */ ExportExcelTool<AreaDeviceFlowStatistical> tool = new ExportExcelTool<AreaDeviceFlowStatistical>(); String title = "市级设备流量统计报表"; String startYear = startTime.split(" ")[0].split("-")[0]; String startMonth = startTime.split(" ")[0].split("-")[1]; String startDay = startTime.split(" ")[0].split("-")[2]; String endYear = endTime.split(" ")[0].split("-")[0]; String endMonth = endTime.split(" ")[0].split("-")[1]; String endDay = endTime.split(" ")[0].split("-")[2]; String subTitle = startYear + "年" + startMonth + "月" + startDay + "日" + "-" + endYear + "年" + endMonth + "月" + endDay + "日"; String[] headers = { "区域名称", "设备名称", "主机名", "总流入流量(bit)", "总流出流量(bit)", "平均流入流量(bit/s)", "平均流出流量(bit/s)", "最大流入流量(bit/s)", "最大流出流量(bit/s)" }; HSSFWorkbook workBook = tool.getWorkBook(title, subTitle, headers, resultlist); OutputStream out = null; String fileName = "市级设备流量统计报表.xls"; String filePath = request.getSession().getServletContext().getRealPath("/") + fileName; try {// 新建文件输出流 out = new FileOutputStream(filePath); try {// 将workBook对象写入文件输出流 workBook.write(out); } catch (IOException e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } finally { try { out.close();// 写入后关闭文件输出流 } catch (IOException e) { e.printStackTrace(); } } /** 下载Excel文档 */ this.setResponseHeader(response, fileName);// 设置响应头和文件名称 OutputStream output = null; FileInputStream fis = null; try { output = response.getOutputStream();// 获取相应输出流 fis = new FileInputStream(filePath);// 创建文件输入流 byte[] b = new byte[2048]; int i = 0; try { while ((i = fis.read(b)) > 0) { output.write(b, 0, i);// 将输入流的内容写入输出流 } } catch (IOException e) { e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } finally {// 关闭流 try { output.flush();// 强制将输出流中的内容写入到文件中并且清空输出流中的内容 fis.close(); output.close(); } catch (IOException e) { e.printStackTrace(); } } return null; } /** 设置响应头 */ public void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(), "ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } }
