List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:in.expertsoftware.colorcheck.Operation_Standard_WorkingSection_count.java
public ArrayList<String> working_Section(int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) { ArrayList<String> workingSection = new ArrayList<String>(); XSSFRow row;/*from w ww . j a va2 s .co m*/ XSSFCell cell; XSSFSheet Sheet = workbook.getSheet("Operation_Standard"); String genrateFormula; for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(2); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1); workingSection.add(genrateFormula); break; case Cell.CELL_TYPE_BLANK: break; default: System.out.println("Error"); break; } } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); } } return workingSection; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private boolean operation_Standard_Workingsection_Verification(int SORQ_tokenLocation, int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {/*from www.jav a 2 s . c om*/ int reporting_Qtr_OS_Working_SectionCount = 0; String reporting_Qtr_Formula_Cell_Formula; String reporting_Qtr_Formula_Cell_Value; boolean isError = false; XSSFRow row; XSSFCell cell; for (int start = (SORQ_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Qtr_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) { start = start + 1; } else if (reporting_Qtr_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = opeartion_standard_workingSectionList .get(reporting_Qtr_OS_Working_SectionCount); if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Qtr_OS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_OS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Cell does not contain formula"); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_OS_Working_SectionCount++; isError = true; } break; } } else { reporting_Qtr_OS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Qtr_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Reporting_Qtr have " + Math.abs(reporting_Qtr_OS_Working_SectionCount - opeartion_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision, int EORQ_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) { int reporting_Qtr_FS_Working_SectionCount = 0; String reporting_Qtr_Formula_Cell_Formula = null; String reporting_Qtr_Formula_Cell_Value; XSSFRow row;//from w ww . j a va2s .com XSSFCell cell; boolean isError = false; for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORQ_tokenLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Qtr_Formula_Cell_Value.equals("Financial Comparison")) { start = start + 3; } else if (reporting_Qtr_FS_Working_SectionCount < financial_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = financial_standard_workingSectionList .get(reporting_Qtr_FS_Working_SectionCount); if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Qtr_FS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_FS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Cell does not contain formula "); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_FS_Working_SectionCount++; isError = true; } break; } } else { reporting_Qtr_FS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Qtr_FS_Working_SectionCount != financial_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Reporting_Qtr have " + Math.abs(reporting_Qtr_FS_Working_SectionCount - financial_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private void reporting_Qtr_operation_Standard_C_To_I_Column_Verification(int start_Point, int first_Occurance_Of_Financial_Comparision, ArrayList<ErrorModel> errorModelList, XSSFSheet Sheet, XSSFWorkbook workbook) {//from www .ja va 2 s. c om String reporting_Qtr_Formula_Cell_Formula = null; String reporting_Qtr_Formula_Cell_Value; String reporting_Qtr_B_Column_Formula = null; XSSFRow row; XSSFCell cell_B, cell_C, cell_E = null, cell_F = null, cell_G = null, cell_H = null, cell_I = null; for (int start = (start_Point - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell_B = row.getCell(1); cell_C = row.getCell(2); cell_E = row.getCell(4); cell_F = row.getCell(5); cell_G = row.getCell(6); cell_H = row.getCell(7); cell_I = row.getCell(8); switch (cell_C.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell_C.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } //verify the formula is correct or not. if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA) && (reporting_Qtr_Formula_Cell_Formula.charAt(19) == 'D')) { if (!(cell_B.getCellFormula().substring(20, cell_B.getCellFormula().length()) .equals(reporting_Qtr_Formula_Cell_Formula.substring(20, reporting_Qtr_Formula_Cell_Formula.length())))) { genrateError(cell_C, errorModelList, "Operation_Standard"); } } //else throw an error else { genrateError(cell_C, errorModelList, "Operation_Standard"); } verify_E(cell_E, cell_B, errorModelList); verify_F(cell_F, cell_B, errorModelList); verify_G(cell_G, cell_B, errorModelList); verify_H(cell_H, cell_B, errorModelList); verify_I(cell_I, cell_B, errorModelList); break; case Cell.CELL_TYPE_STRING: reporting_Qtr_Formula_Cell_Value = cell_C.getStringCellValue(); if (reporting_Qtr_Formula_Cell_Value.contains("USD")) { } else if (reporting_Qtr_Formula_Cell_Value.equalsIgnoreCase("Unit")) { start = start + 4; } else { genrateError(cell_C, errorModelList, "Operation_Standard"); } break; case Cell.CELL_TYPE_BLANK: break; default: genrateError(cell_C, errorModelList, "Operation_Standard"); break; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private boolean operation_Standard_Workingsection_Verification(int SORY_tokenLocation, int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {//from w w w .ja v a2s.c o m int reporting_Year_OS_Working_SectionCount = 0; String reporting_Year_Formula_Cell_Formula; String reporting_Year_Formula_Cell_Value; boolean isError = false; XSSFRow row; XSSFCell cell; for (int start = (SORY_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Year_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) { start = start + 1; } else if (reporting_Year_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Year_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Year_Formula_Cell_Formula.contains("$")) { reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = opeartion_standard_workingSectionList .get(reporting_Year_OS_Working_SectionCount); if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Year_OS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_OS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Cell does not contain formula"); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_OS_Working_SectionCount++; isError = true; } break; } } else { reporting_Year_OS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Year_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Reporting_Year have " + Math.abs( reporting_Year_OS_Working_SectionCount - opeartion_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision, int EORY_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) { int reporting_Year_FS_Working_SectionCount = 0; String reporting_Year_Formula_Cell_Formula = null; String reporting_Year_Formula_Cell_Value; XSSFRow row;//from w ww .j a va 2s.co m XSSFCell cell; boolean isError = false; for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORY_tokenLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Year_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Financial Comparison")) { start = start + 3; } else if (reporting_Year_FS_Working_SectionCount < financial_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Year_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Year_Formula_Cell_Formula.contains("$")) { reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = financial_standard_workingSectionList .get(reporting_Year_FS_Working_SectionCount); if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Year_FS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_FS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Cell does not contain formula "); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_FS_Working_SectionCount++; isError = true; } break; } } else { reporting_Year_FS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Year_FS_Working_SectionCount != financial_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Reporting_Year have " + Math.abs( reporting_Year_FS_Working_SectionCount - financial_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private void reporting_Year_operation_Standard_C_To_I_Column_Verification(int start_Point, int first_Occurance_Of_Financial_Comparision, ArrayList<ErrorModel> errorModelList, XSSFSheet Sheet, XSSFWorkbook workbook) {/*from w w w . jav a2 s . c om*/ String reporting_Year_Formula_Cell_Formula = null; String reporting_Year_Formula_Cell_Value; String reporting_Year_B_Column_Formula = null; XSSFRow row; XSSFCell cell_B, cell_C, cell_E = null, cell_F = null, cell_G = null, cell_H = null, cell_I = null; for (int start = (start_Point - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell_B = row.getCell(1); cell_C = row.getCell(2); cell_E = row.getCell(4); cell_F = row.getCell(5); cell_G = row.getCell(6); cell_H = row.getCell(7); cell_I = row.getCell(8); switch (cell_C.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Year_Formula_Cell_Formula = cell_C.getCellFormula(); if (reporting_Year_Formula_Cell_Formula.contains("$")) { reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } //verify the formula is correct or not. if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA) && (reporting_Year_Formula_Cell_Formula.charAt(19) == 'D')) { if (!(cell_B.getCellFormula().substring(20, cell_B.getCellFormula().length()) .equals(reporting_Year_Formula_Cell_Formula.substring(20, reporting_Year_Formula_Cell_Formula.length())))) { genrateError(cell_C, errorModelList, "Operation_Standard"); } } //else throw an error else { genrateError(cell_C, errorModelList, "Operation_Standard"); } verify_E(cell_E, cell_B, errorModelList); verify_F(cell_F, cell_B, errorModelList); verify_G(cell_G, cell_B, errorModelList); verify_H(cell_H, cell_B, errorModelList); verify_I(cell_I, cell_B, errorModelList); break; case Cell.CELL_TYPE_STRING: reporting_Year_Formula_Cell_Value = cell_C.getStringCellValue(); if (reporting_Year_Formula_Cell_Value.contains("USD")) { } else if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Unit")) { start = start + 4; } else { genrateError(cell_C, errorModelList, "Operation_Standard"); } break; case Cell.CELL_TYPE_BLANK: break; default: genrateError(cell_C, errorModelList, "Operation_Standard"); break; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } }
From source file:io.unravellingtechnologies.excalibur.Sheet.java
License:Open Source License
/** * Initializes the sheet header structure. * //from ww w.j a va2s .c om * @param sheet Sheet POI object used to initialize the header of this sheet. */ private void setSheetHeader(XSSFSheet sheet) { if (logger.isDebugEnabled()) { logger.debug("Setting sheet header..."); } org.apache.poi.ss.usermodel.Row firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow.getPhysicalNumberOfCells() == 0) { return; } for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); sheetHeader.put(cell.getColumnIndex(), cell.getStringCellValue()); } if (logger.isDebugEnabled()) { logger.debug("Finished setting the sheet header."); } }
From source file:listfiles.ListFiles.java
private static Cell checkRowCellExists(XSSFSheet currentSheet, int rowIndex, int colIndex) { Row currentRow = currentSheet.getRow(rowIndex); if (currentRow == null) { currentRow = currentSheet.createRow(rowIndex); }/*from ww w . jav a 2 s . c o m*/ //Check if cell exists Cell currentCell = currentRow.getCell(colIndex); if (currentCell == null) { currentCell = currentRow.createCell(colIndex); } return currentCell; }
From source file:localization.checkURL.java
public static void check(String desktopFile, String serverFile, String inputFolder) { try {/*from ww w. j a va 2 s .co m*/ String desktopFolder = desktopFile.substring(desktopFile.lastIndexOf("\\") + 1, desktopFile.length()); desktop = desktopFolder.substring(0, desktopFolder.indexOf("_")); String serverFolder = serverFile.substring(serverFile.lastIndexOf("\\") + 1, serverFile.length()); server = serverFolder.substring(0, serverFolder.indexOf("_")); pubList = new ArrayList<>(); searchFile(inputFolder); String parFolder = inputFolder.substring(0, inputFolder.lastIndexOf("\\")); HOName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.lastIndexOf("\\") + 5); lang = inputFolder.substring(inputFolder.lastIndexOf("\\") + 1, inputFolder.length()); outputFileName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.length()); outputFilePath = parFolder + "\\" + outputFileName + "_" + lang + ".xlsx"; valueArray = new String[pubList.size() + 1][6]; valueArray[0][0] = "Language"; valueArray[0][1] = "HO#"; valueArray[0][2] = "Publication Name"; valueArray[0][3] = "Type"; valueArray[0][4] = "Topic Name"; valueArray[0][5] = "URL"; for (int i = 0; i < pubList.size(); i++) { String fullPath = pubList.get(i); valueArray[i + 1][0] = lang.toUpperCase().trim(); valueArray[i + 1][1] = HOName.trim(); valueArray[i + 1][4] = fullPath.substring(fullPath.lastIndexOf("\\") + 1, fullPath.length()); if (fullPath.contains("\\topic\\")) { valueArray[i + 1][3] = "topic"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\topic\\")).trim(); } else if (fullPath.contains("\\map\\")) { valueArray[i + 1][3] = "map"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\map\\")).trim(); } } /* for(int i = 0; i < valueArray.length; i++){ for(int j = 0; j < valueArray[i].length; j++){ System.out.print(valueArray[i][j] + ","); } System.out.println(); }*/ File inputDesktopFile = new File(desktopFile); File inputServerFile = new File(serverFile); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputDesktopFile)); XSSFSheet mysheet = workbook.getSheetAt(0); int desktopRowNum = mysheet.getLastRowNum(); XSSFWorkbook serverWorkbook = new XSSFWorkbook(new FileInputStream(inputServerFile)); XSSFSheet serverSheet = serverWorkbook.getSheetAt(0); int serverRowNum = serverSheet.getLastRowNum(); for (int k = 1; k < valueArray.length; k++) { //System.out.println(valueArray[k][3]); if (valueArray[k][3].equals("topic") && (!valueArray[k][4].trim().startsWith("cfg"))) { //System.out.println(k + " " +valueArray[k][3]); String source = valueArray[k][4].trim(); for (int i = 1; i < desktopRowNum + 1; i++) { Row desktopRow = mysheet.getRow(i); if (desktopRow != null) { String targetString = desktopRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString)) { String desktopURL = desktopRow.getCell(0).getStringCellValue().trim(); desktopURL = desktopURL.replace(".com/en\\", ".com/" + lang.toLowerCase() + "\\"); desktopURL = desktopURL.replace("http://" + desktop, "http://" + desktop + "uat"); valueArray[k][5] = desktopURL; } } } for (int j = 0; j < serverRowNum + 1; j++) { Row serverRow = serverSheet.getRow(j); if (serverRow != null) { String targetString1 = serverRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString1)) { String serverURL = serverRow.getCell(0).getStringCellValue().trim(); serverURL = serverURL.replace("/en\\", "/" + lang.toLowerCase() + "\\"); serverURL = serverURL.replace("http://" + server, "http://" + server + "uat"); // System.out.println(serverURL); if (valueArray[k][5] != null) { valueArray[k][5] = valueArray[k][5] + "\n" + serverURL; } else { valueArray[k][5] = serverURL; } } } } } XSSFWorkbook outputworkbook = new XSSFWorkbook(); XSSFSheet outputsheet = outputworkbook.createSheet("sheet1"); XSSFCellStyle outputstyle = outputworkbook.createCellStyle(); outputstyle.setWrapText(true); int outputRowNum = 0; int outputCellNum = 0; for (int i = 0; i < valueArray.length; i++) { Row outputRow = outputsheet.createRow(outputRowNum++); for (int j = 0; j < valueArray[1].length; j++) { Cell outputCell = outputRow.createCell(outputCellNum++); if (valueArray[i][j] != null) { outputCell.setCellValue(valueArray[i][j]); } else { outputCell.setCellValue("N/A"); } if (j == 5) { //outputsheet.autoSizeColumn(4); outputCell.setCellStyle(outputstyle); } } outputCellNum = 0; } outputsheet.autoSizeColumn(2); outputsheet.autoSizeColumn(4); outputsheet.autoSizeColumn(5); FileOutputStream out = new FileOutputStream(new File(outputFilePath)); outputworkbook.write(out); out.close(); } } catch (Exception e) { try { File file = new File(userDir + "\\log.txt"); if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile()); BufferedWriter bw = new BufferedWriter(fw); bw.write(e.getMessage()); bw.write(e.getLocalizedMessage()); bw.close(); fw.close(); } catch (Exception e1) { e1.printStackTrace(); } e.printStackTrace(); } }