JXLS快速实现报表输出

谁践踏了优雅 2024-03-24 15:32 51阅读 0赞

**目录**

制作报表模板

编写文件保护的工具类

准备数据和导出

--------------------

JXLS是基于POI实现的报表导出功能。

[JXLS https://jxls.sourceforge.net/][JXLS https_jxls.sourceforge.net]

目前最新版本是：Jxls 2.12.0 !

源码仓库： [GitHub - jxlsteam/jxls: Java library for creating Excel reports using Excel templates][GitHub - jxlsteam_jxls_ Java library for creating Excel reports using Excel templates]

### 制作报表模板 ###

采用标注进行识别，设置表头和列的标签值以及迭代循环处理。

![60f8a6b588794828b7c8c7ca81dba77e.png][]

### 编写文件保护的工具类 ###

原理是在WorkBook.write写入文件流之前设置Sheet的保护机制。

package com.boonya.jxls.examples.utils;
    
    import cn.hutool.core.io.FileUtil;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.jxls.common.Context;
    import org.jxls.transform.Transformer;
    import org.jxls.transform.poi.PoiTransformer;
    import org.jxls.util.JxlsHelper;
    import org.springframework.http.MediaType;
    import org.springframework.util.StringUtils;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.nio.charset.StandardCharsets;
    import java.util.Iterator;
    import java.util.Map;
    
    /**
     * @author Pengjunlin
     * @date 2023/3/17
     */
    public class JxlsUtils {
    
        public static final String PASSWORD = "888888";
    
        private static String getFileName(String fileName){
            if(StringUtils.isEmpty(fileName)){
                return "未命名数据导出.xlsx";
            }
            if(!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")){
                fileName = fileName+ ".xlsx";
            }
            return fileName;
        }
    
        /**
         * 导出到默认路径
         *
         * @param model
         * @param response
         */
        public static void export(String template, String fileUploadPath,String fileName,Map<String, Object> model, HttpServletResponse response) {
            // 设置系统对应的临时路径
            fileName = getFileName(fileName);
            fileUploadPath = fileUploadPath + fileName;
            try (OutputStream os = new FileOutputStream(fileUploadPath)) {
                // 导出到本地路径
                exportToLocalPath(template,null, os, model);
                // 响应下载到客户端
                exportToClient(response, fileUploadPath, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 导出Excel文件
         *
         * @param template
         * @param password
         * @param fileUploadPath
         * @param fileName
         * @param model
         * @param response
         */
        public static void export(String template,String password,String fileUploadPath,String fileName, Map<String, Object> model, HttpServletResponse response) {
            // 设置系统对应的临时路径
            fileName = getFileName(fileName);
            fileUploadPath = fileUploadPath + fileName;
            try (OutputStream os = new FileOutputStream(fileUploadPath)) {
                // 导出到本地路径
                exportToLocalPath(template,password, os, model);
                // 响应下载到客户端
                exportToClient(response, fileUploadPath, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * Excel导出可编辑
         *
         * @param template
         * @param fileUploadPath
         * @param fileName
         * @param model
         * @param response
         * @throws IOException
         */
        public static void exportByEditable(String template,String fileUploadPath, String fileName, Map<String, Object> model, HttpServletResponse response) throws IOException {
            export(template,null,fileUploadPath,fileName,model,response);
        }
    
        /**
         * Excel导出只读
         *
         * @param template
         * @param fileUploadPath
         * @param fileName
         * @param model
         * @param response
         * @throws IOException
         */
        public static void exportByReadonly(String template,String fileUploadPath, String fileName, Map<String, Object> model, HttpServletResponse response) throws IOException {
            export(template,PASSWORD,fileUploadPath,fileName,model,response);
        }
    
    
        /**
         * 导出文件到客户端
         *
         * @param response
         * @param filePath
         * @param outputFileWithExt
         * @throws IOException
         */
        public static void exportToClient(HttpServletResponse response, String filePath, String outputFileWithExt) throws IOException {
            File file = new File(filePath);
            try {
                while (!file.canRead()){
                    Thread.sleep(10);
                }
                // 设置response的Header
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(outputFileWithExt, StandardCharsets.UTF_8.displayName()));
                try (ServletOutputStream servletOutputStream = response.getOutputStream()) {
                    FileUtil.writeToStream(file, servletOutputStream);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                FileUtil.del(file);
            }
        }
    
        /**
         * 导出Excel到本地路径
         *
         * @param template
         * @param password
         * @param os
         * @param model
         * @throws IOException
         */
        public static void exportToLocalPath(String template,String password, OutputStream os, Map<String, Object> model) throws IOException {
            exportExcel(password,JxlsUtils.class.getClassLoader().getResourceAsStream(template),os,model);
        }
    
        /**
         * 导出Excel自定义
         *
         * @param password
         * @param is
         * @param os
         * @param model
         * @throws IOException
         */
        public static void exportExcel(String password,InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
            Context context = new Context();
            if (model != null) {
                for (String key : model.keySet()) {
                    context.putVar(key, model.get(key));
                }
            }
            JxlsHelper jxlsHelper = JxlsHelper.getInstance();
            Transformer transformer = jxlsHelper.createTransformer(is, os);
            PoiTransformer poiTransformer = (PoiTransformer)transformer;
            // 设置是否只读
            if(!StringUtils.isEmpty(password)){
                Iterator<Sheet> sheetIterator = poiTransformer.getWorkbook().sheetIterator();
                while (sheetIterator.hasNext()){
                    Sheet sheet = sheetIterator.next();
                    if(null != sheet){
                        sheet.protectSheet(password);
                    }
                }
            }
            poiTransformer.getTransformationConfig().getExpressionEvaluator();
            jxlsHelper.processTemplate(context, poiTransformer);
        }
    }

### 准备数据和导出 ###

一个简单的Controller即可

@RestController
    @RequestMapping(value = "/export")
    @AllArgsConstructor
    public class ExportController {
    
        private final ExcelService excelService;
    
        @GetMapping(value = "/quoteList")
        @ResponseBody
        public void export(HttpServletRequest request, HttpServletResponse response){
            String filePath = excelService.getTempPath();
            QuoteDataSummary summary = JMockData.mock(QuoteDataSummary.class);
            summary.totalCount();
            Map<String, Object> model = new ConcurrentHashMap<>();
            model.put("header", summary.getHeader());
            model.put("contractData", summary.getRoomList());
            model.put("ungroupData", summary.getUngroupList());
            model.put("groupData", summary.getGroupList());
            try {
                JxlsUtils.exportByReadonly("quoteList.xlsx",filePath,"导出报价清单.xls",model,response);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

导出效果：

![44069eab6f034d039196c71a8afb5221.png][]

可以参考：[Java 导出Excel利器 JXLS（excel模板配置教程）\_HadryChen的博客-CSDN博客][Java _Excel_ JXLS_excel_HadryChen_-CSDN]

当然我已为大家提供了全部的示例源码： [https://gitee.com/researchOfDaily/k8s-demo][https_gitee.com_researchOfDaily_k8s-demo]

低版本的还支持JXLS标签：[关于jxls的层级循环研究 - 辜圆圆的个人空间 - OSCHINA - 中文开源技术交流社区][jxls_ - _ - OSCHINA -]

[JXLS https_jxls.sourceforge.net]: https://jxls.sourceforge.net/
[GitHub - jxlsteam_jxls_ Java library for creating Excel reports using Excel templates]: https://github.com/jxlsteam/jxls
[60f8a6b588794828b7c8c7ca81dba77e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/a50c8d1d2ec14e74a916ebaa86ea7a14.png
[44069eab6f034d039196c71a8afb5221.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3d7f2ede097e4efd85d10f8baadb4a95.png
[Java _Excel_ JXLS_excel_HadryChen_-CSDN]: https://blog.csdn.net/baidu_14958441/article/details/128528237
[https_gitee.com_researchOfDaily_k8s-demo]: https://gitee.com/researchOfDaily/k8s-demo
[jxls_ - _ - OSCHINA -]: https://my.oschina.net/xlpapapa/blog/887985