目录
1.首先引入依赖
<!--引入easypoi依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
2.定义导出数据基本对象
注意: 这里的实体类需要进行序列化
如果是多个sheef,分别创建每个sheef对应的实体并序列化。
实体上添加@ExcelTarget()注解,字段上添加 @Excel()注解。
另外再添加一个errorMsg用于导出时生成错误信息的列,后期进行判断的时候,也是把错误信息放在该字段中
@Data
@ExcelTarget("QuestionBankImport")
public class QuestionBankImport implements Serializable {
@Excel(name = "分类")
private String classification;
@Excel(name = "类型")
private String questionTypes;
@Excel(name = "错误信息")
private String errorMsg;
}
3.导入Excel中数据
导入第一个sheef
//导入参数
ImportParams params = new ImportParams();
params.setTitleRows(1);//标题列占几行
params.setHeadRows(1);//header列占几行
params.setStartSheetIndex(0);
ZipSecureFile.setMinInflateRatio(-1.0d);
//获取导入数据
List<QuestionBankImport> qbiList = new ArrayList<>();
try {
qbiList = ExcelImportUtil.importExcel(new FileInputStream(filePath), QuestionBankImport.class, params);
} catch (Exception e) {
log.info(e.getMessage());
}
导入第二个sheef
//导入
ImportParams param = new ImportParams();
param.setTitleRows(0);//标题列占几行
param.setHeadRows(1);//header列占几行
param.setStartSheetIndex(1);//读取第二个sheet
List<OutletsPostImport> opiList = null;
try {
opiList = ExcelImportUtil.importExcel(new FileInputStream(filePath), OutletsPostImport.class, param);
} catch (Exception e) {
log.info(e.getMessage());
}
导入第三个sheef
//导入
ImportParams param = new ImportParams();
param.setTitleRows(0);//标题列占几行
param.setHeadRows(1);//header列占几行
param.setStartSheetIndex(2);//读取第三 个sheet
List<OutletsInvestmentImport> oiList = null;
try {
oiList = ExcelImportUtil.importExcel(new FileInputStream(filePath), OutletsInvestmentImport.class, param);
} catch (Exception e) {
log.info(e.getMessage());
}
如果有错误就生成错误文件
File file = FileUtil.touch(tempFilePath);
//导出单个sheet
//ExportParams exportParams = new ExportParams();
//Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null,"XX网点"),OutletsPostImport.class, errListPost);
//导出多个sheet
// 创建参数对象(用来设定excel得sheet得内容等信息)
ExportParams outletsBasicExportParams = new ExportParams();
// 设置sheet得名称
outletsBasicExportParams.setSheetName("AAA网点");
//.xslx格式 默认为.xls格式
outletsBasicExportParams.setType(ExcelType.XSSF);
// 创建sheet1使用得map
Map<String, Object> basicExportMap = new HashMap<>();
// title的参数为ExportParams类型,目前仅仅在ExportParams中设置了sheetName
basicExportMap.put("title", outletsBasicExportParams);
// 模版导出对应得实体类型
basicExportMap.put("entity", OutletsBasicImport.class);
// sheet中要填充得数据
basicExportMap.put("data", errList);
ExportParams outletsPostExportParams = new ExportParams();
//.xslx格式 默认为.xls格式
outletsPostExportParams.setType(ExcelType.XSSF);
outletsPostExportParams.setSheetName("BBB网点");
// 创建sheet2使用得map
Map<String, Object> postExportMap = new HashMap<>();
postExportMap.put("title", outletsPostExportParams);
postExportMap.put("entity", OutletsPostImport.class);
postExportMap.put("data", errListPost);
ExportParams outletsInvestmentExportParams = new ExportParams();
//.xslx格式 默认为.xls格式
outletsInvestmentExportParams.setType(ExcelType.XSSF);
outletsInvestmentExportParams.setSheetName("CCC网点");
// 创建sheet2使用得map
Map<String, Object> investmentExportMap = new HashMap<>();
investmentExportMap.put("title", outletsInvestmentExportParams);
investmentExportMap.put("entity", OutletsInvestmentImport.class);
investmentExportMap.put("data", errListInvestment);
// 将sheet1、sheet2、sheet3使用得map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(basicExportMap);
sheetsList.add(postExportMap);
sheetsList.add(investmentExportMap);
// 执行方法 导出.xlsx格式
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.XSSF);
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(tempFilePath);
} catch (FileNotFoundException e) {
log.info(e.getMessage());
}
try {
workbook.write(outputStream);
outputStream.close();
workbook.close();
} catch (IOException e) {
log.info(e.getMessage());
}
realPath = fileBasePath + ResourceUtil.createFileName("xlsx");
//将文件上传到sftp
Sftp sftp = SftpUtil.getSftp();
SftpUtil.upload(sftp, cp2.getAstr(), realPath);
SftpUtil.close(sftp);