Example usage for org.apache.poi.xssf.usermodel XSSFSheet getCTWorksheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getCTWorksheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getCTWorksheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getCTWorksheet.

Prototype

@Internal
public CTWorksheet getCTWorksheet()

Source Link

Document

Provide access to the CTWorksheet bean holding this sheet's data

Usage

From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java

@SuppressWarnings("deprecation")
private static boolean isColumnGroupHiddenByParent(XSSFSheet sheet, int idx) {
    CTCols cols = sheet.getCTWorksheet().getColsArray(0);
    // Look out outline details of end
    int endLevel = 0;
    boolean endHidden = false;
    // int endOfOutlineGroupIdx = findEndOfColumnOutlineGroup(sheet,idx);
    int endOfOutlineGroupIdx = (Integer) callSheetMethod("findEndOfColumnOutlineGroup", sheet, idx);
    CTCol[] colArray = cols.getColArray();
    /** start *///from  w w w  .  ja  va  2  s .c o  m
    if (endOfOutlineGroupIdx + 1 < colArray.length) {
        /** end */
        CTCol nextInfo = colArray[endOfOutlineGroupIdx + 1];
        if ((Boolean) callSheetMethod("isAdjacentBefore", sheet, colArray[endOfOutlineGroupIdx], nextInfo)) {
            endLevel = nextInfo.getOutlineLevel();
            endHidden = nextInfo.getHidden();
        }
    }
    // Look out outline details of start
    int startLevel = 0;
    boolean startHidden = false;
    // int startOfOutlineGroupIdx = findStartOfColumnOutlineGroup(idx);
    int startOfOutlineGroupIdx = (Integer) callSheetMethod("findStartOfColumnOutlineGroup", sheet, idx);
    if (startOfOutlineGroupIdx > 0) {
        CTCol prevInfo = colArray[startOfOutlineGroupIdx - 1];

        if ((Boolean) callSheetMethod("isAdjacentBefore", sheet, prevInfo, colArray[startOfOutlineGroupIdx])) {
            startLevel = prevInfo.getOutlineLevel();
            startHidden = prevInfo.getHidden();
        }

    }
    if (endLevel > startLevel) {
        return endHidden;
    }
    return startHidden;
}

From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java

private static boolean isRowsInverted(XSSFSheet sheet) {
    boolean inverted = false;
    try {//from w  w w . j a  v  a2s  .  co m
        inverted = sheet.getCTWorksheet().getSheetPr().getOutlinePr().isSetSummaryBelow();
    } catch (NullPointerException IGNORE) {
        // fine
    }

    return inverted;
}

From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java

public static void collapseColumn(XSSFSheet sheet, int columnNumber) {
    CTCols cols = sheet.getCTWorksheet().getColsArray(0);
    CTCol col = sheet.getColumnHelper().getColumn(columnNumber, false);
    int colInfoIx = sheet.getColumnHelper().getIndexOfColumn(cols, col);
    if (colInfoIx == -1) {
        return;//from   w ww.  j  a va 2  s.c o  m
    }
    // Find the start of the group.
    int groupStartColInfoIx = (Integer) callSheetMethod("findStartOfColumnOutlineGroup", sheet, colInfoIx);

    /** START */
    // Hide all the columns until the end of the group
    int lastColMax = (Integer) callSheetMethod("setGroupHidden", sheet,
            new Object[] { groupStartColInfoIx, col.getOutlineLevel(), true });
    /** END */

    // write collapse field
    callSheetMethod("setColumn", sheet, new Object[] { lastColMax + 1, 0, null, null, Boolean.TRUE });
}

From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java

public static short expandColumn(XSSFSheet sheet, int columnIndex) {
    CTCols cols = sheet.getCTWorksheet().getColsArray(0);
    CTCol col = sheet.getColumnHelper().getColumn(columnIndex, false);
    int colInfoIx = sheet.getColumnHelper().getIndexOfColumn(cols, col);

    int idx = (Integer) callSheetMethod("findColInfoIdx", sheet,
            new Object[] { (int) col.getMax(), colInfoIx });
    if (idx == -1) {
        return -1;
    }//from   w w w.ja  va  2s  . co m

    // If it is already expanded do nothing.
    if (!isColumnGroupCollapsed(sheet, idx)) {
        return -1;
    }

    // Find the start/end of the group.
    int startIdx = (Integer) callSheetMethod("findStartOfColumnOutlineGroup", sheet, idx);
    int endIdx = (Integer) callSheetMethod("findEndOfColumnOutlineGroup", sheet, idx);

    // expand:
    // colapsed bit 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
    // uncollapse contained groups
    CTCol[] colArray = cols.getColArray();
    @SuppressWarnings("unused")
    CTCol columnInfo = colArray[endIdx];
    short expandedLevel = -1;
    if (!isColumnGroupHiddenByParent(sheet, idx)) {
        /** Start */
        short outlineLevel = col.getOutlineLevel();
        /** end */
        boolean nestedGroup = false;
        for (int i = startIdx; i <= endIdx; i++) {
            CTCol ci = colArray[i];
            if (outlineLevel == ci.getOutlineLevel()) {
                ci.unsetHidden();
                if (nestedGroup) {
                    nestedGroup = false;
                    ci.setCollapsed(true);
                }
                expandedLevel = outlineLevel;
            } else {
                nestedGroup = true;
            }
        }
    }

    /** start */
    // // Write collapse flag (stored in a single col info record after this
    // // outline group)
    // callSheetMethod("setColumn", sheet,
    // new Object[] { (int) columnInfo.getMax() + 1, null, null,
    // Boolean.FALSE, Boolean.FALSE });
    /** end */
    return expandedLevel;
}

