springboot + easyexcel 简单整合

这次要做一个springboot+easyexcel的整合，因为要处理大量数据，选择了easyexcel，进行了简单的整合。

 1、首先是导入POM依赖

        <!-- 阿里开源框架EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

        <!--fast-json 解析json-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>

2、实体类

这里的@ExcelIgnore，@ExcelProperty是easyexcel的常用注解，第一个是导入导出时忽略该字段，第二个里的index是排序。

package com.hc.store.model;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * 商品实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {

    /**
     * 测试EasyExcel
     * 实现excel表格导入数据库与数据库导出到excel表格
     */
    @ExcelIgnore
    private Integer id;
    @ExcelProperty(value = "分类id",index = 0)
    private Integer category_id;
    @ExcelProperty(value = "商品类型",index = 1)
    private String item_type;
    @ExcelProperty(value = "商品标题",index = 2)
    private String title;
    @ExcelProperty(value = "商品卖点",index = 3)
    private String sell_point;
    @ExcelProperty(value = "单价",index = 4)
    private Long price;
    @ExcelProperty(value = "库存数量",index = 5)
    private Integer num;
    @ExcelProperty(value = "图片路径",index = 6)
    private String image;
    @ExcelProperty(value = "商品状态",index = 7)
    private Integer status;
    @ExcelProperty(value = "优先级",index = 8)
    private Integer priority;
    @ExcelProperty(value = "创建人",index = 9)
    private String created_user;
    @ExcelProperty(value = "创建时间",index = 10)
    private Date created_time;
    @ExcelProperty(value = "最后修改人",index = 11)
    private String modified_user;
    @ExcelProperty(value = "最后修改时间",index = 12)
    private Date modified_time;
}

3、然后创建ExcelController，即easyexcel的控制类

里面规定了两个方法，一个是export，导出，一个是upload上传。

package com.hc.store.controller;

import com.hc.store.listener.ProductListener;
import com.hc.store.model.Product;
import com.hc.store.service.ProductService;
import com.hc.store.util.EasyExcelUtil;
import com.hc.store.util.JsonResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.UUID;

/**
 * 实现表格与数据库的输入输出
 */@Slf4j
@RestController
@RequestMapping("/excels")
public class ExcelController extends BaseController {

    @Autowired
    private ProductService productService;

    @RequestMapping("/export")
    public void export(HttpServletResponse response) {
        List<Product> products = productService.getProsList();

        try {
            //String excelName = UUID.randomUUID().toString().toUpperCase();
            EasyExcelUtil.export2Web(response,"导出测试","商品表",Product.class,products);
        } catch (Exception e) {
            log.info("报表导出异常：",e);
        }

    }

    @RequestMapping("/upload")
    public JsonResult<Void> upload(@RequestParam("excelFile") MultipartFile file, HttpServletRequest request) {
        log.info("-----------开始-----------");
        try {

            EasyExcelUtil.readExcel(file.getInputStream(),Product.class,new ProductListener(productService));

        } catch (Exception e) {
            log.info("报表导入异常：",e);
        }
        return new JsonResult<>(OK);
    }
}

4、创建自己的easyexcel工具类

我这个工具类只有两个方法，进行最简单的导入导出方法即可。

package com.hc.store.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.excel.support.ExcelTypeEnum;
import com.hc.store.controller.ex.FileUploadException;
import com.hc.store.listener.AbstractReadListener;


public class EasyExcelUtil {


    /**
     * 导出Excel(07版.xlsx)到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            excelName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

            EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
        } catch (Exception e) {
            throw new FileUploadException("导出Excel失败");
        }

    }

    public static <T> List<T> readExcel(InputStream is, Class<T> clazz,
                                        AbstractReadListener<T> listener) {
        ExcelReader read = EasyExcelFactory.read(is, clazz, listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        read.read(readSheet);
        read.finish();
        return listener.getData();
    }
}

5、这时候，运行/excels/export，就可以生成excel表了。

6、upload比较麻烦，首先创建一个监听器，编写通用的解析方法，先解析再插入。

首先是通用解析：

package com.hc.store.listener;

import com.alibaba.excel.context.AnalysisContext;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;


/**
 * @author chajintao
 * @date 2021/6/10 11:13
 */
@Slf4j
public abstract class AbstractReadListener<T> extends AnalysisEventListener<T> {

	private List<T> data = new ArrayList<>();

	//原子操作类
	private AtomicInteger index = new AtomicInteger(0);


	/**
	 * 每隔N条存储数据库，实际使用中可以30条，然后清理list ，方便内存回收
	 */
	private static final int BATCH_COUNT = 30;

	/**
	 * 继承该类后实现该方法
	 */
	abstract void saveData();


	@Override
	public void invoke(T t, AnalysisContext analysisContext) {
		int i = index.incrementAndGet();//+1操作
		log.info("解析第 {} 条数据成功", i);
		data.add(t);

		if (data.size() >= BATCH_COUNT) {
			saveData();
			data.clear();
		}


	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext analysisContext) {
		saveData();
		log.info("/*------- 当前sheet读取完毕");
		data.clear();//解析结束销毁不用的资源

	}

	public List<T> getData(){
		return data;
	}
}

然后是productListener，来调用插入方法：

package com.hc.store.listener;

import com.hc.store.model.Product;
import com.hc.store.service.ProductService;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ProductListener extends AbstractReadListener<Product> {

    private ProductService productService;


    public ProductListener(ProductService productService) {
        this.productService = productService;
    }



    @Override
    void saveData() {
        productService.saveBatch(getData());
        log.info("/*------- 写入数据 -------*/");
    }


}

7、再来是业务实现插入操作

void saveBatch(List<Product> list);

直接返回mapper，@Transactional是回滚操作，一旦有异常，就全部移除。 

    @Transactional
    @Override
    public void saveBatch(List<Product> list) {

        productMapper.saveBatch(list);
    }

8、接下来是SQL语句的实现。

mapper：

     //插入EasyExcel
    void saveBatch(List<Product> list);

 mapper.xml：

<insert id="saveBatch" parameterType="com.hc.store.model.Product" keyProperty="id" useGeneratedKeys="true">
        insert into product (category_id,item_type,title,sell_point,
                            price,num,image,status,priority,created_user,created_time,modified_user,
                            modified_time)
        values
            <foreach collection="list" separator="," item="product">
                (#{product.category_id},#{product.item_type},#{product.title},#{product.sell_point},
                #{product.price},#{product.num},#{product.image},#{product.status},#{product.priority},#{product.created_user},
                #{product.created_time},#{product.modified_user},#{product.modified_time})
            </foreach>
    </insert>

如果已经配置了login拦截器，在白名单中配置/excesl/**，就可以正常访问了。

最主要的是注解的使用：@ExcelProperty，还有监听器的配置，最好配置泛型T。