List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.vodafone.poms.ii.helpers.ActivityLoader.java
public void readFile(FileUploadEvent event) { if (event.getFile() != null) { Activity activity = null;// w w w .j a v a 2 s . c o m 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;/*ww w. j a v a 2 s . c o 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.yanglb.utilitys.codegen.core.reader.BaseReader.java
License:Apache License
/** * ?/* ww w . j av a 2 s. c o m*/ * @throws CodeGenException */ private void doReader() throws CodeGenException { // XSSFWorkbook wb = null; try { // jar??new File if (this.excelFile.startsWith("jar:")) { String path = this.excelFile.substring(4); InputStream is = this.getClass().getResourceAsStream(path); wb = new XSSFWorkbook(is); } else { File file = new File(this.excelFile); BufferedInputStream in = new BufferedInputStream(new FileInputStream(file)); wb = new XSSFWorkbook(in); } // ? HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); // ?? if (this.sheets == null || this.sheets.length == 0) { // ? for (int i = 0; i < wb.getNumberOfSheets(); i++) { XSSFSheet sheet = wb.getSheetAt(i); // ???Sheet if (!this.isReadable(sheet.getSheetName())) { continue; } this.results.add(this.onReader(sheet)); } } else { // ?Sheet for (String sheetName : this.sheets) { XSSFSheet sheet = wb.getSheet(sheetName); if (sheet == null) { throw new CodeGenException(String.format(MsgUtility.getString("E_004"), sheetName)); } this.results.add(this.onReader(sheet)); } } } catch (FileNotFoundException e) { // ??? throw new CodeGenException(e.getMessage()); } catch (UnImplementException e) { this.results.clear(); e.printStackTrace(); } catch (IOException e) { throw new CodeGenException(MsgUtility.getString("E_005")); } finally { try { if (wb != null) wb.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<Map<String, String>> readXLSX(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; }//from w w w . j a v a 2 s .c o m for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); XSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; }
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;/*from w w w .java 2 s .c o m*/ Iterator<Row> mvcRowIterator; String mvcSheetName; 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// ww w . ja v a 2 s.c o 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(); }
From source file:comparator.Comparator.java
public static void translation() throws IOException { //Get the input files FileInputStream newMTC = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); String icdCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\CIM-10\\CIM10GM2014_S_FR_ClaML_2014.10.31.xml"; String atcCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\ATCcodes\\ATCDPP.CSV"; //Prepare the output file Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\CIM10-treated.csv"), "UTF-8")); csvW.write('\ufeff'); Writer csvW2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\ATC-treated.csv"), "UTF-8")); csvW2.write('\ufeff'); List<String> translationList = new ArrayList(); Map<String, String> translatList = new HashMap(); Map<String, String> translatAtcList = new HashMap(); Map<String, String> translatAtcList2 = new HashMap(); String codeTemp = ""; boolean prefered = false; InputStream ips = new FileInputStream(icdCodes); //Cp1252 --> ANSI InputStreamReader ipsr = new InputStreamReader(ips, "UTF-8"); BufferedReader br = new BufferedReader(ipsr); String ligne;//from www. j a va2s.com Pattern p1 = Pattern.compile("<Class code=\"(.+?)\""); Pattern p2 = Pattern.compile("xml:space=\"default\">(.+?)<"); Pattern p3 = Pattern.compile("(.+?)\\.."); Pattern pActiveIngredient = Pattern.compile("(?:.*;){8}\"(.+?)\";(?:.*;)\"(.+?)\";(?:.*;){5}.*"); Pattern pActiveIngredient2 = Pattern.compile("(?:.*;){4}\"(.+?)\";(?:.*;){5}\"(.+?)\";(?:.*;){5}.*"); Matcher m1; Matcher m2; Matcher m3; Matcher mActiveIngredient; Matcher mActiveIngredient2; while ((ligne = br.readLine()) != null) { m1 = p1.matcher(ligne); m2 = p2.matcher(ligne); if (ligne.matches("</Class>")) { prefered = false; codeTemp = ""; } if (m1.find()) { codeTemp = m1.group(1); } if (ligne.matches("(.*)kind=\"preferred\"(.*)")) { prefered = true; } if (m2.find() && prefered == true) { translatList.put(codeTemp, m2.group(1)); prefered = false; } //si traduction franais ET anglais if (ligne.matches(".*<FR_OMS>.*</FR_OMS>.*") && ligne.matches(".*<EN_OMS>.*</EN_OMS>.*")) { translationList.add(ligne.replace("\u00A0", " ")); } } br.close(); ips = new FileInputStream(atcCodes); //Cp1252 --> ANSI ipsr = new InputStreamReader(ips, "UTF-8"); br = new BufferedReader(ipsr); while ((ligne = br.readLine()) != null) { mActiveIngredient = pActiveIngredient.matcher(ligne); mActiveIngredient2 = pActiveIngredient2.matcher(ligne); if (mActiveIngredient.find()) { translatAtcList.put(mActiveIngredient.group(1), mActiveIngredient.group(2)); } if (mActiveIngredient2.find()) { translatAtcList2.put(mActiveIngredient.group(1), mActiveIngredient.group(2)); } } br.close(); //Get the workbook instance for XLS file XSSFWorkbook newMtcWorkbook = new XSSFWorkbook(newMTC); XSSFSheet newMtcSheet; Iterator<Row> newMtcRowIterator; Iterator<Cell> newMtcCellIterator; int newMtcCol; boolean newMtcColFound; ArrayList newMtcCodes; ArrayList newMtcCodes2; Row newMtcRow; Row newMtcRow2; Cell newMtcCell; //Get the sheet from the MTC workbook for (int i = 0; i < newMtcWorkbook.getNumberOfSheets(); i++) { newMtcSheet = newMtcWorkbook.getSheetAt(i); //Get iterator to all the rows in current MTC sheet newMtcRowIterator = newMtcSheet.iterator(); //And process the file matching or throw out the file that has no equivalent if (newMtcSheet.getSheetName().equals("VS21_IllnessesandDisorders")) { newMtcCol = 0; newMtcColFound = false; newMtcCodes = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (newMtcRowIterator.hasNext()) { newMtcRow = newMtcRowIterator.next(); if (newMtcColFound == false) { newMtcCellIterator = newMtcRow.cellIterator(); while (newMtcCellIterator.hasNext()) { newMtcCell = newMtcCellIterator.next(); if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("Code")) { newMtcCol = newMtcCell.getColumnIndex(); newMtcColFound = true; break; } } } else { newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim()); } } for (int j = 0; j < newMtcCodes.size(); j++) { csvW.write(newMtcCodes.get(j) + ";"); if (translatList.containsKey(newMtcCodes.get(j))) { csvW.write(translatList.get(newMtcCodes.get(j))); } else { m3 = p3.matcher((String) newMtcCodes.get(j)); if (m3.find() && translatList.containsKey(m3.group(1))) { csvW.write(translatList.get(m3.group(1))); } } /*for (int k=0; k<translationList.size(); k++) { String frTrad = ""; if (translationList.get(k).trim().contains("<EN_OMS>"+newMtcCodes.get(j)+"</EN_OMS>")) { Pattern p = Pattern.compile("<FR_OMS>(.+?)</FR_OMS>"); Matcher m = p.matcher(translationList.get(k).trim()); if (m.find()){ frTrad = m.group(1); translationList.remove(k); } csvW.write(StringUtils.capitalize(frTrad)); } }*/ csvW.write("\n"); } } else if (newMtcSheet.getSheetName().equals("VS3_ActiveIngredient")) { newMtcCol = 0; newMtcColFound = false; newMtcCodes = new ArrayList(); newMtcCodes2 = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (newMtcRowIterator.hasNext()) { newMtcRow = newMtcRowIterator.next(); newMtcRow2 = newMtcRow; if (newMtcColFound == false) { newMtcCellIterator = newMtcRow.cellIterator(); while (newMtcCellIterator.hasNext()) { newMtcCell = newMtcCellIterator.next(); if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("English Display Name")) { newMtcCol = newMtcCell.getColumnIndex(); newMtcColFound = true; break; } } } else { newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim()); newMtcRow2.getCell(newMtcCol - 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); newMtcCodes2.add(newMtcRow.getCell(newMtcCol - 1).getStringCellValue().trim()); } } for (int j = 0; j < newMtcCodes.size(); j++) { csvW2.write(newMtcCodes2.get(j) + ";"); csvW2.write(newMtcCodes.get(j) + ";"); if (translatAtcList.containsKey(newMtcCodes.get(j))) { csvW2.write(translatAtcList.get(newMtcCodes.get(j))); } else if (translatAtcList2.containsKey(newMtcCodes2.get(j))) { csvW2.write(translatAtcList.get(newMtcCodes.get(j))); } else { System.out.println(newMtcCodes.get(j)); } csvW2.write("\n"); } } } csvW.close(); csvW2.close(); newMTC.close(); }
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLSX file and parse it//from w ww. jav a 2 s. c om * @param file The XLSX File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSXFile(File file) { int index = -1; XSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } XSSFSheet sheet = workbook.getSheetAt(index); XSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { XSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:edu.jhu.cvrg.timeseriesstore.opentsdb.store.ExcelStorer.java
License:Apache License
@Override public ArrayList<IncomingDataPoint> extractTimePoints(InputStream inputStream, String[] channels, int samples, long epochTime) { ArrayList<IncomingDataPoint> dataPoints = new ArrayList<IncomingDataPoint>(); XSSFWorkbook subjectWorkbook = getWorkbook(inputStream); HashMap<String, String> tags = new HashMap<String, String>(); for (int i = 0; i < subjectWorkbook.getNumberOfSheets(); i++) { XSSFSheet sheetIn = subjectWorkbook.getSheetAt(i); for (int r = 1; r <= sheetIn.getLastRowNum(); r++) { long currentTime = epochTime; XSSFRow row = sheetIn.getRow(r); String channel = getChannelName(i, channels); dataPoints.add(new IncomingDataPoint("ecg.uv." + channel, currentTime, String.valueOf(row.getCell(1).getNumericCellValue()), tags)); tags.put("format", "excel"); currentTime++;// w w w. ja v a2 s . c o m } } return dataPoints; }
From source file:excelcon.ExcelCon.java
@Override public void start(Stage primaryStage) throws FileNotFoundException, IOException { Button btn = new Button(); Button saveB = new Button(); saveB.setText("Save"); Button run = new Button(); run.setText("Run"); TextArea t = new TextArea(); TextArea saveP = new TextArea(); t.setMaxHeight(1);/*from w ww .j a v a2s .c o m*/ t.setWrapText(true); t.setMaxWidth(300); saveP.setMaxHeight(1); saveP.setWrapText(true); saveP.setMaxWidth(300); Label into = new Label(); Label don = new Label(); btn.setText("Browse"); FileChooser chooser = new FileChooser(); chooser.setTitle("Choose"); FileChooser.ExtensionFilter ef = new ExtensionFilter("xlsx", "*.xlsx"); chooser.getExtensionFilters().add(ef); GridPane gridpane = new GridPane(); gridpane.setPadding(new Insets(5)); gridpane.setHgap(10); gridpane.setVgap(10); into.setText("Start by choosing \"xlsx\" file , press Browse\n " + "{TMX,ABO_Open,TMX_ABO_Open" + ",\nVac.,Caps,Inhibit,Sensor,Sensor_Clip,Color}"); t.setText("File Name"); DirectoryChooser dirCh = new DirectoryChooser(); dirCh.setTitle("Save Files"); //System.out.println(selectedDirectory.getPath()); String[][] names = new String[10][2]; names[0][0] = "TMX"; names[0][1] = "TMX_Of:"; names[1][0] = "ABO_Open"; names[2][0] = "ABO"; names[3][0] = "Vac."; names[4][0] = "Sensor_Clip"; names[5][0] = "Color"; names[6][0] = "Sensor"; names[7][0] = "TMX_ABO_Open"; names[8][0] = "Caps"; names[9][0] = "Inhibit"; names[1][1] = "ABO_Open_Of:"; names[2][1] = "ABO_Of:"; names[3][1] = "Vac._Of:"; names[4][1] = "Sensor_Clip_Of:"; names[5][1] = "Color_Of:"; names[6][1] = "Sensor_Of:"; names[7][1] = "TMX_ABO_Open_Of:"; names[8][1] = "Caps_Of:"; names[9][1] = "Inhibit_Of:"; btn.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { try { File file = chooser.showOpenDialog(new Stage()); xls = new FileInputStream(file); t.setText(file.getPath()); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } finally { try { xls.close(); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } } } }); saveB.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { selectedDirectory = dirCh.showDialog(primaryStage); saveP.setText(selectedDirectory.getPath()); } }); run.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { try { xls = new FileInputStream(t.getText()); XSSFWorkbook wb = new XSSFWorkbook(xls); int sheetNo = wb.getNumberOfSheets(); String error = ""; for (int i = 0; i < sheetNo; i++) { XSSFSheet sheet = wb.getSheetAt(i); error += done(names, sheet, selectedDirectory); } don.setText("if Error :\n" + error + "Done, check your files now"); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } } }); gridpane.add(into, 0, 0, 2, 1); gridpane.add(t, 0, 1); gridpane.add(btn, 1, 1); gridpane.add(saveP, 0, 2); gridpane.add(saveB, 1, 2); gridpane.add(run, 0, 3, 2, 1); gridpane.add(don, 0, 4, 2, 1); t.setVisible(true); StackPane root = new StackPane(); ScrollPane sp = new ScrollPane(); sp.setContent(gridpane); root.getChildren().add(sp); root.setBlendMode(BlendMode.MULTIPLY); Scene scene = new Scene(root, 450, 250, Color.ALICEBLUE); primaryStage.setTitle("Excel txt export"); primaryStage.setScene(scene); primaryStage.show(); }