SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Map<String, Integer> excelValueNum = new HashMap<String, Integer>(); //excel表单【字段名，列数】对应
UUID templateId = DataAccessFactory.getInstance().createUUID(templateIdStr);
Template template = das.queryTemplate(templateId); //得到表单
Flow flow = das.queryFlow(template.getFlowId());
Set<Field> allFields = GetAllFields(template);//表单所有字段,除出废弃字段
String addUser = (String)session.getAttribute("userName");
DataAccessSession das = DataAccessFactory.getInstance().createDataAccessSession(addUser, DataAccessFactory.magic);
Set<Field> allNeedFields = new HashSet<Field>();
//////////////////////////////////////////
try
{
File tmpFile = File.createTempFile("acctachment", ".attachment");
multipartFile.transferTo(tmpFile);
String realFileName = multipartFile.getOriginalFilename();
FileInputStream fis = new FileInputStream(tmpFile);
Workbook workbook = WorkbookFactory.create(fis);
if (fis != null) {
StreamCloserManager.closeInputStream(fis);
}
if (workbook == null) {
return "";
}
try{
Sheet sheet = workbook.getSheet(sheetName);
int rows = sheet.getPhysicalNumberOfRows();
Row row = null;
Cell cell = null;
if(rows==0)
return "";
row = sheet.getRow(0);//获得第一行数据分析每个字段对应的列号
String value = null;
int cells = row.getPhysicalNumberOfCells();
for(int j=0;j<cells;j++){
cell = row.getCell(j);
value = cell.getStringCellValue().trim();
if (value!=null && value.length()>0) {
excelValueNum.put(value, j);
}
}
if (rows==1) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setErrorDescription("数据为空");
errorInfoList.add(errorInfo);
flag = false;
}
Boolean isAllNeedFieldsIn = true; //必须字段是否全部满足
//判断必填字段是否都己存在
String needFieldName = GetNeedFieldString(excelValueNum, template);
if (needFieldName!=null && needFieldName.length()>0) {
ErrorInfo errorInfo = new ErrorInfo();
errorInfo.setErrorDescription("某些字段在表单中无法找到如：【"+needFieldName+"】,全部录入失败");
errorInfoList.add(errorInfo);
flag = false;
isAllNeedFieldsIn = false;
}
if (isAllNeedFieldsIn) {
for(int j=1;j<rows;j++)
{
row = sheet.getRow(j);
if(row!=null)
{
boolean isSingleFail = false; //单条信息录入错误
Map<String, Pair<Object, Object>> baseValueMap = new LinkedHashMap<String, Pair<Object, Object>>();
Map<UUID, Pair<Object, Object>> extValueMap = new LinkedHashMap<UUID, Pair<Object, Object>>();
Data data = das.addData(templateId);
String title = ""; //标题
ErrorInfo errorInfo = null;
if(data == null){
return "";
}
for (Field field : allFields) {
if (excelValueNum.keySet().contains(field.getName())) {
String fieldName = field.getName();
//excel中存在该字段,从excel中获取内容
String cellContentString = "";
java.util.Date cellContentDate = null;
if(row.getCell(excelValueNum.get(fieldName))!=null)
{
if(row.getCell(excelValueNum.get(fieldName)).getCellType()==XSSFCell.CELL_TYPE_NUMERIC) //日期类型
{
cellContentDate = row.getCell(excelValueNum.get(fieldName)).getDateCellValue();
cellContentString = CynthiaUtil.getValue(row, excelValueNum.get(fieldName));
}else{
//其它类型
cellContentString = CynthiaUtil.getValue(row, excelValueNum.get(fieldName));
}
}else {
//判断该字段是否为必填
if (allNeedFields.contains(field.getName())) {
//必填为空,错误
isSingleFail = true;
errorInfo = new ErrorInfo() ;
errorInfo.setErrorDescription("必填字段为空");
errorInfo.setErrorRowNum(j);
errorInfo.setErrorColumnName(field.getName());
break;
}
}
if (field.getDataType()==DataType.dt_timestamp) { //处理日期类型
Date timeDate = null;
try {
timeDate = Date.valueOf(sdf.format(cellContentDate));
} catch (Exception e) {
}
if (timeDate == null) {
try {
timeDate = Date.valueOf(cellContentString);
} catch (Exception e) {
}
}
if (timeDate == null && allNeedFields.contains(fieldName)) {
// 日期类型错误 返回错误
isSingleFail = true;
errorInfo = new ErrorInfo() ;
errorInfo.setErrorDescription("日期类型错误");
errorInfo.setErrorRowNum(j);
errorInfo.setErrorColumnName(field.getName());
break;
}else {
if (timeDate != null) {
data.setDate(field.getId(), timeDate);
extValueMap.put(field.getId(), new Pair<Object, Object>(null, timeDate));
}
}
}else if (field.getType()==Type.t_selection) { //处理单选类型
Option option = field.getOption(cellContentString);
if (option == null) {
if (allNeedFields.contains(field)) { //为空必填
//错误，单选选项错误
isSingleFail = true;
errorInfo = new ErrorInfo() ;
errorInfo.setErrorDescription("单选选项错误");
errorInfo.setErrorRowNum(j);
errorInfo.setErrorColumnName(field.getName());
break;
}
}else {
data.setSingleSelection(field.getId(), option.getId());
extValueMap.put(field.getId(), new Pair<Object, Object>(null,option.getId()));
}
}else { //普通字段类型
data.setString(field.getId(), cellContentString);
extValueMap.put(field.getId(), new Pair<Object, Object>(null, cellContentString));
}
}
}//end foreach
if (isSingleFail) {
//记录错误 开始下一条数据录入
failCount ++;
errorInfoList.add(errorInfo);
continue;
}
//问题概述
if (excelValueNum.get("标题")!=null) {
title = CynthiaUtil.getValue(row, excelValueNum.get("标题"));
}
//标题
data.setTitle(title);
baseValueMap.put("title", new Pair<Object, Object>(null,title));
data.setObject("logCreateUser", addUser); //添加人
//正文
if (excelValueNum.get("正文")!=null) {
String content = CynthiaUtil.getValue(row, excelValueNum.get("正文"));
data.setDescription(content);
baseValueMap.put("description", new Pair<Object, Object>(null, content));
}
//状态
UUID statId = null;
if (excelValueNum.get("状态")!=null) {
String statIdStr = CynthiaUtil.getValue(row, excelValueNum.get("状态"));
if (statIdStr != null && statIdStr.length() >0) {
Stat stat = flow.getStat(statIdStr);
if (stat == null) {
failCount ++;
errorInfo = new ErrorInfo() ;
errorInfo.setErrorDescription("状态不存在");
errorInfo.setErrorRowNum(j);