/** * 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)); }