List of usage examples for org.apache.poi.ss.usermodel Sheet removeRow
void removeRow(Row row);
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 2s . 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.compassplus.gui.MainForm.java
private void removeRow(Sheet sheet, int rowIndex, Workbook wb, boolean debug) { ArrayList<CellRangeAddress> cras = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { cras.add(sheet.getMergedRegion(i)); }/*from ww w. j av a 2 s .c o m*/ while (sheet.getNumMergedRegions() > 0) { sheet.removeMergedRegion(0); } int lastRowNum = sheet.getLastRowNum(); if (debug) { //System.out.println("lastRowNum = " + lastRowNum); //System.out.println("rowIndex = " + rowIndex); } if (rowIndex >= 0 && rowIndex < lastRowNum) { Row removingRow = sheet.getRow(rowIndex); sheet.removeRow(removingRow); sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } if (rowIndex == lastRowNum) { Row removingRow = sheet.getRow(rowIndex); if (removingRow != null) { sheet.removeRow(removingRow); } } for (CellRangeAddress cra : cras) { if (rowIndex >= cra.getFirstRow() && rowIndex <= cra.getLastRow() && cra.getFirstRow() != cra.getLastRow()) { cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex < cra.getFirstRow()) { cra.setFirstRow(cra.getFirstRow() - 1); cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex > cra.getLastRow()) { sheet.addMergedRegion(cra); } } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void clearSheet(int sheetIndex) { Sheet sheet = getSheet(sheetIndex); int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = lastRow; i >= firstRow; i--) { Row r = sheet.getRow(i);// w w w . java2 s .com if (r != null) sheet.removeRow(r); } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void clearRange(int sheetIndex, int[] coords) { Sheet sheet = getSheet(sheetIndex); for (int i = coords[0]; i <= coords[2]; i++) { Row row = sheet.getRow(i);/*from w ww. ja v a 2 s .c o m*/ if (row == null) continue; for (int j = coords[1]; j <= coords[3]; j++) { Cell cell = row.getCell(j); if (cell != null) row.removeCell(cell); } if (row.getLastCellNum() < 0) sheet.removeRow(row); } }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * Excel? /*from www . j ava 2 s. co m*/ * * @Title: WriteExcel * @Date : 2014-9-11 ?01:33:59 * @param wb * @param rowList * @param xlsPath */ private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) { if (wb == null) { out("???"); return; } Sheet sheet = wb.getSheetAt(0);// sheet // ??????? int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1; int t = 0;// out("???" + rowList.size()); for (Row row : rowList) { if (row == null) continue; // ??? int pos = findInExcel(sheet, row); Row r = null;// ?????? if (pos >= 0) { sheet.removeRow(sheet.getRow(pos)); r = sheet.createRow(pos); } else { r = sheet.createRow(lastRowNum + t++); } //?? CellStyle newstyle = wb.createCellStyle(); //? for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = r.createCell(i);// ?? cell.setCellValue(getCellValue(row.getCell(i)));// ??? // cell.setCellStyle(row.getCell(i).getCellStyle());// if (row.getCell(i) == null) continue; copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ???? cell.setCellStyle(newstyle);// ? // sheet.autoSizeColumn(i);// } } out("???:" + (rowList.size() - t) + " ?" + t); // ?? setMergedRegion(sheet); try { // ??Excel FileOutputStream outputStream = new FileOutputStream(xlsPath); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { out("Excel?? "); e.printStackTrace(); } }
From source file:com.opendoorlogistics.studio.LoadedDatastore.java
License:Open Source License
private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) { // parse the original tables; these will be held in the datastore with the same index as the sheet int nbOriginal = originalLoadedDs.getTableCount(); if (nbOriginal != wb.getNumberOfSheets()) { throw new RuntimeException(); }/*from www .j ava 2 s. c o m*/ ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>(); for (int i = nbOriginal - 1; i >= 0; i--) { ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i); ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId()); if (newTable == null) { // table was deleted wb.removeSheetAt(i); } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) { Sheet sheet = wb.getSheetAt(i); boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable, DatastoreComparer.CHECK_ALL); if (sameStructure) { // re-write all values but skip the header row int nbOversized = 0; for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) { int iTargetRow = iRow + 1; Row row = sheet.getRow(iTargetRow); if (row == null) { row = sheet.createRow(iTargetRow); } int nc = newTable.getColumnCount(); for (int col = 0; col < nc; col++) { Cell cell = row.getCell(col); if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) { // don't set the value of formula cells... continue; } if (cell == null) { cell = row.createCell(col); } String sval = TableUtils.getValueAsString(newTable, iRow, col); if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) { nbOversized++; } cell.setCellValue(sval); } } // delete any rows after the last row (including 1 for the header) int lastOKRow = newTable.getRowCount(); while (sheet.getLastRowNum() > lastOKRow) { sheet.removeRow(sheet.getRow(sheet.getLastRowNum())); } if (nbOversized > 0 && report != null) { report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName())); ; } } else { // delete and replace. replace after parsing all original tables as we can get table name conflicts wb.removeSheetAt(i); oldOnesToReadd.add(newTable); } } } // re-add any totally replaced tables for (ODLTableReadOnly table : oldOnesToReadd) { Sheet sheet = wb.createSheet(table.getName()); if (sheet != null) { PoiIO.exportTable(sheet, table, report); } } // add new tables at the end for (int i = 0; i < ds.getTableCount(); i++) { ODLTableReadOnly newTable = ds.getTableAt(i); if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) { // new table... Sheet sheet = wb.createSheet(newTable.getName()); if (sheet != null) { PoiIO.exportTable(sheet, newTable, report); } } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private boolean deleteTestSuitesFromODS(String filePath, String testSuiteName) { boolean delete = false; File file = new File(filePath); org.jopendocument.dom.spreadsheet.Sheet sheet; try {//from w ww. j av a 2s .c om ODPackage createFromFile = ODPackage.createFromFile(file); SpreadSheet spreadSheet = createFromFile.getSpreadSheet(); sheet = spreadSheet.getSheet(0); int nRowCount = sheet.getRowCount(); //Iterating through each row of the selected sheet org.jopendocument.dom.spreadsheet.Cell cell = null; for (int nRowIndex = 3; nRowIndex < nRowCount; nRowIndex++) { cell = sheet.getCellAt(1, nRowIndex); if (cell.getValue() != null && cell.getValue() != "") { String name = cell.getTextValue(); if (name.equalsIgnoreCase(testSuiteName)) { sheet.removeRow(nRowIndex); break; } } } OutputStream out = new FileOutputStream(file); createFromFile.save(out); out.close(); } catch (IOException e) { } return delete; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private boolean deleteTestCasesFromODS(String filePath, String testSuiteName, String testCaseId) { boolean delete = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); File file = new File(filePath); org.jopendocument.dom.spreadsheet.Sheet sheet; try {//from w ww . j a v a 2 s. c om ODPackage createFromFile = ODPackage.createFromFile(file); SpreadSheet spreadSheet = createFromFile.getSpreadSheet(); sheet = spreadSheet.getSheet(testSuiteName); int nColCount = sheet.getColumnCount(); int nRowCount = sheet.getRowCount(); org.jopendocument.dom.spreadsheet.Cell cell = null; for (int nRowIndex = 24; nRowIndex < nRowCount; nRowIndex++) { cell = sheet.getCellAt(3, nRowIndex); if (cell.getValue() != null && cell.getValue() != "") { String testId = cell.getTextValue(); if (testId.equalsIgnoreCase(testCaseId)) { sheet.removeRow(nRowIndex); delete = true; break; } } } if (delete) { OutputStream out = new FileOutputStream(file); createFromFile.save(out); out.close(); for (int nRowIndex = 24; nRowIndex < nRowCount; nRowIndex++) { cell = sheet.getCellAt(1, nRowIndex); if (cell.getValue() != null && cell.getValue() != "") { TestCase testCase = readTestCasesFromODS(nRowIndex, sheet); testCases.add(testCase); } else { break; } } float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; float totalNotExecuted = 0; int totalTestCases = testCases.size(); for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } else { totalNotExecuted = totalNotExecuted + 1; } } ODPackage indexSheet = ODPackage.createFromFile(file); SpreadSheet indexSpreadSheet = indexSheet.getSpreadSheet(); org.jopendocument.dom.spreadsheet.Sheet sheet2 = indexSpreadSheet.getSheet(0); List<TestSuite> testSuites = new ArrayList<TestSuite>(); int indexRowCount = sheet2.getRowCount(); org.jopendocument.dom.spreadsheet.Cell indexCell = null; for (int rowIndex = 3; rowIndex < indexRowCount; rowIndex++) { //Iterating through each column indexCell = sheet2.getCellAt(1, rowIndex); if (indexCell.getValue() != null && indexCell.getValue() != "") { TestSuite testSuite = readDataFromODS(rowIndex, sheet2); testSuites.add(testSuite); if (testSuite.getName().equalsIgnoreCase(testSuiteName)) { sheet2.getCellAt(2, rowIndex).clearValue(); sheet2.setValueAt(totalPass, 2, rowIndex); sheet2.getCellAt(3, rowIndex).clearValue(); sheet2.setValueAt(totalFail, 3, rowIndex); sheet2.getCellAt(4, rowIndex).clearValue(); sheet2.setValueAt(totalNotApplicable, 4, rowIndex); sheet2.getCellAt(5, rowIndex).clearValue(); sheet2.setValueAt(totalNotExecuted, 5, rowIndex); sheet2.getCellAt(6, rowIndex).clearValue(); sheet2.setValueAt(totalBlocked, 6, rowIndex); sheet2.getCellAt(7, rowIndex).clearValue(); sheet2.setValueAt(totalTestCases, 7, rowIndex); sheet2.getCellAt(8, rowIndex).clearValue(); float testCovrge = (float) ((totalTestCases - totalNotExecuted) / totalTestCases) * 100; sheet2.setValueAt(Math.round(testCovrge), 8, rowIndex); break; } } } OutputStream out1 = new FileOutputStream(file); indexSheet.save(out1); Utility.closeStream(out1); } } catch (Exception e) { } return delete; }
From source file:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java
License:Open Source License
@Override public void executeActionOnSelection(Spreadsheet spreadsheet, SelectionChangeEvent event) { Sheet sheet = spreadsheet.getActiveSheet(); CellReference cr = event.getSelectedCellReference(); boolean cellCreated = false, rowCreated = false, commentEdited = false; Row row = sheet.getRow(cr.getRow()); if (row == null) { row = sheet.createRow(cr.getRow()); rowCreated = true;/*from ww w .j ava2 s .c om*/ } Cell cell = spreadsheet.getCell(cr); if (cell == null) { cell = row.createCell(cr.getCol()); cellCreated = true; } if (cell.getCellComment() == null) { createCellComment(spreadsheet, sheet, cell, cr); commentEdited = true; } else { cell.removeCellComment(); if (cellCreated) { sheet.getRow(cr.getRow()).removeCell(cell); } if (rowCreated) { sheet.removeRow(sheet.getRow(cr.getRow())); } } if (cell != null) { spreadsheet.refreshCells(cell); } if (commentEdited) { spreadsheet.editCellComment(cr); } }
From source file:IO.FILES.java
public void overWrite(Persona p, String texto) throws Exception { if (p == null) return;//from w w w .j av a2s .c om Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); boolean encontrado = false; Row row = null; Iterator it = hoja.rowIterator(); while (it.hasNext()) { row = (Row) it.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) { encontrado = true; break; } } if (!encontrado) return; int aux = row.getRowNum(); hoja.removeRow(row); row = hoja.createRow(aux); CentroEducativo centro = p.getCentro(); Redaccion R = p.getRedaccion(); for (int i = 0; i < cols.length; i++) { Cell cell = row.createCell(i); switch (i) { case 0: cell.setCellValue(p.getId()); break; case 1: cell.setCellValue(p.getName()); break; case 2: cell.setCellValue(p.getLastName()); break; case 3: cell.setCellValue(p.getLastName2()); break; case 4: cell.setCellValue(p.getNivel()); break; case 5: cell.setCellValue(p.getEdad()); break; case 6: cell.setCellValue(p.isMale() ? "M" : "F"); break; case 7: cell.setCellValue(centro.getName()); break; case 8: cell.setCellValue(centro.getSiglas()); break; case 9: cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO"); break; case 10: cell.setCellValue(p.isCCA() ? "CCA" : "SCA"); break; case 11: cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-"); break; case 12: cell.setCellValue(p.isAdecuacion() ? "SI" : "NO"); break; case 13: cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-"); break; case 14: cell.setCellValue(R.getUT()); break; case 15: cell.setCellValue(R.getCL()); break; case 16: cell.setCellValue(R.getPAL()); break; case 17: cell.setCellValue(R.getLPUT()); break; case 18: cell.setCellValue(R.getLPCL()); break; case 19: cell.setCellValue(R.getINSUB()); } } REDACCIONES.overWrite(wb, p, texto); save(wb); }