List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getSheetName
@SuppressWarnings("resource") @Override public String getSheetName()
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Create new merge regions in result sheet identically to range's merge regions from template. * Not support copy of frames and rules/*from www . j av a2 s. c om*/ * * @param resultSheet - result sheet * @param rangeName - range name * @param firstTargetRangeRow - first column of target range * @param firstTargetRangeColumn - first column of target range */ protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow, int firstTargetRangeColumn) { int rangeNameIdx = templateWorkbook.getNameIndex(rangeName); if (rangeNameIdx == -1) return; HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx); AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula()); int column = aref.getFirstCell().getCol(); int row = aref.getFirstCell().getRow(); List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName); if (regionsList != null) for (SheetRange sheetRange : regionsList) { if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) { CellRangeAddress cra = sheetRange.getCellRangeAddress(); if (cra != null) { int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1; int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1; int regionVOffset = cra.getFirstRow() - row; int regionHOffset = cra.getFirstColumn() - column; CellRangeAddress newRegion = cra.copy(); newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn); newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn); newRegion.setFirstRow(regionVOffset + firstTargetRangeRow); newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow); boolean skipRegion = false; for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) { CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex); if (!intersects(newRegion, mergedRegion)) { continue; } skipRegion = true; } if (!skipRegion) { resultSheet.addMergedRegion(newRegion); } } } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Returns EscherAggregate from sheet/* w w w .ja v a 2 s . c o m*/ * * @param sheet - HSSFSheet * @return - EscherAggregate from sheet */ protected EscherAggregate getEscherAggregate(HSSFSheet sheet) { EscherAggregate agg = sheetToEscherAggregate.get(sheet.getSheetName()); if (agg == null) { agg = sheet.getDrawingEscherAggregate(); sheetToEscherAggregate.put(sheet.getSheetName(), agg); } return agg; }
From source file:com.mum.processexceldata.RateService.java
public void processCallRates(HSSFSheet sheet, Date effectiveDate) { String serviceName = null;/*from w w w . j av a 2s . c o m*/ String sourceCountryName = null; String sheetName = sheet.getSheetName(); Service serv = null; st = new StringTokenizer(sheetName, "_"); if (st.countTokens() == 2) { serviceName = st.nextToken(); sourceCountryName = st.nextToken(); } if (serviceName != null && !serviceName.isEmpty()) { //get service from DB matching service Name and Source Country serv = new Service(); } Iterator<Row> rowIterator = sheet.iterator(); //Skip first row with headings if (rowIterator.hasNext()) { rowIterator.next(); } //insertNewRates //Service service //destination Country //offPeakRate long //peakRate long //effectiveDate Date while (rowIterator.hasNext()) { Rate rate = new Rate(); Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { //Cell cell = cellIterator.next(); //if(cell.getCellType()== Cell.CELL_TYPE_NUMERIC){ //System.out.print(cell.getNumericCellValue() + "\t\t"); rate.setDestination(countryCodeMap.get(cellIterator.next().getNumericCellValue())); rate.setPeakRate(cellIterator.next().getNumericCellValue()); rate.setOffPeakRate(cellIterator.next().getNumericCellValue()); //} } rate.setService(serv); //insert into Rate and get Id // serviceIdList.add(rateId) } //update Service with RateIds //serv.getRates().addAll(RateIds); saveService }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter.java
License:Open Source License
private boolean setEClassFromSheetName(HSSFSheet sheet) { String name = sheet.getSheetName(); for (int i = 0; i < this.ePackagesToImport.length; i++) { EPackage ePackage = ePackagesToImport[i]; for (EClassifier classifier : ePackage.getEClassifiers()) { if (classifier instanceof EClass) { String cName = ((EClass) classifier).getName(); if (cName.equalsIgnoreCase(name)) { eClassToImport = (EClass) classifier; if (ImportActivator.DEBUG) { System.out.println("CLASS REF: " + eClassToImport.getName()); }/*from w ww . java 2 s .co m*/ } cName = cName + "_refs"; if (cName.equalsIgnoreCase(name)) { eClassToImport = (EClass) classifier; if (ImportActivator.DEBUG) { System.out.println("CLASS MULTI_REF: " + eClassToImport.getName()); } return true; } } } } return false; }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporterBackup.java
License:Open Source License
private boolean setEClassFromSheetName(HSSFSheet sheet) { String name = sheet.getSheetName(); for (int i = 0; i < this.ePackagesToImport.length; i++) { EPackage ePackage = ePackagesToImport[i]; for (EClassifier classifier : ePackage.getEClassifiers()) { if (classifier instanceof EClass) { String cName = ((EClass) classifier).getName(); if (cName.equalsIgnoreCase(name)) { eClassToImport = (EClass) classifier; System.out.println("CLASS REF: " + eClassToImport.getName()); }//w w w . j av a 2 s . c o m cName = cName + "_refs"; if (cName.equalsIgnoreCase(name)) { eClassToImport = (EClass) classifier; System.out.println("CLASS MULTI_REF: " + eClassToImport.getName()); return true; } } } } return false; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {/* ww w . j ava 2 s .com*/ FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet 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++) { HSSFSheet 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 deleteTestCasesFromXls(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {//from w w w. j av a 2 s.c om FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet 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)))) { tstCase = readTest(next); mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; HSSFRow 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++) { HSSFSheet myHSSFSheet = myWorkBook.getSheetAt(j); if (myHSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); if (StringUtils.isNotEmpty(createObject.getTestCaseId())) { 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; // } HSSFSheet 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 List<TestCase> readTestCase(String filePath, String fileName, com.photon.phresco.commons.model.TestCase tstCase) throws PhrescoException { List<TestCase> testCases = new ArrayList<TestCase>(); try {/*from w ww .j a v a 2s.c om*/ 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; } } updateTestCaseToXLSX(fileName, tstCase, testCases, sb); } else { FilenameFilter filter1 = new PhrescoFileFilter("", "xls"); File[] listFiles1 = testDir.listFiles(filter1); if (listFiles1.length != 0) { for (File file2 : listFiles1) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(fileName)) { readTestFromSheet(tstCase, testCases, mySheet); if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { updateIndexPage(fileName, tstCase, testCases, myWorkBook); } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { myInput.close(); FileOutputStream outFile = new FileOutputStream(sb.toString()); myWorkBook.write(outFile); outFile.close(); } } } } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file2 : odsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } testCases = readTestCasesFormODS(sb, testCases, fileName, tstCase); } } } } catch (Exception e) { } return testCases; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void writeTestCaseToXLS(String testSuiteName, String[] cellValue, String status, int numCol, int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException { Iterator<Row> rowIterator; try {//w w w . j a v a 2s .c o m FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet myHssfSheet = myWorkBook.getSheetAt(j); if (myHssfSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHssfSheet.rowIterator(); Row next; for (Cell cell : myHssfSheet.getRow((myHssfSheet.getLastRowNum()) - 1)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; if (cellno == 15) { break; } } 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; HSSFSheet mySheetHssf = myWorkBook.getSheetAt(0); rowIterator = mySheetHssf.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 (myHssfSheet.getSheetName().equalsIgnoreCase("Index")) { r = myHssfSheet.createRow(next.getRowNum() - 1); } else { r = myHssfSheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); if (tryStyle[i] != null) { 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 (FileNotFoundException e) { throw new PhrescoException(e); } catch (IOException e) { throw new PhrescoException(e); } }
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
protected void processSheetHeader(Element htmlBody, HSSFSheet sheet) { Element h2 = htmlDocumentFacade.createHeader2(); h2.appendChild(htmlDocumentFacade.createText(sheet.getSheetName())); htmlBody.appendChild(h2);//from w w w .j av a 2 s. c o m }