最近的项目有一个导出汇总数据的要求，考虑到用户软件的差异，所以要分别导出xls以及xlsx并且打包提供下载，所以有了这个博客，代码如下。
实现思路：
获取需要导出的数据；生成对应的xls、xlsx文件流；生成对应的zip文件，提供下载。
代码主要使用到了POI以及Hutool解压模块。
/*** 利用传入的数据，生成对应的xls文件* * @param data 需要导出的数据* @param sheetName sheet的名字* @param titleValue excel主题（例如第一行显示：“-10月销售汇总”）* @param headArr 标题* * @return 生成的03excel工作簿*/public static <T> HSSFWorkbook exportDataTo03(List<T> data, String sheetName, String titleValue, String[] headArr)throws IOException, IllegalArgumentException, IllegalAccessException {// 新建excelHSSFWorkbook wb = new HSSFWorkbook();// 新建sheetHSSFSheet sheet = wb.createSheet(sheetName);// 1.1创建合并单元格对象CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, headArr.length - 1);sheet.addMergedRegion(callRangeAddress);// 新建第一行表头HSSFRow titleRow = sheet.createRow(0);titleRow.setHeightInPoints(50);HSSFFont titleFont = wb.createFont();titleFont.setBold(true);titleFont.setFontName("微软雅黑");titleFont.setFontHeightInPoints((short) 35);// 样式HSSFCellStyle titleStyle = wb.createCellStyle();// 剧中titleStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);titleStyle.setFont(titleFont);HSSFCell titleCell = titleRow.createCell(0);titleCell.setCellStyle(titleStyle);titleCell.setCellValue(titleValue + "xxx");HSSFCellStyle hearStyle = wb.createCellStyle();hearStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);HSSFFont headFont = wb.createFont();headFont.setBold(true);headFont.setFontName("微软雅黑");headFont.setFontHeightInPoints((short) 50);HSSFRow headRow = sheet.createRow(1);headRow.setHeightInPoints(30);for (int i = 0; i < headArr.length; i++) {HSSFCell headCell = headRow.createCell(i);headCell.setCellStyle(hearStyle);headCell.setCellValue(headArr[i]);// TODO 这里要根据对应的列名设置对应的宽度，更美观一点。我这里偷懒直接所有的设置一样的sheet.setColumnWidth(i, 256 * 30);}HSSFCellStyle valueStyle = wb.createCellStyle();valueStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);HSSFFont valueFont = wb.createFont();valueFont.setFontName("微软雅黑");valueFont.setFontHeightInPoints((short) 15);valueStyle.setFont(valueFont);// 根据反射获取私有字段，与列名匹配，对应的话就插入对应数据for (int valueIndex = 0; valueIndex < data.size(); valueIndex++) {HSSFRow valueRow = sheet.createRow(valueIndex + 2);valueRow.setHeightInPoints(30);Class c = data.get(valueIndex).getClass();Field[] fields = c.getDeclaredFields();for (int i = 0; i < fields.length; i++) {NoExport amazonFiled = fields[i].getAnnotation(NoExport.class);if (amazonFiled != null && amazonFiled.value() == false) {continue;}fields[i].setAccessible(true);String fidldName = fields[i].getName();for (int j = 0; j < headArr.length; j++) {String headName = headArr[j];if (fidldName.trim().equalsIgnoreCase(headName.trim())) {HSSFCell valueCell = valueRow.createCell(j);valueCell.setCellStyle(valueStyle);valueCell.setCellValue(fields[i].get(data.get(valueIndex)) + "");break;}}}}return wb;}
/*** 利用传入的数据，生成对应的xlsx文件* * @param data 需要导出的数据* @param sheetName sheet的名字* @param titleValue excel主题（例如第一行显示：“-10月销售汇总”）* @param headArr 标题* * @return 生成的07excel工作簿*/public static <T> XSSFWorkbook exportDataTo07(List<T> data, String sheetName, String titleValue, String[] headArr)throws IOException, IllegalArgumentException, IllegalAccessException {// 新建excelXSSFWorkbook wb = new XSSFWorkbook();// 新建sheetXSSFSheet sheet = wb.createSheet(sheetName);// 1.1创建合并单元格对象CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, headArr.length - 1);sheet.addMergedRegion(callRangeAddress);// 新建第一行表头XSSFRow titleRow = sheet.createRow(0);titleRow.setHeightInPoints(50);XSSFFont titleFont = wb.createFont();titleFont.setBold(true);titleFont.setFontName("微软雅黑");titleFont.setFontHeightInPoints((short) 35);// 样式XSSFCellStyle titleStyle = wb.createCellStyle();// 剧中titleStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);titleStyle.setFont(titleFont);XSSFCell titleCell = titleRow.createCell(0);titleCell.setCellStyle(titleStyle);titleCell.setCellValue(titleValue + "xxx");XSSFCellStyle hearStyle = wb.createCellStyle();hearStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);XSSFFont headFont = wb.createFont();headFont.setBold(true);headFont.setFontName("微软雅黑");headFont.setFontHeightInPoints((short) 50);XSSFRow headRow = sheet.createRow(1);headRow.setHeightInPoints(30);for (int i = 0; i < headArr.length; i++) {XSSFCell headCell = headRow.createCell(i);headCell.setCellStyle(hearStyle);headCell.setCellValue(headArr[i]);sheet.setColumnWidth(i, 256 * 30);}XSSFCellStyle valueStyle = wb.createCellStyle();valueStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);XSSFFont valueFont = wb.createFont();valueFont.setFontName("微软雅黑");valueFont.setFontHeightInPoints((short) 15);valueStyle.setFont(valueFont);for (int valueIndex = 0; valueIndex < data.size(); valueIndex++) {XSSFRow valueRow = sheet.createRow(valueIndex + 2);valueRow.setHeightInPoints(30);Class c = data.get(valueIndex).getClass();Field[] fields = c.getDeclaredFields();for (int i = 0; i < fields.length; i++) {NoExport amazonFiled = fields[i].getAnnotation(NoExport.class);if (amazonFiled != null && amazonFiled.value() == false) {continue;}fields[i].setAccessible(true);String fidldName = fields[i].getName();for (int j = 0; j < headArr.length; j++) {String headName = headArr[j];if (fidldName.trim().equalsIgnoreCase(headName.trim())) {XSSFCell valueCell = valueRow.createCell(j);valueCell.setCellStyle(valueStyle);valueCell.setCellValue(fields[i].get(data.get(valueIndex)) + "");break;}}}}return wb;}
/*** 把传入的工作簿文件转成输入流* * @param workbooks 工作簿文件* @return 文件流list*/public static List<ByteArrayOutputStream> workBook2Stream(Workbook... workbooks) throws IOException {List<ByteArrayOutputStream> list = new LinkedList<ByteArrayOutputStream>();for (Workbook obj : workbooks) {ByteArrayOutputStream os = new ByteArrayOutputStream();obj.write(os);list.add(os);obj.close();}return list;}/*** 压缩文件* * @param workBook 生成的03工作簿* @param xworkWork 生成的07工作簿* @param fileNames 被压缩的文件名称* * @return File 生成的压缩包文件* * @throws IOException* */public static File zipFile(HSSFWorkbook workBook, XSSFWorkbook xworkWork, String[] fileNames) throws Exception {// 写入输入流List<ByteArrayOutputStream> workList = workBook2Stream(workBook, xworkWork);InputStream[] is = new InputStream[workList.size()];int step = 0;for (ByteArrayOutputStream bs : workList) {byte[] content = bs.toByteArray();ByteArrayInputStream bt = new ByteArrayInputStream(content);is[step] = bt;step++;}// 获取项目路径String rootPath = BusinessUtils.class.getClassLoader().getResource("").getPath();// 创建临时文件tempFileFile zipFile = new File(rootPath + "/tempFile.zip");if (!zipFile.exists()) {zipFile.createNewFile();}zipFile = ZipUtil.zip(zipFile, fileNames, is);// 关闭流for (ByteArrayOutputStream steam : workList) {IoUtil.close(steam);}for (InputStream steam : is) {IoUtil.close(steam);}return zipFile;}
下载：
File zipFile = BusinessUtils.zipFile(workBook, xworkWork, fileNames);String downloadFileName = URLEncoder.encode("XX商户XX公司XXXX报税单销售汇总信息.zip", "UTF-8");response.setHeader("content-type", "application/octet-stream");response.setContentType("application/x-execl");response.setHeader("Content-Disposition","attachment;filename=" + new String((downloadFileName).getBytes(), "UTF-8"));// 读取文件InputStream in = new FileInputStream(zipFile);ServletOutputStream outputStream = response.getOutputStream();// 写文件int b;while ((b = in.read()) != -1) {outputStream.write(b);}in.close();outputStream.close();//删除临时文件zipFile.delete();
没了。
效果如下：
