com.alibaba.excel.metadata.BaseRowModel Java Examples

public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName,
                                BaseRowModel object) {
    this.sheetNo++;
    try {
        Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
        sheet.setSheetName(sheetName);
        this.write(list, sheet);
    } catch (Exception ex) {
        ex.printStackTrace();
        try {
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return this;
}
 

/**
 * 异步导出 Excel ：一个 sheet，带表头
 *
 * @param
 * @param list      数据 list，每个元素为一个 BaseRowModel
 * @param sheetName 导入文件的 sheet 名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static String asyWriteExcel(List<? extends BaseRowModel> list,
                                String sheetName, BaseRowModel object) {
    // 现将数据导出excel到本地
    try {
        String fileName = URLEncoder.encode(createFileName(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(getFileOutputStream(fileName), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        // 读取该excel,并上传到oss，返回下载链接
        // File file = readFileByLines(fileName + ".xlsx");
        // return FileUploadUtil.upload(file, fileName + ".xlsx");
    } catch (UnsupportedEncodingException e) {
        throw new RuntimeException("创建excel失败!");
    }
    return null;
}
 

private static void declaredFields(Class clazz, Boolean convertAllFiled) {
    List<Field> tempFieldList = new ArrayList<Field>();
    Class tempClass = clazz;
    // When the parent class is null, it indicates that the parent class (Object class) has reached the top
    // level.
    while (tempClass != null && tempClass != BaseRowModel.class) {
        Collections.addAll(tempFieldList, tempClass.getDeclaredFields());
        // Get the parent class and give it to yourself
        tempClass = tempClass.getSuperclass();
    }
    // Screening of field
    Map<Integer, List<Field>> orderFiledMap = new TreeMap<Integer, List<Field>>();
    Map<Integer, Field> indexFiledMap = new TreeMap<Integer, Field>();
    Map<String, Field> ignoreMap = new HashMap<String, Field>(16);

    ExcelIgnoreUnannotated excelIgnoreUnannotated =
        (ExcelIgnoreUnannotated) clazz.getAnnotation(ExcelIgnoreUnannotated.class);
    for (Field field : tempFieldList) {
        declaredOneField(field, orderFiledMap, indexFiledMap, ignoreMap, excelIgnoreUnannotated, convertAllFiled);
    }
    FIELD_CACHE.put(clazz, new SoftReference<FieldCache>(
        new FieldCache(buildSortedAllFiledMap(orderFiledMap, indexFiledMap), indexFiledMap, ignoreMap)));
}
 

/**
 * 导出 Excel ：一个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                              String fileName, String sheetName, BaseRowModel object) {
    // WriteModel 是 写入 Excel 的数据模型对象
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    // 异常处理
    writer.write(list, sheet);
    writer.finish();
}
 

/**
 * 读取 整个Excel(多个 sheet 需要 各个 sheet 字段相同)
 *
 * @param excel    文件
 * @param baseRowModel 实体类映射，继承 BaseRowModel 类
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    for (Sheet sheet : reader.getSheets()) {
        if (baseRowModel != null) {
            sheet.setClazz(baseRowModel.getClass());
        }
        reader.read(sheet);
    }
    return excelListener.getDatas();
}
 

/**
 * 读取 Excel 的指定 sheet 指定数据
 *
 * @param excel       文件
 * @param baseRowModel    实体类映射，继承 BaseRowModel 类
 * @param sheetNo     sheet 的序号（从第几个 sheet 开始读）
 * @param headLineNum 表头行数（从第几行开始读）
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel,
                                     int sheetNo, int headLineNum) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    reader.read(new Sheet(sheetNo, headLineNum, baseRowModel.getClass()));
    return excelListener.getDatas();
}
 

/**
 * @return java.util.List<?       extends       com.alibaba.excel.metadata.BaseRowModel>
 * @Author fruiqi
 * @Description 获取数据
 * @Date 23:50 2019/5/13
 **/
private List<? extends BaseRowModel> getData() {
    CustomerMessage message = new CustomerMessage();
    message.setP1("1");
    message.setP2("2");
    message.setP3("3");
    message.setP4("4");
    message.setP5("5");
    message.setP6("6");
    message.setP7("P7");

    List<OnlineData>  list1 = new ArrayList<>();
    OnlineData onlineData = new OnlineData();
    onlineData.setP8("123");
    onlineData.setP9("345");

    OnlineData onlineData1 = new OnlineData();
    onlineData1.setP8("213");
    onlineData1.setP9("789");

    list1.add(onlineData);
    list1.add(onlineData1);

    List<CustomerMessage> list = new ArrayList<>();
    list.add(message);
    CustomerMessage message1 = new CustomerMessage();
    BeanUtils.copyProperties(message, message1);
    message1.setP6("sdasdasdas");
    list.add(message1);
    list.add(message);
    list.add(message1);
    return list;

}
 

/**
 * 导出 Excel ：多个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据 list
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                      String fileName, String sheetName, BaseRowModel object) {
    ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    writer.write(list, sheet);
    return writer;
}
 

/**
 * 读取 Excel 的指定 sheet 全部数据
 *
 * @param excel       文件
 * @param baseRowModel    实体类映射，继承 BaseRowModel 类
 * @param sheetNo     sheet 的序号（从第几个 sheet 开始读）
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel, int sheetNo) {
    return readExcel(excel, baseRowModel, sheetNo, 1);
}