Srpingnoot-POI框架上传解析表格

好怪~

已于 2022-12-16 09:40:19 修改

阅读量224

点赞数

文章标签： java 开发语言 mybatis spring boot

于 2022-11-11 14:53:45 首次发布

本文链接：https://blog.csdn.net/qq_53314126/article/details/127805394

版权

POI框架目前来说是比较简单，且快速的一种方法

poi框架可以支持我们在Java代码中，将数据导出成excel，但是实际开发中，这个表格数据和数据库中存储的数据还是有很大不同

快速开发

1、导入POM

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>4.1.0</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-validator</artifactId>
        <version>5.4.0.Final</version>
    </dependency>


    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>runtime</scope>
        <optional>true</optional>
    </dependency>

2、创建工具类

/**
 * easypoi工具类,
 * 使用new方式创建对象并使用
 *
 * @param <T>
 */
public class EasyPoiTool<T> {

    /**
     * 需要被反射的对象，使用泛型规范传入对象
     */
    public T t;

    /**
     * 修改注解@Excel的属性值
     * @param attributeName
     * @param columnName
     * @param targetValue
     * @throws Exception
     */
    public void changeAttribute(String attributeName, String columnName, Object targetValue) throws Exception {
        if (t == null) {
            throw new ClassNotFoundException("未找到目标类");
        }
        if (StringUtils.isEmpty(attributeName)) {
            throw new NullPointerException("传入的注解属性为空");
        }
        if (StringUtils.isEmpty(columnName)) {
            throw new NullPointerException("传入的属性列名为空");
        }
        //获取目标对象的属性值
        Field field = t.getClass().getDeclaredField(columnName);
        //获取注解反射对象
        Excel excelAnion = field.getAnnotation(Excel.class);
        //获取代理
        InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnion);
        Field excelField = invocationHandler.getClass().getDeclaredField("memberValues");
        excelField.setAccessible(true);
        Map memberValues = (Map) excelField.get(invocationHandler);
        memberValues.put(attributeName, targetValue);
    }
}

3、表格导入导出工具类

/**
 * Excel导入导出工具类
 */

public class ExcelUtils {

    /**
     * excel 导出
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     * @param response
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }



    /**
     * excel 导出
     *
     * @param list           数据列表
     * @param title          表格内数据标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       导出时的excel名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }


    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }



    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入，有错误信息
     *
     * @param file      上传的文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcelMore(file.getInputStream(), pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    private static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//表格内数据标题行
        params.setHeadRows(1);//表头行
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(true);
        try {
            return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }
}

4.、表格导入导出接口


@Api(tags = "本地文件上传下载")
@RestController
@RequestMapping("/load")
public class upAndDownLoad {
    @Autowired
    SupplierService supplierService;
    @Autowired
    AcssVehicleInfoService acssVehicleInfoService;
    @Autowired(required = false)
    AcssVehicleInfoDao acssVehicleInfoDao;

    @ApiOperation(value = "供应商-下载导入模板", response = String.class)
    @GetMapping(value = "/downloadSupplierTemplate")
    public void supplierTemplate(HttpServletResponse response) throws Exception {
        supplierService.downloadSupplierTemplate(response);
    }

    /**
     * 导入数据
     *
     * @param file
     * @return
     * @throws IOException
     */
    @ApiOperation(value = "车辆信息导入")
    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public ResponseObj importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        try {
            List<AcssVehicleInfoDTO> list = ExcelUtils.importExcel(file, AcssVehicleInfoDTO.class);
            int i = acssVehicleInfoDao.insertBatch_(list);
            if (i != 0) {
                return ResponseObj.success("导入成功");
            } else {
                return ResponseObj.fail("导入失败");
            }
        } catch (IOException e) {
            e.printStackTrace();
            return ResponseObj.fail("文件类型格式异常");
        }

    }

}

5、当然在现实中我们更多的数据库会用到字典，这样我们这解析插入的时候需要修改对应的实体类


/**
 * 车辆基本信息表(AcssVehicleInfo)实体类
 *
 * @author makejava
 * @since 2022-10-25 13:13:59
 */
