Java Excel jxls

依赖

<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls</artifactId>
	<version>2.10.0</version>
</dependency>
<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls-poi</artifactId>
	<version>2.10.0</version>
</dependency>

<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls-reader</artifactId>
	<version>2.0.6</version>
</dependency>

DTO

public class ExcelDTO {
    private String key_1;
    private String key_2;
    private String key_3;
    private String key_4;
    private String key_5;
    private String key_6;
    private String key_7;
    private String key_8;
    private String key_9;
    private String key_10;

    public String getKey_1() {
        return key_1;
    }

    public void setKey_1(String key_1) {
        this.key_1 = key_1;
    }

    public String getKey_2() {
        return key_2;
    }

    public void setKey_2(String key_2) {
        this.key_2 = key_2;
    }

    public String getKey_3() {
        return key_3;
    }

    public void setKey_3(String key_3) {
        this.key_3 = key_3;
    }

    public String getKey_4() {
        return key_4;
    }

    public void setKey_4(String key_4) {
        this.key_4 = key_4;
    }

    public String getKey_5() {
        return key_5;
    }

    public void setKey_5(String key_5) {
        this.key_5 = key_5;
    }

    public String getKey_6() {
        return key_6;
    }

    public void setKey_6(String key_6) {
        this.key_6 = key_6;
    }

    public String getKey_7() {
        return key_7;
    }

    public void setKey_7(String key_7) {
        this.key_7 = key_7;
    }

    public String getKey_8() {
        return key_8;
    }

    public void setKey_8(String key_8) {
        this.key_8 = key_8;
    }

    public String getKey_9() {
        return key_9;
    }

    public void setKey_9(String key_9) {
        this.key_9 = key_9;
    }

    public String getKey_10() {
        return key_10;
    }

    public void setKey_10(String key_10) {
        this.key_10 = key_10;
    }

    @Override
    public String toString() {
        return "ExcelDTO{" +
                "key_1='" + key_1 + '\'' +
                ", key_2='" + key_2 + '\'' +
                ", key_3='" + key_3 + '\'' +
                ", key_4='" + key_4 + '\'' +
                ", key_5='" + key_5 + '\'' +
                ", key_6='" + key_6 + '\'' +
                ", key_7='" + key_7 + '\'' +
                ", key_8='" + key_8 + '\'' +
                ", key_9='" + key_9 + '\'' +
                ", key_10='" + key_10 + '\'' +
                '}';
    }
}

Utils

import org.jxls.common.Context;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;
import org.jxls.util.JxlsHelper;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.ObjectUtils;

import java.io.*;
import java.util.Map;

public class ExcelUtil {
    /**
     * 导出方法
     *
     * @param templatePath 模板路径
     * @param os           写入的输出流
     * @param model        数据模型
     */
    public static void export(String templatePath, OutputStream os, Map<String, Object> model) throws IOException {
        ClassPathResource cpr = new ClassPathResource(templatePath);
        InputStream is = cpr.getInputStream();
        Context context = new Context();
        // 填入数据
        if (!ObjectUtils.isEmpty(model)) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper.getInstance().processTemplate(is, os, context);
    }

    /**
     * 读取Excle数据到bean
     * @param readConfigXml 读取配置文件
     * @param inputExcelStream 输入的excle inputSream
     * @param beans 要封装数据的bean
     * @return
     * @throws Exception
     */
    public static boolean readExcelData(String readConfigXml, InputStream inputExcelStream, Map<String, Object> beans) throws Exception {
        ClassPathResource cpr = new ClassPathResource(readConfigXml);
        InputStream in = cpr.getInputStream();
        if (in == null) {
            throw new Exception("配置文件未找到：" + readConfigXml);
        }
        InputStream inputXML = new BufferedInputStream(in);
        XLSReader reader = ReaderBuilder.buildFromXML(inputXML);
        XLSReadStatus readStatus = reader.read(inputExcelStream, beans);
        return readStatus.isStatusOK();
    }

    /**
     * 读取Excle数据到bean
     * @param readConfigXml 读取配置文件
     * @param file 输入的excle文件
     * @param beans 要封装数据的bean
     * @return
     * @throws Exception
     */
    public static boolean readExcelData(String readConfigXml, File file, Map<String, Object> beans) throws Exception {
        if (file == null) {
            throw new Exception("Excel文件为空");
        }
        InputStream inputExcelStream = new FileInputStream(file);
        return readExcelData(readConfigXml, inputExcelStream, beans);
    }
}

使用

/**
 * Excel导出
 */
@GetMapping("/excelExport")
public void excelExport(HttpServletResponse response){
	try {
		String fileName = "测试";
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx");
		String templatePath = "/excel-templates/test.xlsx";
		OutputStream os = response.getOutputStream();
		Map<String, Object> model = new HashMap<>();
		model.put("exportTime", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
		model.put("name", "111");
		model.put("startDate", "2022-01-01");
		model.put("endDate", "2022-02-01");
		List<Object> list = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			Map<String,String> map = new HashMap<>();
			for (int j = 0; j < 10; j++) {
				map.put("key_"+(j+1),"value_"+(i+1)+"_"+(j+1));
			}
			list.add(map);
		}
		model.put("datas", list);
		ExcelUtil.export(templatePath, os, model);
	} catch (Exception e) {
		e.printStackTrace();
	}
}
/**
 * Excel导入
 */
@GetMapping("/excelImport")
public void excelImport(){
	try {
		String xmlPath = "/excel-templates/import-xml/test.xml";
		File file = new File("E:\\Projects\\bdc-bsdt-upload\\src\\main\\resources\\excel-templates\\import-templates\\test.xlsx");
		List<ExcelDTO> list = new ArrayList<>();
		Map<String,Object> map = new HashMap<>();
		map.put("datas",list);
		ExcelUtil.readExcelData(xmlPath,file,map);
		for (ExcelDTO dto:list) {
			System.out.println(dto.toString());
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}

js

function exportExcel(){
	this.exportBtnLoading = true;
	let beginDate = this.searchForm.dateRange ? this.searchForm.dateRange[0] : null;
	let endDate = this.searchForm.dateRange ? this.searchForm.dateRange[1] : null;
	let exportExcelHref = "/registerApply/exportDHHCExcel?version=" + Math.random()
		+ "&type=parent"
		+ "&dyQlrName=" + this.searchForm.dyqlr;
	if (this.searchForm.dateRange.length > 0) {
		exportExcelHref += "&beginDate=" + beginDate + "&endDate=" + endDate
	}

	window.location = exportExcelHref;
	this.exportBtnText = "文件下载中，30秒后可重试";
	let countdown = setInterval(() => {
		this.exportCount--;
		this.exportBtnText = `文件下载中，${this.exportCount}秒后可重试`;
		if (this.exportCount === 0) {
			this.exportBtnLoading = false;
			this.exportBtnText = "导出";
			this.exportCount = 30;
			clearInterval(countdown);
		}
	}, 1000);
}