List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getCellFormula
@Override
public String getCellFormula()
SUM(C4:E4) From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java
private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum, XSSFSheet destinationWorksheet, int destinationRowNum) { // Get the source / new row XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum); XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1); } else {/* w w w. jav a 2s. c o m*/ newRow = destinationWorksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); //newCell.setCellValue(oldCell.getRawValue()); //newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row /* for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } */ }
From source file:com.excel.javafx.frames.MainFrame.java
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) { if ((cell1 == null) && (cell2 == null)) { return true; } else if ((cell1 == null) || (cell2 == null)) { return false; }//w ww .jav a2 s .co m boolean equalCells = false; int type1 = cell1.getCellType(); int type2 = cell2.getCellType(); if (type1 == type2) { if (cell1.getCellStyle().equals(cell2.getCellStyle())) { // Compare cells based on its type switch (cell1.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: if (cell1.getCellFormula().equals(cell2.getCellFormula())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_NUMERIC: if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_STRING: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BLANK: if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BOOLEAN: if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_ERROR: if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) { equalCells = true; } break; default: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; } } else { return false; } } else { return false; } return equalCells; }
From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSLX.java
License:Open Source License
public String convertCellToString(XSSFCell cell) { int type;// w w w.j av a2 s. co m if (cell == null) { type = Cell.CELL_TYPE_BLANK; } else { type = cell.getCellType(); } Object result; switch (type) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BLANK: result = ""; break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: throw new RuntimeException("There are no support fot the cell type"); } return result.toString(); }
From source file:com.xhsoft.framework.common.file.ExcelHandle.java
License:Open Source License
/** * ????.xlsx?//from w ww.j a v a 2s . com * @params {:,:} * @return String * @author lijiangwei * @since 2012-11-12 */ private String getCellValue(XSSFCell xls_cell) { String value = ""; switch (xls_cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: value = ""; break; case XSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(xls_cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: break; case XSSFCell.CELL_TYPE_FORMULA: String.valueOf(xls_cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: value = String.valueOf(xls_cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: value = String.valueOf(xls_cell.getStringCellValue()); break; default: break; } return value; }
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) {/*w w w . j av 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 v a2s .co m*/ 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 v a 2 s . co m*/ 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_Qtr_Verification.java
private void verify_E(XSSFCell cell_E, XSSFCell cell_B, ArrayList<ErrorModel> errorModelList) { String reporting_Qtr_Formula_Cell_Formula; switch (cell_E.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell_E.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula.replaceAll("\\$", "") .replaceAll(" ", ""); }/*from w w w .j a va 2 s . c om*/ //verify the formula if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA) && (reporting_Qtr_Formula_Cell_Formula.charAt(19) == 'F') && (!(reporting_Qtr_Formula_Cell_Formula.contains("/")))) { 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_E, errorModelList, "Operation_Standard"); } } //else throw an error else { genrateError(cell_E, errorModelList, "Operation_Standard"); } break; case Cell.CELL_TYPE_BLANK: break; default: genrateError(cell_E, errorModelList, "Operation_Standard"); break; } }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private void verify_F(XSSFCell cell_F, XSSFCell cell_B, ArrayList<ErrorModel> errorModelList) { String reporting_Qtr_Formula_Cell_Formula; switch (cell_F.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell_F.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula.replaceAll("\\$", "") .replaceAll(" ", ""); }//from w ww .ja va 2 s . c om //verify the formula if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA) && (reporting_Qtr_Formula_Cell_Formula.charAt(19) == 'G') && (!(reporting_Qtr_Formula_Cell_Formula.contains("/")))) { 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_F, errorModelList, "Operation_Standard"); } } //else throw an error else { genrateError(cell_F, errorModelList, "Operation_Standard"); } break; case Cell.CELL_TYPE_BLANK: break; default: genrateError(cell_F, errorModelList, "Operation_Standard"); break; } }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private void verify_G(XSSFCell cell_G, XSSFCell cell_B, ArrayList<ErrorModel> errorModelList) { String reporting_Qtr_Formula_Cell_Formula; switch (cell_G.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell_G.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula.replaceAll("\\$", "") .replaceAll(" ", ""); }/*from ww w . j av a 2 s.c o m*/ //verify the formula if ((cell_B.getCellType() == Cell.CELL_TYPE_FORMULA) && (reporting_Qtr_Formula_Cell_Formula.charAt(19) == 'H') && (!(reporting_Qtr_Formula_Cell_Formula.contains("/")))) { 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_G, errorModelList, "Operation_Standard"); } } //else throw an error else { genrateError(cell_G, errorModelList, "Operation_Standard"); } break; case Cell.CELL_TYPE_BLANK: break; default: genrateError(cell_G, errorModelList, "Operation_Standard"); break; } }