@Data
public class AcssVehicleInfoDTO implements Serializable {
    /**
     * 车牌号码
     */
    @TableId(value = "licensePlateNo")
    @Excel(name = "车牌号码")
    private String licensePlateNo;
    /**
     * 车牌颜色
     */
    @TableField(value = "licensePlateColour")
    @Excel(name = "车牌颜色", replace = {"未知_0", "蓝色_1", "黄色_2", "白色_3", "黑色_4", "绿色_5", "黄绿双色_6", "渐变绿色_7"})
    private String licensePlateColour;
    /**
     * 车牌类型
     */
    @TableField(value = "licensePlateType")
    @Excel(name = "车牌类型", replace = {"大型汽车号牌_1", "小型汽车号牌_2", "使馆汽车牌号_3", "领馆汽车号牌_4", "境外汽车号牌_5",
            "外籍汽车号牌_6", "普通摩托车号牌_7", "轻便摩托车号牌_8", "使馆摩托车号牌_9", "领馆摩托车号牌_10", "境外摩托车号牌_11", "外籍摩托车号牌_12", "低速车号牌_13", "拖拉机号牌_14", "挂车号牌_15",
            "教练汽车号牌_16", "教练摩托车号牌_17", "临时入境汽车号牌_20", "临时入境摩托车号牌_21", "临时行驶车号牌_22", "警用汽车号牌_23", "警用摩托车号牌_24", "其他号牌_99"})
    private String licensePlateType;
    /**
     * 车辆型号
     */
    @TableField(value = "vehicleModel")
    @Excel(name = "车辆型号")
    private String vehicleModel;
    /**
     * 车辆类型
     */
    @TableField(value = "vehicleType")
    @Excel(name = "车辆类型", replace = {"环卫车_1", "邮政车_2", "大客车_3", "货运车_4", "其他_5"})
    private String vehicleType;
    /**
     * 燃料类型(1;汽油,2,柴油,3,混合油,4,液化石油气,5,天然气,6,甲醇,7,乙醇,8,太阳能,9,纯电,10,生物燃料,11,氢)
     */
    @TableField(value = "fuelType")
    @Excel(name = "燃料类型", replace = {"汽油_A", "柴油_B", "纯电_C", "混合油_D", "天然气_E", "液化石油气_F"})
    private String fuelType;
    /**
     * 排放阶段/排放标准(0-国0;1-国Ⅱ，3-国Ⅲ，4-国Ⅳ，5-国Ⅴ，6-国Ⅵ，7-国VII)
     */
    @TableField(value = "dischargeStage")
    @Excel(name = "排放标准", replace = {"国Ⅳ_4", "国Ⅴ_5", "国Ⅵ_6"})
    private String dischargeStage;
    /**
     * 注册日期
     */
    @TableField(value = "registrationDate")
    @Excel(name = "注册日期", isImportField = "false", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd")
    private Date registrationDate;
    /**
     * 车辆识别代码
     */
    @TableField(value = "vehicleIdentificationNo")
    @Excel(name = "车辆识别代码")
    private String vehicleIdentificationNo;
    /**
     * 发动机号码/发动机编号
     */
    @TableField(value = "engineNo")
    @Excel(name = "发动机编号")
    private String engineNo;
    /**
     * 发动机最大扭矩
     */
    @TableField(value = "engineMaxTorque")
    @Excel(name = "发动机最大扭矩")
    private String engineMaxTorque;
    /**
     * 管理单位
     */
    @TableField(value = "managementUnit")
    @Excel(name = "管理单位")
    private String managementUnit;
    /**
     * 核定载重
     */
    @TableField(value = "approvedLoad")
    @Excel(name = "核定载重")
    private String approvedLoad;
    /**
     * 设备序列号
     */
    @TableField(value = "equipmentSerialNo")
    @Excel(name = "设备序列号")
    private String equipmentSerialNo;
    /**
     * 设备公司
     */
    @TableField(value = "equipmentCompany")
    @Excel(name = "设备公司")
    private String equipmentCompany;
    /**
     * 机动车所有人
     */
    @TableField(value = "vehicleOwner")
    @Excel(name = "机动车所有人")
    private String vehicleOwner;
    /**
     * 中文品牌
     */
    @TableField(value = "chineseBrand")
    @Excel(name = "中文品牌")
    private String chineseBrand;
    /**
     * 车辆OBD细分类型
     */
    @TableField(value = "obdType")
    @Excel(name = "车辆OBD细分类型")
    private String obdType;

    /**
     * 强制报废期止
     */
    @TableField(value = "forceScrapEndDate")
    @Excel(name = "强制报废期止", isImportField = "false", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd")
    private Date forceScrapEndDate;
    /**
     * 逾期检验强制报废期止
     */
    @TableField(value = "overdueForceScrapEndDate")
    @Excel(name = "逾期检验强制报废期止", isImportField = "false", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd")
    private Date overdueForceScrapEndDate;
    /**
     * 出厂日期
     */
    @TableField(value = "productionDate")
    @Excel(name = "出厂日期",isImportField = "true",exportFormat = "yyyy-MM-dd", importFormat =  "yyyy-MM-dd" ,databaseFormat = "yyyy-MM-dd")
    private Date productionDate;
//    /**
//     * SIM卡ICCID号
//     */
//    private String simIccid;
    /**
     * SIM卡卡号
     */
    @TableField(value = "simNo")
    @Excel(name = "SIM卡卡号")
    private String simNo;
//    /**
//     * SIM卡运营商
//     */
//    private String simOperator;
    /**
     * 终端安装日期
     */
    @TableField(value = "terminalInstallDate")
    @Excel(name = "终端安装日期", isImportField = "false", importFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd")
    private Date terminalInstallDate;
    /**
     * 所在省
     */
    @TableField(value = "province")
    @Excel(name = "所属省", replace = {"陕西省_610000"})
    private String province;
    /**
     * 所在市
     */
    @TableField(value ="city")
    @Excel(name = "所属市", replace = {"商洛市_611000"})
    private String city;
    /**
     * 所在区
     */
    @TableField(value = "area")
    @Excel(name = "所属区", replace = {"柞水县_611026","商州区_611002","丹凤县_611022","洛南县_611021","镇安县_611025","山阳县_611024","商南县_611023"})
    private String area;
    /**
     * 油箱
     */
    @TableField(value = "fuelTank")
    @Excel(name = "油箱")
    private String fuelTank;
    /**
     * 尿素
     */
    @TableField(value = "urea")
    @Excel(name = "尿素")
    private String urea;
    /**
     * 氮氧化物排放
     */
    @TableField(value = "nNitrogen")
    @Excel(name = "氮氧化物排放")
    private String nNitrogen;

    public String getNNitrogen() {
        return nNitrogen;
    }

    public void setNNitrogen(String nNitrogen) {
        this.nNitrogen = nNitrogen;
    }

    public String getnNitrogen() {
        return nNitrogen;
    }

    public void setnNitrogen(String nNitrogen) {
        this.nNitrogen = nNitrogen;
    }
}