Apache POI实现数据的Excel导出

POI实现导出数据到excel--------------------------以前也没做过数据导出，公司项目需要就去学习了一下，可能做的不好，然后公司同事直接扔给我一个封装好的帮助类，很方便，不过还是建议大家学习了解一下原理，市面上有很多流行的导出Excle方法，我这个是 Apache poi

先把jar包下载下来，https://mvnrepository.com/search?q=poi  把第一个和第二个都copy到pom.xml

具体的不多说了，注释写的比较清楚

// Controller方法

@RequestMapping("IntoProductByMeExportExcel.do")    public void IntoProductByMeExportExcel(HttpSession session, HttpServletResponse response,ProductRecord productRecord,ProductWarehouseManage productWarehouseManage,MaterialRecord materialRecord,MaterialWarehouseManage materialWarehouseManage, SpareWarehouseManage spareWarehouseManage,SpareRecord spareRecord ,String bwhinTime, String ewhinTime) {        // 封装参数        User user = (User)session.getAttribute("userSession");//得到登录session        Map<String,Object>  map=StringUtil.objectToMap(productRecord);        String st = StringUtil.IN_STATUS;//默认入库记录        map.put("status",st);//入库        map.put("bwhinTime", TimeUtil.BEGIN_TIME(bwhinTime));        map.put("ewhinTime",TimeUtil.BEGIN_TIME(ewhinTime));        List<ProductRecord> date = productRecordService.queryProductRecordAll(map);//获取数据集        Map<String, String> headMap = new HashMap<String, String>();// 获取属性-列头        headMap.put("id", "id");        headMap.put("internal_tag", "内部追溯号");        headMap.put("productNo", "零件存货编号");        headMap.put("number", "数量");        headMap.put("truckNumber", "送货车牌号");        headMap.put("forklift", "叉车员");        headMap.put("whInTime", "入库日期 ");        headMap.put("location", "货位编号");        headMap.put("outTime", "出库日期");        headMap.put("inoutOperator", "出入库操作人");        headMap.put("status", "状态区分");        headMap.put("companyFlag", "公司区分");        headMap.put("operator", "操作人");        headMap.put("operatingTime", "操作时间");        headMap.put("flag", "有效无效");        String title = "成品"+productRecord.getStatus()+"记录单";        String condition = "";        condition = condition + "审批人：" +  user.getUsername()+ "";        String message = "导出条件：(" + condition + ")；导出人：(" + user.getUsername() + ")；导出时间：("                + OAUtil.getNowTime() + ")";        ExportExcelUtil.downloadExcelFile("成品"+productRecord.getStatus()+"记录单", title, message, headMap, date, response);    }

//导出方法 ExoportExcelUtil.java

public static void exportExcelX(String title, String message, Map<String, String> headMap, JSONArray jsonArray,String datePattern, int colWidth, OutputStream out) {// 声明一个工作薄,对应一个Excel文件SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 内存存储数据大小workbook.setCompressTempFiles(true);// 设置临时文件是否应该被压缩// 表头样式CellStyle titleStyle = workbook.createCellStyle();// 创建一个新的单元格样式,并将其添加到工作簿的样式表// titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中对齐Font titleFont = workbook.createFont();// 创建一个新的字体,并将它添加到工作簿的字体表titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 宽度titleStyle.setFont(titleFont);// 列头样式CellStyle headerStyle = workbook.createCellStyle();headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置左右居中对齐Font headerFont = workbook.createFont();headerFont.setFontHeightInPoints((short) 12);headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);headerStyle.setFont(headerFont);// 单元格样式CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 单元格内容水平居中对齐// cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);Font cellFont = workbook.createFont();cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);cellStyle.setFont(cellFont);// 生成一个(带标题)表格,对应Excel中的一个sheetSheet sheet = workbook.createSheet(title);// 设置列宽int minBytes = colWidth;int[] arrColWidth = new int[headMap.size()];// 列头大小为数组长度// 产生表格标题行,以及设置列宽String[] properties = new String[headMap.size()];// 标题所占宽度与列头相同，居中与列头String[] headers = new String[headMap.size()];// 列头int ii = 0;// 遍历列头for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {String fieldName = iter.next();// 列头信息名字properties[ii] = fieldName;// 列头信息名字headers[ii] = headMap.get(fieldName);// 列头信息名字int bytes = fieldName.getBytes().length;// 列头每列信息长度arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;sheet.setColumnWidth(ii, arrColWidth[ii] * 256);ii++;}// 遍历集合数据，产生数据行int rowIndex = 0;for (Object obj : jsonArray) {if (rowIndex == 65535 || rowIndex == 0) {if (rowIndex != 0)sheet = workbook.createSheet();// 如果数据超过了，则在第二页显示Row titleRow = sheet.createRow(0);// 表头 rowIndex=0titleRow.createCell(0).setCellValue(message);// 产生标题,并设置标题内容titleRow.getCell(0).setCellStyle(titleStyle);// 设置标题样式// CellRangeAddress(起始行号，终止行号，// 起始列号，终止列号):(cell单元格,range范围,address地址)sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格Row headerRow = sheet.createRow(1); // 列头 rowIndex =1for (int i = 0; i < headers.length; i++) {headerRow.createCell(i).setCellValue(headers[i]);headerRow.getCell(i).setCellStyle(headerStyle);}rowIndex = 2;// 数据内容从 rowIndex=2开始}JSONObject jo = (JSONObject) JSONObject.toJSON(obj);// json数据转换成对象Row dataRow = sheet.createRow(rowIndex);// 创建第rowIndex行for (int i = 0; i < properties.length; i++) {Cell newCell = dataRow.createCell(i);// 创建单元格Object o = jo.get(properties[i]);// 得到第rowIndex的第i列单元格的值String cellValue = "";if (o == null)cellValue = "";else if (o instanceof Date)cellValue = new SimpleDateFormat(datePattern).format(o);else if (o instanceof Float || o instanceof Double)cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();elsecellValue = o.toString();// System.out.println(cellValue);newCell.setCellValue(cellValue);newCell.setCellStyle(cellStyle);}rowIndex++;}// 自动调整宽度/* * for (int i = 0; i < headers.length; i++) { * sheet.setColumnWidth(i,headers[i].getBytes().length*2*256); } */try {workbook.write(out);out.close();} catch (IOException e) {e.printStackTrace();}}