List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCTRow
@Internal
public CTRow getCTRow()
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
public static void expandRow(XSSFSheet sheet, int rowNumber) { if (rowNumber == -1) { return;/*from w ww . j av a 2 s.co m*/ } XSSFRow row = sheet.getRow(rowNumber); // If it is already expanded do nothing. if (!row.getCTRow().isSetHidden()) { return; } // Find the start of the group. int startIdx = findStartOfRowOutlineGroup(sheet, rowNumber); // Find the end of the group. int endIdx = findEndOfRowOutlineGroup(sheet, rowNumber); // expand: // collapsed must be unset // hidden bit gets unset _if_ surrounding groups are expanded you can // determine // this by looking at the hidden bit of the enclosing group. You will // have // to look at the start and the end of the current group to determine // which // is the enclosing group // hidden bit only is altered for this outline level. ie. don't // un-collapse contained groups short level = row.getCTRow().getOutlineLevel(); if (!isRowGroupHiddenByParent(sheet, rowNumber)) { /** change start */ // start and end are off by one because POI did edge detection. Move // start back to correct pos: startIdx++; // end is already correct because of another bug (using '<' instead // of '<=' below) /** change end */ for (int i = startIdx; i < endIdx; i++) { XSSFRow r = sheet.getRow(i); if (level == r.getCTRow().getOutlineLevel()) { r.getCTRow().unsetHidden(); } else if (!isRowGroupOrParentCollapsed(sheet, i, level)) { r.getCTRow().unsetHidden(); } } } // Write collapse field /** start */ if (isRowsInverted(sheet)) { XSSFRow r = sheet.getRow(startIdx - 1); if (r != null && r.getCTRow().getCollapsed()) { r.getCTRow().unsetCollapsed(); } } else { CTRow ctRow = sheet.getRow(endIdx).getCTRow(); // This avoids an IndexOutOfBounds if multiple nested groups are // collapsed/expanded if (ctRow.getCollapsed()) { ctRow.unsetCollapsed(); } } /** end */ }
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
private static int findEndOfRowOutlineGroup(XSSFSheet sheet, int row) { short level = sheet.getRow(row).getCTRow().getOutlineLevel(); int currentRow; /** start *///from www .ja v a 2 s.co m int lastRowNum = sheet.getLastRowNum() + 1; /** end */ for (currentRow = row; currentRow < lastRowNum; currentRow++) { XSSFRow row2 = sheet.getRow(currentRow); if (row2 == null || row2.getCTRow().getOutlineLevel() < level) { break; } } return currentRow; }
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
public static void collapseRow(XSSFSheet sheet, int rowIndex) { XSSFRow row = sheet.getRow(rowIndex); if (row != null) { int startRow = findStartOfRowOutlineGroup(sheet, rowIndex); // Hide all the columns until the end of the group int lastRow = writeHidden(sheet, row, startRow, true); /** start */ if (isRowsInverted(sheet)) { if (sheet.getRow(startRow) != null) { sheet.getRow(startRow).getCTRow().setCollapsed(true); } else if (startRow < 0) { // happens when inverted group starts at 0; Excel does not // write a collapsed prop for this case. } else { XSSFRow newRow = sheet.createRow(startRow); newRow.getCTRow().setCollapsed(true); }//from ww w . ja v a 2 s . c om } else { if (sheet.getRow(lastRow) != null) { sheet.getRow(lastRow).getCTRow().setCollapsed(true); } else { XSSFRow newRow = sheet.createRow(lastRow); newRow.getCTRow().setCollapsed(true); } } /** end */ } }
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
private static int writeHidden(XSSFSheet sheet, XSSFRow xRow, int rowIndex, boolean hidden) { short level = xRow.getCTRow().getOutlineLevel(); /** completely rewritten after this line */ // row index is the first row BEFORE group, not what we want rowIndex++;//from www. java2s .com // row will be null at some point, this is safe while (true) { XSSFRow row = sheet.getRow(rowIndex); if (row == null) { break; } short outlineLevel = row.getCTRow().getOutlineLevel(); if (outlineLevel < level) { break; } row.getCTRow().setHidden(hidden); rowIndex++; } /** old code, for reference */ // for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) { // xRow = (XSSFRow) it.next(); // // // skip rows before the start of this group // if (xRow.getRowNum() < rowIndex) { // continue; // } // // if (xRow.getCTRow().getOutlineLevel() >= level) { // xRow.getCTRow().setHidden(hidden); // rowIndex++; // } // // } return rowIndex; }
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
/** * @return A row index, which can uniquely identify the group that exists * between the given indexes, and has the given level. (the row at * <code>start</code> might have a level that is higher than we * want). 0-based./*w ww . j a va 2 s.co m*/ */ public static long findUniqueRowIndex(Spreadsheet sheet, int start, int end, int lastlevel) { for (int i = start; i <= end; i++) { XSSFRow current = (XSSFRow) sheet.getActiveSheet().getRow(i); if (current.getCTRow().getOutlineLevel() == lastlevel) { return i; } } return -1; }
From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java
/** * @return The end index of the row group that spans the given row, with the * given level. 0-based.// w ww . j av a2 s . c om */ public static long findEndOfRowGroup(Spreadsheet sheet, int rowindex, XSSFRow row, short level) { while (rowindex < sheet.getRows()) { XSSFRow r = (XSSFRow) sheet.getActiveSheet().getRow(rowindex); if (r == null || r.getCTRow().getOutlineLevel() < level) { // end return rowindex - 1; } rowindex++; } return -1; }
From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java
License:Open Source License
/** * Called when a grouping level header is clicked * /*from w ww. j a v a 2 s.com*/ * @param isCols * true if the user clicked on cols, false for row level headers * @param level * which level the user clicked */ protected void levelHeaderClicked(boolean isCols, int level) { /* * A click on a header should change groupings so that all levels above * the selected are expanded, and the selected level is all collapsed * (which hides any levels underneath this). */ if (getActiveSheet() instanceof HSSFSheet) { return; } XSSFSheet xsheet = (XSSFSheet) getActiveSheet(); CTWorksheet ctWorksheet = xsheet.getCTWorksheet(); if (isCols) { CTCols ctCols = ctWorksheet.getColsList().get(0); List<CTCol> colList = ctCols.getColList(); for (CTCol col : colList) { short l = col.getOutlineLevel(); // It's a lot easier to not call expand/collapse if (l >= 0 && l < level) { // expand if (col.isSetHidden()) { col.unsetHidden(); } } else { // collapse col.setHidden(true); } } } else { /* * Groups are more complicated than cols, use existing * collapse/expand functionality. */ int lastlevel = 0; for (int i = 0; i < getRows(); i++) { XSSFRow row = xsheet.getRow(i); if (row == null) { lastlevel = 0; continue; } short l = row.getCTRow().getOutlineLevel(); if (l != lastlevel) { // group starts here int end = (int) GroupingUtil.findEndOfRowGroup(this, i, row, l); long uniqueIndex = GroupingUtil.findUniqueRowIndex(this, i, end, l); if (l > 0 && l < level) { // expand GroupingUtil.expandRow(xsheet, (int) uniqueIndex); } else if (l >= level) { // collapse GroupingUtil.collapseRow(xsheet, (int) uniqueIndex); } lastlevel = l; } } } SpreadsheetFactory.reloadSpreadsheetComponent(this, workbook); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Loads all data relating to grouping if the current sheet is a * {@link XSSFSheet}.//w ww. j a va 2 s. c o m */ static void loadGrouping(Spreadsheet spreadsheet) { if (spreadsheet.getActiveSheet() instanceof HSSFSheet) { // API not available return; } CTWorksheet ctWorksheet = ((XSSFSheet) spreadsheet.getActiveSheet()).getCTWorksheet(); CTSheetProtection sheetProtection = ctWorksheet.getSheetProtection(); if (sheetProtection != null) { spreadsheet.getState().lockFormatColumns = sheetProtection.getFormatColumns(); spreadsheet.getState().lockFormatRows = sheetProtection.getFormatRows(); } spreadsheet.getState().colGroupingMax = 0; spreadsheet.getState().rowGroupingMax = 0; if (ctWorksheet.getSheetPr() != null && ctWorksheet.getSheetPr().getOutlinePr() != null) { CTOutlinePr outlinePr = ctWorksheet.getSheetPr().getOutlinePr(); spreadsheet.getState().colGroupingInversed = !outlinePr.getSummaryRight(); spreadsheet.getState().rowGroupingInversed = !outlinePr.getSummaryBelow(); } else { spreadsheet.getState().colGroupingInversed = false; spreadsheet.getState().rowGroupingInversed = false; } // COLS CTCols colsArray = ctWorksheet.getColsArray(0); /* * Columns are grouped so that columns that are beside each other and * share properties have a single CTCol with a min and max index. * * A column that is part of a group has an outline level. Each col also * has a property called 'collapsed', which doesn't appear to be used * for anything. If a group is collapsed, each col in the group has its * 'visibility' property set to false. */ List<GroupingData> data = new ArrayList<GroupingData>(); short lastlevel = 0; CTCol prev = null; for (CTCol col : colsArray.getColList()) { if (prev != null && prev.getMax() + 1 < col.getMin()) { // break in cols, reset level lastlevel = 0; } if (col.getOutlineLevel() > lastlevel) { // new group starts // multiple groups might start on the same column, go through // each in order while (lastlevel != col.getOutlineLevel()) { lastlevel++; if (spreadsheet.getState(false).colGroupingMax < lastlevel) { spreadsheet.getState().colGroupingMax = lastlevel; } // do not add children of collapsed groups if (!data.isEmpty()) { GroupingData previous = data.get(data.size() - 1); if (previous.collapsed && previous.endIndex >= col.getMin() && previous.level < col.getOutlineLevel()) { continue; } } boolean columnHidden = GroupingUtil.checkHidden(colsArray, col, lastlevel); long end = GroupingUtil.findEndOfColGroup(colsArray, col, lastlevel) - 1; long unique = GroupingUtil.findUniqueColIndex(colsArray, col, lastlevel) - 1; GroupingData d = new GroupingData(col.getMin() - 1, end, lastlevel, unique, columnHidden); data.add(d); } } else if (col.getOutlineLevel() < lastlevel) { // groups end lastlevel = col.getOutlineLevel(); } prev = col; } /* * There is a Excel data model inconsistency here. Technically, multiple * groups can start or end on the same column. However, the * collapse/expanded property is stored only as a boolean on the column; * if there are multiple groups in one column, there is no way to know * which of the groups is collapsed and which isn't, since there is only * one boolean value. The way Excel 'solves' this is to not render the * lower level groups fully in this particular case (the line and expand * button are not visible). So, let's not display them here either. */ Set<GroupingData> toRemove = new HashSet<GroupingData>(); for (int i = 0; i < data.size(); i++) { for (int j = i + 1; j < data.size(); j++) { GroupingData d1 = data.get(i); GroupingData d2 = data.get(j); if (spreadsheet.getState().colGroupingInversed) { if (d1.startIndex == d2.startIndex) { toRemove.add(d2); } } else { if (d1.endIndex == d2.endIndex) { toRemove.add(d2); } } } } data.removeAll(toRemove); spreadsheet.getState().colGroupingData = data; // ROWS data = new ArrayList<GroupingData>(); /* * Each row that has data (or grouping props) exists separately, they * are not grouped like columns. * * Each row that is part of a group has a set outline level. Unlike * cols, the 'collapse' property is actually used for rows, in * conjuction with the 'hidden' prop. If a group is collapsed, each row * in the group has its 'hidden' prop set to true. Also, the column * after the group (or before, if inverted) has its 'collapsed' property * set to true. */ Stack<GroupingData> rows = new Stack<GroupingData>(); lastlevel = 0; for (int i = 0; i <= spreadsheet.getRows(); i++) { XSSFRow row = (XSSFRow) spreadsheet.getActiveSheet().getRow(i); if (row == null || row.getCTRow().getOutlineLevel() < lastlevel) { // end any groups short level; if (row == null) { level = 0; } else { level = row.getCTRow().getOutlineLevel(); } GroupingData g = null; while (level != lastlevel) { g = rows.pop(); lastlevel--; boolean collapsed = false; if (spreadsheet.getState().rowGroupingInversed) { // marker is before group XSSFRow r = (XSSFRow) spreadsheet.getActiveSheet().getRow(g.startIndex - 1); if (r != null) { collapsed = r.getCTRow().getCollapsed(); } } else if (row != null) { // collapse marker is after group, so it is on this // row collapsed = row.getCTRow().getCollapsed(); } g.collapsed = collapsed; // remove children of collapsed parent if (collapsed) { toRemove = new HashSet<GroupingData>(); for (GroupingData d : data) { if (d.startIndex >= g.startIndex && d.endIndex <= g.endIndex && d.level > g.level) { toRemove.add(d); } } data.removeAll(toRemove); } data.add(g); } continue; } short level = row.getCTRow().getOutlineLevel(); if (level > lastlevel) { // group start // possibly many groups start here while (level != lastlevel) { lastlevel++; int end = (int) GroupingUtil.findEndOfRowGroup(spreadsheet, i, row, lastlevel); long uniqueIndex = GroupingUtil.findUniqueRowIndex(spreadsheet, i, end, lastlevel); GroupingData d = new GroupingData(i, end, lastlevel, uniqueIndex, false); rows.push(d); if (spreadsheet.getState(false).rowGroupingMax < d.level) { spreadsheet.getState().rowGroupingMax = d.level; } } } } /* * Same issue as with groups starting or ending on same row, only * process top level one. */ toRemove = new HashSet<GroupingData>(); for (int i = 0; i < data.size(); i++) { for (int j = i + 1; j < data.size(); j++) { GroupingData d1 = data.get(i); GroupingData d2 = data.get(j); if (spreadsheet.getState().rowGroupingInversed) { if (d1.startIndex == d2.startIndex) { toRemove.add(d2); } } else { if (d1.endIndex == d2.endIndex) { toRemove.add(d2); } } } } data.removeAll(toRemove); spreadsheet.getState().rowGroupingData = data; }
From source file:packtest.AligningCells.java
License:Apache License
/** * Center a text over multiple columns using ALIGN_CENTER_SELECTION * * @param wb the workbook/* w w w. jav a 2 s .co m*/ * @param row the row to create the cell in * @param start_column the column number to create the cell in and where the selection starts * @param end_column the column number where the selection ends * @param valign the horizontal alignment for the cell. * * @author Cristian Petrula, Romania */ private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column, short valign) { // Create cell style with ALIGN_CENTER_SELECTION XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(valign); // Create cells over the selected area for (int i = start_column; i <= end_column; i++) { XSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); } // Set value to the first cell XSSFCell cell = row.getCell(start_column); cell.setCellValue(new XSSFRichTextString("Align It")); // Make the selection CTRowImpl ctRow = (CTRowImpl) row.getCTRow(); // Add object with format start_coll:end_coll. For example 1:3 will span from // cell 1 to cell 3, where the column index starts with 0 // // You can add multiple spans for one row Object span = start_column + ":" + end_column; List<Object> spanList = new ArrayList<Object>(); spanList.add(span); //add spns to the row ctRow.setSpans(spanList); }
From source file:poi.xssf.usermodel.examples.AligningCells.java
License:Apache License
/** * Center a text over multiple columns using ALIGN_CENTER_SELECTION * * @param wb the workbook// ww w.j a v a 2s. c o m * @param row the row to create the cell in * @param start_column the column number to create the cell in and where the selection starts * @param end_column the column number where the selection ends * @param valign the horizontal alignment for the cell. * * @author Cristian Petrula, Romania */ private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column, short valign) { // Create cell style with ALIGN_CENTER_SELECTION XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(valign); // Create cells over the selected area for (int i = start_column; i <= end_column; i++) { XSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); } // Set value to the first cell XSSFCell cell = row.getCell(start_column); cell.setCellValue(new XSSFRichTextString("Align It")); // Make the selection CTRowImpl ctRow = (CTRowImpl) row.getCTRow(); List spanList = new ArrayList(); // Add object with format start_coll:end_coll. For example 1:3 will span from // cell 1 to cell 3, where the column index starts with 0 // // You can add multiple spans for one row Object span = start_column + ":" + end_column; spanList.add(span); //add spns to the row ctRow.setSpans(spanList); }