List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:com.unicomer.oer.harvester.reader.ExcelBrokerRemoteReader.java
public List<Set<Entity>> read() throws Exception { List<Set<Entity>> list = new ArrayList<Set<Entity>>(); HashMap<String, Entity> servicesMap = new HashMap<String, Entity>(); String defEnvironment = prop.getProperty("default.environment"); String defaultAssetType = prop.getProperty("broker.service.asset-type"); String resourceEnvironmentXPath = prop.getProperty("broker.custom-data.environment"); String resourceNameXPath = prop.getProperty("broker.custom-data.endpoint"); // String defServiceToAppRelation = prop.getProperty("broker.service-to-app-relation"); String harvestType = prop.getProperty("broker.harvest-type"); String transportProtocolXpath = prop.getProperty("broker.custom-data.transport-protocol"); String authenticationMethod = prop.getProperty("broker.custom-data.authentication"); String authorizationMethod = prop.getProperty("broker.custom-data.authentication"); boolean hasHeader = Boolean.valueOf(prop.getProperty("broker.has-header")); try {//from ww w .ja va 2 s. c o m FileInputStream file = new FileInputStream(new File(templateFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (hasHeader) { hasHeader = false; } else { Iterator<Cell> cellIterator = row.cellIterator(); String name = cellIterator.next().getStringCellValue(); String description = cellIterator.next().getStringCellValue(); String originProtocol = cellIterator.next().getStringCellValue(); String resourceName = cellIterator.next().getStringCellValue(); // String location = cellIterator.next().getStringCellValue(); // String originSystem = cellIterator.next().getStringCellValue(); // String targetProtocol = cellIterator.next().getStringCellValue(); // String targetSystem = cellIterator.next().getStringCellValue(); String version = getVersion(name); logger.info("Asset: " + name + " - " + version); UnicomerEntity entity = new UnicomerEntity(defaultAssetType, name, name, description, version, ArtifactAlgorithm.DEFAULT); entity.addCategorization("LineOfBusiness", prop.getProperty("broker.line-of-business")); entity.addCategorization("AssetLifecycleStage", prop.getProperty("default.asset-lifecycle-stage")); entity.addCategorization("Technology", prop.getProperty("broker.technology")); entity.addCategorization("Region", prop.getProperty("default.region")); entity.addHarvesterProperty("Modulo", harvestType); entity.addHarvesterProperty("Harvester Description", prop.getProperty("default.harvester-description")); entity.addCustomData("acquisition-method", prop.getProperty("default.acquisition-method")); entity.addCustomData(resourceEnvironmentXPath, defEnvironment); entity.addCustomData(resourceNameXPath, resourceName); entity.addCustomData(transportProtocolXpath, originProtocol); entity.addCustomData("authentication-method", authenticationMethod); entity.addCustomData("authorization-method", authorizationMethod); entity.addCustomData("has-test-scripts", "true"); entity.addCustomData("needs-performance-testing", "false"); entity.addCustomData("has-automated-testing", "false"); entity.addCustomData("consistent-with-business-mission", "true"); entity.addCustomData("passes-legal-review", "true"); entity.addCustomData("approved-for-internal-use", "true"); entity.addCustomData("approved-for-external-use", "false"); entity.addCustomData("passes-technical-review", "true"); entity.addCustomData("downtime-impact", prop.getProperty("broker.downtime-impact")); entity.addCustomData("license-terms", prop.getProperty("broker.license-terms")); servicesMap.put(name, entity); } } workbook.close(); file.close(); list.add(new HashSet<Entity>(servicesMap.values())); for (Set<Entity> entitySet : list) { if (entitySet != null && entitySet.size() > 0) { YamlWriter.writeToYaml(entitySet, harvestType); } } } catch (Exception e) { e.printStackTrace(); } return list; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public List<Object> readexcel(String path) { // String data = ""; List<Object> table = new ArrayList<Object>(); try {//from w ww . j a v a2 s. c o m // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rows = new ArrayList<String>(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: rows.add(cell.getRichStringCellValue().getString()); // data+=cell.getRichStringCellValue().getString(); // System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { rows.add(cell.getDateCellValue() + ""); // data+=cell.getDateCellValue(); // System.out.print(cell.getDateCellValue()); } else { rows.add(cell.getNumericCellValue() + ""); // data+=cell.getNumericCellValue(); // System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: rows.add(cell.getBooleanCellValue() + ""); // data+=cell.getBooleanCellValue(); // System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: rows.add(cell.getCellFormula() + ""); // data+=cell.getCellFormula(); // System.out.print(cell.getCellFormula()); break; default: // System.out.print(""); } // data += "-"; // System.out.print(" - "); } table.add(rows); // data += ";;;"; // System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return table; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public String readexcel2(String path) { try {/*w ww .j a v a 2s . c o m*/ // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.print(cell.getDateCellValue()); } else { System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.print(cell.getCellFormula()); break; default: System.out.print(""); } System.out.print(" - "); } System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return null; }
From source file:com.vodafone.poms.ii.helpers.ActivityCodeLoader.java
public static List<ActivityCode> readFile(InputStream fis) { List<ActivityCode> activitiesCode = new ArrayList<>(); ActivityCode activityCode = null;/* w w w. j a va 2s .com*/ try { XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); int numberOfSheets = myWorkBook.getNumberOfSheets(); XSSFSheet sheet = null; System.out.println(numberOfSheets); for (int i = 0; i < numberOfSheets; i++) { if (myWorkBook.getSheetAt(i).getSheetName().toLowerCase().contains("code")) { sheet = myWorkBook.getSheetAt(i); break; } } if (sheet != null) { Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { activityCode = new ActivityCode(); activityCode.setMaterialId(getCellValue(row.getCell(0))); activityCode.setDescription(getCellValue(row.getCell(1))); if (getCellValue(row.getCell(2)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setQuantityRequested(new Integer(getCellValue(row.getCell(2)))); } if (getCellValue(row.getCell(3)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setVendorPrice(new Float(getCellValue(row.getCell(3)))); } else { continue; } if (getCellValue(row.getCell(4)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setSubcontractorPrice(new Float(getCellValue(row.getCell(4)))); } else { continue; } activityCode.setUm(activityCode.getVendorPrice() - activityCode.getSubcontractorPrice()); activityCode.setUmPercent(activityCode.getUm() / activityCode.getVendorPrice()); activitiesCode.add(activityCode); } } } } catch (FileNotFoundException ex) { Logger.getLogger(ActivityCodeLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ActivityCodeLoader.class.getName()).log(Level.SEVERE, null, ex); } return activitiesCode; }
From source file:com.vodafone.poms.ii.helpers.ActivityLoader.java
public void readFile(FileUploadEvent event) { if (event.getFile() != null) { Activity activity = null;//from w w w . j ava 2 s. com List<Activity> activities = new ArrayList<>(); try { String errors = ""; XSSFWorkbook myWorkBook = new XSSFWorkbook(event.getFile().getInputstream()); int numberOfSheets = myWorkBook.getNumberOfSheets(); XSSFSheet sheet = null; System.out.println(numberOfSheets); for (int i = 0; i < numberOfSheets; i++) { if (myWorkBook.getSheetAt(i).getSheetName().toLowerCase().contains("rack")) { sheet = myWorkBook.getSheetAt(i); break; } } if (sheet != null) { int numOfMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numOfMergedRegions; i++) { sheet.removeMergedRegion(0); } Iterator<Row> rowIterator = sheet.iterator(); if (rowIterator.hasNext()) { rowIterator.next(); } while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { activity = new Activity(); Sites site = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if ((site = sitesController .getSites(getCellValue(row.getCell(0)).toUpperCase())) != null) { activity.setSite(site); // Site activity.setAsp( subContractorsController.getSubcontractors(getCellValue(row.getCell(1)))); // ASP if (activity.getAsp() == null) { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized ASP: Current Value: " + getCellValue(row.getCell(1)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized ASP: Current Value: " + getCellValue(row.getCell(1))); continue; } activity.setArea(areaController.getArea(getCellValue(row.getCell(2)))); // Area if (activity.getArea() == null) { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized Area: Current Value: " + getCellValue(row.getCell(2)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized Area: Current Value: " + getCellValue(row.getCell(2))); continue; } if (vendorOwnerController.getByName(getCellValue(row.getCell(3))) != null) { activity.setVendorOwner( vendorOwnerController.getByName(getCellValue(row.getCell(3)))); // Owner } else { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized Vendor Owner: Current Value: " + getCellValue(row.getCell(3)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized Vendor Owner: Current Value: " + getCellValue(row.getCell(3))); continue; } activity.setClaimStatus( claimStatusController.getClaimStatus(getCellValue(row.getCell(4)))); // Claim activity.setApprovalStatus( approvalStatusController.getApprovalStatus(getCellValue(row.getCell(5)))); // Approval activity.setActivityType( domainNamesController.getDomainNames(getCellValue(row.getCell(6)))); // type if (activity.getActivityType() == null) { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity Type is not recognized, Current Value: " + getCellValue(row.getCell(6))); errors += "Row:" + (row.getRowNum() + 1) + " Activity Type is not recognized, Current Value: " + getCellValue(row.getCell(6)) + "\n"; continue; } activity.setPhase(phasesController.getPhases(getCellValue(row.getCell(7)))); // phase if (row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { activity.setActivityDate(getDateCellValue(row.getCell(8))); // date } else { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity date is not recognized, Current Value: " + getCellValue(row.getCell(8))); errors += "Row:" + (row.getRowNum() + 1) + " Activity date is not recognized, Current Value: " + getCellValue(row.getCell(8)) + "\n"; continue; } activity.setActivityCode( activityCodeController.getActivityCode(getCellValue(row.getCell(9)))); // Activity Code activity.setAcDescription(getCellValue(row.getCell(10))); // Claim activity.setActivityDetails(getCellValue(row.getCell(11))); // Claim if (activity.getActivityDetails() == null) { System.out.println("Row:" + (row.getRowNum() + 1) + " Empty Activity Details/Merged Cells"); errors += "Row:" + (row.getRowNum() + 1) + " Empty Activity Details/Merged Cells" + "\n"; continue; } if (activity.getActivityDetails().length() == 0) { activity.setActivityDetails("Regular Activity"); } try { activity.setQty(Double.parseDouble( (getCellValue(row.getCell(12)) != null ? getCellValue(row.getCell(12)) : "0"))); // Qty } catch (NumberFormatException e) { activity.setQty(0); } activity.setAcVendorPrice(Float.parseFloat( (getCellValue(row.getCell(13)) != null ? getCellValue(row.getCell(13)) : "0"))); // vendor price activity.setTotalPriceVendor(Float.parseFloat( (getCellValue(row.getCell(14)) != null ? getCellValue(row.getCell(14)) : "0"))); // vendor price activity.setTotalPriceVendorTaxes(Float.parseFloat( (getCellValue(row.getCell(15)) != null ? getCellValue(row.getCell(15)) : "0"))); // vendor price activity.setAcSubcontractorPrice(Float.parseFloat( (getCellValue(row.getCell(16)) != null ? getCellValue(row.getCell(16)) : "0"))); // vendor price activity.setTotalPriceAsp(Float.parseFloat( (getCellValue(row.getCell(17)) != null ? getCellValue(row.getCell(17)) : "0"))); // vendor price activity.setTotalUm(Float.parseFloat( (getCellValue(row.getCell(18)) != null ? getCellValue(row.getCell(18)) : "0"))); // vendor price activity.setTotalUmPercent(Float.parseFloat( (getCellValue(row.getCell(19)) != null ? getCellValue(row.getCell(19)) : "0"))); // vendor price activity.setActivityComment(getCellValue(row.getCell(20))); // vendor price activity.setCorrelateTo(getCellValue(row.getCell(21))); // vendor price activity.setSysDate(new Date()); activity.setCreator(usersController.getLoggedInUser()); activity.setTaxes(13.0); activities.add(activity); } else { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity Site is not recognized, Current Value: " + getCellValue(row.getCell(0))); errors += "Row:" + (row.getRowNum() + 1) + " Activity Site is not recognized, Current Value: " + getCellValue(row.getCell(0)) + "\n"; } } } } if (!testFile) { activityController.createMultiple(activities); } if (errors.length() == 0) { setErrors("No Errors Found"); } else { setErrors(errors); } } catch (FileNotFoundException ex) { Logger.getLogger(ActivityLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ActivityLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { e.printStackTrace(); } finally { } } }
From source file:com.vodafone.poms.ii.helpers.SiteLoader.java
public static List<Sites> readFile(InputStream fis) { List<Sites> sites = new ArrayList<>(); Sites site = null;//from w ww . j a va 2 s. co m try { XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); int numberOfSheets = myWorkBook.getNumberOfSheets(); XSSFSheet sheet = null; System.out.println(numberOfSheets); for (int i = 0; i < numberOfSheets; i++) { sheet = myWorkBook.getSheetAt(i); if (sheet.getRow(0) != null && sheet.getRow(0).getCell(0) != null) { if (sheet.getRow(0).getCell(0).getStringCellValue().toLowerCase().contains("site")) { //<editor-fold defaultstate="collapsed" desc="Process Sheet"> Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { site = new Sites(); site.setSitePhysical(getCellValue(row.getCell(0))); site.setGfRt(getCellValue(row.getCell(1))); sites.add(site); } } //</editor-fold> } } } } catch (FileNotFoundException ex) { Logger.getLogger(SiteLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(SiteLoader.class.getName()).log(Level.SEVERE, null, ex); } return sites; }
From source file:com.xl.main.ReadExcelSampleSilk.java
public static String read(String filename) { Gson gson = new Gson(); Map<String, List<SampleSinkBean>> values = new HashMap<String, List<SampleSinkBean>>(); List<SampleSinkBean> byRow = new ArrayList<SampleSinkBean>(); try {//w w w. j a v a2 s.co m FileInputStream file = null; if (filename == null) { file = new FileInputStream(new File("H:\\anil\\sample-sink.xlsx")); } else { file = new FileInputStream(new File(filename)); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() > 0 && row.getRowNum() < 20) { SampleSinkBean sb = new SampleSinkBean(); //System.out.println("row value" + sheet.getRow(3).getCell(3)); while (cellIterator.hasNext()) {// Cell cell = cellIterator.next(); String cellString = " "; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cellString = cell.getNumericCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: cellString = cell.getStringCellValue() + ""; break; case Cell.CELL_TYPE_ERROR: cellString = cell.getErrorCellValue() + ""; break; default: cellString = cell.getStringCellValue() + ""; } switch (cell.getColumnIndex()) { case 0: sb.setFrYear(cellString); break; case 1: sb.setVpmod(cellString); case 2: sb.setProjectName(cellString); case 3: sb.setProjectWorktype(cellString); case 4: sb.setBusinessObjective(cellString); } } byRow.add(sb); } // System.out.println(""); } values.put("sink", byRow); System.out.println("output *********" + gson.toJson(values)); file.close(); } catch (Exception e) { e.printStackTrace(); } return gson.toJson(values); }
From source file:comparararchivos.CompararArchivos.java
/** * @param args the command line arguments *///from www. ja v a 2 s . c o m public static void main(String[] args) { // TODO code application logic here File excel1 = null; FileInputStream fl1 = null; XSSFWorkbook book1 = null; File excel2 = null; FileInputStream fl2 = null; XSSFWorkbook book2 = null; try { excel1 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorB.xlsx"); excel2 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorD.xlsx"); PrintWriter file = new PrintWriter("diferencias.txt", "UTF-8"); fl1 = new FileInputStream(excel1); fl2 = new FileInputStream(excel2); book1 = new XSSFWorkbook(fl1); book2 = new XSSFWorkbook(fl2); XSSFSheet sheet_A = book1.getSheetAt(0); XSSFSheet sheet_B = book2.getSheetAt(0); Iterator<Row> itrA = sheet_A.iterator(); Iterator<Row> itrB = sheet_B.iterator(); int totalDiferencias = 0; int numFila = 2; while (itrA.hasNext() && itrB.hasNext()) { Row rowA = itrA.next(); Row rowB = itrB.next(); if (rowA.getRowNum() == 0) continue; Iterator<Cell> cellitA = rowA.cellIterator(); Iterator<Cell> cellitB = rowB.cellIterator(); Cell celA = cellitA.next(); Cell celB = cellitB.next(); //Se esta en las celdas del numero de Aviso celA = cellitA.next(); celB = cellitB.next(); int numAvisoA = (int) celA.getNumericCellValue(); int numAvisoB = (int) celB.getNumericCellValue(); if (numAvisoA != numAvisoB) { System.out.println("Numero de Aviso: " + numAvisoA); continue; } //Se esta en las celdas de la categoria celA = cellitA.next(); celB = cellitB.next(); String textA = celA.getStringCellValue(); //System.out.println("Categoria A: "+textA); String textB = celB.getStringCellValue(); //System.out.println("Categoria B: "+textB); if (!textA.equals(textB)) { System.out.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); file.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); totalDiferencias++; } numFila++; } System.out.println("\nTotal diferencias: " + totalDiferencias); file.println("\nTotal diferencias: " + totalDiferencias); file.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException ie) { ie.printStackTrace(); } }
From source file:comparator.Comparator.java
public static void transcoding_Map_HUG() throws IOException { //Get the input files FileInputStream mvcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile); XSSFSheet mvcSheet; Iterator<Row> mvcRowIterator; String mvcSheetName;//from w w w. j a v a2 s .c om int mvcCol; boolean mvcColFound; Row mvcRow; Row mvcRow2; Iterator<Cell> mvcCellIterator; boolean statusOK = false; //OUTPUT String code_src; String code_dest; String name_dest = ""; String value_set_name_dest = ""; String status = "none"; String value_set_name_source = ""; String value_set_oid_dest = ""; String parent_system_code_dest = ""; String parent_system_oid_dest = ""; String comment = ""; String map_level = "0"; String review = "0"; String version = ""; //Prepare the output file Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Map_HUG\\map_hug_to_mvc_2.0.csv"), "UTF-8")); csvW.write('\ufeff'); csvW.write( "code_src;code_dest;name_dest;value_set_name_dest;status;value_set_name_source;value_set_oid_dest;parent_system_code_dest;parent_system_oid_dest;comment;map_level;review;version;"); csvW.write("\n"); //Read csv map String map = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Map_HUG\\map_hug_to_mvc_1_9.csv"; try { BufferedReader br = new BufferedReader(new FileReader(map)); String line = ""; String csvSplitBy = ";"; String[] maLigne; //jump over the first line br.readLine(); //pour chaque ligne de la map while ((line = br.readLine()) != null) { statusOK = false; maLigne = line.split(csvSplitBy); code_src = maLigne[0]; code_dest = maLigne[1]; //Get the sheet from the MTC workbook for (int i = 0; i < mvcWorkbook.getNumberOfSheets(); i++) { mvcSheet = mvcWorkbook.getSheetAt(i); //Get iterator to all the rows in current MTC sheet mvcRowIterator = mvcSheet.iterator(); //Get the name of MTTC sheet, compare them MAP entries //MVC data files are called "epSOSsheetName" mvcSheetName = mvcSheet.getSheetName(); //And process the file matching to find the good sheet if (mvcSheetName.equals(maLigne[3])) { value_set_name_dest = mvcSheetName; value_set_name_source = maLigne[5]; mvcCol = 0; mvcColFound = false; while (mvcRowIterator.hasNext()) { mvcRow = mvcRowIterator.next(); mvcRow2 = mvcRow; if (mvcColFound == false) { mvcCellIterator = mvcRow.cellIterator(); while (mvcCellIterator.hasNext()) { Cell mvcCell = mvcCellIterator.next(); if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("Parent Code System:"))) { mvcCol = mvcCell.getColumnIndex() + 1; mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); parent_system_code_dest = mvcRow.getCell(mvcCol).getStringCellValue() .trim(); } if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("OID Parent Code System:"))) { mvcCol = mvcCell.getColumnIndex() + 1; mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); parent_system_oid_dest = mvcRow.getCell(mvcCol).getStringCellValue().trim(); } if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS OID:"))) { mvcCol = mvcCell.getColumnIndex() + 1; mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); value_set_oid_dest = mvcRow.getCell(mvcCol).getStringCellValue().trim(); } if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("version:"))) { mvcCol = mvcCell.getColumnIndex() + 1; mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); version = mvcRow.getCell(mvcCol).getStringCellValue().trim(); } if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code") || mvcCell.getStringCellValue().equals("Code"))) { mvcCol = mvcCell.getColumnIndex(); mvcColFound = true; break; } } } else { mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); if (mvcRow.getCell(mvcCol).getStringCellValue().trim().equals(code_dest)) { statusOK = true; mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); name_dest = mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim(); break; } } } if (statusOK == true) { break; } else { parent_system_code_dest = ""; parent_system_oid_dest = ""; value_set_oid_dest = ""; version = ""; } } } if (statusOK != true) { //TO CHECK MANUALY status = "manual"; name_dest = maLigne[2]; comment = "mvc2.0 no hug code"; } //Write the mapping csvW.write(code_src + ";" + code_dest + ";" + name_dest + ";" + value_set_name_dest + ";" + status + ";" + value_set_name_source + ";" + value_set_oid_dest + ";" + parent_system_code_dest + ";" + parent_system_oid_dest + ";" + comment + ";" + map_level + ";" + review + ";" + version + ";"); csvW.write("\n"); //reset status status = "none"; comment = ""; } br.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } csvW.flush(); csvW.close(); }
From source file:comparator.Comparator.java
public static void delta_MVC_MTC() throws IOException { //Get the input files //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx")); //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx")); FileInputStream mtcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mtcFile2 = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mvcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx")); //Prepare the output file //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8")); Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"), "UTF-8")); csvW.write('\ufeff'); csvW.write("Expand Project;"); csvW.write("\n\n"); //Get the workbook instance for XLS file XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile); XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2); XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile); //Output/*from ww w. j a v a 2 s.co m*/ csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;"); csvW.write("\n"); csvW.write("********************;"); csvW.write("\n"); csvW.write("Set name;"); csvW.write("\n"); csvW.write("MTC mismatches;List of the codes missing in MVC"); csvW.write("\n"); csvW.write("MVC mismatches;List of the codes missing in MTC"); csvW.write("\n"); csvW.write("********************;"); XSSFSheet mtcSheet; XSSFSheet mtcSheet2; Iterator<Row> mtcRowIterator; Iterator<Row> mtcRowIterator2; Iterator<Row> mvcRowIterator; Iterator<Cell> mtcCellIterator; Iterator<Cell> mvcCellIterator; int mtcCol; int mvcCol; boolean mtcColFound; boolean mvcColFound; ArrayList mtcCodes; ArrayList mvcCodes; ArrayList mtcEnglishNames; ArrayList mvcEnglishNames; ArrayList englishNamesdifferences; Row mtcRow; Row mtcRow2; Row mvcRow; Row mvcRow2; Row newRow; Cell newCell; CellStyle myStyle; String mtcSplit[]; String mvcSplit[]; String mtcSheetName; String mvcSheetName; //Get the sheet from the MTC workbook for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) { mtcSheet = mtcWorkbook.getSheetAt(i); mtcSheet2 = mtcWorkbook2.getSheetAt(i); //Get iterator to all the rows in current MTC sheet mtcRowIterator = mtcSheet.iterator(); mtcRowIterator2 = mtcSheet2.iterator(); //Get the sheet from the MVC workbook for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) { XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j); //Get iterator to all the rows in current MVC sheet mvcRowIterator = mvcSheet.iterator(); //Get the name of MTC sheet and MVC sheet, compare them if they contain data //MTC data files are called "VSX_sheetName" //MVC data files are called "epSOSsheetName" mtcSplit = mtcSheet.getSheetName().split("_"); mvcSplit = mvcSheet.getSheetName().split("SOS"); mtcSheetName = mtcSplit[mtcSplit.length - 1]; mvcSheetName = mvcSplit[mvcSplit.length - 1]; //And process the file matching or throw out the file that has no equivalent if (mtcSheetName.equals(mvcSheetName)) { mtcCol = 0; mvcCol = 0; mtcColFound = false; mvcColFound = false; mtcCodes = new ArrayList(); mvcCodes = new ArrayList(); mtcEnglishNames = new ArrayList(); mvcEnglishNames = new ArrayList(); englishNamesdifferences = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (mtcRowIterator.hasNext()) { mtcRow = mtcRowIterator.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim()); mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()); } } //In MVC while (mvcRowIterator.hasNext()) { mvcRow = mvcRowIterator.next(); mvcRow2 = mvcRow; if (mvcColFound == false) { mvcCellIterator = mvcRow.cellIterator(); while (mvcCellIterator.hasNext()) { Cell mvcCell = mvcCellIterator.next(); if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code") || mvcCell.getStringCellValue().equals("Code"))) { mvcCol = mvcCell.getColumnIndex(); mvcColFound = true; break; } } } else { mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim()); mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim()); } } //Processing colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences); //Output //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {} csvW.write("\n\n"); csvW.write(mtcSheetName + ";"); csvW.write("\n"); csvW.write("MTC mismatches;"); for (int a = 0; a < mtcCodes.size(); a++) { csvW.write(mtcCodes.get(a) + ";"); } csvW.write("\n"); csvW.write("MVC mismatches\n"); for (int b = 0; b < mvcCodes.size(); b++) { csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n"); } csvW.write("english names differences\n"); if (!englishNamesdifferences.isEmpty()) { csvW.write("code;MTC 2.0;MVC 2.0.1\n"); for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1) + ";" + englishNamesdifferences.get(c + 2) + "\n"); } } /* work on currents MTC2.0 sheet */ mtcColFound = false; mtcCol = 0; List<Integer> delRows = new ArrayList(); //recreate iterator to all the rows in current MTC sheet while (mtcRowIterator2.hasNext()) { mtcRow = mtcRowIterator2.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); for (int a = 0; a < mtcCodes.size(); a++) { if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) { // delete row corresponding to useless code delRows.add(mtcRow.getRowNum()); break; } } if (!englishNamesdifferences.isEmpty()) { for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim() .equals(englishNamesdifferences.get(c + 1))) { mtcRow2.getCell(mtcCol + 1) .setCellValue(englishNamesdifferences.get(c + 2).toString()); break; } } } } } for (int d = delRows.size() - 1; d >= 0; d--) { mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1); } myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle(); for (int b = 0; b < mvcCodes.size(); b++) { newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1); for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) { newCell = newRow.createCell(bb); newCell.setCellStyle(myStyle); if (bb == mtcCol) { newCell.setCellValue(mvcCodes.get(b).toString()); } else if (bb == mtcCol + 1) { newCell.setCellValue(mvcEnglishNames.get(b).toString()); } } } } } } //close InputStream mtcFile.close(); mtcFile2.close(); mvcFile.close(); //close OutputStream csvW.close(); //Open FileOutputStream to write updates FileOutputStream output_file = new FileOutputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx")); //write changes mtcWorkbook2.write(output_file); //close the stream output_file.close(); }