//Controller
Sheet sheet = DataUtil.getSheet(request, "myFile");
int rowBeginIndex = 1;
int cellEndIndex =5;
int cellBeginIndex = 0;
//批量导入
StringBuffer msg = quotaService.addBatch(sheet, rowBeginIndex,cellEndIndex, cellBeginIndex, user.getId());
if("<font color=\"red\">".equals(msg.toString())) {
msg.append("所有数据导入成功!");
}
msg.append("</font>");
//services
public StringBuffer addBatch(Sheet sheet, int rowBeginIndex, int cellEndIndex,
int cellBeginIndex, int id){
StringBuffer sb = new StringBuffer("<font color=\"red\">");
int i = 0;
int j = 0;
String quotaTypeName = "xxx";
String code = "";
int typeId = 0;
String quotaName = "";
String quotaCode = "";
int order = 0;
int quotaTypeId = 0;
int rows = sheet.getLastRowNum();
for (i = rowBeginIndex; i < rows + 1; i++) {
Row row = sheet.getRow(i);
try {
for (j = cellBeginIndex; j < cellEndIndex; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
code = "";
}else{
//防止单元格是数字时有异常
try {
code = cell.getStringCellValue();
} catch (Exception e) {
code = cell.getNumericCellValue() + "";
}
}
switch(j){
case 0:
if (StringUtils.isNotBlank(code)) {
if (quotaTypeName.equals(code)) {
typeId = quotaDAO.getQuotaIdByName(code);
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx名称错误),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx名称不能为空),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
break;
case 1:
if(StringUtils.isNotBlank(code)){
quotaName = code;
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx名称不能为空),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
break;
case 2:
if (StringUtils.isNotBlank(code)) {
if (quotaDAO.isCodeUnique(code,0)) {
quotaCode = code;
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(已存在xx),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx不能为空),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
break;
case 3:
if (StringUtils.isNotBlank(code)) {
try {
order = Math.round(Float.parseFloat(code)); //去掉数字后面自带的.0
if (!quotaDAO.isTypeOrderUnique(order, 0, typeId)) {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx存在重复),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
}catch(NumberFormatException ex){
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx不是数字),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx不能为空),该行导入失败!<br />");
j = cellEndIndex;
continue;
}
break;
case 4:
if (StringUtils.isNotBlank(code)) {
for (QuotaTypes q : QuotaTypes.values()) {
if (code.equals(q.getLable())) {
quotaTypeId = q.getCode();
}
}
if (quotaTypeId == 0) {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(无法指定xx),该行导入失败!<br />");
continue;
}
Quota quota = new Quota();
quota.setName(quotaName);
quota.setCreateTime(new Date());
quota.setCreateUser(id);
quota.setStatus(2);
quota.setCode(quotaCode);
quota.setTypeId(typeId);
quota.setQuotaType(quotaTypeId);
quota.setOrders(order);
quotaDAO.add(quota);
}else {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效(xx能为空),该行导入失败!<br />");
continue;
}
break;
default:
break;
}
}
}catch (Exception e) {
sb.append("第" + (i + 1) + "行,第" + (j + 1) + "列数据无效,该行导入失败!<br />");
e.printStackTrace();
}
}