前言
这个功能前一阵在工作中也是遇到过,我用的POI版本也是比较低的,在这里我也只是想简单的记录一下思路及方法,为了方便日后查看……
maven依赖
<dependency>
<artifactId>poi-ooxml</artifactId>
<groupId>org.apache.poi</groupId>
<version>3.13</version>
</dependency>
<dependency>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
<version>3.13</version>
</dependency>
定义bean
一个sheet页对应一个bean,方便读取Excel数据做转换(我是五个sheet页,建议bean的成员变量类型都选择String,防止报错,需要其它类型后期还可以再转换)
/**
* 基本信息
* @author fuzhongxu
*
*/
public class BasicInformation {
private String CaseTitle;
private String CaseType;
private String CauseOfAction;//案由
private String HearingProcedure;//审理程序
private String TrialInstitutions;//审理机构
private String TrialInstitutionsCaseNo;//审理机构案号
private String TheCaseNumber;//所内案号
private String bdObject;//标的
private String CounselFee;//律师费数额
private String CounselFeePaymentAgreement;//律师费付款约定
private String Remarks;//备注
构造方法:略……
set/get:略……
}
/**
* 当事人-个人信息
* @author fuzhongxu
*
*/
public class PersonalInformationOfParties {
private String CaseTitle;
private String Attribute;//属性
private String EntrustingParty;//标为委托方
private String PartiesType;//当事人类型(个人/单位)
private String PartiesName;//当事人姓名
private String PartiesSex;//当事人性别
private String InsertCrm;//新建至主体管理系统
private String CertificateType;//证件类型
private String IDNumber;//证件号码
private Date DateOfBirth;//出生日期
private String Nation;//民族
private String Address;//住址
private String contactWay;//联系方式
private String PartiesRemarks;//当事人备注
构造方法:略……
set/get:略……
}
/**
* 当事人单位信息
* @author fuzhongxu
*
*/
public class PartyUnitInformation {
private String CaseTitle;
private String Attribute;//属性
private String EntrustingParty;//标为委托方
private String PartiesType;//当事人类型(个人/单位)
private String PartiesName;//当事人姓名
private String InsertCrm;//新建至主体管理系统
private String Domicile;//住所地
private String LegalRepresentative;//法定代表人
private String UnifiedSocialCreditCode;//统一社会信用代码
private String Remarks;//备注
构造方法:略……
set/get:略……
}
/**
* 审理人员信息
* @author fuzhongxu
*
*/
public class InformationOfAdjudicators {
private String CaseTitle;
private String AdjudicatorsName;//审理人员名称
private String contactWay;//联系方式
private String OtherInformation;//其他信息
构造方法:略……
set/get:略……
}
/**
* 辅助人员信息
* @author fuzhongxu
*
*/
public class SupportStaffInformation {
private String CaseTitle;
private String StaffPersonnelName;//辅助人员姓名
private String contactWay;//联系方式
private String OtherInformation;//其他信息
构造方法:略……
set/get:略……
}
核心代码(任意bean转换)
/**
* 实现任意类的封装
* @param sheet
* @param workBook
* @param aimClass
* @return
*/
public <T> List<T> parseFromExcel(Sheet sheet, int firstIndex, Class<T> aimClass) {
List<T> result = new ArrayList<T>();
//String format = null;
Date cellValue = null;
String cellContent = null;
try {
//对excel文档的第一页,即sheet1进行操作
int lastRaw = sheet.getPhysicalNumberOfRows();
for (int i = firstIndex; i <= lastRaw; i++) {
//第i行
Row row = sheet.getRow(i);
if(row == null){
continue;
}
T parseObject = aimClass.newInstance();
Field[] fields = aimClass.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
field.setAccessible(true);
Class<?> type = field.getType();
//第j列
Cell cell = row.getCell(j);
if(type.equals(Date.class)){
if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){
cellValue = cell.getDateCellValue();
/*SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
format = ft.format(cellValue);*/
}
}
if (cell == null)
continue;
//很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
cell.setCellType(Cell.CELL_TYPE_STRING);
cellContent = cell.getStringCellValue();
cellContent = "".equals(cellContent) ? "0" : cellContent;
if (type.equals(String.class)) {
field.set(parseObject, cellContent);
} else if (type.equals(char.class) || type.equals(Character.class)) {
field.set(parseObject, cellContent.charAt(0));
} else if (type.equals(int.class) || type.equals(Integer.class)) {
field.set(parseObject, Integer.parseInt(cellContent));
} else if (type.equals(long.class) || type.equals(Long.class)) {
field.set(parseObject, Long.parseLong(cellContent));
} else if (type.equals(float.class) || type.equals(Float.class)) {
field.set(parseObject, Float.parseFloat(cellContent));
} else if (type.equals(double.class) || type.equals(Double.class)) {
//field.set(parseObject, Double.parseDouble(cellContent));
field.set(parseObject, cellContent);//根据实际情况自行选择要double还是string(我这里虽然要的是string,但后期还是转成double了)
} else if (type.equals(short.class) || type.equals(Short.class)) {
field.set(parseObject, Short.parseShort(cellContent));
} else if (type.equals(byte.class) || type.equals(Byte.class)) {
field.set(parseObject, Byte.parseByte(cellContent));
} else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
field.set(parseObject, Boolean.parseBoolean(cellContent));
} else if(type.equals(Date.class)){
field.set(parseObject, cellValue);
}
}
result.add(parseObject);
}
return result;
} catch (Exception e) {
e.printStackTrace();
System.err.println("An error occured when parsing object from Excel. at " + this.getClass());
}
return result;
}
校验数据
这里就简单说一下,刚才需要转换成double类型的数据是如何转换校验的
Sheet sheet1 = workBook.getSheet("基本信息");
private static final Pattern PATTERN = Pattern.compile("^(([1-9]{1}\\d{0,11})|(0{1}))(\\.\\d{1,2})?$");
//parseFromExcel方法参数解读:(1)得到的sheet页(2)从第几行开始读数据(3)当前sheet页要转成对应的bean
List<BasicInformation> basicInformations = parseFromExcel(sheet1, 3, BasicInformation.class);
for(int i = 0; i < basicInformations.size();i++){
if(StringUtils.isNotBlank(basicInformations.get(i).getBdObject())){
try {
Double bdObject = Double.parseDouble(basicInformations.get(i).getBdObject());
if(bdObject instanceof Double == true){
BigDecimal bd = new BigDecimal(bdObject.toString());
BigDecimal db = new BigDecimal("999999999999.99");
//最大值是999999999999.99
if(1 == bd.compareTo(db)){
map = new HashMap<String, Object>();
map.put("particulars", "标的");
map.put("type", 8);
resultList1.add(map);
//小数点后最多保留两位
}else if(StringUtils.isNotBlank(bd.toPlainString().toString()) && !PATTERN.matcher(bd.toPlainString().toString()).matches()){
map = new HashMap<String, Object>();
map.put("particulars", "标的");
map.put("type", 7);
resultList1.add(map);
}
}
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
map = new HashMap<String, Object>();
map.put("particulars", "标的");
map.put("type", 2);
resultList1.add(map);
}
}
}