List of usage examples for org.apache.poi.ss.util CellReference getRow
public int getRow()
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);// w w w . j a va2 s .co m Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;// www .ja v a2 s . c o m int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException { XPath xPath = XPathFactory.newInstance().newXPath(); NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET); NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET); Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE); Sheet sheet = workbook.getSheetAt(0); for (int i = 0; i < cellValueList.getLength(); i++) { Node cellValue = cellValueList.item(i); String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent(); String type = cellValue.getAttributes().getNamedItem("type").getTextContent(); String value = cellValue.getTextContent(); CellReference cellRef = new CellReference(cellName); Row row = sheet.getRow(cellRef.getRow()); Cell cell = row.getCell(cellRef.getCol()); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellFormula(value);//from w w w . ja va 2 s . c o m } else { cell.setCellValue(value); } } if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) { CellReference startCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("startRef").getTextContent()); CellReference endCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("endRef").getTextContent()); int startRowIndex = startCellRef.getRow(); int startColIndex = startCellRef.getCol(); int endColIndex = endCellRef.getCol(); CellStyle[] cellStyles = new CellStyle[endColIndex + 1]; Row firstRow = sheet.getRow(startRowIndex); for (int i = startColIndex; i <= endColIndex; i++) { cellStyles[i] = firstRow.getCell(i).getCellStyle(); } for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) { Row templeteRow = sheet.getRow(i); if (templeteRow != null) { sheet.removeRow(templeteRow); } } int rowNodeIndex = 0; for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) { Row row = sheet.createRow(i); int cellNodeIndex = 0; Node rowNode = rowNodeList.item(rowNodeIndex); NodeList rowValueNodeList = rowNode.getChildNodes(); ArrayList<Node> nodes = new ArrayList<Node>(); for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) { Node currentNode = rowValueNodeList.item(idx); if (currentNode.getNodeType() == Node.ELEMENT_NODE) { nodes.add(currentNode); } } for (int j = startColIndex; j <= endColIndex; j++) { Cell cell = row.createCell(j); Node cellNode = nodes.get(cellNodeIndex); String type = cellNode.getAttributes().getNamedItem("type").getTextContent(); String value = cellNode.getTextContent(); CellStyle cellStyle = cellStyles[j]; cell.setCellStyle(cellStyle); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula(value); } else if ("string".equals(type)) { if (value != null && value.length() > 0) { cell.setCellValue(value); } else { cell.setCellValue(""); } } else { cell.setCellValue(""); } cellNodeIndex++; } rowNodeIndex++; } } return workbook; }
From source file:com.accenture.control.ExtraiPlanilha.java
public void extraiConfPlanilha(String diretorio) throws IOException, ClassNotFoundException, SQLException { boolean existedados = false; ManipulaDadosSQLite bdLite = new ManipulaDadosSQLite(); FileInputStream arquivo;/*from w ww.j ava 2 s.com*/ arquivo = new FileInputStream(new File(diretorio)); System.out.println(diretorio); XSSFWorkbook workbook = new XSSFWorkbook(arquivo); XSSFSheet sheetConf = workbook.getSheetAt(2); int linha = 7; int numCelula = 1; //Capturando dados da Complexidade na celula A1 int i = 1; CellReference cellReference = new CellReference("A" + i); Row row = sheetConf.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; bdLite.deletaTabelaConf("TB_COMPLEXIDADE"); while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_COMPLEXIDADE", "DESC_COMPLEXIDADE", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } bdLite.deletaTabelaConf("TB_AUTOMATIZAVEL"); //Capturando dados Automatizvel na celula A7 i = 7; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_AUTOMATIZAVEL"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_AUTOMATIZAVEL", "DESC_AUTOMATIZAVEL", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados type i = 12; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TYPE"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TYPE", "DESC_TYPE", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados TRG i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TRG"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TRG", "DESC_TRG", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados CADEIA i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CADEIA"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CADEIA", "DESC_CADEIA", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados TP REQUISITO i = 32; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TP_REQUISITO"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TP_REQUISITO", "DESC_TP_REQUISITO", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados criao/alterao i = 27; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CRIACAO"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CRIACAO", "DESC_CRIACAO", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados SISTEMA MASTER i = 1; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_SISTEMA_MASTER"); i++; while (cell != null) { cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_SISTEMA_MASTER", "DESC_SISTEMA_MASTER", cell.getStringCellValue()); i++; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); if (row == null) { cell = null; } else { cell = row.getCell(cellReference.getCol(), row.RETURN_BLANK_AS_NULL); } } //Capturando dados FUNCIONALIDADE i = 1; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_FUNCIONALIDADE"); i++; while (cell != null) { cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", cell.getStringCellValue()); i++; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados CENARIO INTEGRADO i = 1; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CENARIO_INTEGRADO"); i++; while (cell != null) { cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CENARIO_INTEGRADO", "DESC_CENARIO_INTEGRADO", cell.getStringCellValue()); i++; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } if (existedados == true) { bdLite.insertVersaCarga(sheetConf.getSheetName()); } }
From source file:com.accenture.control.ImportaDadosConf.java
public int getQtdDadosConf(String diretorio) throws FileNotFoundException, IOException { FileInputStream arquivo;// w ww . ja va 2 s .c o m arquivo = new FileInputStream(new File(diretorio)); // XSSFWorkbook workbook = new XSSFWorkbook(arquivo); //Selecionando a planilha de configuraes XSSFSheet sheetConf = workbook.getSheetAt(2); int linha = 7; int numCelula = 1; int i = 1; //Caontando COmplexidade CellReference cellReference = new CellReference("A" + i); Row row = sheetConf.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } i = 7; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados type i = 12; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados TRG i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados CADEIA i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados TP REQUISITO i = 32; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados criao/alterao i = 27; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } //Capturando dados SISTEMA MASTER i = 1; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); qtdConf++; if (row == null) { cell = null; } else { cell = row.getCell(cellReference.getCol(), row.RETURN_BLANK_AS_NULL); } } //Capturando dados FUNCIONALIDADE i = 1; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); qtdConf++; i++; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados CENARIO INTEGRADO i = 1; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; while (cell != null) { cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); texto1.setText("Importando itens de configurao: " + cell.getStringCellValue()); texto1.paintAll(texto1.getGraphics()); i++; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); qtdConf++; } return qtdConf; }
From source file:com.adanac.excel.reader.sax.ExcelXSSFSheetXMLHandler.java
License:Apache License
/** * Do a check for, and output, comments in otherwise empty cells. * /*from w ww .j a v a 2 s . co m*/ * @throws BingSaxReadStopException */ private void checkForEmptyCellComments(EmptyCellCommentsCheckType type) throws BingSaxReadStopException { if (commentCellRefs != null && !commentCellRefs.isEmpty()) { // If we've reached the end of the sheet data, output any // comments we haven't yet already handled if (type == EmptyCellCommentsCheckType.END_OF_SHEET_DATA) { while (!commentCellRefs.isEmpty()) { outputEmptyCellComment(commentCellRefs.remove()); } return; } // At the end of a row, handle any comments for "missing" rows // before us if (this.cellRef == null) { if (type == EmptyCellCommentsCheckType.END_OF_ROW) { while (!commentCellRefs.isEmpty()) { if (commentCellRefs.peek().getRow() == rowNum) { outputEmptyCellComment(commentCellRefs.remove()); } else { return; } } return; } else { throw new IllegalStateException( "Cell ref should be null only if there are only empty cells in the row; rowNum: " + rowNum); } } CellReference nextCommentCellRef; do { CellReference cellRef = new CellReference(this.cellRef); CellReference peekCellRef = commentCellRefs.peek(); if (type == EmptyCellCommentsCheckType.CELL && cellRef.equals(peekCellRef)) { // remove the comment cell ref from the list if we're about // to handle it alongside the cell content commentCellRefs.remove(); return; } else { // fill in any gaps if there are empty cells with comment // mixed in with non-empty cells int comparison = cellRefComparator.compare(peekCellRef, cellRef); if (comparison > 0 && type == EmptyCellCommentsCheckType.END_OF_ROW && peekCellRef.getRow() <= rowNum) { nextCommentCellRef = commentCellRefs.remove(); outputEmptyCellComment(nextCommentCellRef); } else if (comparison < 0 && type == EmptyCellCommentsCheckType.CELL && peekCellRef.getRow() <= rowNum) { nextCommentCellRef = commentCellRefs.remove(); outputEmptyCellComment(nextCommentCellRef); } else { nextCommentCellRef = null; } } } while (nextCommentCellRef != null && !commentCellRefs.isEmpty()); } }
From source file:com.canoo.webtest.plugins.exceltest.AbstractExcelCellStep.java
License:Open Source License
protected Cell getExcelCell() { final CellReference cellReference = ExcelCellUtils.getCellReference(this, getCell(), getRow(), getCol()); return ExcelCellUtils.getExcelCellAt(this, cellReference.getRow(), cellReference.getCol()); }
From source file:com.canoo.webtest.plugins.exceltest.AbstractExcelCellStepTest.java
License:Open Source License
private void checkCellReference(final int expRow, final int expCol, final String cell, final String row, final String col) throws Exception { final AbstractExcelCellStep step = (AbstractExcelCellStep) getStep(); step.setCell(cell);//from ww w . j ava2 s.c o m step.setRow(row); step.setCol(col); executeStep(step); final CellReference cellReference = step.getCellReference(); assertEquals("Row", expRow, cellReference.getRow()); assertEquals("Column", expCol, cellReference.getCol()); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelFindRow.java
License:Open Source License
public void doExecute() { final CellReference cellReference = ExcelCellUtils.getCellReference(this, null, getStartRow(), getCol()); final Sheet excelSheet = getExcelSheet(); int row = cellReference.getRow(); while (row <= excelSheet.getLastRowNum()) { final Cell excelCellAt = ExcelCellUtils.getExcelCellAt(this, row, cellReference.getCol()); if (verifyText(ExcelCellUtils.getCellValueAt(excelCellAt))) { setWebtestProperty(getProperty(), String.valueOf(row + 1), getPropertyType()); return; }/*from w w w. java 2 s.com*/ row++; } throw new StepFailedException( "No cells were found matching '" + getText() + "' starting from " + cellReference.formatAsString(), this); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelVerifyCellStyle.java
License:Open Source License
Cell getAdjacentCell(final int dir) { final CellReference cellReference = getCellReference(); short yofs = 0; int xofs = 0; switch (dir) { case TOP://from w w w. ja v a2 s. co m yofs = -1; break; case RIGHT: xofs = 1; break; case BOTTOM: yofs = 1; break; case LEFT: xofs = -1; break; default: throw new IllegalArgumentException("Invalid side: " + dir); } return ExcelCellUtils.getExcelCellAt(this, cellReference.getRow() + yofs, (short) (cellReference.getCol() + xofs)); }