List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.flexive.extractor.ExcelExtractor.java
License:Open Source License
/** * Extracts the text informations from the excel file. * * @param in the input stream to read from * @return the extraxted informations, or null if no text extraction was possible *//*from www .j a va 2 s . co m*/ public ExtractedData extract(final InputStream in) { BufferedInputStream bis = null; try { writer = new ByteArrayOutputStream(); // We need to read the stream 2 times, so we use a buffered input stream and mark the // beginning bis = new BufferedInputStream(in); bis.mark(Integer.MAX_VALUE); // Retrieve summary information POIFSReader r = new POIFSReader(); r.registerListener(this, "\005SummaryInformation"); r.read(bis); bis.reset(); // Retrieve text by processing all sheets HSSFWorkbook wb = new HSSFWorkbook(bis); for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); processSheet(sheet); } // Append summary info to text if (fxsi != null) { writer.write(FxSharedUtils.getBytes(fxsi.getFTIndexInformations())); } writer.flush(); return new ExtractedData(fxsi, writer.toString()); } catch (Exception exc) { exc.printStackTrace(); return null; } finally { try { if (writer != null) writer.close(); } catch (Exception exc) { /*ignore*/} try { if (bis != null) bis.close(); } catch (Exception exc) { /*ignore*/} } }
From source file:com.frameworkset.platform.cms.searchmanager.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //ww w.j a v a2 s . c o m * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (CmsStringUtil.isNotEmpty(excelWb.getSheetName(i))) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if (CmsStringUtil.isNotEmpty(text)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java
License:Apache License
@Override public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) { final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>(); try {// w w w . java 2 s.c om final HSSFWorkbook myWorkBook = new HSSFWorkbook( EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls")); for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) { final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr); addMinistryPerYearToMap(name, map, mySheet); } myWorkBook.close(); } catch ( final IOException e) { LOGGER.warn("Problem loading", e); } return map; }
From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java
License:Apache License
@Override public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(String name) { final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>(); try {//w w w .j a va2 s . c o m final HSSFWorkbook myWorkBook = new HSSFWorkbook( EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls")); for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) { final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr); addDataForYearToMap(name, map, mySheet); } myWorkBook.close(); } catch ( final IOException e) { LOGGER.warn("Problem loading", e); } return map; }
From source file:com.hl7.main.ImportDataFromXLStoXML.java
public void displayFromExcel(String xlsPath) { InputStream inputStream = null; try {/* w w w.j a va2 s .com*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { // Initializing the XML document DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element rootElement = document.createElement("categories"); document.appendChild(rootElement); fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); Integer count = workBook.getNumberOfSheets(); ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>(); for (int i = 0; i < count; i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cells = row.cellIterator(); ArrayList<String> rowData = new ArrayList<String>(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); String richTextString = cell.getStringCellValue(); System.out.println("String: " + richTextString); rowData.add(richTextString); } // end while data.add(rowData); } // end while } int numOfProduct = data.size(); for (int i = 0; i < numOfProduct; i++) { Element productElement = document.createElement("categoryName"); rootElement.appendChild(productElement); int index = 0; for (String s : data.get(i)) { String headerString = data.get(0).get(index); if (data.get(0).get(index).equals("image link")) { headerString = "image_link"; } if (data.get(0).get(index).equals("product type")) { headerString = "product_type"; } Element headerElement = document.createElement(headerString); productElement.appendChild(headerElement); headerElement.appendChild(document.createTextNode(s)); index++; } } TransformerFactory tFactory = TransformerFactory.newInstance(); Transformer transformer = tFactory.newTransformer(); // Add indentation to output transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2"); DOMSource source = new DOMSource(document); StreamResult result = new StreamResult(new File("products.xml")); // StreamResult result = new StreamResult(System.out); transformer.transform(source, result); } catch (IOException e) { System.out.println("IOException " + e.getMessage()); } catch (ParserConfigurationException e) { System.out.println("ParserConfigurationException " + e.getMessage()); } catch (TransformerConfigurationException e) { System.out.println("TransformerConfigurationException " + e.getMessage()); } catch (TransformerException e) { System.out.println("TransformerException " + e.getMessage()); } }
From source file:com.hrr3.services.DayStarFileImportService.java
License:Apache License
public ImportDayStarData parseFileToObjects(String fileName, int hotelId) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); ImportDayStarData dayStarData = null; //Container to save glance/response/summary SSRSnapshotDayStar glance = null; //To save glance data List<SSRSnapshotDayStarHotel> responses = null; //To save response data List<SSRSnapshotDayStarData> summaries = null; //To be implemented while reading excel in future version //1. Validate Glance SheetName at tab#2 if (wb.getNumberOfSheets() == 0 || wb.getSheet("Glance") == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Glance'"); //2. Validate Glance SheetName at tab#7 or tab#12 if (wb.getNumberOfSheets() == 0 || wb.getSheet("Response") == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Response'"); //3. Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet glanceSheet = wb.getSheet("Glance"); HSSFSheet responseSheet = wb.getSheet("Response"); //3.1 Instantiate main container dayStarData = new ImportDayStarData(); //4. Fill out glance Data glance = new SSRSnapshotDayStar(); glance.setHotelId(hotelId);/* www .j a v a2s. c o m*/ glance.setCapHotel(getStringCellValue(glanceSheet.getRow(1).getCell(1)));//2B glance.setCapHotel2(getStringCellValue(glanceSheet.getRow(2).getCell(1)));//3B glance.setDateFrom(null); glance.setDateTo(null); glance.setCapWeek(getStringCellValue(glanceSheet.getRow(3).getCell(1)));//4B System.out.println(glance); //4.1 Fill out Summary Data (part of the same Glance sheet) summaries = new ArrayList<SSRSnapshotDayStarData>(); for (int iIndex = 1; iIndex <= 16; iIndex++) //16 is equals SUN - SAT by two weeks in the Glance sheet { String sType = ""; String sDateCurrYr = ""; String sDateLastYr = ""; String sDateRange = ""; String sYearCurr = ""; String sYearLast = ""; String sOCCProp = ""; String sOCCPropPC = ""; String sOCCCompSet = ""; String sOCCCompSetPC = ""; String sOCCIndex = ""; String sOCCIndexPC = ""; String sARRProp = ""; String sARRPropPC = ""; String sARRCompSet = ""; String sARRCompSetPC = ""; String sARRIndex = ""; String sARRIndexPC = ""; String sRPProp = ""; String sRPPropPC = ""; String sRPCompSet = ""; String sRPCompSetPC = ""; String sRPIndex = ""; String sRPIndexPC = ""; int sCol = 0;//Index for left value int sColPC = 0;//Index for right value switch (iIndex) { case 1: case 9: sType = "SUN"; sCol = 4; sColPC = 5; break; case 2: case 10: sType = "MON"; sCol = 7; sColPC = 8; break; case 3: case 11: sType = "TUE"; sCol = 10; sColPC = 11; break; case 4: case 12: sType = "WED"; sCol = 13; sColPC = 14; break; case 5: case 13: sType = "THU"; sCol = 16; sColPC = 17; break; case 6: case 14: sType = "FRI"; sCol = 19; sColPC = 20; break; case 7: case 15: sType = "SAT"; sCol = 22; sColPC = 23; break; case 8: case 16: sType = "SUBTOT"; sCol = 25; sColPC = 26; break; } //Read Weekly section if (iIndex >= 1 && iIndex <= 8) { sType = "CURR" + sType; sDateRange = getStringCellValue(glanceSheet.getRow(5).getCell(1)).trim();//Read 6B sOCCProp = getStringCellValue(glanceSheet.getRow(9).getCell(sCol)).trim(); sOCCPropPC = getStringCellValue(glanceSheet.getRow(9).getCell(sColPC)).trim(); sOCCCompSet = getStringCellValue(glanceSheet.getRow(10).getCell(sCol)).trim(); sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(10).getCell(sColPC)).trim(); sOCCIndex = getStringCellValue(glanceSheet.getRow(11).getCell(sCol)).trim(); sOCCIndexPC = getStringCellValue(glanceSheet.getRow(11).getCell(sColPC)).trim(); sARRProp = getStringCellValue(glanceSheet.getRow(13).getCell(sCol)).trim(); sARRPropPC = getStringCellValue(glanceSheet.getRow(13).getCell(sColPC)).trim(); sARRCompSet = getStringCellValue(glanceSheet.getRow(14).getCell(sCol)).trim(); sARRCompSetPC = getStringCellValue(glanceSheet.getRow(14).getCell(sColPC)).trim(); sARRIndex = getStringCellValue(glanceSheet.getRow(15).getCell(sCol)).trim(); sARRIndexPC = getStringCellValue(glanceSheet.getRow(15).getCell(sColPC)).trim(); sRPProp = getStringCellValue(glanceSheet.getRow(17).getCell(sCol)).trim(); sRPPropPC = getStringCellValue(glanceSheet.getRow(17).getCell(sColPC)).trim(); sRPCompSet = getStringCellValue(glanceSheet.getRow(18).getCell(sCol)).trim(); sRPCompSetPC = getStringCellValue(glanceSheet.getRow(18).getCell(sColPC)).trim(); sRPIndex = getStringCellValue(glanceSheet.getRow(19).getCell(sCol)).trim(); sRPIndexPC = getStringCellValue(glanceSheet.getRow(19).getCell(sColPC)).trim(); } //Read Running section else if (iIndex > 8) { sType = "RUN" + sType; sDateRange = getStringCellValue(glanceSheet.getRow(22).getCell(1)).trim();//Read 23B sOCCProp = getStringCellValue(glanceSheet.getRow(26).getCell(sCol)).trim(); sOCCPropPC = getStringCellValue(glanceSheet.getRow(26).getCell(sColPC)).trim(); sOCCCompSet = getStringCellValue(glanceSheet.getRow(27).getCell(sCol)).trim(); sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(27).getCell(sColPC)).trim(); sOCCIndex = getStringCellValue(glanceSheet.getRow(28).getCell(sCol)).trim(); sOCCIndexPC = getStringCellValue(glanceSheet.getRow(28).getCell(sColPC)).trim(); sARRProp = getStringCellValue(glanceSheet.getRow(30).getCell(sCol)).trim(); sARRPropPC = getStringCellValue(glanceSheet.getRow(30).getCell(sColPC)).trim(); sARRCompSet = getStringCellValue(glanceSheet.getRow(31).getCell(sCol)).trim(); sARRCompSetPC = getStringCellValue(glanceSheet.getRow(31).getCell(sColPC)).trim(); sARRIndex = getStringCellValue(glanceSheet.getRow(32).getCell(sCol)).trim(); sARRIndexPC = getStringCellValue(glanceSheet.getRow(32).getCell(sColPC)).trim(); sRPProp = getStringCellValue(glanceSheet.getRow(34).getCell(sCol)).trim(); sRPPropPC = getStringCellValue(glanceSheet.getRow(34).getCell(sColPC)).trim(); sRPCompSet = getStringCellValue(glanceSheet.getRow(35).getCell(sCol)).trim(); sRPCompSetPC = getStringCellValue(glanceSheet.getRow(35).getCell(sColPC)).trim(); sRPIndex = getStringCellValue(glanceSheet.getRow(36).getCell(sCol)).trim(); sRPIndexPC = getStringCellValue(glanceSheet.getRow(36).getCell(sColPC)).trim(); } SSRSnapshotDayStarData summary = new SSRSnapshotDayStarData(); summary.setType(sType); summary.setTab(2); summary.setSequence(iIndex); summary.setDatethisyr(sDateCurrYr); summary.setDatelastyr(sDateLastYr); summary.setCurrentyr(sYearCurr); summary.setLastyr(sYearLast); summary.setDaterange(sDateRange); summary.setOccProp(sOCCProp); summary.setOccPropPc(sOCCPropPC); summary.setOccCompset(sOCCCompSet); summary.setOccCompsetPc(sOCCCompSetPC); summary.setOccIndex(sOCCIndex); summary.setOccIndexPc(sOCCIndexPC); summary.setArrProp(sARRProp); summary.setArrPropPc(sARRPropPC); summary.setArrCompset(sARRCompSet); summary.setArrCompsetPc(sARRCompSetPC); summary.setArrIndex(sARRIndex); summary.setArrIndexPc(sARRIndexPC); summary.setRevparProp(sRPProp); summary.setRevparPropPc(sRPPropPC); summary.setRevparCompset(sRPCompSet); summary.setRevparCompsetPc(sRPCompSetPC); summary.setRevparIndex(sRPIndex); summary.setRevparIndexPc(sRPIndexPC); System.out.println(summary); summaries.add(summary); } //5. Find STR ID value on C (index=2) column int responseTotalRows = responseSheet.getPhysicalNumberOfRows(); int strIDHeaderRow = -1; int currentRow = -1; int currentCol = -1; int strIDHeaderCol = 2; // Column C String strIDHeaderVal = "STR ID"; String strHotelName = "Name"; String columnNameForHotelName = "N/A"; for (int i = 0; i < responseTotalRows; i++) { String cellValue = getStringCellValue(responseSheet.getRow(i).getCell(2)); if (cellValue.equalsIgnoreCase(strIDHeaderVal)) { strIDHeaderRow = i; break; } } //Validate of Name is on column D or E if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(3)).equalsIgnoreCase(strHotelName)) columnNameForHotelName = "D"; else if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(4)).equalsIgnoreCase(strHotelName)) columnNameForHotelName = "E"; else throw new ApplicationException("Column for Hotel Name not found in row[" + strIDHeaderRow + "]"); //6. If STRID could not be found, throws an error if (strIDHeaderRow == -1) throw new ApplicationException("Response Sheet must contain STR ID Header in Column C."); //7. Once STR ID was found, we look for QRSTUVW columns at index-> strIDHeaderRow String sDate1 = responseSheet.getRow(strIDHeaderRow).getCell(16).toString(); String sDate2 = responseSheet.getRow(strIDHeaderRow).getCell(17).toString(); String sDate3 = responseSheet.getRow(strIDHeaderRow).getCell(18).toString(); String sDate4 = responseSheet.getRow(strIDHeaderRow).getCell(19).toString(); String sDate5 = responseSheet.getRow(strIDHeaderRow).getCell(20).toString(); String sDate6 = responseSheet.getRow(strIDHeaderRow).getCell(21).toString(); String sDate7 = responseSheet.getRow(strIDHeaderRow).getCell(22).toString(); //8. Move cursor to next row below to start reading Hotel compsets currentRow = ++strIDHeaderRow; currentCol = strIDHeaderCol; //9. Fill out response Data responses = new ArrayList<SSRSnapshotDayStarHotel>(); //9.1 Process response Rows while cell value is not 0 or empty while (currentRow < responseTotalRows && (!getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).equalsIgnoreCase("0") && !getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).isEmpty())) { SSRSnapshotDayStarHotel currentResponse = new SSRSnapshotDayStarHotel(); //Date Data currentResponse.setDate1(sDate1);//sDate1 currentResponse.setDate2(sDate2);//sDate2 currentResponse.setDate3(sDate3);//sDate3 currentResponse.setDate4(sDate4);//sDate4 currentResponse.setDate5(sDate5);//sDate5 currentResponse.setDate6(sDate6);//sDate6 currentResponse.setDate7(sDate7);//sDate7 //Str Data currentResponse.setStrId(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol = columnNameForHotelName.equalsIgnoreCase("D") ? currentCol + 1 : currentCol + 2;//Sum 1 if "Name" found in Column D, or Sum 2 if found in E currentResponse.setHotel(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//E currentResponse.setCity(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//F currentResponse.setZip(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//G currentResponse.setPhone(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//H currentResponse.setRooms(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//I currentResponse.setOpendate(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 7;//J //DayStar Data currentResponse .setDataDaystar1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Q currentResponse .setDataDaystar2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//R currentResponse .setDataDaystar3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//S currentResponse .setDataDaystar4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//T currentResponse .setDataDaystar5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//U currentResponse .setDataDaystar6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//V currentResponse .setDataDaystar7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 3;//W //Segmented Data currentResponse.setDataSeg1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Z currentResponse.setDataSeg2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AA currentResponse.setDataSeg3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AB currentResponse.setDataSeg4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AC currentResponse.setDataSeg5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AD currentResponse.setDataSeg6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AE currentResponse.setDataSeg7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 3;//AF //F&B Data currentResponse.setDataFb1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AI currentResponse.setDataFb2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AJ currentResponse.setDataFb3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AK currentResponse.setDataFb4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AL currentResponse.setDataFb5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AM currentResponse.setDataFb6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AN currentResponse.setDataFb7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AO System.out.println(currentResponse); responses.add(currentResponse); currentRow++; currentCol = strIDHeaderCol; } dayStarData.setGlance(glance); dayStarData.setSummaries(summaries); dayStarData.setResponse(responses); return dayStarData; }
From source file:com.hrr3.services.FileImportService.java
License:Apache License
public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRLRRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR")) throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'"); //2. Validate row 3 (index 2) contains at least 14 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14) throw new ApplicationException( "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet lrrSheet = wb.getSheetAt(0); int totalRows = lrrSheet.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate int dateColumnIndex = 1; //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }//from w w w . j a v a 2 s . com //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRLRRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String statdate = dateFormat.format(statdateCol); String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3)); String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4)); String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5)); String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6)); String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7)); String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8)); String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9)); String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10)); String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11)); String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12)); String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13)); //Fill rowData object ImportSSRLRRData rowData = new ImportSSRLRRData(); rowData.setStatdate(statdate); rowData.setLrr1(new BigDecimal(lrr1)); rowData.setLrr2(new BigDecimal(lrr2)); rowData.setLrr3(new BigDecimal(lrr3)); rowData.setLrr4(new BigDecimal(lrr4)); rowData.setLrr5(new BigDecimal(lrr5)); rowData.setLrr6(new BigDecimal(lrr6)); rowData.setLrr7(new BigDecimal(lrr7)); rowData.setLrr8(new BigDecimal(lrr8)); rowData.setLrr9(new BigDecimal(lrr9)); rowData.setLrrHp1(new BigDecimal(hp1)); rowData.setLrrHp2(new BigDecimal(hp2)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.hrr3.services.LRRFileImportService.java
License:Apache License
public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRLRRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR")) throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'"); //2. Validate row 3 (index 2) contains at least 14 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14) throw new ApplicationException( "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet lrrSheet = wb.getSheetAt(0); int totalRows = lrrSheet.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate int dateColumnIndex = 1; //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }//from ww w .j a v a 2s . co m //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); if (startDateIndex == -1 || endDateIndex == -1) throw new ApplicationException( "Sheet does not contain data for the date range. Please provide a valid one."); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRLRRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String statdate = dateFormat.format(statdateCol); String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3)); String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4)); String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5)); String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6)); String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7)); String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8)); String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9)); String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10)); String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11)); String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12)); String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13)); //Fill rowData object ImportSSRLRRData rowData = new ImportSSRLRRData(); rowData.setStatdate(statdate); rowData.setLrr1(new BigDecimal(lrr1)); rowData.setLrr2(new BigDecimal(lrr2)); rowData.setLrr3(new BigDecimal(lrr3)); rowData.setLrr4(new BigDecimal(lrr4)); rowData.setLrr5(new BigDecimal(lrr5)); rowData.setLrr6(new BigDecimal(lrr6)); rowData.setLrr7(new BigDecimal(lrr7)); rowData.setLrr8(new BigDecimal(lrr8)); rowData.setLrr9(new BigDecimal(lrr9)); rowData.setLrrHp1(new BigDecimal(hp1)); rowData.setLrrHp2(new BigDecimal(hp2)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.hrr3.services.SSRFileImportService.java
License:Apache License
public List<ImportSSRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("SellStrat")) throw new ApplicationException("Excel file must contain a valid sheet called 'SellStrat'"); //2. Validate row 3 (index 2) contains at least 22 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 22) throw new ApplicationException( "'SellStrat sheet must contains at least 22 columns at Row#3 to identify SSR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet sellStrat = wb.getSheetAt(0); int totalRows = sellStrat.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = sellStrat.getRow(rowIndex).getCell(2); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }/*w w w. ja v a 2s . c o m*/ //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); if (startDateIndex == -1 || endDateIndex == -1) throw new ApplicationException( "Sheet does not contain data for the date range. Please provide a valid one."); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = sellStrat.getRow(i).getCell(2); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String comments = sellStrat.getRow(i).getCell(0) != null ? sellStrat.getRow(i).getCell(0).getStringCellValue() : ""; String statdate = dateFormat.format(statdateCol); Calendar c = Calendar.getInstance(); try { c.setTime(dateFormat.parse(statdate)); } catch (ParseException e) { // TODO Auto-generated catch block e.printStackTrace(); } String[] strDays = new String[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thusday", "Friday", "Saturday" }; String down = strDays[c.get(Calendar.DAY_OF_WEEK) - 1]; //sellStrat.getRow(i).getCell(1).getStringCellValue(); String a1 = sellStrat.getRow(i).getCell(3).getStringCellValue(); String b2 = sellStrat.getRow(i).getCell(4).getStringCellValue(); String c3 = sellStrat.getRow(i).getCell(5).getStringCellValue(); String d4 = sellStrat.getRow(i).getCell(6).getStringCellValue(); String e5 = sellStrat.getRow(i).getCell(7).getStringCellValue(); String f6 = sellStrat.getRow(i).getCell(8).getStringCellValue(); String g7 = sellStrat.getRow(i).getCell(9).getStringCellValue(); String h8 = sellStrat.getRow(i).getCell(10).getStringCellValue(); String i9 = sellStrat.getRow(i).getCell(11).getStringCellValue(); String hp1 = getStringCellValue(sellStrat.getRow(i).getCell(12)); String hp2 = getStringCellValue(sellStrat.getRow(i).getCell(13)); String ssrTransient = getStringCellValue(sellStrat.getRow(i).getCell(14)); String ssrGrpblock = getStringCellValue(sellStrat.getRow(i).getCell(15)); String ssrContract = getStringCellValue(sellStrat.getRow(i).getCell(16)); String ssrGrppu = getStringCellValue(sellStrat.getRow(i).getCell(18)); String ssrGrprem = getStringCellValue(sellStrat.getRow(i).getCell(19)); String ssrDemandtd = getStringCellValue(sellStrat.getRow(i).getCell(20)); String ssrPricetd = getStringCellValue(sellStrat.getRow(i).getCell(21)); //Fill rowData object ImportSSRData rowData = new ImportSSRData(); rowData.setComment(comments); rowData.setDow(down); rowData.setStatdate(statdate); rowData.setA1(a1); rowData.setB2(b2); rowData.setC3(c3); rowData.setD4(d4); rowData.setE5(e5); rowData.setF6(f6); rowData.setG7(g7); rowData.setH8(h8); rowData.setI9(i9); rowData.setHp1(hp1); rowData.setHp2(hp2); rowData.setSsrTransient(new BigDecimal(ssrTransient)); rowData.setSsrGrpblock(new BigDecimal(ssrGrpblock)); rowData.setSsrContract(new BigDecimal(ssrContract)); rowData.setSsrGrppu(new BigDecimal(ssrGrppu)); rowData.setSsrGrprem(new BigDecimal(ssrGrprem)); rowData.setSsrDemandtd(new BigDecimal(ssrDemandtd)); rowData.setSsrPricetd(new BigDecimal(ssrPricetd)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.hrr3.services.StarFileImportService.java
License:Apache License
public List<SSRSnapshotStarData> parseFileToObjects(String fileName, int hotelId) throws ApplicationException, IOException { String sTemp;//from www . ja v a 2s . com String sDayFrom; String sDayTo; String iPos; String sMonth; String sYear = ""; String sTract; String sTractScale; String sOCCProp; String sOCCPropPC; String sOCCCompSet; String sOCCCompSetPC; String sOCCIndex; String sOCCIndexPC; String sARRProp; String sARRPropPC; String sARRCompSet; String sARRCompSetPC; String sARRIndex; String sARRIndexPC; String sRPProp; String sRPPropPC; String sRPCompSet; String sRPCompSetPC; String sRPIndex; String sRPIndexPC; String sMktShSupply = ""; String sMktShDemand = ""; String sMktShRev = ""; String sType = ""; String sCapHotel; String sCapWeek; int nDayStarID; boolean bSkip; int iWSColIndex; int iGroup; boolean bIsSummary; String sMonthSummary = ""; String strCol; final String SSR_WRKSHT = "Comp"; final String SSR_WRKSHTSUM = "Summary"; final int COL_START = 2; final int COL_END = 31; final String REPLACE_TRACT = "Tract: "; final String REPLACE_TRACT_SCALE = "Tract Scale: "; String sOutput = ""; SSRSnapshotStarData dayData; List<SSRSnapshotStarData> dayDataList = new ArrayList<SSRSnapshotStarData>(); //Day data HSSFWorkbook wb = this.readFile(fileName); //1. Validate Summary SheetName if (wb.getNumberOfSheets() == 0 || wb.getSheet(SSR_WRKSHTSUM) == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Summary'"); //3. Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet summarySheet = wb.getSheet(SSR_WRKSHTSUM); HSSFSheet compSheet = wb.getSheet(SSR_WRKSHT); HSSFSheet currentSheet = compSheet; //3.1 Instantiate main container dayData = new SSRSnapshotStarData(); //4. Fill out Comp Data sCapHotel = getStringCellValue(compSheet.getRow(1).getCell(1)).trim().split(" ")[0]; sCapWeek = "STAR Trend Report - " + formatMonth( getStringCellValue(compSheet.getRow(3).getCell(1)).trim().split(" ")[0].split(":")[1]); //Define global vars final int ROW_BASE_INDEX = 1; final int ROW_OCC_TRACT = 11 - ROW_BASE_INDEX; final int ROW_OCC_TRACTSCALE = 12 - ROW_BASE_INDEX; final int ROW_ADR_TRACT = 21 - ROW_BASE_INDEX; final int ROW_ADR_TRACTSCALE = 22 - ROW_BASE_INDEX; final int ROW_REVPAR_TRACT = 31 - ROW_BASE_INDEX; final int ROW_REVPAR_TRACTSCALE = 32 - ROW_BASE_INDEX; final int ROW_OCC_MY_PROP = 21 - ROW_BASE_INDEX; final int ROW_OCC_MY_PROP_PER_CHG = 26 - ROW_BASE_INDEX; final int ROW_OCC_COMP_SET = 22 - ROW_BASE_INDEX; final int ROW_OCC_COMP_SET_PER_CHG = 27 - ROW_BASE_INDEX; final int ROW_OCC_INDEX = 23 - ROW_BASE_INDEX; final int ROW_OCC_INDEX_PER_CHG = 28 - ROW_BASE_INDEX; final int ROW_ADR_MY_PROP = 33 - ROW_BASE_INDEX; final int ROW_ADR_MY_PROP_PER_CHG = 38 - ROW_BASE_INDEX; final int ROW_ADR_COMP_SET = 34 - ROW_BASE_INDEX; final int ROW_ADR_COMP_SET_PER_CHG = 39 - ROW_BASE_INDEX; final int ROW_ADR_INDEX = 35 - ROW_BASE_INDEX; final int ROW_ADR_INDEX_PER_CHG = 40 - ROW_BASE_INDEX; final int ROW_REVPAR_MY_PROP = 45 - ROW_BASE_INDEX; final int ROW_REVPAR_MY_PROP_PER_CHG = 50 - ROW_BASE_INDEX; final int ROW_REVPAR_COMP_SET = 46 - ROW_BASE_INDEX; final int ROW_REVPAR_COMP_SET_PER_CHG = 51 - ROW_BASE_INDEX; final int ROW_REVPAR_INDEX = 47 - ROW_BASE_INDEX; final int ROW_REVPAR_INDEX_PER_CHG = 52 - ROW_BASE_INDEX; //5. Fill out Summary Data for (iWSColIndex = COL_START; iWSColIndex <= COL_END + 6; iWSColIndex++) { //Initialize bSkip = false; sMonth = ""; //sType = "" sTract = ""; sTractScale = ""; iGroup = 0; bIsSummary = false; sOCCProp = ""; sOCCPropPC = ""; sOCCCompSet = ""; sOCCCompSetPC = ""; sOCCIndex = ""; sOCCIndexPC = ""; sARRProp = ""; sARRPropPC = ""; sARRCompSet = ""; sARRCompSetPC = ""; sARRIndex = ""; sARRIndexPC = ""; sRPProp = ""; sRPPropPC = ""; sRPCompSet = ""; sRPCompSetPC = ""; sRPIndex = ""; sRPIndexPC = ""; strCol = CellReference.convertNumToColString(iWSColIndex); //System.out.println("StrCol: " + strCol); if (iWSColIndex == COL_END + 1) {//Use summary worksheet //2. Validate Comp SheetName if (wb.getNumberOfSheets() == 0 || wb.getSheet(SSR_WRKSHT) == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Comp'"); //Set symmarySheet currentSheet = summarySheet; } if (iWSColIndex <= COL_END) { bIsSummary = false; if (isBetween(iWSColIndex, 3 - ROW_BASE_INDEX, 20 - ROW_BASE_INDEX)) { sType = ""; sMonth = formatMonth(getStringCellValue(currentSheet.getRow(20 - ROW_BASE_INDEX) .getCell(CellReference.convertColStringToIndex(strCol)))); iGroup = 1; } if (isBetween(iWSColIndex, 26 - ROW_BASE_INDEX, 28 - ROW_BASE_INDEX)) { iGroup = 2; } if (isBetween(iWSColIndex, 22 - ROW_BASE_INDEX, 24 - ROW_BASE_INDEX)) { iGroup = 3; } if (isBetween(iWSColIndex, 30 - ROW_BASE_INDEX, 32 - ROW_BASE_INDEX)) { iGroup = 4; } if (iWSColIndex == 20 - ROW_BASE_INDEX) { sMonthSummary = sMonth; } if (iWSColIndex != (21 - ROW_BASE_INDEX) && iWSColIndex != (25 - ROW_BASE_INDEX) && iWSColIndex != (29 - ROW_BASE_INDEX)) { if (isBetween(iWSColIndex, 3 - ROW_BASE_INDEX, 20 - ROW_BASE_INDEX)) { sYear = findYear(currentSheet, iWSColIndex); //sYear = getStringCellValue(currentSheet.getRow(19 - ROW_BASE_INDEX).getCell(CellReference.convertColStringToIndex(strCol))).trim(); } if (isBetween(iWSColIndex, 22 - ROW_BASE_INDEX, 32 - ROW_BASE_INDEX)) { sYear = getStringCellValue(currentSheet.getRow(20 - ROW_BASE_INDEX) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); } sOCCProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_MY_PROP) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sOCCPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_MY_PROP_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_COMP_SET) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_COMP_SET_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sOCCIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_INDEX) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sOCCIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_INDEX_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_MY_PROP) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_MY_PROP_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_COMP_SET) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_COMP_SET_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_INDEX) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sARRIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_INDEX_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_MY_PROP) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_MY_PROP_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_COMP_SET) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_COMP_SET_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_INDEX) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); sRPIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_INDEX_PER_CHG) .getCell(CellReference.convertColStringToIndex(strCol))).trim(); } else //Heading rows { bSkip = true; switch (iWSColIndex) { case 20: sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)), sMonthSummary); break; case 24: sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)), sMonthSummary); break; case 28: sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)), sMonthSummary); break; } } } //Summary Worksheet else { bIsSummary = true; switch (iWSColIndex) { case COL_END + 1: //current month sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 1; sType = "SummaryTract"; sMonth = sMonthSummary; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("E"))) .trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("F"))) .trim(); sARRCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("G"))) .trim(); sARRCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("H"))) .trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("H"))).trim(); break; case COL_END + 2: sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 1; sType = "SummaryTractScale"; sMonth = sMonthSummary; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("E"))).trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("F"))).trim(); sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("H"))).trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("H"))).trim(); break; case COL_END + 3: sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 3; sType = "SummaryTract"; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("G"))) .trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("H"))) .trim(); sARRCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("G"))) .trim(); sARRCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("H"))) .trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("H"))).trim(); break; case COL_END + 4: sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 3; sType = "SummaryTractScale"; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("H"))).trim(); sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("H"))).trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("G"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("H"))).trim(); break; case COL_END + 5: sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 4; sType = "SummaryTract"; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("K"))) .trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("L"))) .trim(); sARRCompSet = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("K"))) .trim(); sARRCompSetPC = getNumberAsStringInExcelFormat( currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("L"))) .trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("K"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT) .getCell(CellReference.convertColStringToIndex("L"))).trim(); break; case COL_END + 6: sTract = getStringCellValue( currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B"))) .trim().replaceAll(REPLACE_TRACT, ""); sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("B"))).trim() .replaceAll(REPLACE_TRACT_SCALE, ""); iGroup = 4; sType = "SummaryTractScale"; //set OC, ARR, RPC - TODO roundNumber sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("K"))).trim(); sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("L"))).trim(); sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("K"))).trim(); sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("L"))).trim(); sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("K"))).trim(); sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE) .getCell(CellReference.convertColStringToIndex("L"))).trim(); break; } } //Else if (iGroup > 0) { dayData = new SSRSnapshotStarData(); dayData.setHotelId(hotelId); dayData.setType(sType); dayData.setGroup(iGroup); dayData.setSequence(iWSColIndex + 5 + ROW_BASE_INDEX); dayData.setStarYear(Double.valueOf(sYear).intValue()); dayData.setStarMonth(sMonth); dayData.setHotelCaption(sCapHotel); dayData.setMonthCaption(sCapWeek); dayData.setTract(sTract); dayData.setTractScale(sTractScale); //OCC dayData.setOccProp(sOCCProp); dayData.setOccPropPc(sOCCPropPC); dayData.setOccCompset(sOCCCompSet); dayData.setOccCompsetPc(sOCCCompSetPC); dayData.setOccIndex(sOCCIndex); dayData.setOccIndexPc(sOCCIndexPC); //ARR dayData.setArrProp(sARRProp); dayData.setArrPropPc(sARRPropPC); dayData.setArrCompset(sARRCompSet); dayData.setArrCompsetPc(sARRCompSetPC); dayData.setArrIndex(sARRIndex); dayData.setArrIndexPc(sARRIndexPC); //REVPAR dayData.setRevparProp(sRPProp); dayData.setRevparPropPc(sRPPropPC); dayData.setRevparCompset(sRPCompSet); dayData.setRevparCompsetPc(sRPCompSetPC); dayData.setRevparIndex(sRPIndex); dayData.setRevparIndexPc(sRPIndexPC); //MKTSH dayData.setMktshSupply(sMktShSupply); dayData.setMktshDemand(sMktShDemand); dayData.setMktshRev(sMktShRev); System.out.println(dayData); dayDataList.add(dayData); } } //For return dayDataList; }