// read the data from the excel
public List readFromXML(InputStream input) throws Exception {
List<TeamRecord> teamRecord = new ArrayList<TeamRecord>();
List<String> wrong_data = new ArrayList<String>();
List rt_list = new ArrayList<List>();
HashSet<String> wrong_users = new HashSet<String>();
// String log="Start read Excel";
// System.out.println(log);
// all_log.add(log);
try {
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
boolean vali_date = true;
boolean firstLine = true;
setRmLoginNames(trackingTicketScriptDao.findrmLoginNames());
for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
Row row = rit.next();
if (firstLine) {
firstLine = false;
} else {
vali_date = validation.validate_exceldata(row, wrong_users, wrong_data);
if (vali_date == true) {
teamRecord.add(readexcelline(row));
}
}
rt_list.add(teamRecord);
rt_list.add(wrong_data);
rt_list.add(wrong_users);
}
} catch (IOException ex) {
wrong_data.add("Excel is NULL,Please check Excel!");
rt_list.add(teamRecord);
rt_list.add(wrong_data);
rt_list.add(wrong_users);
return rt_list;
}
return rt_list;
}
public List<String> insertExcelData(InputStream input) throws Exception {
// ExcelReader e_reader = new ExcelReader();
List all_records = readFromXML(input);
List<TeamRecord> records1 = (List<TeamRecord>) all_records.get(0);
List<TeamRecord> records = new ArrayList<TeamRecord>();
Set<String> right_user = new HashSet<String>();
List alldata = new ArrayList<List>();
List wrong_data = (List) all_records.get(1);
HashSet wrong_user = (HashSet) all_records.get(2);
for (TeamRecord record : records1) {
if (!wrong_user.contains(record.getRmlogin())) {
records.add(record);
}
}
Set<String> vali_wrong_user = new HashSet<String>();
Set<String> vali_right_user = new HashSet<String>();
List vali_wrong_data = new ArrayList<String>();
if (records.isEmpty()) {
String nodata = "There is no data that can be used in Excel";
System.out.println(nodata);
wrong_data.add(nodata);
alldata.add(wrong_data);
alldata.add(wrong_user);
alldata.add(right_user);
return alldata;
} else {
List<TaskData> taskdatas = parseTaskData(records);
List<UserTicketTrackingData> userdatas = parseUserData(taskdatas);
// validate user data and gain the right data
List list = validation.ValideUserData(userdatas, getStartDate(), getEndDate());
List<UserTicketTrackingData> validUserdatas = (List<UserTicketTrackingData>) list.get(0);
vali_wrong_user = (HashSet<String>) list.get(1);
vali_wrong_data = (List) list.get(2);
vali_right_user = (HashSet<String>) list.get(3);
// List<UserTicketTrackingData> validUserdatas = (List<UserTicketTrackingData>)
// (validation.ValideUserData(userdatas,getStartDate(),getEndDate())).get(0);
// vali_wrong_user =(HashSet)
// (validation.ValideUserData(userdatas,getStartDate(),getEndDate())).get(1);
// vali_wrong_data =(List)
// (validation.ValideUserData(userdatas,getStartDate(),getEndDate())).get(2);
// vali_right_user=(HashSet)
// (validation.ValideUserData(userdatas,getStartDate(),getEndDate())).get(3);
updateTracktingTicketData(validUserdatas);
}
// wrong_data and wrong_user is put into wrongdata
// alldata.add(vali_wrong_data);
// alldata.add(vali_wrong_user);
wrong_data.addAll(vali_wrong_data);
wrong_user.addAll(vali_wrong_user);
right_user.addAll(vali_right_user);
alldata.add(wrong_data);
alldata.add(wrong_user);
alldata.add(right_user);
return alldata;
}