From source file:com.vaadin.addon.spreadsheet.GroupingUtil.java

private static boolean isColumnGroupCollapsed(XSSFSheet sheet, int idx) {

    /**//from  w w w.j a v  a2s  .c o  m
     * The APIDoc for this method says that cols work as rows, with the
     * 'collapsed' attribute being after the col group. It isn't, the
     * 'hidden' attribute is used instead. Hence, rewrite:
     */

    CTCols cols = sheet.getCTWorksheet().getColsArray(0);

    CTCol col = cols.getColArray(idx);
    return col.isSetHidden();

    /**
     * original code for reference
     */

    // CTCols cols = sheet.getCTWorksheet().getColsArray(0);
    // CTCol[] colArray = cols.getColArray();
    // int endOfOutlineGroupIdx = findEndOfColumnOutlineGroup(sheet, idx);
    // int nextColInfoIx = endOfOutlineGroupIdx + 1;
    // if (nextColInfoIx >= colArray.length) {
    // return false;
    // }
    // CTCol nextColInfo = colArray[nextColInfoIx];
    //
    // CTCol col = colArray[endOfOutlineGroupIdx];
    // if (!isAdjacentBefore(col, nextColInfo)) {
    // return false;
    // }
    //
    // return nextColInfo.getCollapsed();
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Called when a grouping level header is clicked
 * /*from   w  w  w.  jav  a 2 s  . c  o  m*/
 * @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.vodafone.poms.ii.helpers.ExportManager.java

private static void addObjectToShape(XSSFSheet sh, XSSFClientAnchor imgAnchor, int shapeId, String objRelId,
        String imgRelId, String progId) {
    CTWorksheet cwb = sh.getCTWorksheet();
    CTOleObjects oo = cwb.isSetOleObjects() ? cwb.getOleObjects() : cwb.addNewOleObjects();

    CTOleObject ole1 = oo.addNewOleObject();
    ole1.setProgId(progId);/*from  w  w w .  jav a 2s  .c  o  m*/
    ole1.setShapeId(shapeId);
    ole1.setId(objRelId);

    XmlCursor cur1 = ole1.newCursor();
    cur1.toEndToken();
    cur1.beginElement("objectPr", XSSFRelation.NS_SPREADSHEETML);
    cur1.insertAttributeWithValue("id", relationshipsNS, imgRelId);
    cur1.insertAttributeWithValue("defaultSize", "0");
    cur1.beginElement("anchor", XSSFRelation.NS_SPREADSHEETML);
    cur1.insertAttributeWithValue("moveWithCells", "1");

    CTTwoCellAnchor anchor = CTTwoCellAnchor.Factory.newInstance();
    anchor.setFrom(imgAnchor.getFrom());
    anchor.setTo(imgAnchor.getTo());

    XmlCursor cur2 = anchor.newCursor();
    cur2.copyXmlContents(cur1);
    cur2.dispose();

    cur1.toParent();
    cur1.toFirstChild();
    cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "from"));
    cur1.toNextSibling();
    cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "to"));

    cur1.dispose();
}

From source file:migrationextraction.MainWindowUI.java

