/**
* 捕获插入每一行错误数据的对象
* Created by zaz on 2018/10/15.
*/
public class RespCapitAssertsExportMsg implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "行数")
private Long rows;
/* @ApiModelProperty(value = "每行对应的错误对象")
private CapitalAssertsMsg assertsMsg;*/
@ApiModelProperty(value = "错误")
private String errorsMsg;
@ApiModelProperty(value = "资产编号")
private String capitalNoMsg;
/*
public CapitalAssertsMsg getAssertsMsg() {
return assertsMsg;
}
public void setAssertsMsg(CapitalAssertsMsg assertsMsg) {
this.assertsMsg = assertsMsg;
}*/
public Long getRows() {
return rows;
}
public void setRows(Long rows) {
this.rows = rows;
}
public String getErrorsMsg() {
return errorsMsg;
}
public void setErrorsMsg(String errorsMsg) {
this.errorsMsg = errorsMsg;
}
public String getCapitalNoMsg() {
return capitalNoMsg;
}
public void setCapitalNoMsg(String capitalNoMsg) {
this.capitalNoMsg = capitalNoMsg;
}
@Override
public String toString() {
return "RespCapitAssertsExportMsg{" +
"rows=" + rows +
", errorsMsg='" + errorsMsg + '\'' +
", capitalNoMsg='" + capitalNoMsg + '\'' +
'}';
}
}
/**
* H0110=文件为空
* H0111=Excel文件大小超过15M
* H0112=Excel文件格式错误
* H0113=Excel文件模型错误
*
* @param multipartFile
* @return
*/
public List<RespCapitAssertsExportMsg> insertBatchExport(MultipartFile multipartFile) {
if (null == multipartFile) {
throw new GlobalException("H0110");
} else {
//获取原始文件名(包括类型)
String fileName = multipartFile.getOriginalFilename();
//获取文件类型
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
if (multipartFile.getSize() > CapitalAssertsConstant.EXCEL_MAX_SIZE) {
throw new GlobalException("H0111");
} else if (!fileType.toLowerCase().matches(CapitalAssertsConstant.EXCEL_RULE)) {
throw new GlobalException("H0112");
} else {
HSSFWorkbook workbook;
HSSFSheet sheet;
try {
workbook = new HSSFWorkbook(multipartFile.getInputStream());
sheet = workbook.getSheetAt(0);
} catch (IOException e) {
throw new GlobalException("H0113");
}
// 获取到excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows(); //总行数
int lastRow = sheet.getLastRowNum(); //最后一行数
if (rows <= 1) {
throw new GlobalException("H0110"); //
}
if (!validateExcel(sheet.getRow(0))) { //校验EXport格式是否正确
throw new GlobalException("H0113");
}
List<RespCapitAssertsExportMsg> msgs = new ArrayList<>();
for (int i = 1; i <= lastRow; i++) {
// 检测去除空格时是否存在数据 导入时存在空格会默认为一条数据 1去除空格为没有数据 2为有数据
StringBuffer msgsbf = new StringBuffer("");
RespCapitAssertsExportMsg msg = new RespCapitAssertsExportMsg();
Row row = sheet.getRow(i);
if (row != null) {
//资产编号*
Cell cell = row.getCell(0); //第(i)第一列
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
//检查重复
if(StringUtil.isBlank(cell.getStringCellValue())){
msg.setRows((long)row.getRowNum()); //第几行
msgsbf.append("资产编号:不能为空\r");
}else{
if(!"字符长度64".equals("字符长度")){
msg.setRows((long)row.getRowNum()); //第几行
msgsbf.append("资产编号:最大字符长度64\n");
}
}
}
}
if(msg.getRows()!=null){
msg.setErrorsMsg(msgsbf.toString());
msgs.add(msg);
}
}
return msgs;
}
}
}
/**
EXP01=资产编号*
EXP02=资产名称*
EXP03=资产类别*
EXP04=入库时间*
EXP05=来源*
EXP06=购入时间*
EXP07=品牌*
EXP08=型号
EXP09=计量单位
EXP10=渠道
EXP11=金额
EXP12=使用期限*
EXP13=使用期限单位*
EXP14=保修开始时间
EXP15=过保时间
EXP16=备注
* <p>
* 判断excel文件模板是否正确
* </p>
* Author: zj <br/>
*/
private boolean validateExcel(Row row) {
if (!MessageUtil.getMsg("EXP01").equals(row.getCell(0).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP02").equals(row.getCell(1).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP03").equals(row.getCell(2).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP04").equals(row.getCell(3).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP05").equals(row.getCell(4).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP06").equals(row.getCell(5).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP07").equals(row.getCell(6).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP08").equals(row.getCell(7).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP09").equals(row.getCell(8).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP10").equals(row.getCell(9).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP11").equals(row.getCell(10).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP12").equals(row.getCell(11).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP13").equals(row.getCell(12).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP14").equals(row.getCell(13).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP15").equals(row.getCell(14).getStringCellValue().trim())) {
return false;
}
if (!MessageUtil.getMsg("EXP16").equals(row.getCell(15).getStringCellValue().trim())) {
return false;
}
return true;
}