Java使用jxl实现导出Excel表格功能

1.pom文件

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-jexl</artifactId>
<version>2.1.1</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>

2.java文件

package com.lib;

import cn.hutool.core.util.StrUtil;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static Workbook createExcel(Map<String, Object> dataParam, String templateFilePath) throws InvalidFormatException, FileNotFoundException {
        InputStream is = new FileInputStream(templateFilePath);
        XLSTransformer transformer = new XLSTransformer();
        Workbook workbook = transformer.transformXLS(is, dataParam);
        if (dataParam.get("sheetName") != null)
            workbook.setSheetName(0, dataParam.get("sheetName").toString());
        return workbook;
    }

    public static void writeExcel(Workbook workBook, String targetFilePath) throws IOException {
        OutputStream os = new FileOutputStream(targetFilePath);
        workBook.write(os);
        os.flush();
        os.close();
    }

    public static void createExcel(Map<String, Object> dataParam, String templateFilePath, String targetFilePath) throws InvalidFormatException, IOException {
        checkFileExtension(templateFilePath, targetFilePath);
        Workbook workbook = createExcel(dataParam, templateFilePath);
        writeExcel(workbook, targetFilePath);
    }

    private static void checkFileExtension(String templateFilePath, String targetFilePath) {
        if (!Objects.equals(FilenameUtils.getExtension(templateFilePath),
                FilenameUtils.getExtension(templateFilePath)))
            throw new IllegalArgumentException("模板后缀跟要保存的Excel文件后缀不一样");
    }

    public static String export(String excelName, Map<String, Object> excelParam) throws Exception {
        String path = ExcelUtil.class.getResource("/template/").getPath();
        // getResource对中文进行了编码，需要调用decode解码
        path = java.net.URLDecoder.decode(path, "utf-8");
        String fileNames = "";
        //模板全路径
        String templateFilePath = path + excelName + ".xlsx";

        //判断是否是windows
        boolean flag = System.getProperty("os.name").contains("Window");
        String fileName = UUID.randomUUID().toString();
        String targetFilePath = "";
        if (flag) {
            //获取系统临时目录
            targetFilePath = System.getProperty("java.io.tmpdir") + fileName + ".xlsx";
        } else {
            //如果是linux需要添加File.separator把/改为\
            targetFilePath = System.getProperty("java.io.tmpdir") + File.separator + fileName + ".xlsx";
        }

        //excelParam存放数据的map，templateFilePath模板全路径，targetFilePath存放临时路径
        createExcel(excelParam, templateFilePath, targetFilePath);
        System.out.println(targetFilePath);
        return targetFilePath;
    }

    public static void export(String path, String excelName, Map<String, Object> excelParam, HttpServletResponse response) throws Exception {
        try (ServletOutputStream fout = response.getOutputStream();) {
            path = StrUtil.replace(path, "\\", "/");
            logger.info("export path:[{}]", path);
            // getResource对中文进行了编码，需要调用decode解码
            path = java.net.URLDecoder.decode(path, "utf-8");
            String fileNames = "";
            //模板全路径
            String templateFilePath = path + excelName + ".xlsx";

            Workbook workbook = createExcel(excelParam, templateFilePath);

            // 1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            response.setContentType("application/msexcel");
            // 2.设置文件头：最后一个参数是设置下载文件名(假如我们叫a.pdf)
            String filename = new SimpleDateFormat("YYYYMMddHHmm").format(new Date().getTime()) + ".xlsx";
            response.setHeader("Content-Disposition", "attachment;fileName=" + filename);
            workbook.write(fout);    //保存Excel文件
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws InvalidFormatException, IOException {

        String path = ExcelUtil.class.getResource("/template/").getPath();
        // getResource对中文进行了编码，需要调用decode解码
        path = java.net.URLDecoder.decode(path, "utf-8");
        String fileNames = "";
        //模板全路径
        String templateFilePath = path + "kecheng" + ".xlsx";

        //判断是否是windows
        boolean flag = System.getProperty("os.name").contains("Window");
        String fileName = UUID.randomUUID().toString();
        String targetFilePath = "";
        if (flag) {
            //获取系统临时目录
            targetFilePath = System.getProperty("java.io.tmpdir") + fileName + ".xlsx";
        } else {
            //如果是linux需要添加File.separator把/改为\
            targetFilePath = System.getProperty("java.io.tmpdir") + File.separator + fileName + ".xlsx";
        }

        //模拟数据，一般项目当中都会以实体类来作为对象，这里我们用map为例，这个是用什么都可以的。
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Map<String, Object> map1 = new HashMap<String, Object>();
        map1.put("aaa", "asasdf");
        list.add(map1);
        //  list.add(map2);

        //将数据存入map当中
        Map<String, Object> excelParam = new HashMap<>();
        excelParam.put("aaa", "dsfas");

        //excelParam存放数据的map，templateFilePath模板全路径，targetFilePath存放临时路径
        createExcel(excelParam, templateFilePath, targetFilePath);
        System.out.println(targetFilePath);
    }
}

3.excel表格

4.测试结果

5.文章参考链接
a. https://blog.csdn.net/weixin_43888891/article/details/109438500