亲宝软件园·资讯

展开

使用EasyPoi完成复杂一对多excel表格导出功能全过程

南独酌酒nvn 人气:0

业务需求

从一个简单的仓库业务说起,仓库业务,会有进库记录,会有出库记录,会有库存,客户的需求就是需要一个库存盘点单,盘点单通俗来讲:将库存中每个商品的出入库记录都统计出来,看看每个商品出过多少货物,入过多少货物,本月库存多少,上月库存多少。

需求难点

一个货物会出过多次货物,入过多次货物,导出的 excel 就要做成 一对多 格式的导出

简单举例:

啤酒:入库2次,出库3次,最终体现在 excel 中效果如下图:

通过 EasyPoi 实现需求

EasyPoi 文档地址:http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8

SpringBoot 使用:

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.2.0</version>
</dependency>

Gradle 使用:

implementation 'cn.afterturn:easypoi-base:4.2.0'
implementation 'cn.afterturn:easypoi-annotation:4.2.0'
implementation 'cn.afterturn:easypoi-web:4.2.0'

使用 EasyPoi 提供的注解,自定义导出类模板

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * 导出 excel 模板类
 */
@Getter
@Setter
public class ExportTemplate implements Serializable {
    @Excel(name = "序号", needMerge = true, type = 10)
    private int index;
    
    @Excel(name = "商品名称", needMerge = true, width = 30.0)
    private String goodName;
    
    @Excel(name = "商品单位", needMerge = true)
    private String goodUnit;
    
    @Excel(name = "上月库存数量", needMerge = true, type = 10)
    private Integer lastMonthSurplusNum;
    
    @Excel(name = "本月库存数量", needMerge = true, type = 10)
    private Integer thisMonthSurplusNum;
    
    @ExcelCollection(name = "本月入库信息")
    private List<GoodInItem> goodInItems;
    
    @ExcelCollection(name = "本月出库信息")
    private List<GoodOutItem> goodOutItems;
    
    @Excel(name = "备注", needMerge = true, width = 30.0)
    private String remark;

    /**
     * 入库信息
     */
    @Getter
    @Setter
    public static class GoodInItem {
        @Excel(name = "入库日期", exportFormat = "yyyy-MM-dd", width = 20.50)
        private Date purchaseDate;
        
        @Excel(name = "入库号", width = 25.50)
        private String purchaseNum;
        
        @Excel(name = "入库单价", type = 10)
        private BigDecimal unitPrice;
        
        @Excel(name = "入库数量", type = 10)
        private Integer totalNum;
    }

    /**
     * 出库信息
     */
    @Getter
    @Setter
    public static class GoodOutItem {
        @Excel(name = "出库日期", exportFormat = "yyyy-MM-dd", width = 20.50)
        private Date outDate;
        
        @Excel(name = "出库号", width = 25.50)
        private String sellNum;
        
        @Excel(name = "出库数量", type = 10)
        private Integer totalNum;
        
        @Excel(name = "成本金额", type = 10)
        private BigDecimal priceIn;
        
        @Excel(name = "销售金额", type = 10)
        private BigDecimal priceOut;
    }
}

实体类中使用的注解作用解释:

最后模板弄好之后,就可以通过easypoi 的工具类来导出,easypoi 推荐的导出工具类如下:

这个方法的三个参数表示含义解释:

具体实现

@GetMapping(value = "export")
public void export(HttpServletRequest req, HttpServletResponse resp) {
	List<ExportTemplate> exportData = new ArrayList();
	// 步骤1:构建要导出excel的数据集合
	for (int i = 0; i < 5; i++) {
		ExportTemplate data = new ExportTemplate();
        data.setIndex(i);
		data.setGoodName("测试商品");
		data.setGoodUnit("瓶");
		data.setLastMonthSurplusNum(5); // 上月库存
		data.setThisMonthSurplusNum(3); // 本月库存
		// ... 剩下的就是类似的加值
        exportData.add(data);
	}

	try {
		// 步骤2:开始导出 excel
		ExportParams params = new ExportParams();
		params.setTitle("库存盘点单标题");
		params.setSheetName("库存盘点单工作表名称");
		params.setType(ExcelType.XSSF);
		Workbook workbook = ExcelExportUtil.exportExcel(params, ExportTemplate.class, exportData);
		
		String nowStr = DateTimeFormatter.ofPattern(LocalDateTime.now()).format("yyyyMMddHHmm"); // 时间串
		String fileName = nowStr + "_库存盘点单"; // 文件名称
		String tempDir = "C:/Users/huxim/Downloads";
		File filePath = new File(tempDir + File.separator);
		if (!filePath.exists()) filePath.mkdirs(); // 如果文件目录不存在就创建这个目录

		FileOutputStream fos = new FileOutputStream(tempDir + File.separator + fileName);
		workbook.write(fos);
		fos.close();

		resp.setContentType("application/octet-stream");
		resp.setCharacterEncoding("utf-8");
		response.addHeader("Content-disposition", "attachment; filename="
                    + this.makeDownloadFileName(req, fileName));
		IOUtils.copy(new FileInputStream(tempFile), response.getOutputStream());
		System.out.println("导出成功~~~");
	} catch (Exception e) {
		throw new RuntimeException("导出 excel 失败~~~");
	}
}

/**
 * 判断是否是 IE 浏览器
 * 返回对应的字符串格式
 */
public static String makeDownloadFileName(HttpServletRequest request, String fileName) {
	String agent = request.getHeader("User-Agent");
    byte[] bytes = fileName.getBytes(StandardCharsets.UTF_8);
    if (agent.contains("MSIE") || agent.contains("Trident") || agent.contains("Edge")) {
    	// IE
        return new String(bytes, StandardCharsets.UTF_8);
	} else {
       	return new String(bytes, StandardCharsets.ISO_8859_1);
	}
}

导出成功后的excel 就类似于如下这种:

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

加载全部内容

相关教程
猜你喜欢
用户评论