Excel导入导出(easypoi)

目前使用easypoi进行文件导入导出

1.引入jar，版本可以自己选择使用量多的，两个jar版本最好一致，每个版本有细微差异，easypoi有部分bug，在实际使用过程中遇到可以跟下源码,根据业务进行调整

<dependency>

<groupId>cn.afterturn</groupId>

<artifactId>easypoi-base</artifactId>

<version>${easypoi.version}</version>

</dependency>

<dependency>

<groupId>cn.afterturn</groupId>

<artifactId>easypoi-annotation</artifactId>

<version>${easypoi.version}</version>

</dependency>

2.Excel文件导入（支持多sheet）

/**
     * 文件根据sheet导入
     * @param file  excel文件
     * @param index  sheet下标
     * @param titleRows 标题行数
     * @param headerRows 表头行数
     * @param tClass 表实体类
     * @return
     */
    public static <T> List<T> importSheet(File file, int index, Integer titleRows, Integer headerRows, Class<T> tClass) {
        ImportParams importParams = new ImportParams();
        importParams.setStartSheetIndex(index);
        importParams.setTitleRows(titleRows);
        importParams.setHeadRows(headerRows);
        importParams.setNeedVerify(false);
        List<T> ts = null;
        try {
            ts = ExcelImportUtil.importExcel(file,tClass,importParams);
        }catch (Exception e){
            e.printStackTrace();
        }
        return ts;
    }

附加：实体案例，调用案例，仅供参考

2.1实体类

@ExcelTarget("xx")
public class xxDto extends Model<xxxDto> {
    //orderNum 表示表格显示顺序，从0开始
    @Excel(name = "cardNum",orderNum = "0")
    @ApiModelProperty(value = "身份证号码")
    private String cardNum;
    @Excel(name = "cardNum",orderNum = "1")
    @ApiModelProperty(value = "姓名")
    private String name;
}

2.2调用类

public ResponseWrapper importFile(MultipartFile multipartFilefile){
    //springboot 传输文件为multipartFile，需要转换文件类型为File
    File file = FileUtils.transferToFile(multipartFilefile);
    //传输参数与工具类对应
    List<xxDto> xxDtos = WorkBookUtils.importSheet(file, 0, 0, 1, xxDto.class);
    //解析完的数据做相应业务处理
}

3.Excel文件导出（支持多sheet）

public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data){
        ExportParams exportParams = new ExportParams(null,sheetName, ExcelType.XSSF);
        return createOneSheet(exportParams,clazz,data);
    }
public static Map<String, Object> createOneSheet(ExportParams exportParams,Class<?> clazz,List<?> data){
        Map<String, Object> map = new HashMap<>();
        map.put("title",exportParams);
        map.put("entity", clazz);
        map.put("data",data);
        return map;
    }
public static Workbook mutiSheet(List<Map<String, Object>> mapListList){
    Workbook workbook;
    workbook = ExcelExportUtil.exportExcel(mapListList,ExcelType.XSSF);
    return workbook;
}

附加：调用案例，仅供参考

3.1调用类

private File fillData(File savefile) throws Exception {
    List<Map<String, Object>> lists = new ArrayList<>();
    List<xx> xxDatas = jcxxService.getBaseMapper().selectList(xxWrapper);
    Map<String, Object> xxTemp = WorkBookUtils.createOneSheet("xx", xx.class, xxDatas);
    lists.add(xxTemp);
    Workbook workbook = WorkBookUtils.mutiSheet(lists);
    FileOutputStream fos = new FileOutputStream(savefile);
    workbook.write(fos);
    fos.close();
    return savefile;
}

4.文件下载

/**
     * 下载文件
     *
     * @param request
     * @param response
     * @param file
     * @param fileName
     * @throws IOException
     */
    private void datatoResponse(HttpServletRequest request, HttpServletResponse response, File file, String fileName) throws IOException {
        OutputStream out = null;
        FileInputStream in = null;
        try {
            // 1.读取要下载的内容
            in = new FileInputStream(file);
            response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
            String mineType = request.getServletContext().getMimeType(fileName);
            response.setContentType(mineType);
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            out = response.getOutputStream();
            int len = 0;
            byte[] buffer = new byte[1024];
            while ((len = in.read(buffer)) > 0) {
                out.write(buffer, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.close();
            }
            if (in != null) {
                in.close();
            }
        }
    }