/**
* This method is used to display the Excel content to command line.
*
* @param xlsPath
*/
@SuppressWarnings("unchecked")
public void displayFromExcel(InputStream inputStream) {
POIFSFileSystem fileSystem = null;
try {
fileSystem = new POIFSFileSystem(inputStream);
Map headerTable = null;
HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
System.out.println("LastRowNum:" + sheet.getLastRowNum());
Iterator<HSSFRow> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = rows.next();
// display row number in the console.
System.out.println("Row No.: " + row.getRowNum());
Contact contact = null;
if (row.getRowNum() == 0) {
headerTable = headerTable(row);
} else {
contact = processRow(row, headerTable);
if (contact != null) contactManager.saveContact(contact);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
throws UploadActionException {
StringBuffer response = new StringBuffer();
for (FileItem item : sessionFiles) {
if (false == item.isFormField()) {
try {
if (item.getName().endsWith(".xls")) {
POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
} else if (item.getName().endsWith(".xlsx")) {
// POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
XSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
XSSFRow row = (XSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
XSSFCell cell = (XSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
}
} catch (Exception e) {
throw new UploadActionException(e);
}
}
}
// / Remove files from session because we have a copy of them
removeSessionFileItems(request);
// / Send your customized message to the client.
return response.toString();
}
@DataProvider(name = "placeOrderCredentials")
public Iterator<Object[]> readFromExcelIterator() throws FileNotFoundException, IOException {
// prop.load(getClass().getResourceAsStream("AutomationEnv.properties"));
// String sourceXlsFileName=(String)prop.get("signUpAndOrderCard");
String fileName =
"C:\\Users\\WINQA\\workspace\\NewHkAutomationSuite\\Excel\\signUpandPlace.xls";
int sheetNo = 0;
ArrayList<Object[]> excelDataArray = new ArrayList<Object[]>();
int cnt = 0;
try {
InputStream input = new BufferedInputStream(new FileInputStream(fileName));
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(sheetNo);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
int cellCount = 0;
int flagStop = 0;
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
List<String> readExcelData = new LinkedList<String>();
while (cells.hasNext()) {
if (cellCount <= 10) {
int cellValueInt;
String CellValue;
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// System.out.print( cell.getNumericCellValue()+" " );
cellValueInt = (int) cell.getNumericCellValue();
CellValue = Integer.toString(cellValueInt);
} else {
CellValue = cell.getStringCellValue();
}
readExcelData.add(CellValue);
cnt++;
cellCount++;
}
if (cellCount == 11) break;
}
excelDataArray.add(new Object[] {readExcelData});
}
} catch (IOException ex) {
ex.printStackTrace();
}
return excelDataArray.iterator();
}
/** @param args */
public static void main(String[] args) {
// TODO Auto-generated method stub
// Directory path here
String path = "./plantillas";
String files;
File folder = new File(path);
File[] listOfFiles = folder.listFiles();
for (int i = 0; i < listOfFiles.length; i++) {
if (listOfFiles[i].isFile()) {
files = listOfFiles[i].getName();
System.out.println(files);
}
}
InputStream input;
try {
input = new BufferedInputStream(new FileInputStream("./plantillas/Panama.xls"));
POIFSFileSystem fs;
fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType())
System.out.print(cell.getNumericCellValue() + " ");
else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType())
System.out.print(cell.getStringCellValue() + " ");
else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType())
System.out.print(cell.getBooleanCellValue() + " ");
else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK ");
else System.out.print("Unknown cell type");
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
static StringBuilder readSheet(HSSFSheet sheet) {
StringBuilder json_sheet = new StringBuilder(1024 * 100);
int row_length = sheet.getPhysicalNumberOfRows();
Iterator<Row> rows = sheet.rowIterator();
HSSFRow row;
if (rows.hasNext()) row = (HSSFRow) rows.next();
else return null;
int col_length = row.getPhysicalNumberOfCells();
Iterator<Cell> cells = row.cellIterator();
HSSFCell cell;
if (cells.hasNext()) cell = (HSSFCell) cells.next();
else return null;
int first_row = cell.getRowIndex();
json_sheet.append("{'categories':[");
a:
for (int x = first_row; x < row_length; x++) {
row = sheet.getRow(x);
if (x > first_row) json_sheet.append("{");
for (int y = 0; y < col_length; y++) {
cell = row.getCell(y);
String value = "空白";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
value = "" + cell.getNumericCellValue();
// Log.v("数字",value);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
// Log.v("布尔",""+cell.getBooleanCellValue());
break a;
case HSSFCell.CELL_TYPE_STRING:
value = "'" + cell.getStringCellValue() + "'";
// Log.v("文本",value);
break;
}
if (y == 0) {
if (x == first_row) continue;
else json_sheet.append("'name':" + value + ",'type':'line','data':[");
} else json_sheet.append(value + ",");
}
json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
if (x == first_row) json_sheet.append("}\n[");
else json_sheet.append("},");
}
json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
// Log.v("JSON",json_sheet.toString());
return json_sheet;
}
// 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;
}
private List<GoodsDTO> parseFile(File file) {
try {
LOG.info("Starting parsing goods from file {}", file.getName());
List<GoodsDTO> goodsList = new ArrayList<>();
FileInputStream fileInputStream = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
HSSFSheet hssfSheet = workbook.getSheetAt(0);
Iterator rowIterator = hssfSheet.rowIterator();
while (rowIterator.hasNext()) {
HSSFRow row = (HSSFRow) rowIterator.next();
GoodsDTO parsedGoods = parseRow(row);
if (parsedGoods != null) {
goodsList.add(parsedGoods);
}
}
return goodsList;
} catch (IOException e) {
LOG.error("Could not parse file {}", file.getName(), e);
return null;
}
}
private boolean searchInExcel(String file) throws Exception {
Row row;
Cell cell;
String text;
boolean found = false;
InputStream in = null;
try {
in = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(in);
int sheets = wb.getNumberOfSheets();
OUTERMOST:
for (int i = 0; i < sheets; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
row = (Row) rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_STRING) {
text = cell.getStringCellValue();
found = searchText(text);
break OUTERMOST;
}
}
}
}
return found;
} finally {
if (in != null)
try {
in.close();
} catch (Exception e) {
}
}
}
/**
* 添加临时表格内容
*
* @param mainSheet -- 原表单内容
* @param subSheet -- 临时表单内容
* @return
*/
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
if (mainSheet == null || subSheet == null) return null;
// 判断报表是否允许输出
if (!isAllowOut(mainSheet)) return mainSheet;
// 原报表的最后一行
int endRowNum = mainSheet.getPhysicalNumberOfRows();
HSSFRow sourow = null, descrow = null;
HSSFCell sourcell = null, descell = null, orgcell = null;
int i = 0, offsetcnt = 0;
// 复制表格中的图片
copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());
// 设置以合并的单元格
CellRangeAddress range = null;
int mergedNum = subSheet.getNumMergedRegions();
for (i = 0; i < mergedNum; i++) {
range = subSheet.getMergedRegion(i);
range.setFirstRow(range.getFirstRow() + endRowNum);
range.setLastRow(range.getLastRow() + endRowNum);
mainSheet.addMergedRegion(range);
}
range = null;
// int k = 0;
// 设置相关参数
mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
mainSheet.setAutobreaks(subSheet.getAutobreaks());
mainSheet.setDialog(subSheet.getDialog());
mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
mainSheet.setFitToPage(subSheet.getFitToPage());
for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
sourow = (HSSFRow) iterow.next();
offsetcnt = sourow.getRowNum() + endRowNum;
descrow = mainSheet.createRow(offsetcnt);
descrow.setHeight(sourow.getHeight());
descrow.setHeightInPoints(sourow.getHeightInPoints());
java.util.Iterator<Cell> iter = sourow.cellIterator();
while (iter.hasNext()) {
sourcell = (HSSFCell) iter.next();
int column = sourcell.getColumnIndex();
descell = descrow.createCell(column);
// 取模板中的单元格，与来源表单位置相同
int row = sourcell.getRowIndex();
orgcell = mainSheet.getRow(row).getCell(column);
if (orgcell != null) {
// 取模板中的类型赋值
descell.setCellType(orgcell.getCellType());
// 取模板中的样式赋值
descell.setCellStyle(orgcell.getCellStyle());
} else {
_log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
}
if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
descell.setCellValue(sourcell.getStringCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
descell.setCellValue(sourcell.getNumericCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
}
sourow = null;
sourcell = null;
descrow = null;
orgcell = null;
}
return mainSheet;
}
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String filepath = null;
String id = null,
name = null,
email = null,
mobile = null,
doj = null,
password = null,
man1 = null,
man2 = null,
check = "success";
int level = 0, layer = 0;
/* filepath=request.getParameter("file");
*/
String message = "";
// out.print("<html><body><h3>"+filepath+"</h3></body></html>");
boolean isMultiPart = ServletFileUpload.isMultipartContent(request);
HttpSession session = request.getSession();
if (isMultiPart) {
ServletFileUpload upload = new ServletFileUpload();
try {
FileItemIterator itr = upload.getItemIterator(request);
while (itr.hasNext()) {
FileItemStream item = itr.next();
if (!item.isFormField()) {
String path = getServletContext().getInitParameter("file-upload");
request.setAttribute("path", path);
if (FileUpload.processFile(path, item)) {
filepath =
"C:\\Users\\shagayaraj\\Documents\\Tina\\webtechlab\\Ideation\\WebContent"
+ "\\"
+ item.getName();
String[] ext = filepath.split("\\.");
String ext1 = ext[0];
String ext2 = ext[1];
if (ext2.equals("xlsx")) {
int rc = 0;
FileInputStream fis = new FileInputStream(new File(filepath));
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet spreadsheet = wb.getSheetAt(0);
// System.out.println(list);
Iterator<Row> rowIterator = spreadsheet.iterator();
while (rowIterator.hasNext()) {
check = "success";
row = (XSSFRow) rowIterator.next();
rc++;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// out.print("<h3>"+cell.getCellType()+"</h3><br>");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (cell.getColumnIndex() == 4) {
DataFormatter df = new DataFormatter();
if ((df.formatCellValue(cell))
.matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
doj = df.formatCellValue(cell);
else {
message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
check = "error";
}
// out.print( "<h3>"+cvd+"</h3><br>");
} else if (cell.getColumnIndex() == 7) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]{10}"))
mobile = cell.getStringCellValue();
else {
message +=
"<p>Invalid Mobile Number format at row:"
+ rc
+ " and Column:8</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 5) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
level = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 6) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
layer = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
check = "error";
}
}
break;
case Cell.CELL_TYPE_STRING:
if (cell.getColumnIndex() == 0) {
id = cell.getStringCellValue();
String test;
if (id.isEmpty()) test = "invalid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(id);
if (user == null) {
test = "valid";
} else test = "invalid";
}
if (test.equals("invalid")) {
message +=
"<p>User Id is either null or already exists at row:"
+ rc
+ " and Column:1</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 8) {
man1 = cell.getStringCellValue();
String test;
if (man1.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man1);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 9) {
man2 = cell.getStringCellValue();
String test;
if (man2.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man2);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 1) {
if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
name = cell.getStringCellValue();
else {
message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 2) {
if ((cell.getStringCellValue())
.matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
email = cell.getStringCellValue();
else {
message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
break;
}
}
if (check.equals("success")) {
java.sql.Date date_joined = java.sql.Date.valueOf(doj);
userdao userd = new userdao();
userd.setUser(
id,
name,
email,
password,
date_joined,
level,
layer,
0,
mobile,
man1,
man2,
false,
true);
user_roledao user_roled = new user_roledao();
user_roled.setUserRolebyUId(id, "3");
}
}
fis.close();
} // if closed
else if (ext2.equals("xls")) {
int rc = 0;
File file = new File(filepath);
InputStream input = new BufferedInputStream(new FileInputStream(file));
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
rc++;
check = "success";
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (cell.getColumnIndex() == 4) {
DataFormatter df = new DataFormatter();
if ((df.formatCellValue(cell))
.matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
doj = df.formatCellValue(cell);
else {
message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
check = "error";
}
// out.print( "<h3>"+cvd+"</h3><br>");
} else if (cell.getColumnIndex() == 7) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]{10}"))
mobile = cell.getStringCellValue();
else {
message +=
"<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 5) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
level = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 6) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
layer = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
check = "error";
}
}
} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
if (cell.getColumnIndex() == 0) {
id = cell.getStringCellValue();
String test;
if (id.isEmpty()) test = "invalid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(id);
if (user == null) {
test = "valid";
} else test = "invalid";
}
if (test.equals("invalid")) {
message +=
"<p>User Id is either null or already exists at row:"
+ rc
+ " and Column:1</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 8) {
man1 = cell.getStringCellValue();
String test;
if (man1.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man1);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 9) {
man2 = cell.getStringCellValue();
String test;
if (man2.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man2);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 1) {
if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
name = cell.getStringCellValue();
else {
message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 2) {
if ((cell.getStringCellValue())
.matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
email = cell.getStringCellValue();
else {
message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
}
}
if (check.equals("success")) {
java.sql.Date date_joined = java.sql.Date.valueOf(doj);
userdao userd = new userdao();
userd.setUser(
id,
name,
email,
password,
date_joined,
level,
layer,
0,
mobile,
man1,
man2,
false,
true);
user_roledao user_roled = new user_roledao();
user_roled.setUserRolebyUId(id, "3");
}
}
} else {
message += "Unsupported File format!Choose files of format .xls or .xlsx only!";
}
if (message == null) {
message = "<p>Users added successfully</p>";
}
} else {
message += "File upload unsuccessful";
}
}
}
request.setAttribute("errormsg", message);
RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
rd.forward(request, response);
} catch (FileNotFoundException e) {
message += "The given file cannot be found!";
request.setAttribute("errormsg", message);
RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
rd.forward(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
private ArrayList<WorkInfoDto> readExcelFromInputstream(InputStream inputstream) {
// // HSSFWorkbook wb = new HSSFWorkbook(new
// // FileInputStream("e:\\workbook.xls"));
// // HSSFSheet sheet = wb.getSheetAt(0);
// //
// // for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator();
// // iter.hasNext();) {
// // Row row = iter.next();
// // for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator();
// // iter2.hasNext();) {
// // Cell cell = iter2.next();
// // String content = cell.getStringCellValue();// 除非是sring类型，否则这样迭代读取会有错误
// // System.out.println(content);
// HSSFWorkbook rwb = null;
// try {
//
// rwb = new HSSFWorkbook(inputstream);
// HSSFSheet st = rwb.getSheetAt(0);
// if (st != null) {
// ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
// ArrayList<String> lables = new ArrayList<String>();
// boolean first = true;
// for (Iterator<Row> iter = st.rowIterator(); iter.hasNext();) {
// Row row = iter.next();
// if (first) {
// for (Iterator<Cell> iter2 = row.cellIterator(); iter2
// .hasNext();) {
// Cell cell = iter2.next();
// lables.add(cell.getStringCellValue());
// }
// first = false;
// }
//
// else {
// int j = 0;
// WorkInfoDto e = new WorkInfoDto();
// for (Iterator<Cell> iter2 = row.cellIterator(); iter2
// .hasNext();) {
// Cell cell = iter2.next();
// String val = "";
// if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
// val = String
// .valueOf(cell.getBooleanCellValue());
// else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
// val = String.valueOf((int) cell
// .getNumericCellValue());
// else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
// val = cell.getStringCellValue();
// setEmployeeValue(lables.get(j++), val, e);
// }
// list.add(e);
// }
// }
// return list;
// }
// } catch (Exception ex) {
// ex.printStackTrace();
// } finally {
// try {
// inputstream.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
// return null;
boolean success = true;
HSSFWorkbook rwb = null;
try {
rwb = new HSSFWorkbook(inputstream);
HSSFSheet st = rwb.getSheetAt(0);
if (st != null) {
ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
ArrayList<String> lables = new ArrayList<String>();
boolean first = true;
for (Iterator<Row> iter = st.rowIterator(); iter.hasNext(); ) {
Row row = iter.next();
if (first) {
for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
Cell cell = iter2.next();
lables.add(cell.getStringCellValue());
}
first = false;
} else {
int j = 0;
WorkInfoDto e = new WorkInfoDto();
boolean complete = true;
for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
Cell cell = iter2.next();
String val = "";
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
val = String.valueOf(cell.getBooleanCellValue());
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
val = String.valueOf((int) cell.getNumericCellValue());
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
val = cell.getStringCellValue();
if (lables.get(j).toLowerCase().equals("id") && exist(val)) {
System.out.println(lables.get(j) + " : " + val);
complete = false;
success = false;
}
setEmployeeValue(lables.get(j++), val, e);
}
if (complete) list.add(e);
else {
sessionManager.addGlobalMessageWarn(
"ID: " + e.getId() + " has existed. This employee is not added into system.",
null);
// FacesMessage message = new FacesMessage("ID: " +
// e.getId() +
// " has existed. This employee is not added into system.",null);
}
}
}
if (success)
sessionManager.addGlobalMessageInfo("The data file is successfully uploaded.", null);
else
sessionManager.addGlobalMessageWarn(
"There are employees that not be added to the system.", null);
return list;
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
inputstream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* Process the specified HTTP request, and create the corresponding HTTP response (or forward to
* another web component that will create it). Return an <code>ActionForward</code> instance
* describing where and how control should be forwarded, or <code>null</code> if the response has
* already been completed.
*
* @param mapping The ActionMapping used to select this instance
* @param form The optional ActionForm bean for this request (if any)
* @param request The HTTP request we are processing
* @param response The HTTP response we are creating
* @exception Exception if business logic throws an exception
*/
public ActionForward execute(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// Extract attributes we will need
MessageResources messages = getResources(request);
// save errors
ActionMessages errors = new ActionMessages();
// START check for login (security)
if (!SecurityService.getInstance().checkForLogin(request.getSession(false))) {
return (mapping.findForward("welcome"));
}
// END check for login (security)
// START get id of current quote from either request, attribute, or cookie
// id of quote from request
String projectId = null;
projectId = request.getParameter("projectViewId");
// check attribute in request
if (projectId == null) {
projectId = (String) request.getAttribute("projectViewId");
}
// id of quote from cookie
if (projectId == null) {
projectId = StandardCode.getInstance().getCookie("projectViewId", request.getCookies());
}
// default client to first if not in request or cookie
if (projectId == null) {
List results = QuoteService.getInstance().getQuoteList();
Quote1 first = (Quote1) results.get(0);
projectId = String.valueOf(first.getQuote1Id());
}
// projectId="6553";
Integer id = Integer.valueOf(projectId);
String[] dataValue = new String[11];
Project p = ProjectService.getInstance().getSingleProject(Integer.parseInt(projectId));
// END get id of current quote from either request, attribute, or cookie
// get quote to add files to
// Quote1 q = QuoteService.getInstance().getSingleQuote(p.getQuotes());
Set sources = p.getSourceDocs();
// get the lin task to update
// String linTaskId = StandardCode.getInstance().getCookie("quoteViewGeneralTradosUploadId",
// request.getCookies());
System.out.println(sources.size());
File folder = new File("C:/log");
File[] listOfFiles = folder.listFiles();
for (int ij = 0; ij < listOfFiles.length; ij++) {
if (listOfFiles[ij].isFile() && (listOfFiles[ij].getName().endsWith(".log"))
|| listOfFiles[ij].getName().endsWith(".xls")
|| listOfFiles[ij].getName().endsWith(".xlsx")
|| listOfFiles[ij].getName().endsWith(".xml")) {
String lang = "";
System.out.println("File " + listOfFiles[ij].getName());
String myFile = listOfFiles[ij].getName();
Integer leng = myFile.length();
if (listOfFiles[ij].getName().endsWith(".log")) {
lang =
(String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4));
} else if (listOfFiles[ij].getName().endsWith(".xlsx")) {
lang =
(String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 7, leng - 5));
} else {
lang =
(String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4));
}
// List sourceLang = QuoteService.getInstance().getSourceLang1(q);
for (Iterator sourceIter = sources.iterator(); sourceIter.hasNext(); ) {
SourceDoc sd = (SourceDoc) sourceIter.next();
List targetLang = QuoteService.getInstance().getTargetLang(sd.getSourceDocId());
for (Iterator linTargetIter = sd.getTargetDocs().iterator(); linTargetIter.hasNext(); ) {
TargetDoc td = (TargetDoc) linTargetIter.next();
if (td.getLanguage().equalsIgnoreCase(lang)) {
List linTasklist = QuoteService.getInstance().getLinTask(td.getTargetDocId());
for (int k = 0; k < linTasklist.size(); k++) {
LinTask lt = (LinTask) linTasklist.get(k);
// get input stream
// InputStream in = listOfFiles[ij].getInputStream();
if (listOfFiles[ij].getName().endsWith(".log")) {
FileInputStream in = new FileInputStream(listOfFiles[ij]);
// byte[] fileData = listOfFiles[ij].getFileData(); //byte array of entire file
long length = listOfFiles[ij].length();
byte[] fileData = new byte[(int) length];
in.read(fileData); // read data into fileData
String entireRead = new String(fileData); // the entire file as a string
String[] lines = entireRead.split("\n"); // lines within the file
String line = new String(""); // each line
// scroll to totals
int j = 0; // line numbers
try {
while (true) {
line = lines[j++];
if (line != null
&& line.length() > 12
&& line.substring(0, 13).equals("Analyse Total")) {
break;
}
}
// move to repetitions line
j++;
j++;
j++;
String[] parts; // each number per line
String wordRep = null;
String word100 = null;
String word95 = null;
String word85 = null;
String word75 = null;
String word50 = null;
String wordNo = null;
String wordPerfect = null;
String wordContext = null;
String wordTotal = null;
// wordRep
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 3) { // if at the words column
wordRep = parts[i];
break;
}
}
// word100
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 3) { // if at the words column
word100 = parts[i];
break;
}
}
// word95
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 5) { // if at the words column
word95 = parts[i];
break;
}
}
// word85
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 5) { // if at the words column
word85 = parts[i];
break;
}
}
// word75
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 5) { // if at the words column
word75 = parts[i];
break;
}
}
// word50
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 5) { // if at the words column
word50 = parts[i];
break;
}
}
// wordNo
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 4) { // if at the words column
wordNo = parts[i];
break;
}
}
// wordTotal
line = lines[j++];
parts = line.split(" ");
for (int i = 0, counter = 0; i < parts.length; i++) {
if (parts[i].length() > 0) { // look for non-blank items and count them
counter++;
}
if (counter == 3) { // if at the words column
wordTotal = parts[i];
break;
}
}
// END process the trados .log file
// remove commas from trados values
wordRep = wordRep.replaceAll(",", "");
word100 = word100.replaceAll(",", "");
word95 = word95.replaceAll(",", "");
word85 = word85.replaceAll(",", "");
word75 = word75.replaceAll(",", "");
word50 = word50.replaceAll(",", "");
wordNo = wordNo.replaceAll(",", "");
// wordPerfect=null;
// wordContext=null;
wordTotal = wordTotal.replaceAll(",", "");
// convert trados values from strings to numbers
Integer numRep = Integer.valueOf(wordRep);
Integer num100 = Integer.valueOf(word100);
Integer num95 = Integer.valueOf(word95);
Integer num85 = Integer.valueOf(word85);
Integer num75 = Integer.valueOf(word75);
Integer num50 = Integer.valueOf(word50);
Integer numNo = Integer.valueOf(wordNo);
Double numTotal = Double.valueOf(wordTotal);
// find totals to save to lin task
int numNew = num50.intValue() + numNo.intValue();
int num8599 = num95.intValue() + num85.intValue();
int numNew4 = num75.intValue() + numNew;
if (lt.getTaskName().equalsIgnoreCase("Translation")) {
// set new trados values for the lin task
lt.setWordRep(numRep);
lt.setWord100(num100);
lt.setWord95(num95);
lt.setWord85(num85);
lt.setWord75(num75);
lt.setWordNew(new Integer(numNew));
lt.setWord8599(new Integer(num8599));
lt.setWordNew4(new Double(numNew4));
lt.setWordTotal(numTotal);
} else if (lt.getTaskName().equalsIgnoreCase("editing")) {
lt.setWordNew4(numTotal);
lt.setWordTotal(numTotal);
}
// upload the new trados values to db
ProjectService.getInstance().updateLinTask(lt);
// START get file list
// get input stream
in.close();
in = new FileInputStream(listOfFiles[ij]);
length = listOfFiles[ij].length();
fileData = new byte[(int) length];
in.read(fileData); // read data into fileData
entireRead = new String(fileData); // the entire file as a string
lines = entireRead.split("\n"); // lines within the file
line = new String(""); // each line
} catch (Exception e) {
}
in.close();
} else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xls")) {
POIFSFileSystem fs =
new POIFSFileSystem(
new FileInputStream("C:/log/" + listOfFiles[ij].getName()));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int count = 0, i = 0;
String flag = "true";
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
row = (HSSFRow) rows.next();
count = 0;
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
cell = (HSSFCell) cells.next();
count++;
try {
if (count == 4 && flag.equalsIgnoreCase("true")) {
dataValue[i++] = cell.toString();
System.out.println("cel value----------> " + cell.toString());
if (i > 10) {
flag = "false";
}
}
} catch (Exception e) {
System.out.println("Integer Value" + count++);
}
}
}
Integer numRep = Math.round(Float.parseFloat(dataValue[2]));
Integer num100 = Math.round(Float.parseFloat(dataValue[4]));
Integer num95 = Math.round(Float.parseFloat(dataValue[5]));
Integer num85 = Math.round(Float.parseFloat(dataValue[6]));
Integer num75 = Math.round(Float.parseFloat(dataValue[7]));
Integer num50 = Math.round(Float.parseFloat(dataValue[8]));
Integer numNo = Math.round(Float.parseFloat(dataValue[9]));
Integer numPerfect = Math.round(Float.parseFloat(dataValue[1]));
Integer numContext = Math.round(Float.parseFloat(dataValue[3]));
Double numTotal = Double.valueOf(dataValue[10]);
// numRep = Integer.parseInt(dataValue[1]);
int numNew = num50.intValue() + numNo.intValue();
int num8599 = num95.intValue() + num85.intValue();
int numNew4 = num75.intValue() + numNew;
if (lt.getTaskName().equalsIgnoreCase("Translation")) {
// set new trados values for the lin task
lt.setWordRep(numRep);
lt.setWord100(num100);
lt.setWord95(num95);
lt.setWord85(num85);
lt.setWord75(num75);
lt.setWordNew(new Integer(numNew));
lt.setWord8599(new Integer(num8599));
lt.setWordNew4(new Double(numNew4));
lt.setWordContext(numContext);
lt.setWordPerfect(numPerfect);
lt.setWordTotal(numTotal);
} else if (lt.getTaskName().equalsIgnoreCase("editing")) {
lt.setWordNew4(numTotal);
lt.setWordTotal(numTotal);
}
// upload the new trados values to db
ProjectService.getInstance().updateLinTask(lt);
} else if (listOfFiles[ij].isFile()
&& listOfFiles[ij].getName().endsWith(".xlsx")) {
// POIFSFileSystem fs = new
// POIFSFileSystem(new FileInputStream("C:/log/" + listOfFiles[ij].getName()));
// File file = new File("C:/log/" +
// listOfFiles[ij].getName());
// OPCPackage pkg = OPCPackage.open(new
// FileInputStream(file.getAbsolutePath()));
// XSSFWorkbook wb = new XSSFWorkbook(pkg);
InputStream fs = new FileInputStream("C:/log/" + listOfFiles[ij].getName());
XSSFWorkbook wb = new XSSFWorkbook(fs);
// XSSFWorkbook wb = new XSSFWorkbook(fs);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
int count = 0, i = 0;
String flag = "true";
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
row = (XSSFRow) rows.next();
count = 0;
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
cell = (XSSFCell) cells.next();
count++;
try {
if (count == 4 && flag.equalsIgnoreCase("true")) {
dataValue[i++] = cell.toString();
System.out.println("cel value----------> " + cell.toString());
if (i > 10) {
flag = "false";
}
}
} catch (Exception e) {
System.out.println("Integer Value" + count++);
}
}
}
Integer numRep = Math.round(Float.parseFloat(dataValue[2]));
Integer num100 = Math.round(Float.parseFloat(dataValue[4]));
Integer num95 = Math.round(Float.parseFloat(dataValue[5]));
Integer num85 = Math.round(Float.parseFloat(dataValue[6]));
Integer num75 = Math.round(Float.parseFloat(dataValue[7]));
Integer num50 = Math.round(Float.parseFloat(dataValue[8]));
Integer numNo = Math.round(Float.parseFloat(dataValue[9]));
Double numTotal = Double.valueOf(dataValue[10]);
// numRep = Integer.parseInt(dataValue[1]);
int numNew = num50.intValue() + numNo.intValue();
int num8599 = num95.intValue() + num85.intValue();
int numNew4 = num75.intValue() + numNew;
if (lt.getTaskName().equalsIgnoreCase("Translation")) {
// set new trados values for the lin task
lt.setWordRep(numRep);
lt.setWord100(num100);
lt.setWord95(num95);
lt.setWord85(num85);
lt.setWord75(num75);
lt.setWordNew(new Integer(numNew));
lt.setWord8599(new Integer(num8599));
lt.setWordNew4(new Double(numNew4));
lt.setWordTotal(numTotal);
} else if (lt.getTaskName().equalsIgnoreCase("editing")) {
lt.setWordNew4(numTotal);
lt.setWordTotal(numTotal);
}
// upload the new trados values to db
ProjectService.getInstance().updateLinTask(lt);
} else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xml")) {
InputStream in = new FileInputStream("C:/log/" + listOfFiles[ij].getName());
System.setProperty(
"javax.xml.parsers.DocumentBuilderFactory",
"com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl");
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(in);
doc.getDocumentElement().normalize();
System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
Integer numRep = 0;
Integer num100 = 0;
Integer num95 = 0;
Integer num85 = 0;
Integer num75 = 0;
Integer num50 = 0;
Integer numNo = 0;
Integer numTotal = 0;
Integer numContext = 0;
Integer numPerfect = 0;
NodeList batchTotal = doc.getElementsByTagName("batchTotal");
// Element eElement = (Element) batchTotal;
// NodeList analyse =
// eElement.getElementsByTagName("analyse");
if (batchTotal != null && batchTotal.getLength() > 0) {
Node node = batchTotal.item(0);
if (node.getNodeType() == Node.ELEMENT_NODE) {
Element eElement = (Element) node;
NodeList analyse = eElement.getElementsByTagName("analyse");
if (analyse != null && analyse.getLength() > 0) {
Node node1 = analyse.item(0);
if (node1.getNodeType() == Node.ELEMENT_NODE) {
Element eElement1 = (Element) node1;
// NodeList analyse1 =
// eElement.getElementsByTagName("analyse");
eElement1.getElementsByTagName("fuzzy").item(0).getTextContent();
NodeList fuzzy = doc.getElementsByTagName("fuzzy");
for (int temp = 0; temp < fuzzy.getLength(); temp++) {
Node nNode = fuzzy.item(temp);
System.out.println("\nCurrent Element :" + nNode.getNodeName());
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
if (eElement2.getAttribute("min").equalsIgnoreCase("50")
&& eElement2.getAttribute("max").equalsIgnoreCase("74")) {
num50 = Integer.parseInt(eElement2.getAttribute("words"));
}
if (eElement2.getAttribute("min").equalsIgnoreCase("75")
&& eElement2.getAttribute("max").equalsIgnoreCase("84")) {
num75 = Integer.parseInt(eElement2.getAttribute("words"));
}
if (eElement2.getAttribute("min").equalsIgnoreCase("85")
&& eElement2.getAttribute("max").equalsIgnoreCase("94")) {
num85 = Integer.parseInt(eElement2.getAttribute("words"));
}
if (eElement2.getAttribute("min").equalsIgnoreCase("95")
&& eElement2.getAttribute("max").equalsIgnoreCase("99")) {
num95 = Integer.parseInt(eElement2.getAttribute("words"));
}
}
}
//
eElement1.getElementsByTagName("new").item(0).getTextContent();
NodeList new1 = doc.getElementsByTagName("new");
for (int temp = 0; temp < new1.getLength(); temp++) {
Node nNode = new1.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numNo = Integer.parseInt(eElement2.getAttribute("words"));
}
}
eElement1.getElementsByTagName("total").item(0).getTextContent();
NodeList total = doc.getElementsByTagName("total");
for (int temp = 0; temp < total.getLength(); temp++) {
Node nNode = total.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numTotal = Integer.parseInt(eElement2.getAttribute("words"));
}
}
eElement1.getElementsByTagName("exact").item(0).getTextContent();
NodeList exact = doc.getElementsByTagName("exact");
for (int temp = 0; temp < exact.getLength(); temp++) {
Node nNode = exact.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
num100 = Integer.parseInt(eElement2.getAttribute("words"));
}
}
eElement1.getElementsByTagName("perfect").item(0).getTextContent();
NodeList perfect = doc.getElementsByTagName("perfect");
for (int temp = 0; temp < perfect.getLength(); temp++) {
Node nNode = perfect.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numPerfect = Integer.parseInt(eElement2.getAttribute("words"));
}
}
eElement1.getElementsByTagName("repeated").item(0).getTextContent();
NodeList repeated = doc.getElementsByTagName("repeated");
for (int temp = 0; temp < repeated.getLength(); temp++) {
Node nNode = repeated.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numRep = Integer.parseInt(eElement2.getAttribute("words"));
}
}
try {
eElement1
.getElementsByTagName("crossFileRepeated")
.item(0)
.getTextContent();
NodeList crossFileRepeated =
doc.getElementsByTagName("crossFileRepeated");
for (int temp = 0; temp < crossFileRepeated.getLength(); temp++) {
Node nNode = crossFileRepeated.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numRep += Integer.parseInt(eElement2.getAttribute("words"));
}
}
} catch (Exception e) {
}
eElement1.getElementsByTagName("inContextExact").item(0).getTextContent();
NodeList inContextExact = doc.getElementsByTagName("inContextExact");
for (int temp = 0; temp < inContextExact.getLength(); temp++) {
Node nNode = inContextExact.item(temp);
if (nNode.getNodeType() == Node.ELEMENT_NODE) {
Element eElement2 = (Element) nNode;
numContext = Integer.parseInt(eElement2.getAttribute("words"));
}
}
}
}
}
}
int numNew = num50.intValue() + numNo.intValue();
int num8599 = num95.intValue() + num85.intValue();
int numNew4 = num75.intValue() + numNew;
if (lt.getTaskName().equalsIgnoreCase("Translation")) {
// set new trados values for the lin task
lt.setWordRep(numRep);
lt.setWord100(num100);
lt.setWord95(num95);
lt.setWord85(num85);
lt.setWord75(num75);
lt.setWordNew(new Integer(numNew));
lt.setWord8599(new Integer(num8599));
lt.setWordNew4(new Double(numNew4));
lt.setWordTotal(new Double(numTotal));
lt.setWordContext(numContext);
lt.setWordPerfect(numPerfect);
} else if (lt.getTaskName().equalsIgnoreCase("editing")) {
lt.setWordNew(new Integer(numTotal));
lt.setWordNew4(new Double(numTotal));
lt.setWordTotal(new Double(numTotal));
}
// upload the new trados values to db
ProjectService.getInstance().updateLinTask(lt);
} else {
System.out.println("no Match");
request.setAttribute("isError", "error");
return (mapping.findForward("Error"));
}
}
}
}
}
} else if (listOfFiles[ij].isDirectory()) {
System.out.println("Directory " + listOfFiles[ij].getName());
}
}
deleteFile("C:/log");
// END get file list
// Forward control to the specified success URI
return (mapping.findForward("Success"));
}
@Override
public ExcelData getExcelData(String relativeExcelPath) {
FileInputStream fis = null;
ExcelData dataMap = new ExcelData();
try {
fis = new FileInputStream(relativeExcelPath);
//
// Create an excel workbook from the file system.
//
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
boolean headerFlag = true;
List<String> hdrList = new ArrayList<String>();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
if (headerFlag) {
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
String hdrValue = cell.getStringCellValue();
dataMap.put(hdrValue, new ArrayList<String>());
hdrList.add(hdrValue);
headerFlag = false;
}
} else {
Iterator<String> hdrIterator = hdrList.iterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
String cellvalue = "";
if (cell.getCellType() == 0) cell.setCellType(Cell.CELL_TYPE_STRING);
cellvalue = cell.getStringCellValue();
String headerName = hdrIterator.next();
List<String> data = dataMap.get(headerName);
data.add(cellvalue);
dataMap.put(headerName, data);
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return dataMap;
}
/**
* 导入 excel
*
* @param inputstream : 文件输入流
* @param pojoClass : 对应的导入对象 (每行记录)
* @return
*/
public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) {
Collection dist = new ArrayList<Object>();
try {
// 得到目标目标类的所有的字段列表
Field filed[] = pojoClass.getDeclaredFields();
// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
Map<String, Method> fieldSetMap = new HashMap<String, Method>();
Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
// 循环读取所有字段
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
// 得到单个字段上的Annotation
Excel excel = f.getAnnotation(Excel.class);
// 如果标识了Annotationd的话
if (excel != null) {
// 构造设置了Annotation的字段的Setter方法
String fieldname = f.getName();
String setMethodName =
"set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
// 构造调用的method，
Method setMethod = pojoClass.getMethod(setMethodName, new Class[] {f.getType()});
// 将这个method以Annotaion的名字为key来存入。
// 对于重名将导致 覆盖 失败，对于此处的限制需要
fieldSetMap.put(excel.exportName(), setMethod);
if (excel.importConvertSign() == 1) {
// ----------------------------------------------------------------
// update-begin--Author:Quainty Date:20130524 for：[8]excel导出时间问题
// 用get/setXxxxConvert方法名的话， 由于直接使用了数据库绑定的Entity对象，注入会有冲突
StringBuffer setConvertMethodName = new StringBuffer("convertSet");
setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
setConvertMethodName.append(fieldname.substring(1));
// update-end--Author:Quainty Date:20130524 for：[8]excel导出时间问题
// ----------------------------------------------------------------
Method getConvertMethod =
pojoClass.getMethod(setConvertMethodName.toString(), new Class[] {String.class});
fieldSetConvertMap.put(excel.exportName(), getConvertMethod);
}
}
}
// 将传入的File构造为FileInputStream;
// // 得到工作表
HSSFWorkbook book = new HSSFWorkbook(inputstream);
// // 得到第一页
HSSFSheet sheet = book.getSheetAt(0);
// // 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
// 得到第一行，也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 将标题的文字内容放入到一个map中。
Map titlemap = new HashMap();
// 从标题第一列开始
int i = 0;
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
titlemap.put(i, value);
i = i + 1;
}
// 用来格式化日期的DateFormat
// SimpleDateFormat sf;
while (row.hasNext()) {
// 标题下的第一行
Row rown = row.next();
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
// 得到传入类的实例
Object tObject = pojoClass.newInstance();
int k = 0;
// 遍历一行的列
while (cellbody.hasNext()) {
Cell cell = cellbody.next();
// 这里得到此列的对应的标题
String titleString = (String) titlemap.get(k);
// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
if (fieldSetMap.containsKey(titleString)) {
Method setMethod = (Method) fieldSetMap.get(titleString);
// 得到setter方法的参数
Type[] ts = setMethod.getGenericParameterTypes();
// 只要一个参数
String xclass = ts[0].toString();
// 判断参数类型
if (Cell.CELL_TYPE_STRING == cell.getCellType()
&& fieldSetConvertMap.containsKey(titleString)) {
// 目前只支持从String转换
fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue());
} else {
if (xclass.equals("class java.lang.String")) {
// 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了：
cell.setCellType(Cell.CELL_TYPE_STRING);
setMethod.invoke(tObject, cell.getStringCellValue());
} else if (xclass.equals("class java.util.Date")) {
// update-start--Author:Quainty Date:20130523 for：日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
Date cellDate = null;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// 日期格式
cellDate = cell.getDateCellValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING: 如果不是 yyyy-mm-dd hh:mm:ss 的格式就不对(wait to
// do:有局限性)
cellDate = stringToDate(cell.getStringCellValue());
}
setMethod.invoke(tObject, cellDate);
//// update-start--Author:lihuan Date:20130423 for：导入bug修复直接将导出的Excel导入出现的bug的修复
////
// --------------------------------------------------------------------------------------------
// String cellValue = cell.getStringCellValue();
// Date theDate = stringToDate(cellValue);
// setMethod.invoke(tObject, theDate);
//// update-end--Author:lihuan Date:20130423 for：导入bug修复直接将导出的Excel导入出现的bug的修复
////
// --------------------------------------------------------------------------------------------
} else if (xclass.equals("class java.lang.Boolean")) {
boolean valBool;
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
valBool = cell.getBooleanCellValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valBool =
cell.getStringCellValue().equalsIgnoreCase("true")
|| (!cell.getStringCellValue().equals("0"));
}
setMethod.invoke(tObject, valBool);
} else if (xclass.equals("class java.lang.Integer")) {
Integer valInt;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valInt = (new Double(cell.getNumericCellValue())).intValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valInt = new Integer(cell.getStringCellValue());
}
setMethod.invoke(tObject, valInt);
} else if (xclass.equals("class java.lang.Long")) {
Long valLong;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valLong = (new Double(cell.getNumericCellValue())).longValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valLong = new Long(cell.getStringCellValue());
}
setMethod.invoke(tObject, valLong);
} else if (xclass.equals("class java.math.BigDecimal")) {
BigDecimal valDecimal;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valDecimal = new BigDecimal(cell.getNumericCellValue());
} else { // 全认为是 Cell.CELL_TYPE_STRING
valDecimal = new BigDecimal(cell.getStringCellValue());
}
setMethod.invoke(tObject, valDecimal);
//// ----------------------------------------------------------------
//// update-begin--Author:sky Date:20130422
// for：取值类型调整cell.getNumberCellValue-->>getStringCellValue
// setMethod.invoke(tObject, new BigDecimal(cell.getStringCellValue()));
//// update-end--Author:sky Date:20130422 for：取值类型调整
//// ----------------------------------------------------------------
// update-end--Author:Quainty Date:20130523 for：日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
}
}
}
// 下一列
k = k + 1;
}
dist.add(tObject);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return dist;
}
/**
* Enregistrer des notes a partir de fichier excel (.xls) importés
*
* @return la chaine de navigation pour la suite des opérations
*/
public String enregistrerImport() {
if (fichierImport != null && fichierImport.getSize() != 0) {
ByteArrayInputStream stream = new ByteArrayInputStream(fichierImport.getContents());
try {
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cellmat = null;
HSSFCell cellnote = null;
int numcell = 0;
// recupere tous les eleves figurant dans le fichier importé
for (Iterator<Row> rowIt = sheet.rowIterator(); rowIt.hasNext(); ) {
if (numcell >= 4) {
row = (HSSFRow) rowIt.next();
cellnote = row.getCell(5);
cellmat = row.getCell(2);
if (cellmat != null && cellnote != null) {
// nous verifions ici si la note est comprise dans la fourchette voulue
if ((float) cellnote.getNumericCellValue() > 20
|| (float) cellnote.getNumericCellValue() < 0) {
Repertoire.addMessageerreur(
"vous avez entrez une ou plusieurs note(s) qui n'est (sont) pas comprise en zero et 20");
return "";
}
// fonction qui take le matricule et la note dun eleve pour mettre dans le bean
insererNoteEleve(
cellmat.getStringCellValue(), (float) cellnote.getNumericCellValue());
} else {
if (cellmat != null && cellnote == null) {
// fonction qui take le matricule et la note dun eleve pour mette dans le bean
insererNoteEleve(cellmat.getStringCellValue(), (float) 0);
}
}
} else {
numcell++;
rowIt.next();
}
} // End for
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
this.service.enregistrerNotes(compositions, codeevaluation);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Repertoire.addMessageinfo("Notes enregistrées");
return OperationResults.navWithParam(
"visualiserNotes", "codeevaluation", String.valueOf(codeevaluation));
}