关于Easyexcel读取EXCEL合并单元格信息
1.excel模版
@Configuration
public class TestModel {
@ExcelProperty(value = "开发部",index = 0)
private String kaifabu;
@ExcelProperty(value = "A2",index = 1)
private String a2qiyoutian;
@ExcelProperty(value = "区块",index =2)
private String qukuai;
@ExcelProperty(value = "项目部",index =3)
private String xiangmubu;
@ExcelProperty(value = "开发部1部", index =4)
private String kaifabu1;
public String getKaifabu() {
return kaifabu;
}
public void setKaifabu(String kaifabu) {
this.kaifabu = kaifabu;
}
public String geta2qiyoutian() {
return a2qiyoutian;
}
public void seta2qiyoutian(String a2qiyoutian) {
this.a2qiyoutian = a2qiyoutian;
}
public String getQukuai() {
return qukuai;
}
public void setQukuai(String qukuai) {
this.qukuai = qukuai;
}
public String getXiangmubu() {
return xiangmubu;
}
public void setXiangmubu(String xiangmubu) {
this.xiangmubu = xiangmubu;
}
public String getKaifabu1() {
return kaifabu1;
}
public void setKaifabu1(String kaifabu1) {
this.kaifabu1 = kaifabu1;
}
@Override
public String toString() {
return "TestModel [kaifabu=" + kaifabu + ", a2qiyoutian=" + a2qiyoutian + ", qukuai=" + qukuai + ", xiangmubu="
+ xiangmubu + ", kaifabu1=" + kaifabu1 + "]";
}
}
2.Service层(处理单元格)
@Service
public class TryService extends AnalysisEventListener<Map<Integer,String>>{
private static final Logger log = LoggerFactory.getLogger(TryService.class);
public static List<TestModel> datas = new ArrayList<>();
Map<Integer, String>preContent;
@Override
public void invoke(Map<Integer, String> o, AnalysisContext analysisContext) {
TestModel testModel = new TestModel();
String kaifabu = o.get(0);
String a2qiyoutian = o.get(1);
String qukuai = o.get(2);
String xiangmubu = o.get(3);
String kaifabu1 = o.get(4);
if(StringUtils.isEmpty(kaifabu)){
kaifabu = preContent.get(0);
}
if(StringUtils.isEmpty(a2qiyoutian)) {
a2qiyoutian = preContent.get(1);
}
if(StringUtils.isEmpty(qukuai)) {
qukuai = preContent.get(2);
}
if(StringUtils.isEmpty(xiangmubu)) {
xiangmubu = preContent.get(3);
}
if(StringUtils.isEmpty(kaifabu1)) {
kaifabu1 = preContent.get(4);
}
testModel.setKaifabu(kaifabu);
testModel.seta2qiyoutian(a2qiyoutian);
testModel.setQukuai(qukuai);
testModel.setXiangmubu(xiangmubu);
testModel.setKaifabu1(kaifabu1);
datas.add(testModel);
String uniquKey = o.get(0);
if(!StringUtils.isEmpty(uniquKey) || preContent == null) {
preContent = o;
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("读取数据完成");
}
}
这里面主要还是基于传统EasyExcel读取信息的原理,在对单元格方面做出了处理,并且excel表格数据都主要存储在datas里面
datas.add(testModel);
原理是将合并元格数据放入Map中进行存储,然后对单元格里面的内容进行判断,如果该区间的单元格为空,则将缓存中的值赋给其单元格。
String uniquKey = o.get(0);
if(!StringUtils.isEmpty(uniquKey) || preContent == null) {
preContent = o;
}
如果A列的值为空,那么说明这是跨行列,如果取的值不是空,那说明这是第一次读取跨行列数据,如果preContent为空,说明这是第一次读取sheet表格的数据
3.Rest层
这里代码比较糙(可能存在不必要的代码),主要是用于测试方法是否合理正确。
@RequestMapping("uploadProjectDepartmentExcel")
public JSONObject uploadProjectDepartmentExcel(@RequestParam(value = "file") MultipartFile file) {
JSONObject returnData = new JSONObject();
JSONObject response = new JSONObject();
returnData.put("cmd", "uploadProjectDepartmentExcel");
returnData.put("type", "response");
returnData.put("response", response);
try {
if(file == null || file.isEmpty()) {
throw new MyException("未找到 file字段");
}
String path = FilePath.CACHE_PATH + "/uploadFile" + System.currentTimeMillis() + "/";
String originalFilename = file.getOriginalFilename();
log.info("缓存文件:"+originalFilename );
File filepath = new File(path);
if(!filepath.exists()) {
filepath.mkdirs();
}
File uploadFile = new File(path + originalFilename);
file.transferTo(uploadFile);
int sheetNo = 0;
int headRownumber = 0;
EasyExcel.read(uploadFile,new ProjectManagementExcelService()).sheet(sheetNo).headRowNumber(headRownumber).doRead();
List<ProjectManagementExcelModel> message = ProjectManagementExcelService.datas;
response.put("res", true);
response.put("message", message);
}catch (MyException e) {
log.error("uploadProjectDepartmentExcel",e.getReason());
response.put("res", false);
response.put("exception", e.getReason());
}catch (Exception e) {
log.error("uploadProjectDepartmentExcel",e.getMessage());
response.put("res", false);
response.put("exception", e.getMessage());
}
return returnData;
}
试过文本地址和文本流两种方法来导入excel,后来发现通过地址来导入excel是行的通的。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)