List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:ExcelRead.CrbRead.java
public void readFromExcel(String file, JTable table) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next();//from ww w .j a va 2s. c o m Iterator<Cell> cells = row.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { old_reg_cod = String.valueOf((int) cell.getNumericCellValue()); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { old_reg_cod = cell.getStringCellValue(); break; } case 1: name = cell.getStringCellValue(); break; case 2: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { new_reg_cod = String.valueOf((int) cell.getNumericCellValue()); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { new_reg_cod = cell.getStringCellValue(); break; } default: System.out.print("|"); break; } } DefaultTableModel model = (DefaultTableModel) table.getModel(); String[] data = { old_reg_cod, name, new_reg_cod }; model.addRow(data); removeAllFields(); } }
From source file:ExcelRead.PatientRead.java
public void readFromExcel(String file, JTable table) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next();// w ww.j av a 2 s . c om Iterator<Cell> cells = row.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: ID = (int) cell.getNumericCellValue(); // table.setValueAt(ID, i, 0); break; case 1: String[] fullName = cell.getStringCellValue().split(" "); name = fullName[1]; surname = fullName[0]; middleName = fullName[2]; // table.setValueAt(cell.getStringCellValue(), i, 1); break; case 2: SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); birthdate = sdf.format(cell.getDateCellValue()); //table.setValueAt(birthdate, i, 2); break; case 3: sex = cell.getStringCellValue(); //table.setValueAt(sex, i, 3); break; case 4: address = cell.getStringCellValue(); // table.setValueAt(address, i, 4); break; case 5: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { lpu_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(lpu_id, i, 5); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { lpu_id = cell.getStringCellValue(); //table.setValueAt(lpu_id, i, 5); break; } case 6: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { crb_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(crb_id, i, 6); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { crb_id = cell.getStringCellValue(); // table.setValueAt(crb_id, i, 6); break; } case 7: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { snils = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(snils, i, 7); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { snils = cell.getStringCellValue(); //table.setValueAt(snils, i, 7); break; } case 8: String[] passport = cell.getStringCellValue().split(" "); //table.setValueAt(cell.getStringCellValue(), i, 8); pass_ser = passport[0]; pass_num = passport[1]; break; case 9: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { old_police = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(old_police, i, 9); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { old_police = cell.getStringCellValue(); // table.setValueAt(old_police, i, 9); break; } case 10: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { new_police = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(new_police, i, 10); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { new_police = cell.getStringCellValue(); //table.setValueAt(new_police, i, 10); break; } case 11: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { phoneNum = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(phoneNum, i, 11); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { phoneNum = cell.getStringCellValue(); //table.setValueAt(phoneNum, i, 11); break; } default: System.out.print("|"); break; } } DefaultTableModel model = (DefaultTableModel) table.getModel(); String[] data = { String.valueOf(ID), surname + " " + name + " " + middleName, birthdate, sex, address, lpu_id, crb_id, snils, pass_ser + " " + pass_num, old_police, new_police, phoneNum }; model.addRow(data); removeAllFields(); } }
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 ww w . j a v 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(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:FileHelper.ExcelHelper.java
public DataSheet ReadTestCaseFileFromSheet(String fileName, String sheetName, MyDataHash myDataHash, String rawData) {/*from w ww . j ava2 s .c o m*/ try { File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); XSSFSheet sheet = book.getSheet(sheetName); Iterator<Row> itr = sheet.iterator(); DataSheet dataSheet = new DataSheet(); ArrayList<RowDataFromFile> datas = new ArrayList<RowDataFromFile>(); ArrayList<DataHash> dataHash = new ArrayList<>(); int colmnDataStart = 0, colmnDataStop = 0, numReal = 0; ArrayList<NameDynamic> nameDynamic = new ArrayList<NameDynamic>(); ArrayList<DataInput> listDataInput = new ArrayList<>(); ArrayList<DataInputLevel2> dataInputLevel2 = new ArrayList<>(); while (itr.hasNext()) { RowDataFromFile dataRow = new RowDataFromFile(); JsonObject jObjReq = new JsonObject(); String caller = ""; Row row = itr.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: { String str = cell.getStringCellValue(); if (str.equals("STT")) { while (cellIterator.hasNext()) { Cell cell1 = cellIterator.next(); switch (cell1.getCellType()) { case Cell.CELL_TYPE_STRING: { // System.out.println(cell1.getStringCellValue()); if (cell1.getStringCellValue().equals("Data Request")) { colmnDataStart = cell1.getColumnIndex(); } if (cell1.getStringCellValue().equals("Threads")) { colmnDataStop = cell1.getColumnIndex() - 1; } if (cell1.getStringCellValue().equals("Result Real")) { // System.out.println("Colmn Reail: " + cell1.getColumnIndex()); numReal = cell1.getColumnIndex(); } break; } case Cell.CELL_TYPE_NUMERIC: { System.out.println(cell1.getNumericCellValue()); break; } } } Row row1 = sheet.getRow(1); Row row2 = sheet.getRow(2); Row row3 = sheet.getRow(3); Row row4 = sheet.getRow(4); Cell cellColmn; Cell cellColmn2; int numColmn = colmnDataStart; while (numColmn <= colmnDataStop) { cellColmn = row1.getCell(numColmn); String temp = GetValueStringFromCell(cellColmn); cellColmn2 = row2.getCell(numColmn); NameDynamic nameDy = CutStrGetNameDynamic(GetValueStringFromCell(cellColmn2)); if (nameDy.getIsDyn().equals("1")) { // Check Data is change when run Thread nameDynamic.add(nameDy); } // Add to list save data api listDataInput.add(new DataInput(temp, nameDy.getName())); DataHash dataHt = myDataHash.CheckNameDataIsHash(sheetName, nameDy.getName()); if (dataHt != null) { dataHt.setNumColumn(numColmn); dataHash.add(dataHt); } if (temp.equals("Object")) { // Exist object group datas name ArrayList<DataInput> listDataIputLevel2 = new ArrayList<>(); cellColmn = row3.getCell(numColmn); cellColmn2 = row4.getCell(numColmn); String tempT = GetValueStringFromCell(cellColmn); if (!tempT.equals("")) { while (!GetValueStringFromCell(cellColmn).equals("")) { nameDy = CutStrGetNameDynamic(GetValueStringFromCell(cellColmn2)); if (nameDy.getIsDyn().equals("1")) { // Check Data is change when run Thread nameDynamic.add(nameDy); } dataHt = myDataHash.CheckNameDataIsHash(sheetName, nameDy.getName()); if (dataHt != null) { dataHt.setNumColumn(numColmn); dataHash.add(dataHt); } listDataIputLevel2.add( new DataInput(GetValueStringFromCell(cellColmn), nameDy.getName())); numColmn++; cellColmn = row3.getCell(numColmn); cellColmn2 = row4.getCell(numColmn); } numColmn--; dataInputLevel2.add(new DataInputLevel2(listDataIputLevel2)); } else { dataInputLevel2.add(new DataInputLevel2(listDataIputLevel2)); } } numColmn++; } Gson gson = new Gson(); System.out.println(gson.toJson(listDataInput)); System.out.println(gson.toJson(dataHash)); } break; } case Cell.CELL_TYPE_NUMERIC: { // System.out.println(cell.getNumericCellValue()); if (cell.getNumericCellValue() > 0) { dataRow.setId(row.getRowNum()); String isSecutiry = "no"; int arrIndex = 0; int arrIndexReq = 0; // Object con int arrIndexRow = 0; while (cellIterator.hasNext()) { Cell cell1 = cellIterator.next(); if ((cell1.getColumnIndex() >= colmnDataStart) && (cell1.getColumnIndex() < colmnDataStop)) { if (listDataInput.get(arrIndex).getType().equals("Object")) { JsonObject jObj = new JsonObject(); int i = 0; int size = dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().size(); while (i < size) { if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("String")) { String value = GetValueStringFromCell(cell1); if (!dataHash.isEmpty()) { for (DataHash dataH : dataHash) { if (dataH.getNumColumn() == cell1.getColumnIndex()) { value = EncryptHelper.EncryptData(value, dataH.getAlgorithm(), dataH.getKey(), dataH.getIv()); } } } jObj.addProperty(dataInputLevel2.get(arrIndexReq) .getListDataIputLevel2().get(i).getName(), value); } else if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("Integer")) { int value = GetValueIntegerFromCell(cell1); jObj.addProperty(dataInputLevel2.get(arrIndexReq) .getListDataIputLevel2().get(i).getName(), value); } else if (dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getType().equals("Object")) { String value = GetValueStringFromCell(cell1); Gson gson = new Gson(); JsonObject obj = gson.fromJson(value, JsonObject.class); jObj.add(dataInputLevel2.get(arrIndexReq).getListDataIputLevel2().get(i) .getName(), obj); } i++; if (i < size) { cell1 = cellIterator.next(); } } arrIndexReq++; jObjReq.add(listDataInput.get(arrIndex).getName(), jObj); } else if (listDataInput.get(arrIndex).getType().equals("String")) { String value = GetValueStringFromCell(cell1); if (!dataHash.isEmpty()) { for (DataHash dataH : dataHash) { if (dataH.getNumColumn() == cell1.getColumnIndex()) { value = EncryptHelper.EncryptData(value, dataH.getAlgorithm(), dataH.getKey(), dataH.getIv()); } } } jObjReq.addProperty(listDataInput.get(arrIndex).getName(), value); } else if (listDataInput.get(arrIndex).getType().equals("Integer")) { int value = GetValueIntegerFromCell(cell1); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), value); } arrIndex++; } else if (cell1.getColumnIndex() == colmnDataStop) { isSecutiry = GetValueStringFromCell(cell1); dataRow.setNameAlgorithm(isSecutiry); } else if (cell1.getColumnIndex() > colmnDataStop) { if (arrIndexRow == 0) { dataRow.setThread(GetValueIntegerFromCell(cell1)); } else if (arrIndexRow == 1) { dataRow.setResultExpect(GetValueStringFromCell(cell1)); } arrIndexRow++; } } // System.out.println("data: " + jObj.toString()); // System.out.println("data Req: " + jObjReq.toString()); String[] arrR = rawData.split(","); String rawDataNew = ""; char a = '"'; for (String str : arrR) { if (str.charAt(0) == a) { String value = str.substring(1, str.length() - 1); rawDataNew += value; } else { JsonElement je = jObjReq.get(str); if (je.isJsonObject()) { String value = je.toString(); rawDataNew += value; } else { String value = je.getAsString(); rawDataNew += value; } } } String[] arr = isSecutiry.split("-"); if (arr[0].equals("chksum")) { String chksum = CheckSumInquireCard.createCheckSum(isSecutiry, rawDataNew); // System.out.println("chksum: " + chksum); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), chksum); } else if (arr[0].equals("signature")) { String signature = RSASHA1Signature.getSignature(isSecutiry, rawDataNew); // System.out.println("signature: " + signature); jObjReq.addProperty(listDataInput.get(arrIndex).getName(), signature); } // System.out.println("data Request: " + jObjReq.toString()); dataRow.setData(jObjReq); dataRow.setNumReal(numReal); Gson gson = new Gson(); System.out.println("data row: " + gson.toJson(dataRow)); datas.add(dataRow); } break; } } } dataSheet.setDatas(datas); dataSheet.setNameDynamic(nameDynamic); dataSheet.setListDataInput(listDataInput); dataSheet.setDataInputLevel2(dataInputLevel2); Gson gson = new Gson(); // System.out.println("save data: " + gson.toJson(datas)); fis.close(); return dataSheet; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new DataSheet(); } }
From source file:FileHelper.ExcelHelper.java
public URLs ReadURLS(String fileName, String sheetName) { try {/*ww w. j av a 2 s. c o m*/ File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); XSSFSheet sheet = book.getSheet(sheetName); Iterator<Row> itr = sheet.iterator(); URLs urls = new URLs(); ArrayList<DataURL> dataUrls = new ArrayList<DataURL>(); Row row = itr.next(); while (itr.hasNext()) { row = itr.next(); DataURL dataUrl = new DataURL(); Cell cell = row.getCell(0); if ((cell != null) && (!GetValueStringFromCell(cell).equals(""))) { dataUrl.setNameSheet(GetValueStringFromCell(cell)); cell = row.getCell(1); dataUrl.setUrl(GetValueStringFromCell(cell)); cell = row.getCell(2); dataUrl.setAcceptType(GetValueStringFromCell(cell)); cell = row.getCell(3); dataUrl.setContentType(GetValueStringFromCell(cell)); cell = row.getCell(4); dataUrl.setRawData(GetValueStringFromCell(cell)); dataUrls.add(dataUrl); } // Gson gson = new Gson(); // System.out.println("url: " + gson.toJson(dataUrl)); } urls.setUrls(dataUrls); return urls; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new URLs(); } }
From source file:FileHelper.ExcelHelper.java
public MyDataHash ReadNameHash(String fileName, String sheetName) { try {/*ww w. j av a 2 s.c o m*/ File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); XSSFSheet sheet = book.getSheet(sheetName); Iterator<Row> itr = sheet.iterator(); Row row = itr.next(); MyDataHash myDataHash = new MyDataHash(); ArrayList<DataHash> dataHahs = new ArrayList<>(); while (itr.hasNext()) { row = itr.next(); DataHash dataH = new DataHash(); Cell cell = row.getCell(0); dataH.setNameApi(GetValueStringFromCell(cell)); cell = row.getCell(1); dataH.setNameData(GetValueStringFromCell(cell)); cell = row.getCell(2); dataH.setAlgorithm(GetValueStringFromCell(cell)); cell = row.getCell(3); dataH.setKey(GetValueStringFromCell(cell)); cell = row.getCell(4); if (cell != null) { dataH.setIv(GetValueStringFromCell(cell)); } dataHahs.add(dataH); } myDataHash.setDataHashs(dataHahs); return myDataHash; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new MyDataHash(); } }
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 {//from ww w . jav a 2 s.com 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: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 a v a 2 s.c om*/ 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.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java
public static void moveColumns(XSSFSheet sheet) throws Exception { Row first = sheet.iterator().next(); Cell firstCell = first.cellIterator().next(); int firstIndex = firstCell.getColumnIndex(); if (firstIndex > 2) { int difference = firstIndex - 2; for (int i = 0; i < difference; i++) { deleteColumn(sheet, 0);/*from www.j a v a2 s . c o m*/ } } }
From source file:Funcionalidad.LeerExcel.java
public boolean leer(File archivo, Contenedor almacenamiento) { boolean ok = true; try {// w ww. j a va 2 s.co m FileInputStream fs = new FileInputStream(archivo); XSSFWorkbook workbook = new XSSFWorkbook(fs); for (int i = 0; i < 5; i++) { XSSFSheet sheet = null; sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.iterator(); Row row; while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; ArrayList<String> nombres = new ArrayList<>(); ArrayList<Integer> numeros = new ArrayList<>(); while (cellIterator.hasNext()) { celda = cellIterator.next(); switch (celda.getCellType()) { case Cell.CELL_TYPE_STRING: nombres.add(celda.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: Double d = celda.getNumericCellValue(); numeros.add(d.intValue()); break; } } switch (i) { case 0: almacenamiento.anadirProfesor( new Profesor(nombres.get(0), nombres.get(1), nombres.get(2), numeros.get(0))); break; case 1: almacenamiento .anadidTitulacion(new Titulacion(numeros.get(0), nombres.get(0), numeros.get(1))); break; case 2: almacenamiento.anadirAsignatura(new Asignatura(nombres.get(0), almacenamiento.getProfesorPorId(numeros.get(0)), numeros.get(1), almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), numeros.get(4))); break; case 3: almacenamiento.anadirAula(new Aula(numeros.get(0), nombres.get(0))); break; case 4: almacenamiento.anadirGrupo(new Grupo(numeros.get(0), almacenamiento.getAulaPorId(numeros.get(1)), almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), nombres.get(0))); break; } } } } catch (Exception ex) { //Logger.getLogger(LeerExcel.class.getName()).log(Level.SEVERE, null, ex); ok = false; } return ok; }