JXLS快速实现报表输出

boonya

已于 2023-04-23 09:59:03 修改

阅读量674

点赞数

分类专栏：报表管理文章标签： excel jxls java

于 2023-04-06 17:40:16 首次发布

本文链接：https://blog.csdn.net/boonya/article/details/129994855

版权

报表管理专栏收录该内容

2 篇文章 0 订阅

订阅专栏

制作报表模板

编写文件保护的工具类

准备数据和导出

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

JXLS https://jxls.sourceforge.net/

目前最新版本是：Jxls 2.12.0 !

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

制作报表模板

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

编写文件保护的工具类

原理是在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();
        }
    }
}

导出效果：