便捷开发——EXCEL批量导入导出

导出模板功能

1.映射实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

@Data
@ColumnWidth(40)
public class ElectricMeterSettingImportDro {

    @ExcelProperty(value = "考场名", index = 0)
    private String examPointName;
    @ExcelProperty(value = "考场IP：端口号（比如192.168.0.1:1234）", index = 1)
    private String electricMeterIp;

    }

2.controller层

 /**
     * 下载模板文件接口
     */
    @GetMapping("/electric-meter-setting/download")
    public void download(HttpServletResponse response) throws IOException {
        electricMeterSettingService.download(response);
    }

3.service层

 public void download(HttpServletResponse response) throws IOException {
        String fileName = "电表参数设置导入模板";
        ArrayList<ElectricMeterSettingImportDro> electricMeterSettingImportDros = new ArrayList<>();
        ServletOutputStream outputStream = EasyExcelUtil.getServletOutputStream(response, fileName);
        EasyExcelUtil.writeExcelWithModel(outputStream, fileName, electricMeterSettingImportDros, ElectricMeterSettingImportDro.class);
    }

4.EasyExcel工具类

package com.visualization.common.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.visualization.business.guaranteeSystem.service.AnalysisEventListenerImpl;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

//import org.springframework.mock.web.MockMultipartFile;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
public class EasyExcelUtil {
    /**
     * 字典结果组合: 名字-编码
     */
    public static final String NAME_CODE = "NAME_CODE";

    /**
     * 字典结果组合: 编码-名字
     */
    public static final String CODE_NAME = "CODE_NAME";



    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class TestVO {
        @ExcelProperty(value = "*姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "学校", index = 2)
        private String school;
    }

    @Data
    @ColumnWidth(20)
    public static class TestVO1 {
        @ExcelProperty(value = "姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "*学校", index = 2)
        private String school;
    }

    @Data
    @ColumnWidth(20)
    public static class MultiLanguage {
        @ExcelProperty(value = "字段1", index = 0)
        private String value1;
        @ExcelProperty(value = "字段2", index = 1)
        private String value2;
        @ExcelProperty(value = "字段3", index = 2)
        private String value3;
        @ExcelProperty(value = "字段4", index = 3)
        private String value4;
    }

    /**
     * 使用 模型 来写入Excel
     *
     * @param outputStream Excel的输出流
     * @param dataList     要写入的以 模型 为单位的数据
     * @param clazz        模型的类
     */
    public static void writeExcelWithModel(OutputStream outputStream, String sheetName, List<? extends Object> dataList, Class<? extends Object> clazz) {
        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = EasyExcel.write(outputStream).build();
        //创建writeSheet，设置基本信息
        WriteSheet sheet = EasyExcel.writerSheet().sheetName(sheetName).head(clazz).build();
        writer.write(dataList, sheet);
        writer.finish();
    }


    /**
     * 获取输出流
     *
     * @param response
     * @param fileName
     * @return
     * @throws IOException
     */
    public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) throws IOException {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }



    /**
     * 使用模型读取EXCEL数据
     * @param file 上传的文件
     * @param classA  模板类
     * @param <T> 泛型
     * @return
     */
    public static <T> List<T> readExcelWithModel(MultipartFile file, Class<T> classA) throws Exception {
        List<T> list = new ArrayList<>();
        try {
            // 获取输入流
            InputStream inputStream = file.getInputStream();
            // 数据收集器
            AnalysisEventListenerImpl<T> listener = new AnalysisEventListenerImpl<>();
            ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
            // 第一个sheet读取类型
            ReadSheet readSheet = EasyExcel.readSheet(0).head(classA).build();
            // 开始读取第一个sheet
            excelReader.read(readSheet);
            list = listener.getDatas();
        } catch (IOException e) {
            throw new Exception("excel解析出错");
        }
        return list;
    }


}

导入模板数据功能

1.controller层

  /**
     * 批量导入
     */
    @PostMapping("/electric-meter-setting/import")
    public ResponseData importFromExcel(@RequestParam("file")MultipartFile file) throws Exception {
        List<String> errorList = electricMeterSettingService.importFromExcel(file);
        if(errorList.size() != 0)
            return ResponseData.error(202,"输入数据不正确",errorList);
        return ResponseData.success();
    }

2.service层

public List<String> importFromExcel(MultipartFile file) throws Exception {
        //创建放置错误数据的容器
        List<String> errorList = new ArrayList<>();

        //读取excel内的数据内容
        List<ElectricMeterSettingImportDro> electricMeterSettingImportDros = EasyExcelUtil.readExcelWithModel(file, ElectricMeterSettingImportDro.class);
		
		//具体业务处理
        }