List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.excel.javafx.frames.MainFrame.java
private void getDestinationFileHeaders(File file) { try {/*from w w w.j a v a 2 s .c o m*/ FileInputStream destFile = new FileInputStream(file); XSSFWorkbook workbook1 = new XSSFWorkbook(destFile); destSheetSelector.removeAllItems(); //to clear existing headers for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) { destSheetSelector.addItem(workbook1.getSheetName(sheetno)); } //destinationColumnSelector(); // to fill columnSelection } catch (FileNotFoundException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java
public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) { ArrayList<String[]> data = new ArrayList<String[]>(); File selectedFile = null;/*from ww w. j ava 2s .c o m*/ FileInputStream fis = null; ; XSSFWorkbook workbook = null; //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx"); boolean sheetFound = false; XSSFSheet sheet = null; try { int sheetIndex = -1; fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().equals(sheetName)) { sheetFound = true; sheetIndex = i; selectedFile = inputFile; break; } } XSSFWorkbook myWorkBook; try { myWorkBook = new XSSFWorkbook(selectedFile); // Return first sheet from the XLSX workbook // XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); String query; String[] values = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowHasData(row) && (row.getRowNum() >= 9)) { int endColumn = row.getLastCellNum(); int startColumn = row.getFirstCellNum(); // For each row, iterate through each columns values = new String[endColumn + 2]; for (int i = startColumn; i < endColumn; i++) { String cellValue; Cell cell = row.getCell(i); values[0] = Integer.toString(row.getRowNum() + 1); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Date cellDateValue = cell.getDateCellValue(); cellValue = df.format(cellDateValue); } else { cell.setCellType(cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue().replaceAll("'", ""); } if (!"".equals(cellValue) && cellValue != null) { values[i + 1] = cellValue; } else if (cellValue.isEmpty() || "".equals(cellValue)) { values[i + 1] = ""; } } else { values[i + 1] = ""; } } data.add(values); } } } catch (InvalidFormatException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } finally { try { fis.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } return data; }
From source file:com.krawler.spring.importFunctionality.ImportController.java
License:Open Source License
public ModelAndView fileUploadXLSX(HttpServletRequest request, HttpServletResponse response) { String View = "jsonView-ex"; JSONObject jobj = new JSONObject(); try {//from w w w . ja v a2 s.c o m System.out.println("A(( Upload XLSX start : " + new Date()); jobj.put("success", true); FileItemFactory factory = new DiskFileItemFactory(4096, new File(ConfigReader.getinstance().get("UploadTempDir", "/tmp"))); ServletFileUpload upload = new ServletFileUpload(factory); upload.setSizeMax(10485760); // 10Mb List fileItems = upload.parseRequest(request); Iterator i = fileItems.iterator(); String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; String fileName = null; String fileid = UUID.randomUUID().toString(); fileid = fileid.replaceAll("-", ""); // To append UUID without "-" [SK] String Ext = ""; while (i.hasNext()) { java.io.File destDir = new java.io.File(destinationDirectory); if (!destDir.exists()) { //Create xls file's folder if not present destDir.mkdirs(); } FileItem fi = (FileItem) i.next(); if (fi.isFormField()) continue; fileName = fi.getName(); if (fileName.contains(".")) { Ext = fileName.substring(fileName.lastIndexOf(".")); int startIndex = fileName.contains("\\") ? (fileName.lastIndexOf("\\") + 1) : 0; fileName = fileName.substring(startIndex, fileName.lastIndexOf(".")); } if (fileName.length() > 28) { // To fixed Mysql ERROR 1103 (42000): Incorrect table name throw new DataInvalidateException("Filename is too long, use upto 28 characters."); } fi.write(new File(destinationDirectory, fileName + "_" + fileid + Ext)); } FileInputStream fs = new FileInputStream(destinationDirectory + "/" + fileName + "_" + fileid + Ext); XSSFWorkbook wb = new XSSFWorkbook(fs); int count = wb.getNumberOfSheets(); JSONArray jArr = new JSONArray(); for (int x = 0; x < count; x++) { JSONObject obj = new JSONObject(); obj.put("name", wb.getSheetName(x)); obj.put("index", x); jArr.put(obj); } jobj.put("file", destinationDirectory + "/" + fileName + "_" + fileid + Ext); jobj.put("filename", fileName + "_" + fileid + Ext); jobj.put("data", jArr); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); } catch (FileUploadBase.SizeLimitExceededException ex) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, ex); jobj.put("msg", "File exceeds max size limit i.e 10MB."); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception e) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, e); try { jobj.put("msg", e.getMessage()); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception ex) { } } finally { System.out.println("A(( Upload XLS end : " + new Date()); return new ModelAndView(View, "model", jobj.toString()); } }
From source file:com.niles.excel2json.objects.ExcelFile.java
License:Open Source License
public void process() throws Exception { File excel = new File(path); FileInputStream fis = SystemTools.getFileInputStream(excel); XSSFWorkbook wb = null; try {/*from w ww.ja va 2 s. com*/ wb = new XSSFWorkbook(fis); } catch (IOException ex) { logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage()); return; } this.fileName = excel.getName().replace(StringConstants.XLSX, ""); this.folderPath = fileName + " Files"; folderPath = SystemTools.createFolder(folderPath); int sheetCount = wb.getNumberOfSheets(); int currentSheetNumber = 0; int rowCount = 0; int columnCount = 0; HashMap<Integer, String> headers = new HashMap<Integer, String>(); /* * Itterate through the Excel sheets here and convert them to JSON */ while (currentSheetNumber < sheetCount) { XSSFSheet current = wb.getSheetAt(currentSheetNumber); sheetName = current.getSheetName(); // System.out.println(sheetName); if (current.getRow(0) == null) { // logger.error("[{}] Sheet contains no data", sheetName); } else { //logger.info("[{}] Processing sheet", sheetName); rowCount = current.getLastRowNum() + 1; columnCount = current.getRow(0).getPhysicalNumberOfCells(); // System.out.println("Col Count: " + columnCount); // System.out.println("Row Count: " + rowCount); JSONArray myJSONArray = new JSONArray(); JSONObject currentJSONObject = null; for (int a = 0; a < rowCount; a++) { XSSFRow currentRow = current.getRow(a); if (a == 0) { logger.info("[{}] Loading header information", sheetName); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String header = "Header" + currentCellNumber; if (currentCell != null) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { header = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { header = currentCell.getNumericCellValue() + ""; } } headers.put(currentCellNumber, header.replaceAll(" ", "")); } } else { currentJSONObject = new JSONObject(); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String value = ""; if (currentCell != null) { if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { value = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { value = currentCell.getNumericCellValue() + ""; } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { value = currentCell.getBooleanCellValue() + ""; } if (value == null) { value = ""; } } } currentJSONObject.put(headers.get(currentCellNumber), value); } myJSONArray.add(currentJSONObject); } } writeToJson(myJSONArray); } currentSheetNumber++; } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {/*from w ww. ja v a 2 s. c om*/ FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet1 = myWorkBook.getSheetAt(j); if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) { myWorkBook.removeSheetAt(j); hasTrue = true; break; } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); break; } } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestCasesFromXLSX(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {//www . j a va 2s . co m FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(testSuiteName)) { rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; XSSFRow row = mySheet.getRow(newNum); if (row != null) { mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); } hasTrue = true; break; } } } } if (hasTrue) { for (int j = 0; j < numberOfSheets; j++) { XSSFSheet myXSSFSheet = myWorkBook.getSheetAt(j); if (myXSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myXSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); testCases.add(createObject); } float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; int totalTestCases = testCases.size(); for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } if (tstCase.getStatus().equalsIgnoreCase("success")) { totalPass = totalPass - 1; } else if (tstCase.getStatus().equalsIgnoreCase("failure")) { totalFail = totalFail - 1; } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable - 1; } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked - 1; } XSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(tstCase.getTestCaseId()) && createObject.getName().equals(testSuiteName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, totalTestCases, "delete"); } } } } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void updateTestCaseToXLSX(String fileName, com.photon.phresco.commons.model.TestCase tstCase, List<TestCase> testCases, StringBuilder sb) throws FileNotFoundException, InvalidFormatException, IOException, UnknownHostException, PhrescoException { FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(fileName)) { Iterator<Row> rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next();//w w w . j a v a2s. c o m } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1)))) { TestCase createObject = readTest(next); testCases.add(createObject); if (tstCase != null && createObject.getTestCaseId().equals(tstCase.getTestCaseId())) { Cell preConditionsCell = next.getCell(5); preConditionsCell.setCellValue(tstCase.getPreconditions()); Cell stepsCell = next.getCell(6); stepsCell.setCellValue(tstCase.getSteps()); Cell expectedCell = next.getCell(9); expectedCell.setCellValue(tstCase.getExpectedResult()); Cell actualCell = next.getCell(10); actualCell.setCellValue(tstCase.getActualResult()); Cell statusCell = next.getCell(11); statusCell.setCellValue(tstCase.getStatus()); Cell commentCell = next.getCell(12); commentCell.setCellValue(tstCase.getBugComment()); } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); String testId = tstCase.getTestCaseId(); String status = tstCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalBlocked = totalBlocked + 1; } if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("success") && status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("failure") && status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("notApplicable") && status.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("blocked") && status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } XSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(testId) && createObject.getName().equals(fileName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, 0, ""); } } } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { myInput.close(); FileOutputStream outFile = new FileOutputStream(sb.toString()); myWorkBook.write(outFile); outFile.close(); } } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {/*from w w w. j a va 2s . c o m*/ //FileInputStream myInput = new FileInputStream(filePath); int numCol; int cellno = 0; CellStyle tryStyle[] = new CellStyle[20]; String sheetName = testName; //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""}; Iterator<Row> rowIterator; File testDir = new File(filePath); StringBuilder sb = new StringBuilder(filePath); if (testDir.isDirectory()) { FilenameFilter filter = new PhrescoFileFilter("", "xlsx"); File[] listFiles = testDir.listFiles(filter); if (listFiles.length != 0) { for (File file1 : listFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(next.getRowNum() - 1); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls"); File[] xlsListFiles = testDir.listFiles(xlsFilter); if (xlsListFiles.length != 0) { for (File file2 : xlsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(mySheet.getLastRowNum() - 2); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; if (numCell == 15) { break; } } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file1 : odsListFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } File file = new File(sb.toString()); addTestSuiteToOds(file, cellValue); } } } } catch (Exception e) { // throw new PhrescoException(e); } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void writeTestCasesToXLSX(String testSuiteName, String[] cellValue, String status, int numCol, int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException { Iterator<Row> rowIterator; try {/*from w w w . ja va 2 s . c om*/ FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(testSuiteName)) { rowIterator = mySheet.rowIterator(); Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } float totalPass = 0; float totalFail = 0; float totalNotApp = 0; float totalBlocked = 0; float notExecuted = 0; float totalTestCases = 0; for (int i = 0; i <= 22; i++) { rowIterator.next(); } do { next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1))) && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) { String value = getValue(next.getCell(11)); if (StringUtils.isNotEmpty(value)) { if (value.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (value.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (value.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (value.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } else { notExecuted = notExecuted + 1; } } } while (rowIterator.hasNext()); //to update the status in the index page if (status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (status.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } else { notExecuted = notExecuted + 1; } totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted; XSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (createObject.getName().equals(testSuiteName)) { addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted, totalTestCases, next1); } } } Row r = null; if (mySheet.getSheetName().equalsIgnoreCase("Index")) { r = mySheet.createRow(next.getRowNum() - 1); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); cell.setCellStyle(tryStyle[i]); } FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } } } catch (PhrescoException e) { throw new PhrescoException(e); } catch (IOException e) { throw new PhrescoException(e); } catch (InvalidFormatException e) { throw new PhrescoException(e); } }
From source file:com.vodafone.poms.ii.helpers.ActivityCodeLoader.java
public static List<ActivityCode> readFile(InputStream fis) { List<ActivityCode> activitiesCode = new ArrayList<>(); ActivityCode activityCode = null;/*from ww w . j ava2s.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++) { if (myWorkBook.getSheetAt(i).getSheetName().toLowerCase().contains("code")) { sheet = myWorkBook.getSheetAt(i); break; } } if (sheet != null) { Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { activityCode = new ActivityCode(); activityCode.setMaterialId(getCellValue(row.getCell(0))); activityCode.setDescription(getCellValue(row.getCell(1))); if (getCellValue(row.getCell(2)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setQuantityRequested(new Integer(getCellValue(row.getCell(2)))); } if (getCellValue(row.getCell(3)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setVendorPrice(new Float(getCellValue(row.getCell(3)))); } else { continue; } if (getCellValue(row.getCell(4)).matches("\\d+(?:\\.\\d+)?")) { activityCode.setSubcontractorPrice(new Float(getCellValue(row.getCell(4)))); } else { continue; } activityCode.setUm(activityCode.getVendorPrice() - activityCode.getSubcontractorPrice()); activityCode.setUmPercent(activityCode.getUm() / activityCode.getVendorPrice()); activitiesCode.add(activityCode); } } } } catch (FileNotFoundException ex) { Logger.getLogger(ActivityCodeLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ActivityCodeLoader.class.getName()).log(Level.SEVERE, null, ex); } return activitiesCode; }