@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(); }
private void analyzeTable(XSSFRow row) { Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); // System.out.println(cell.getStringCellValue()); switch (readCells(cell)) { case "Name ": NAME_COLUMN = cell.getColumnIndex(); break; case "Value ": VALUE_COLUMN = cell.getColumnIndex(); break; case "Modification Status ": STATUS_COLUMN = cell.getColumnIndex(); break; } } }
private void processRow(Element table, XSSFRow row, XSSFSheet sheet) { Element tr = htmlDocumentFacade.createTableRow(); Iterator<Cell> cells = row.cellIterator(); if (row.isFormatted()) { // TODO build row style... } if (row.getCTRow().getCustomHeight()) tr.setAttribute( "style", "height:".concat(String.valueOf(row.getHeightInPoints())).concat("pt;")); while (cells.hasNext()) { Cell cell = cells.next(); if (cell instanceof XSSFCell) processCell(tr, (XSSFCell) cell); } table.appendChild(tr); }
private void readExcel() throws IOException, URISyntaxException { URL inputStream = this.getClass().getResource("ReleaseNotes-002.xlsx"); File file = new File(inputStream.toURI()); FileInputStream fIP = new FileInputStream(file); XSSFRow row; // Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet spreadsheet = workbook.getSheetAt(i); // System.out.println(spreadsheet.getSheetName()); if (!spreadsheet.getSheetName().equals("EAR SAI")) { continue; } StringBuilder sb = new StringBuilder(); Iterator<Row> rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); System.out.println(row.getRowNum() + " " + row.getRowStyle()); if (row.getRowStyle() != null) { System.out.println(row.getRowNum() + " " + row.getRowStyle().getBorderBottom()); } Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); if (row.getRowNum() < 4) { if (row.getRowNum() == 3) analyzeTable(row); continue; } while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); if (cell.getColumnIndex() == NAME_COLUMN) { if (!(sb.lastIndexOf("\n") == sb.length() - 1)) sb.append("\n"); } if (cell.getColumnIndex() < VALUE_COLUMN) { sb.append(readCells(cell)); continue; } if (cell.getColumnIndex() == VALUE_COLUMN) { sb.append(" -> " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } if (cell.getColumnIndex() == STATUS_COLUMN) { sb.append(" => " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } // if (!cellIterator.hasNext()) { // sb.append("\n"); // } } // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // sb.append(readCells(cell)); // if (cellIterator.hasNext()) { // // if (cell.getColumnIndex() == 5 && cell.getCellType() != 3) { // sb.append("\t->\t"); // sb.append(readCells(cell)); // if (!cellIterator.hasNext()) { // sb.append("\t=>\tstate\n"); // continue; // } // cell = cellIterator.next(); // if (cell.getCellType() != 3) { // sb.append("\t=>\t"); // sb.append(readCells(cell)); // sb.append("\n"); // } else { // sb.append("\t=>\tstate\n"); // } // continue; // } // sb.append(readCells(cell)); // } else { // sb.append("\\"); // } // } } // System.out.println(); System.out.println(sb); } if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); } else { System.out.println("Error to open openworkbook.xlsx file."); } }
/** @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(); } } }
/** * 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")); }
@POST @Path("/documents") @Consumes({MediaType.MULTIPART_FORM_DATA}) @Produces({MediaType.APPLICATION_JSON}) public String createUploadFile( @HeaderParam("Content-Length") Long fileSize, @FormDataParam("file") InputStream inputStream, @FormDataParam("file") FormDataContentDisposition fileDetails, @FormDataParam("file") FormDataBodyPart bodyPart, @FormDataParam("name") String name, @FormDataParam("description") String description) { FileUtils.validateFileSizeWithinPermissibleRange( fileSize, name, ApiConstants.MAX_FILE_UPLOAD_SIZE_IN_MB); int i; /*DocumentCommand documentCommand = new DocumentCommand(null, null, null, null, name, fileDetails.getFileName(), fileSize, bodyPart.getMediaType().toString(), description, null);*/ try { String fileUploadLocation = FileUtils.generateXlsFileDirectory(); String fileName = fileDetails.getFileName(); if (!new File(fileUploadLocation).isDirectory()) { new File(fileUploadLocation).mkdirs(); } String fileLocation = FileUtils.saveToFileSystem(inputStream, fileUploadLocation, fileName); InputStream excelFileToRead = new FileInputStream(fileLocation); XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String serialno = "0"; int countno = Integer.parseInt(serialno); if (countno == 0) { countno = countno + 2; } else if (countno == 1) { countno = countno + 1; } System.out.println("Excel Row No is: " + countno); Iterator rows = sheet.rowIterator(); Vector<XSSFCell> v = new Vector<XSSFCell>(); if (countno > 0) { countno = countno - 1; } while (rows.hasNext()) { row = (XSSFRow) rows.next(); i = row.getRowNum(); if (i > 0) { if (i >= countno) { Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // System.out.print(cell.getStringCellValue() + // " "); v.add(cell); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // System.out.print(cell.getNumericCellValue() + // " "); v.add(cell); } else { v.add(cell); } } // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Integer.parseInt(v.elementAt(0).toString()),v.elementAt(1).toString(),Integer.parseInt(v.elementAt(2).toString()),v.elementAt(3).toString(),v.elementAt(4).toString(),v.elementAt(5).toString(),Integer.parseInt(v.elementAt(6).toString()),Integer.parseInt(v.elementAt(7).toString()),Integer.parseInt(v.elementAt(8).toString()),v.elementAt(9).toString(),null); List<ItemDetailsCommand> ItemDetailsCommandList = new ArrayList<ItemDetailsCommand>(); /*for(i=0;i<10;i++) {*/ /* Iterator iterator = v.iterator(); while(iterator.hasNext()) { System.out.println(v.elementAt(0).toString()); System.out.println( v.elementAt(1).toString()); System.out.println( v.elementAt(2).toString()); System.out.println( v.elementAt(3).toString()); System.out.println( v.elementAt(4).toString()); System.out.println( v.elementAt(5).toString()); System.out.println( v.elementAt(6).toString()); System.out.println( v.elementAt(7).toString()); System.out.println( v.elementAt(8).toString()); System.out.println( v.elementAt(9).toString()); // itemDetails = ItemDetails.create(command.getItemMasterId(), command.getSerialNumber(), command.getGrnId(),command.getProvisioningSerialNumber(), command.getQuality(),command.getStatus(), command.getOfficeId(), command.getClientId(), command.getWarranty(), command.getRemark()); //ItemDetailsCommand itemdetails=new ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), v.elementAt(4).toString(), v.elementAt(5).toString(), Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); //ItemDetailsCommand itemdetails=new ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), v.elementAt(4).toString(), v.elementAt(5).toString(), Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); //CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemdetails); //ItemDetailsCommandList.add(itemdetails); ItemDetailsCommand itemDetailsCommand=new ItemDetailsCommand(); new Double(v.elementAt(0).toString()).longValue(); itemDetailsCommand.setItemMasterId(new Double(v.elementAt(0).toString()).longValue()); itemDetailsCommand.setSerialNumber(v.elementAt(1).toString()); itemDetailsCommand.setGrnId(new Double(v.elementAt(2).toString()).longValue()); itemDetailsCommand.setProvisioningSerialNumber( v.elementAt(3).toString()); itemDetailsCommand.setQuality( v.elementAt(4).toString()); itemDetailsCommand.setRemark(v.elementAt(9).toString()); itemDetailsCommand.setStatus(v.elementAt(5).toString()); itemDetailsCommand.setOfficeId(new Double(v.elementAt(6).toString()).longValue()); itemDetailsCommand.setClientId(new Double(v.elementAt(7).toString()).longValue()); itemDetailsCommand.setWarranty(new Double(v.elementAt(8).toString()).longValue()); ItemDetailsCommandList.add(itemDetailsCommand); CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemDetailsCommand); } */ } } } Iterator iterator = v.iterator(); while (iterator.hasNext()) { System.out.println(v.elementAt(0).toString()); System.out.println(v.elementAt(1).toString()); System.out.println(v.elementAt(2).toString()); System.out.println(v.elementAt(3).toString()); System.out.println(v.elementAt(4).toString()); System.out.println(v.elementAt(5).toString()); System.out.println(v.elementAt(6).toString()); System.out.println(v.elementAt(7).toString()); System.out.println(v.elementAt(8).toString()); System.out.println(v.elementAt(9).toString()); // itemDetails = ItemDetails.create(command.getItemMasterId(), command.getSerialNumber(), // command.getGrnId(),command.getProvisioningSerialNumber(), // command.getQuality(),command.getStatus(), command.getOfficeId(), command.getClientId(), // command.getWarranty(), command.getRemark()); // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), // Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), // v.elementAt(4).toString(), v.elementAt(5).toString(), // Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), // Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), // Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), // v.elementAt(4).toString(), v.elementAt(5).toString(), // Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), // Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); // CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemdetails); // ItemDetailsCommandList.add(itemdetails); ItemDetailsCommand itemDetailsCommand = new ItemDetailsCommand(); new Double(v.elementAt(0).toString()).longValue(); itemDetailsCommand.setItemMasterId(new Double(v.elementAt(0).toString()).longValue()); itemDetailsCommand.setSerialNumber(v.elementAt(1).toString()); itemDetailsCommand.setGrnId(new Double(v.elementAt(2).toString()).longValue()); itemDetailsCommand.setProvisioningSerialNumber(v.elementAt(3).toString()); itemDetailsCommand.setQuality(v.elementAt(4).toString()); itemDetailsCommand.setRemark(v.elementAt(9).toString()); itemDetailsCommand.setStatus(v.elementAt(5).toString()); itemDetailsCommand.setOfficeId(new Double(v.elementAt(6).toString()).longValue()); itemDetailsCommand.setClientId(new Double(v.elementAt(7).toString()).longValue()); itemDetailsCommand.setWarranty(new Double(v.elementAt(8).toString()).longValue()); // ItemDetailsCommandList.add(itemDetailsCommand); CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemDetailsCommand); } } catch (Exception e) { e.printStackTrace(); } /** * TODO: also need to have a backup and stop reading from stream after max size is reached to * protect against malicious clients */ /** TODO: need to extract the actual file type and determine if they are permissable */ /// ItemDetailsCommand itemDetailsCommand=new ItemDetailsCommand() // DocumentCommand documentCommand = new DocumentCommand(null, null, entityType, entityId, name, // fileDetails.getFileName(), fileSize, /// bodyPart.getMediaType().toString(), description, null); // Long documentId = this.documentWritePlatformService.createDocument(documentCommand, // inputStream); // return this.toApiJsonSerializer.serialize(CommandProcessingResult.resourceResult(1, null)); return null; }