EasyExcel导入导出

导出： 

/**
 * 导出 Excel ：一个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      数据 list，每个元素为一个 BaseRowModel
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param model     映射实体类，Excel 模型
 */
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
    String fileName, String sheetName, BaseRowModel model) throws Exception {
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ".xlsx");
    Sheet sheet = new Sheet(1, 0, model.getClass());
    sheet.setSheetName(sheetName);
    writer.write(list, sheet);
    writer.finish();
}

/**
 * 导出文件时为Writer生成OutputStream
 *
 * @param fileName
 * @param response
 * @return
 */
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    try {
        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();
    } catch (IOException e) {
        throw new Exception("导出excel表格失败!", e);
    }
}
try {
    String fileName = "电子围栏表导出";
    String sheetName = "电子围栏表导出";
    ExcelUtils.writeExcel(response, exportList, fileName, sheetName, new ExportTemplate());
} catch (Exception e) {
    return false;
}

 

导入：

/**
 * 导入Excel
 *
 * @param excelFile 导入文件
 * @return 处理结果
 */
public List<VehicleImportTemplate> collectImportData(MultipartFile excelFile) {
    List<VehicleImportTemplate> vehicleImportTemplateList = new ArrayList<>();
    List<Object> dataList;
    try {
        dataList = EasyExcelFactory.read(excelFile.getInputStream(), new Sheet(1, 1, VehicleImportTemplate.class));
        for (Object data : dataList) {
            if (data instanceof VehicleImportTemplate) {
                vehicleImportTemplateList.add((VehicleImportTemplate) data);
            }
        }
    } catch (Exception e) {
        return CommonResult.failed(VEHICLE_IMPORT_ERROR);
    }
    return vehicleImportTemplateList;
}

 

实体类：

@Data
public class VehicleImportTemplate extends BaseRowModel {
    @ExcelProperty(value = {"车牌号"}, index = 0)
    private String numberPlate;
    @ExcelProperty(value = {"车牌颜色"}, index = 1)
    private String plateColor;
    @ExcelProperty(value = {"车辆类型"}, index = 2)
    private String type;
    @ExcelProperty(value = {"终端号码"}, index = 3)
    private String terminalId;
    @ExcelProperty(value = {"SIM卡号"}, index = 4)
    private String simNumber;
    @ExcelProperty(value = {"企业名称"}, index = 5)
    private String groupName;
    @ExcelProperty(value = {"协议类型"}, index = 6)
    private String protocolType;
    @ExcelProperty(value = {"终端类型"}, index = 7)
}

 

ExayExcel 提供注解的方式, 来方便的定义 Excel 需要的数据模型。

①：首先，定义的写入模型必须要继承自 BaseRowModel.java;

②：通过 @ExcelProperty 注解来指定每个字段的列名称，以及下标位置；