private void processBasicInfoSheet(XSSFWorkbook wb) throws InvalidFormatException {
    try {//from  w ww  . j  a v a 2 s .c o m
        XSSFSheet sheet = wb.getSheet("1. Supplier Basic Info");
        CTWorksheet ctws = sheet.getCTWorksheet();
        /*String xml10pattern = "[^"
            + "\u0009\r\n"
            + "\u0020-\uD7FF"
            + "\uE000-\uFFFD"
            + "\ud800\udc00-\udbff\udfff"
            + "]";
        String xml = ctws.toString().replaceAll(xml10pattern, "");*/
        //Get embedded objects positions.
        SAXBuilder saxB = new SAXBuilder();
        org.jdom2.Document doc = saxB.build(new StringReader(ctws.toString()));

        List<OleObject> oleObjects = new ArrayList<>();
        List<Element> elements = doc.getRootElement().getChildren();
        elements.stream().forEach((element) -> {
            if (element.getName().equals("oleObjects")) {
                element.getChildren();
                getXMLUntilOle(element.getContent(), 0);
                //Get content
                System.out.println("");
                //for(Content content:element.getContent())
            }
        });
    } catch (JDOMException | IOException ex) {
        Logger.getLogger(MainWindowUI.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?/*from  w  ww .  ja va 2s .  c  om*/
 * 
 * @param fromSheet 
 * @param rangeAddress 
 * @param toSheet 
 * @param toRowNum 
 * @param toColumnNum 
 * @param clearFromRange 
 */
public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum,
        int toColumnNum, boolean clearFromRange) {

    if (fromSheet == null || rangeAddress == null || toSheet == null) {
        return;
    }

    int fromRowIndex = rangeAddress.getFirstRow();
    int fromColumnIndex = rangeAddress.getFirstColumn();

    int rowNumOffset = toRowNum - fromRowIndex;
    int columnNumOffset = toColumnNum - fromColumnIndex;

    // 
    CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset,
            rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset,
            rangeAddress.getLastColumn() + columnNumOffset);

    Workbook fromWorkbook = fromSheet.getWorkbook();
    Sheet baseSheet = fromSheet;

    Sheet tmpSheet = null;
    // ?????
    if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) {
        // ?
        tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME);
        if (tmpSheet == null) {
            tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME);
        }
        baseSheet = tmpSheet;

        int lastColNum = getLastColNum(fromSheet);
        for (int i = 0; i <= lastColNum; i++) {
            tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }

        copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(),
                false);

        // ?
        if (clearFromRange) {
            clearRange(fromSheet, rangeAddress);
        }
    }

    // ????
    Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress);
    // ???
    clearRange(toSheet, toAddress);

    // ???
    for (CellRangeAddress mergeAddress : targetCellSet) {

        toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset,
                mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset,
                mergeAddress.getLastColumn() + columnNumOffset));

    }

    for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) {
        // 
        Row fromRow = baseSheet.getRow(i);
        if (fromRow == null) {
            continue;
        }
        Row row = toSheet.getRow(i + rowNumOffset);
        if (row == null) {
            row = toSheet.createRow(i + rowNumOffset);
            row.setHeight((short) 0);
        }

        // ??????
        int fromRowHeight = fromRow.getHeight();
        int toRowHeight = row.getHeight();
        if (toRowHeight < fromRowHeight) {
            row.setHeight(fromRow.getHeight());
        }

        ColumnHelper columnHelper = null;
        if (toSheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook()
                    .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet));
            CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
            columnHelper = new ColumnHelper(ctWorksheet);
        }

        for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) {
            Cell fromCell = fromRow.getCell(j);
            if (fromCell == null) {
                continue;
            }
            int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns();
            if (toSheet instanceof XSSFSheet) {
                maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns();
            }
            if (j + columnNumOffset >= maxColumn) {
                break;
            }
            Cell cell = row.getCell(j + columnNumOffset);
            if (cell == null) {
                cell = row.createCell(j + columnNumOffset);
                if (toSheet instanceof XSSFSheet) {
                    // XSSF??????????
                    CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false);
                    if (col == null || !col.isSetWidth()) {
                        toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j));
                    }
                }
            }

            // ?
            copyCell(fromCell, cell);

            // ??????
            int fromColumnWidth = baseSheet.getColumnWidth(j);
            int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset);

            if (toColumnWidth < fromColumnWidth) {
                toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j));
            }
        }
    }

    if (tmpSheet != null) {
        // 
        fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet));
    } else if (clearFromRange) {
        // ????
        clearRange(fromSheet, rangeAddress);
    }

}

From source file:org.keyboardplaying.xtt.xlsx.XlsxNormalizer.java

License:Apache License

private void normalizeSheet(XSSFSheet sheet, String activeRange) {
    sheet.setZoom(ZOOM_100);/* w  w  w. j a  v a2  s  .  c o m*/
    sheet.setDisplayGridlines(false);
    sheet.setSelected(false);

    PaneInformation pane = sheet.getPaneInformation();
    if (pane == null) {
        /* Reset cell */
        sheet.setActiveCell(new CellAddress(activeRange));
        /* Reset view */
        sheet.getCTWorksheet().getSheetViews().getSheetViewArray(0)
                .setTopLeftCell(CellAddress.A1.formatAsString());
    } else {
        /* Reset cell */
        sheet.createFreezePane(0, 0); // Remove panes
        sheet.setActiveCell(new CellAddress(activeRange));
        sheet.createFreezePane(pane.getVerticalSplitLeftColumn(), pane.getHorizontalSplitTopRow()); // Reset panes
        /* Reset view */
        sheet.showInPane(pane.getHorizontalSplitPosition(), pane.getVerticalSplitPosition());
    }
}