springboot使用EasyExcel进行导入导出

1、添加依赖

        
        
            　　com.alibaba
            　　easyexcel
            　　2.1.1
        
        
        
            　　org.apache.poi
            　　poi
            　　3.17
        
        
            　　org.apache.poi
            　　poi-ooxml
            　　3.17
        

2、创建导入导出的映射实体类

导出

package com.zl.model.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Builder;
import lombok.Data;

/**
 * 导出VO
 *
 * @author z
 * @date 2022-03-14 18:01
 */
@Data
@HeadRowHeight(value = 20)
public class ExportModeSheetOneVO {
    /**
     * id  
     */
    @ExcelProperty(value = "编号",index = 0)
    @ColumnWidth(value = 20)
    private String id;

    /**
     * 名称
     */
    @ExcelProperty(value = "名称",index = 1)
    @ColumnWidth(value = 20)
    private String name;

    /**
     * 类型
     */
    @ExcelProperty(value = "类型",index = 2)
    @ColumnWidth(value = 20)
    private String type;

    /**
     * 描述
     */
    @ExcelProperty(value = "描述",index = 3)
    @ColumnWidth(value = 20)
    private String des;

    /**
     * 创建日期
     */
    @ExcelProperty(value = "创建日期",index = 4)
    @ColumnWidth(value = 20)
    private String createDt;

    /**
     * 创建者
     */
    @ExcelProperty(value = "创建者",index = 5)
    @ColumnWidth(value = 20)
    private String createBy;

    /**
     * 更新日期
     */
    @ExcelProperty(value = "更新日期",index = 6)
    @ColumnWidth(value = 20)
    private String updateDt;

    /**
     * 更新者
     */
    @ExcelProperty(value = "更新者",index = 7)
    @ColumnWidth(value = 20)
    private String updateBy;
}

导入

package com.zl.model.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * 导入VO
 *
 * @author z
 * @date 2022-03-15 16:08
 */
@Data
public class ImportModeVO {
    /**
     * 模型名称
     */
    @ExcelProperty("名称")
    private String name;
    /**
     * 类型
     */
    @ExcelProperty("类别")
    private String type;
    /**
     * 描述
     */
    @ExcelProperty("描述")
    private String des;
}

建议类型都写为String,可以减少不必要的类型转换

3、实现

Controller层代码实现:

/**
 * 导入导出测试
 *
 * @author z
 * @since 2022-03-12
 */
@RestController
@RequestMapping("/myTest")
@Api(tags = "数据导入导出")
@Slf4j
public class MyInfoController {
    @Autowired
    private MyInfoService myInfoService; 
   /**
     * 模型导入
     * @param file
     * @return
     */
    @PostMapping("/importMode")
    @ApiOperation("导入")
    @ApiImplicitParam(name = "file",value = "上传的数据",required = true)
    public Result importMode(@RequestParam("file") MultipartFile file){
        if (file==null||file.isEmpty()) {
            return Result.error();
        }
        int flag= 0;
        try {
            flag = myInfoService.importMode(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return flag==1?Result.success():Result.error();
    } 
 
   /**
     * 导出
     * @param type 导出类别
     */
    @GetMapping("/exportMode")
    @ApiOperation("导出")
    @ApiImplicitParam(name="type",value="导出类别",required = true)
    public void exportMode(String type, HttpServletResponse response){
        try {
            myInfoService.exportMode(type,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Service层实现：

    /**
     * 导入
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int importMode(MultipartFile file) throws IOException {
        InputStream in =file.getInputStream();
            EasyExcel.read(in, ImportModeVO.class,new ImportExcelListenerUtil())
                    .sheet("Sheet1")
                    .doRead();

            // 每次EasyExcel的read方法读取完之后都会关闭流，我这里为了试验doReadAll方法，所以重新获取了一次
            // in = file.getInputStream();
            // EasyExcel.read(in,ImportModeVO.class,new ImportExcelListenerUtil()).doReadAll();
        List list = ImportExcelListenerUtil.list;
        for(int i=0;ipackage com.zl.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zl.model.vo.ImportModeVO;

import java.util.ArrayList;
import java.util.List;

/**
 * 导入监听工具类
 *
 * @author z
 * @date 2022-03-15 16:17
 */
public class ImportExcelListenerUtil extends AnalysisEventListener {
    public static final List list = new ArrayList<>();

    @Override
    public void invoke(ImportModeVO excelEntity, AnalysisContext context) {
        list.add(excelEntity);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

• 自定义表头需要的工具类

package com.zl.util;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导出自适应列宽
 *
 * @author z
 * @date 2022-03-15 14:57
 */
public class CustomCellWriteHandlerUtil extends AbstractColumnWidthStyleStrategy {
    private Map> CACHE = new HashMap<>();
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth>= 0) {
                if (columnWidth> 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth> maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }
    private Integer dataLength(List cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

4、测试

• 使用 postman 测试导入导出接口