Java Excel文件导出功能

最近写了一个Excel导出功能，大体的设计思路如下：

1.模板   这里说的模板不文件虽然以前也那么做过，这里的模板是导出的表头以及相应实体中的参数名。我是把模板数据放到数据库了，可以根据自己的情况考虑放在配置文件或者其他地方。

2.规则 5000条为一个文件，当数据多余5000条时以压缩包的形式导出。

 

具体实现

压缩使用的是java.util.zip包，Excel使用的是POI

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

代码实现：

/*导出zip格式的压缩包,默认file是单独一个文件*/
    public void createZip(HttpServletResponse response,List<File> file,String fileName){
        ZipOutputStream zipOut = null;
        OutputStream outputStream = null;
        FileInputStream fileInputStream = null;
        try {
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", new StringBuilder("attachment;filename=").append(fileName).append(".zip")
                    .toString());
            outputStream = response.getOutputStream();
            zipOut = new ZipOutputStream(outputStream);
            if(file != null){
                for(int i = 0,length = file.size(); i < length; i++){
                    zipOut.putNextEntry(new ZipEntry(file.get(i).getName()));
                    fileInputStream = new FileInputStream(file.get(i));
                    int len = 0;
                    byte[] bytes = new byte[5*1024];
                    while ((len = fileInputStream.read(bytes)) != -1){
                        zipOut.write(bytes, 0, len);
                    }
                    zipOut.closeEntry();
                    fileInputStream.close();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(fileInputStream != null){
                    fileInputStream.close();
                }
                if(zipOut != null){
                    zipOut.close();
                }
                if(outputStream != null){
                    outputStream.close();
                }
                if(file != null){
                    for (int i = 0,length = file.size(); i < length; i++){
                       file.get(i).delete();
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

/*生成表格文件,第一个基础版本之后会改进。*/
    public File buildExcel(List<?> list, String exportFlag, String fileName) {
        String[] headerArr = null;
        String[] fieldArr = null;
        HSSFRow headerRow = null;
        File file = null;

        //创建一个workbook对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            //获取表头
            if(headersMap.get(exportFlag) == null){
                CodeTrans codeTrans = codeTransService.findCodeTrans("ExportExcelHeaders", exportFlag);
                headerArr = codeTrans.getCodeAlias().split(",");
                headersMap.put("exportFlag",headerArr);
            }else {
                headerArr = headersMap.get("exportFlag");
            }
            if(headerArr == null){
                throw new Exception("表头获取失败");
            }

            //获取实体标记
            if(fieldsMap.get(exportFlag) == null){
                CodeTrans codeTrans = codeTransService.findCodeTrans("ExportExcelFields", exportFlag);
                fieldArr = codeTrans.getCodeAlias().split(",");
                fieldsMap.put("exportFlag",fieldArr);
            }else {
                fieldArr = fieldsMap.get("exportFlag");
            }
            if(fieldArr == null){
                throw new Exception("实体字段获取失败");
            }

            //创建一个sheet
            HSSFSheet sheet = workbook.createSheet();
            //设置sheet的默认列宽度
            sheet.setDefaultColumnWidth(17);

            //设置sheet的头样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            //设置左右居中
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            //创建字号
            HSSFFont headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short)12);
            headerStyle.setFont(headerFont);

            //设置普通单元格样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            //添加表头
            headerRow = sheet.createRow(0);
            headerRow.setRowStyle(headerStyle);

            //构建表头
            buildHeaders(headerRow,headerArr,headerStyle);
            //构建数据
            buildFields(sheet,fieldArr,cellStyle,list,exportFlag);

            file = new File(fileName + ".xlsx");
            workbook.write(file);
        }catch (IOException e) {
            e.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(workbook != null){
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return file;
    }

    /*构建表头*/
    public void buildHeaders(HSSFRow row, String[] headerArr, HSSFCellStyle headerStyle){
        HSSFCell headCell = null;
        for(int i = 0, length = headerArr.length; i < length; i++){
            headCell = row.createCell(i);
            headCell.setCellValue(headerArr[i]);
            headCell.setCellStyle(headerStyle);
        }
    }

    /*构建Excel数据*/
    public void buildFields(HSSFSheet sheet, String[] fieldArr, HSSFCellStyle cellStyle, List<?> records,
                            String exportFlag){
        HSSFRow row = null;
        HSSFCell cell = null;
        String objString = null;
        ObjectMapper mapper = new ObjectMapper();
        JsonNode node = null;
        JsonNode cellNode = null;
        String fieldValue = null;
        for(int i = 0, length = records.size(); i < length; i++){
            //第0行是headers
            row = sheet.createRow(i+1);
            try {
                objString = mapper.writeValueAsString(records.get(i));
                node = mapper.readTree(objString);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            for(int j = 0, fieldlen = fieldArr.length; j < fieldlen ; j++){
                cellNode = node.path(fieldArr[j]);
                if(cellNode.isNull()){
                    fieldValue = "";
                }else{
                    if(cellNode.isTextual()){
                        fieldValue= cellNode.asText();
                    }else if(cellNode.isNumber()){
                        fieldValue = cellNode.decimalValue().toPlainString();
                    }
                }
                cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(fieldValue);
            }
        }
    }

/*导出规则5000条以内一个文件*/
    public void expotExcel(HttpServletResponse response, Map map) throws IOException {
        BigDecimal limits = new BigDecimal(5000);
        File file = null;
        List records = null;
        OutputStream outputStream = null;

        List list = (List)map.get("data");

        if(list != null && list.size() <= limits.intValue()){
            file = buildExcel(list, "XXXX", "excel");
            FileInputStream fileInputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition",new StringBuilder("attachment;filename=").append(file.getName())
                    .toString());

            BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
            byte[] bytes = new byte[5*1024];
            int len = 0;
            while ((len = fileInputStream.read(bytes)) != -1){
                bufferedOutputStream.write(bytes, 0, len);
            }
            if(fileInputStream != null){
                fileInputStream.close();
            }
            if(bufferedOutputStream != null){
                bufferedOutputStream.close();
            }
            if(outputStream != null){
                outputStream.close();
            }
            if(file != null){
                file.delete();
            }
        }else {
            BigDecimal listSize = new BigDecimal(list.size());
            double flag = Math.ceil(listSize.divide(limits).doubleValue());
            List<File> files = new ArrayList<>();
            for(int i = 0; i < flag; i++){
                file = null;
                records = null;
                if(i+1 == flag){
                    records = list.subList(i*5000, list.size());
                }else {
                    records = list.subList(i*5000, (i+1)*5000);
                }
                file = buildExcel(records, "XXXX", "excel" + i);
                files.add(file);
            }
            createZip(response, files, "name");
        }
    }

这里说一下ObjectMapper，使用的时候需要添加依赖

<dependency>
	<groupId>com.fasterxml.jackson.core</groupId>
	<artifactId>jackson-databind</artifactId>
	<version>2.9.4</version>
</dependency>

使用SpringBoot会将jackson-databind的3个依赖自动添加好，但是还是需要注意版本问题。高版本可能有些方法找不到哦。