List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java
License:Apache License
public static HSSFSheet getSheet(HSSFWorkbook wb, String name) { HSSFSheet sheet = name != null ? wb.getSheet(name) : wb.getSheetAt(0); if (sheet == null) { String s = name != null ? "name '" + name + "'" : ""; throw new IllegalArgumentException("No worksheet " + s + "found in spreadsheet"); }//from ww w .j a v a2s . c o m return sheet; }
From source file:com.diversityarrays.kdxplore.importdata.bms.BmsExcelImportHelper.java
License:Open Source License
public BmsExcelImportHelper(File excelFile) throws IOException { this.excelFile = excelFile; if (!excelFile.getName().toLowerCase().endsWith(".xls")) { //$NON-NLS-1$ throw new IllegalArgumentException("Only .xls files supported"); }//from ww w .j av a2 s. c om POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile)); HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(fs); try { List<String> missing = new ArrayList<>(); description = workbook.getSheet(SHEET_NAME_DESCRIPTION); if (null == description) { missing.add(SHEET_NAME_DESCRIPTION); } observation = workbook.getSheet(SHEET_NAME_OBSERVATION); if (null == observation) { missing.add(SHEET_NAME_OBSERVATION); } if (!missing.isEmpty()) { throw new IOException(StringUtil.join("Missing required worksheet(s): ", ",", missing)); } } finally { if (workbook != null) { try { workbook.close(); } catch (IOException ignore) { } } } }
From source file:com.dv.util.DVExcelIO.java
License:Open Source License
public static Vector<Vector> readExcelReturnArrayList(String fileName, String sheetName, int rowCount) { Vector<Vector> hm = new Vector<Vector>(); File file = new File(fileName); FileInputStream in = null;/* w w w . ja va 2 s . c o m*/ try { in = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(in); HSSFSheet sheet = workbook.getSheet(sheetName); HSSFRow row = null; HSSFCell cell = null; for (int i = 0; i < rowCount; i = i + 3) { Vector cellList = new Vector(); row = sheet.getRow((short) i); if (row != null) { cell = row.getCell(0); String cellString = cell.toString().replace(".0", " ").trim(); cellString = cellString.replace("\n", " ").trim(); cellList.add(0, cellString); } row = sheet.getRow((short) i + 1); if (row != null) { cell = row.getCell(0); String cellString = cell.toString().replace(".0", " ").trim(); cellString = cellString.replace("\n", " ").trim(); cellList.add(1, cellString); } row = sheet.getRow((short) i + 2); if (row != null) { cell = row.getCell(0); String cellString = cell.toString().replace(".0", " ").trim(); cellString = cellString.replace("\n", " ").trim(); cellList.add(2, cellString); } hm.addElement(cellList); } } catch (Exception e) { e.printStackTrace(); } finally { if (in != null) { try { in.close(); } catch (IOException e1) { } } } return hm; }
From source file:com.dv.util.DVExcelIO.java
License:Open Source License
public static Vector setExcelBHTIMFormat(String fullExcelFileName, String sheetName, int rowNumbers) { File file = new File(fullExcelFileName); FileInputStream in = null;// w w w .j a v a2 s . c o m Vector cols = new Vector(); try { in = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(in); HSSFSheet sheet = workbook.getSheet(sheetName); HSSFRow row = null; HSSFCell cell = null; for (int i = 2; i < rowNumbers; i++) { row = sheet.getRow(i); cell = row.getCell(4);//9 for cty String ppp = cell.toString().trim(); cell = row.getCell(9); String fff = cell.toString().trim(); if (!ppp.equals("")) { String contents = "Verify from FMS side for " + ppp + "(" + fff + ")"; // cols.addElement(contents); row.getCell(16).setCellValue(contents); } else { return null; } } FileOutputStream fOut = new FileOutputStream(file); workbook.write(fOut); fOut.flush(); fOut.close(); } catch (Exception eee) { } return cols; }
From source file:com.example.selenium.util.ApachePOIXLSReader.java
public static Object[][] excelRead(String filePath) throws Exception { File excel = new File(filePath); FileInputStream fis = new FileInputStream(excel); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet ws = wb.getSheet("Sheet1"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); Object[][] data = new Object[(rowNum - 1)][colNum]; int k = 0;/* w w w. j a v a 2 s . c o m*/ for (int i = 1; i < rowNum; i++) { HSSFRow row = ws.getRow(i); for (int j = 0; j < colNum; j++) { HSSFCell cell = row.getCell(j); Object value = cellToObject(cell); data[k][j] = value; } k++; } return data; }
From source file:com.framework.common.ExcelSpreadsheet.java
public ExcelSpreadsheet(ExcelFile excelConnectionDetails) throws FileNotFoundException, IOException { String current = System.getProperty("user.dir"); this.filePathAndName = current + "/" + excelConnectionDetails.getFilename(); FileInputStream fileInputStream = new FileInputStream(excelConnectionDetails.getFilename()); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet(excelConnectionDetails.getSheetname()); this.requiredWorksheet = worksheet; }
From source file:com.haulmont.yarg.formatters.impl.xls.HSSFRangeHelper.java
License:Apache License
public static HSSFSheet getTemplateSheetForRangeName(HSSFWorkbook workbook, String rangeName) { int rangeNameIdx = workbook.getNameIndex(rangeName); if (rangeNameIdx == -1) return null; HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx); String sheetName = aNamedRange.getSheetName(); return workbook.getSheet(sheetName); }
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);/*from w ww. j ava2 s. com*/ 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.StarFileImportService.java
License:Apache License
public List<SSRSnapshotStarData> parseFileToObjects(String fileName, int hotelId) throws ApplicationException, IOException { String sTemp;/* www . j a v a 2s .c om*/ 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; }
From source file:com.msopentech.odatajclient.engine.performance.PerfTestReporter.java
License:Open Source License
public static void main(final String[] args) throws Exception { // 1. check input directory final File reportdir = new File(args[0] + File.separator + "target" + File.separator + "surefire-reports"); if (!reportdir.isDirectory()) { throw new IllegalArgumentException("Expected directory, got " + args[0]); }/* w ww . j av a2 s .co m*/ // 2. read test data from surefire output final File[] xmlReports = reportdir.listFiles(new FilenameFilter() { @Override public boolean accept(final File dir, final String name) { return name.endsWith("-output.txt"); } }); final Map<String, Map<String, Double>> testData = new TreeMap<String, Map<String, Double>>(); for (File xmlReport : xmlReports) { final BufferedReader reportReader = new BufferedReader(new FileReader(xmlReport)); try { while (reportReader.ready()) { String line = reportReader.readLine(); final String[] parts = line.substring(0, line.indexOf(':')).split("\\."); final String testClass = parts[0]; if (!testData.containsKey(testClass)) { testData.put(testClass, new TreeMap<String, Double>()); } line = reportReader.readLine(); testData.get(testClass).put(parts[1], Double.valueOf(line.substring(line.indexOf(':') + 2, line.indexOf('[')))); } } finally { IOUtils.closeQuietly(reportReader); } } // 3. build XSLX output (from template) final HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(args[0] + File.separator + "src" + File.separator + "test" + File.separator + "resources" + File.separator + XLS)); for (Map.Entry<String, Map<String, Double>> entry : testData.entrySet()) { final Sheet sheet = workbook.getSheet(entry.getKey()); int rows = 0; for (Map.Entry<String, Double> subentry : entry.getValue().entrySet()) { final Row row = sheet.createRow(rows++); Cell cell = row.createCell(0); cell.setCellValue(subentry.getKey()); cell = row.createCell(1); cell.setCellValue(subentry.getValue()); } } final FileOutputStream out = new FileOutputStream( args[0] + File.separator + "target" + File.separator + XLS); try { workbook.write(out); } finally { IOUtils.closeQuietly(out); } }