List of usage examples for org.apache.poi.ss.usermodel DataFormatter formatCellValue
public String formatCellValue(Cell cell)
Returns the formatted value of a cell as a String regardless of the cell type.
From source file:eventHandlers.CompareDrotVSRoster.java
private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); DataFormatter df = new DataFormatter(); String ssnString = df.formatCellValue(ssnCell); //return ***-**-**** ssnString = ssnString.replace("-", ""); Cell soCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String signOutDateString = df.formatCellValue(soCell); //return ***-**-**** Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssnString)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> leaves = proclibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); }/*from w w w .j a v a2 s . co m*/ } } else if (rejlibrary.containsKey(ssnString)) { Map<String, String> leaves = rejlibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } else if (recylibrary.containsKey(ssnString)) { Map<String, String> leaves = recylibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } }
From source file:eventHandlers.XLSX2BatchHandler.java
public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try {/* w ww .j a va2 s . co m*/ FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // check if the excel is the leave roster int globalCount = 1; MyDate soDate = new MyDate(); MyDate siDate = new MyDate(); while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<String, String>(); int keys = 0; //index of the cell // reset date every row Boolean soDateAssigned = false; Boolean siDateAssigned = false; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int type = cell.getCellType(); // distinguish the cell content, in the xlsx file, // SSN and dates are CELL_TYPE_NUMERIC // number of leave days is CELL_TYPE_FORMULA // the rest is CELL_TYPE_STRING if (type == HSSFCell.CELL_TYPE_NUMERIC && keys != GlobalVar.LAST4_CELL_INDEX_V1) { // dates DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String dateString = df.formatCellValue(cell); if (!soDateAssigned && !siDateAssigned) { //System.out.println(dateString); //150201 soDate = new MyDate(dateString); soDateAssigned = true; } else if (soDateAssigned && !siDateAssigned) { siDate = new MyDate(dateString); siDateAssigned = true; } else { // do nothing //System.out.println("We should never come here!"); } //String value = (int) cell.getNumericCellValue() + ""; // number of days container.put(keyList.get(keys), dateString); // System.out.println(value); // } else if (type == HSSFCell.CELL_TYPE_STRING // && keys == GlobalVar.SSN_CELL_INDEX){ } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** // String lastFour = cell.getStringCellValue(); //ssnString = ssnString.replace("-",""); lastFour = GlobalVar.last4Generator(lastFour); // System.out.println("SSN: " + lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); // } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // String days = soDate.getDaysDifftoString(siDate); // container.put(keyList.get(keys),days); // //reset the dates } else if (type == HSSFCell.CELL_TYPE_STRING) { String value; if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { String str2 = cell.getStringCellValue(); //System.out.println(str2); value = GlobalVar.getDMOLeaveArea(str2); //System.out.println(value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); //System.out.println(value); } else { value = cell.getStringCellValue(); } container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1)); String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); String leaveDays = GlobalVar.computeNumOfDays(new MyDate(signOutDate), new MyDate(signInDate)); int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, leaves); if (leaveMsg == GlobalVar.GOOD_LEAVE) { String lc = GlobalVar.LC; String inputSource = ctrlNum.substring(0, 2); String cycle = GlobalVar.CYCLE; String data = "0SB03" + SSN + first5 + signOutDate + signInDate + leaveType + leaveDays + leaveArea + ctrlNum + "000" + GlobalVar.whiteSpace() + lc + inputSource + cycle + "@" + GlobalVar.globalCountGenerator(globalCount); output.println(data); globalCount++; } else if (leaveMsg == GlobalVar.OVERLAP_LEAVE) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); String msg = "Line" + lineCount + " : " + ctrlNum + " " + lastName + " " + signOutDate + " - " + signInDate; JOptionPane.showMessageDialog(null, msg + "\n is overlapping with a leave posted before."); } else if (leaveMsg == GlobalVar.DUPLICATE_CTRL_NUM) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); JOptionPane.showMessageDialog(null, "Line" + lineCount + " : Duplicate control number " + ctrlNum + " for " + lastName + "."); } } fis.close(); output.close(); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:features.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *//*from w ww.j av a2s.c o m*/ public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream(new File(locOfInputUfmExcelFile)); //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); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
static void printSheet(Sheet sheet) { final DataFormatter dataFormatter = new DataFormatter(); List<List<String>> rows = new ArrayList<>(); for (Row row : sheet) { List<String> r = new ArrayList<>(); for (Cell cell : row) { r.add(dataFormatter.formatCellValue(cell)); }// w w w. j a v a 2 s .c om rows.add(r); } printSheet(rows); }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
protected List<String> getDataFromRow(Row row) { final DataFormatter dataFormatter = new DataFormatter(); return stream(spliteratorUnknownSize(row.cellIterator(), ORDERED), false) .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList()); }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
protected List<String> getDataFromColumn(Sheet sheet, int colnum) { final DataFormatter dataFormatter = new DataFormatter(); return stream(spliteratorUnknownSize(sheet.rowIterator(), ORDERED), false).map(row -> row.getCell(colnum)) .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList()); }
From source file:Files.XLSX2BatchHandler.java
public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try {//w ww . j a v a 2 s.co m FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); List<String> errorList = new ArrayList<>(); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList numCell++; } System.out.println("XLSX2BatchHandler.java UCFR loaded = " + DB.isUCFRLoaded()); if (numCell == GlobalVar.LEAVE_TITLES_V1.length && DB.isUCFRLoaded()) { // check if the excel is the leave roster System.out.println("XLSX2BatchHandler.java: V1, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<>(); int keys = 0; //index of the cell // reset date every row while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V1 || keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** lastFour = GlobalVar.last4Generator(lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1 || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); String dateString = df.formatCellValue(cell); container.put(keyList.get(keys), dateString); } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { int type = cell.getCellType(); String value = null; if (type == HSSFCell.CELL_TYPE_STRING) { value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string } container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } if (leaveDays != null) { int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, first5, leaves); //add leave into leaves globalCount = GlobalVar.batchGenerator(SSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid dates are entered!"); } } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && DB.isUCFRLoaded()) { // full SSN xlsx System.out.println("XLSX2BatchHandler.java: V2, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = DB.getLastNamefromSSN(fullSSN); String lastFour = GlobalVar.last4Generator(fullSSN); String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && !DB.isUCFRLoaded()) { System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = GlobalVar.TEMP_DMO_NAME; String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = GlobalVar.TEMP_DMO_NAME; MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.getDay() != null && soDate != null && soDate.getDay() != null) { if (siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid date entered"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); // write the leave on the batch file (PrintStream output) globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); } else if (!DB.isUCFRLoaded()) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: UCFR needs to be loaded to process this type of xlsx."); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file output.close(); outputFile.close(); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Files.XLSX2BatchHandler.java
public void rowContainerBuilder(Map<String, String> rowContainer, List<String> keyList, Iterator<Cell> cellIterator) { int keys = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V2) { String value = cell.getStringCellValue(); rowContainer.put(keyList.get(keys), value); } else if (keys == GlobalVar.FULL_SSN_CELL_INDEX_V2) { int type = cell.getCellType(); String fullSSN = null; if (type == HSSFCell.CELL_TYPE_STRING) { fullSSN = cell.getStringCellValue(); //string type ssn fullSSN = fullSSN.replaceAll("-", ""); } else { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); fullSSN = df.formatCellValue(cell); //return ***-**-**** }/*w w w . ja v a2s. c om*/ fullSSN = GlobalVar.fullSSNgenerator(fullSSN); rowContainer.put(keyList.get(keys), fullSSN); } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2 || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2) { DataFormatter df = new DataFormatter(); String dateString = df.formatCellValue(cell); rowContainer.put(keyList.get(keys), dateString); } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V2) { int type = cell.getCellType(); String value = null; if (type == HSSFCell.CELL_TYPE_STRING) { value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string } rowContainer.put(keyList.get(keys), value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V2) { String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); rowContainer.put(keyList.get(keys), value); } keys++; } }
From source file:FLCSS.floridaautomationtestsuite.ufm.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *//* ww w. j av a 2 s .com*/ public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream( new File("C:\\Users\\23319\\Downloads\\FLCSS-java(5)\\a1\\src\\QATP_R0.xlsx")); //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); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; // HashMap<String,String> headerColumnNames = new HashMap<String,String>(); // //int[][] rowWithData = new int[5][]; // for (i = 0; i < 84; i++) { // // System.out.println("hello"); // headerColumnNames.put(formatter.formatCellValue((rowWithColumnNames.getCell(i))),""); // // System.out.println(headerColumnNames.size()); // } //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:fr.amapj.service.engine.excelreader.ExcelReader.java
License:Open Source License
private String getValue(Row row, int i, DataFormatter df) { Cell cell = row.getCell(i);//w w w .j av a 2 s . c om if (cell == null) { return null; } return df.formatCellValue(cell); }