List of usage examples for org.apache.poi.ss.util CellReference CellReference
public CellReference(Cell cell)
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {//w w w. j a v a 2 s . c o m db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:CreatePivotTable.java
License:Apache License
public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet();/* ww w . ja v a2 s .c o m*/ //Create some data to build the pivot table on setCellData(sheet); XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5")); //Configure the pivot table //Use first column as row label pivotTable.addRowLabel(0); //Sum up the second column pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1); //Set the third column as filter pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2); //Add filter on forth column pivotTable.addReportFilter(3); FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {//from w w w.j av a 2 s .c o m db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length == 0) { throw new IllegalArgumentException("Filename must be given"); }//from www .j a v a2 s .c om Workbook wb = WorkbookFactory.create(new File(args[0])); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
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 ww w . ja v a 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:br.ufal.cideei.util.count.MetricsTable.java
License:Open Source License
private void printFooters() { int columns = columnMapping.size(); Row firstRow = sheet.getRow(1);// w w w. ja v a 2 s.co m Row lastRow = sheet.getRow(rowCount - 1); Row sumFooterRow = sheet.createRow(rowCount++); Cell sumFooterLabelCell = sumFooterRow.createCell(0); sumFooterLabelCell.setCellValue("SUM"); Row averageFooterRow = sheet.createRow(rowCount++); Cell averageFooterLabelCell = averageFooterRow.createCell(0); averageFooterLabelCell.setCellValue("AVERAGE"); for (int index = 0; index <= columns; index++) { Cell cell = firstRow.getCell(index); if (cell == null) { cell = firstRow.createCell(index); } Cell sumFooterCell = sumFooterRow.createCell(index); Cell averageFooterCell = averageFooterRow.createCell(index); CellReference firstCell = new CellReference(firstRow.getCell(index)); Cell lastRowCell = lastRow.getCell(index); if (lastRowCell == null) { lastRowCell = lastRow.createCell(index); } CellReference lastCell = new CellReference(lastRowCell); sumFooterCell .setCellFormula("SUM(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); averageFooterCell.setCellFormula( "AVERAGE(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); } }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook;/*from w w w. j a v a2 s.co m*/ if (!outputFile.exists()) { outputFile.createNewFile(); outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java
License:Apache License
private static double populateCell(Map<String, CellStyle> styles, double value, Calendar calendar, int rowIndex, Row row, int colIndex) { Cell cell = row.createCell(colIndex); String address = new CellReference(cell).formatAsString(); switch (colIndex) { case 0:/*from ww w. j av a 2 s . c o m*/ // column A: default number format cell.setCellValue(value++); break; case 1: // column B: #,##0 cell.setCellValue(value++); cell.setCellStyle(styles.get("#,##0.00")); break; case 2: // column C: $#,##0.00 cell.setCellValue(value++); cell.setCellStyle(styles.get("$#,##0.00")); break; case 3: // column D: red bold text on yellow background cell.setCellValue(address); cell.setCellStyle(styles.get("red-bold")); break; case 4: // column E: boolean // TODO booleans are shown as 1/0 instead of TRUE/FALSE cell.setCellValue(rowIndex % 2 == 0); break; case 5: // column F: date / time cell.setCellValue(calendar); cell.setCellStyle(styles.get("m/d/yyyy")); calendar.roll(Calendar.DAY_OF_YEAR, -1); break; case 6: // column F: formula // TODO formulas are not yet supported in SXSSF //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")"); //break; default: cell.setCellValue(value++); break; } return value; }
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 w w. jav a 2s .co m 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.ExtraiPlanilha.java
public static void gravaCTPlanilha(List<Plano> plano, String panilha, int linhaCelula) throws FileNotFoundException, IOException, InvalidFormatException, SQLException, ClassNotFoundException {// www . j a va 2 s.com ManipulaDadosSQLite banco = new ManipulaDadosSQLite(); //recebe a planilha e atribui a variavel arquivo FileInputStream arquivo = new FileInputStream(new File(panilha)); System.out.println(panilha); //instacia um workbook passando arquivo como paramentro XSSFWorkbook workbook = new XSSFWorkbook(arquivo); XSSFSheet sheetPlano = workbook.getSheetAt(1); String cadeia = "B", segmento = "C", produto = "D", funcionalidade = "E", cenarioItegrado = "F", sistemaMaster = "G", sistemasEnvolvidos = "H", fornecedor = "I", tpRequisito = "J", requisito = "K", cenarioTeste = "L", casoTeste = "M", descricao = "N", nomeStep = "P", descricaoStep = "Q", resultadoEsperado = "R", cenarioAuto = "U", type = "V", trg = "W", subject = "X", criacao = "Y"; CellReference cellReference = new CellReference("B8"); // Row row = sheetPlano.getRow(cellReference.getRow()); // Cell cell = row.getCell(cellReference.getCol()); int tamanhoLista = plano.size(); int numeroCelula = 8; int linha = linhaCelula; int celula = 12; Row row = sheetPlano.getRow(linha); Cell cell;//= row.getCell(celula); Cell celCadeia = row.getCell(1); Cell celSegmento = row.getCell(2); Cell celProduto = row.getCell(3); Cell celFuncionalidade = row.getCell(4); Cell celCenarioIntegracao = row.getCell(5); Cell celSistemaMaster = row.getCell(6); Cell celSistemaEnvolvidos = row.getCell(7); Cell celFornecedor = row.getCell(8); Cell celTpRequisito = row.getCell(9); Cell celRequisito = row.getCell(10); Cell celCenario = row.getCell(11); Cell celCasoTeste = row.getCell(12); Cell celDescricao = row.getCell(13); Cell celQtdSistemas = row.getCell(19); Cell celCenarioAuto = row.getCell(21); Cell celType = row.getCell(22); Cell celTrg = row.getCell(23); Cell celSubject = row.getCell(24); Cell celCriacao = row.getCell(25); Cell celStep = row.getCell(16); Step steps = new Step(); for (int i = 0; i < tamanhoLista; i++) { row = sheetPlano.getRow(linha); celCadeia = row.getCell(1); celSegmento = row.getCell(2); celProduto = row.getCell(3); celFuncionalidade = row.getCell(4); celCenarioIntegracao = row.getCell(5); celSistemaMaster = row.getCell(6); celSistemaEnvolvidos = row.getCell(7); celFornecedor = row.getCell(8); celTpRequisito = row.getCell(9); celRequisito = row.getCell(10); celCenario = row.getCell(11); celCasoTeste = row.getCell(12); celDescricao = row.getCell(13); Cell celComplexidade = row.getCell(15); celQtdSistemas = row.getCell(19); Cell celQtdStep = row.getCell(20); celCenarioAuto = row.getCell(21); celType = row.getCell(22); celTrg = row.getCell(23); celSubject = row.getCell(24); celCriacao = row.getCell(25); celCadeia.setCellValue(plano.get(i).getCadeia()); celSegmento.setCellValue(plano.get(i).getSegmento()); celProduto.setCellValue(plano.get(i).getProduto()); celFuncionalidade.setCellValue(plano.get(i).getFuncionalidade()); celCenarioIntegracao.setCellValue(plano.get(i).getCenarioIntegrado()); celSistemaMaster.setCellValue(plano.get(i).getSistemaMaster()); celSistemaEnvolvidos.setCellValue(plano.get(i).getSistemasEnvolvidos()); celFornecedor.setCellValue(plano.get(i).getFornecedor()); celTpRequisito.setCellValue(plano.get(i).getTpRequisito()); celRequisito.setCellValue(plano.get(i).getRequisito()); celCenario.setCellValue(plano.get(i).getCenarioTeste()); celCasoTeste.setCellValue(plano.get(i).getCasoTeste()); celDescricao.setCellValue(plano.get(i).getDescCasoTeste()); String formulaComplexibilidade = celComplexidade.getCellFormula(); celComplexidade.setCellFormula(formulaComplexibilidade); celQtdSistemas.setCellValue(plano.get(i).getQtdSistemas()); String formulaQtdStep = celQtdStep.getCellFormula(); celQtdStep.setCellFormula(formulaQtdStep); celCenarioAuto.setCellValue(plano.get(i).getCenarioAutomatizavel()); celType.setCellValue(plano.get(i).getType()); celTrg.setCellValue(plano.get(i).getTrg()); celSubject.setCellValue(plano.get(i).getSubject()); celCriacao.setCellValue(plano.get(i).getCriacaoAlteracao()); celStep = row.getCell(16); row = sheetPlano.getRow(linha); // celCasoTeste = row.getCell(12); int linhaStep = linha; List<Plano> listPlanos = banco.selectPlanoPorId(plano.get(i)); List<Step> listStep = banco.getStepPorPlano(plano.get(i)); int linhaLimpeza = linha; //limpa as clulas de step for (int cont = 0; cont <= 24; cont++) { Cell celNomeStep = row.getCell(16); Cell celDescStep = row.getCell(17); Cell celResultadoStep = row.getCell(18); String valor = null; celNomeStep.setCellValue(valor); celDescStep.setCellValue(valor); celResultadoStep.setCellValue(valor); linhaLimpeza = linhaLimpeza + 1; row = sheetPlano.getRow(linhaLimpeza); } //fim row = sheetPlano.getRow(linha); int tamanho = listStep.size(); int idTemp = 0; for (int cont = 0; cont < tamanho; cont++) { // if(idTemp != listPlanos.get(cont).getStep().getId()){ // model.addRow(new String[]{String.valueOf(listPlanos.get(cont).getStep().getNomeStep()), listPlanos.get(cont).getStep().getDescStep(), // listPlanos.get(cont).getStep().getResultadoStep(), String.valueOf(listPlanos.get(cont).getStep().getId())}); // } Cell celNomeStep = row.getCell(16); Cell celDescStep = row.getCell(17); Cell celResultadoStep = row.getCell(18); celNomeStep.setCellValue(listStep.get(cont).getNomeStep()); celDescStep.setCellValue(listStep.get(cont).getDescStep()); celResultadoStep.setCellValue(listStep.get(cont).getResultadoStep()); //caso o ct seja alterao pinta os steps de amarelo - inicio if (plano.get(i).getCriacaoAlteracao().equals("Alterao")) { Color color = new XSSFColor(java.awt.Color.yellow); // XSSFCellStyle style = workbook.createCellStyle(); // style.setBorderTop((short) 6); // double lines border // style.setBorderBottom((short) 1); // single line border // style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); // // celNomeStep.setCellStyle(style); } //fim linhaStep = linhaStep + 1; row = sheetPlano.getRow(linhaStep); celStep = row.getCell(16); idTemp = listPlanos.get(cont).getId(); } linha = linha + 25; row = sheetPlano.getRow(linha); cell = celCadeia; } FileOutputStream fileOut = new FileOutputStream(new File(panilha)); workbook.write(fileOut); fileOut.close(); }