Search in sources :

Example 6 with CTSheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in project poi by apache.

the class XSSFWorkbook method createSheet.

/**
     * Create a new sheet for this Workbook and return the high level representation.
     * Use this to create new sheets.
     *
     * <p>
     *     Note that Excel allows sheet names up to 31 chars in length but other applications
     *     (such as OpenOffice) allow more. Some versions of Excel crash with names longer than 31 chars,
     *     others - truncate such names to 31 character.
     * </p>
     * <p>
     *     POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
     *     Example:
     *
     *     <pre><code>
     *     Sheet sheet = workbook.createSheet("My very long sheet name which is longer than 31 chars"); // will be truncated
     *     assert 31 == sheet.getSheetName().length();
     *     assert "My very long sheet name which i" == sheet.getSheetName();
     *     </code></pre>
     * </p>
     *
     * Except the 31-character constraint, Excel applies some other rules:
     * <p>
     * Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following characters:
     * <ul>
     * <li> 0x0000 </li>
     * <li> 0x0003 </li>
     * <li> colon (:) </li>
     * <li> backslash (\) </li>
     * <li> asterisk (*) </li>
     * <li> question mark (?) </li>
     * <li> forward slash (/) </li>
     * <li> opening square bracket ([) </li>
     * <li> closing square bracket (]) </li>
     * </ul>
     * The string MUST NOT begin or end with the single quote (') character.
     * </p>
     *
     * <p>
     * See {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
     *      for a safe way to create valid names
     * </p>
     * @param sheetname  sheetname to set for the sheet.
     * @return Sheet representing the new sheet.
     * @throws IllegalArgumentException if the name is null or invalid
     *  or workbook already contains a sheet with this name
     * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)
     */
@Override
public XSSFSheet createSheet(String sheetname) {
    if (sheetname == null) {
        throw new IllegalArgumentException("sheetName must not be null");
    }
    validateSheetName(sheetname);
    // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
    if (sheetname.length() > 31) {
        sheetname = sheetname.substring(0, 31);
    }
    WorkbookUtil.validateSheetName(sheetname);
    CTSheet sheet = addSheet(sheetname);
    int sheetNumber = 1;
    outerloop: while (true) {
        for (XSSFSheet sh : sheets) {
            sheetNumber = (int) Math.max(sh.sheet.getSheetId() + 1, sheetNumber);
        }
        // Bug 57165: We also need to check that the resulting file name is not already taken
        // this can happen when moving/cloning sheets
        String sheetName = XSSFRelation.WORKSHEET.getFileName(sheetNumber);
        for (POIXMLDocumentPart relation : getRelations()) {
            if (relation.getPackagePart() != null && sheetName.equals(relation.getPackagePart().getPartName().getName())) {
                // name is taken => try next one
                sheetNumber++;
                continue outerloop;
            }
        }
        // no duplicate found => use this one
        break;
    }
    RelationPart rp = createRelationship(XSSFRelation.WORKSHEET, XSSFFactory.getInstance(), sheetNumber, false);
    XSSFSheet wrapper = rp.getDocumentPart();
    wrapper.sheet = sheet;
    sheet.setId(rp.getRelationship().getId());
    sheet.setSheetId(sheetNumber);
    if (sheets.isEmpty()) {
        wrapper.setSelected(true);
    }
    sheets.add(wrapper);
    return wrapper;
}
Also used : CTSheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet) POIXMLDocumentPart(org.apache.poi.POIXMLDocumentPart)

Example 7 with CTSheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in project poi by apache.

the class XSSFWorkbook method setSheetOrder.

/**
     * sets the order of appearance for a given sheet.
     *
     * @param sheetname the name of the sheet to reorder
     * @param pos the position that we want to insert the sheet into (0 based)
     */
@Override
public void setSheetOrder(String sheetname, int pos) {
    int idx = getSheetIndex(sheetname);
    sheets.add(pos, sheets.remove(idx));
    // Reorder CTSheets
    CTSheets ct = workbook.getSheets();
    XmlObject cts = ct.getSheetArray(idx).copy();
    workbook.getSheets().removeSheet(idx);
    CTSheet newcts = ct.insertNewSheet(pos);
    newcts.set(cts);
    //notify sheets
    CTSheet[] sheetArray = ct.getSheetArray();
    for (int i = 0; i < sheetArray.length; i++) {
        sheets.get(i).sheet = sheetArray[i];
    }
    updateNamedRangesAfterSheetReorder(idx, pos);
    updateActiveSheetAfterSheetReorder(idx, pos);
}
Also used : CTSheets(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets) CTSheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet) XmlObject(org.apache.xmlbeans.XmlObject)

Example 8 with CTSheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in project poi by apache.

the class XSSFWorkbook method onSheetDelete.

/**
     * Gracefully remove references to the sheet being deleted
     *
     * @param index the 0-based index of the sheet to delete
     */
private void onSheetDelete(int index) {
    //delete the CTSheet reference from workbook.xml
    workbook.getSheets().removeSheet(index);
    //calculation chain is auxiliary, remove it as it may contain orphan references to deleted cells
    if (calcChain != null) {
        removeRelation(calcChain);
        calcChain = null;
    }
    //adjust indices of names ranges
    List<XSSFName> toRemove = new ArrayList<XSSFName>();
    for (XSSFName nm : namedRanges) {
        CTDefinedName ct = nm.getCTName();
        if (!ct.isSetLocalSheetId()) {
            continue;
        }
        if (ct.getLocalSheetId() == index) {
            toRemove.add(nm);
        } else if (ct.getLocalSheetId() > index) {
            // Bump down by one, so still points at the same sheet
            ct.setLocalSheetId(ct.getLocalSheetId() - 1);
        }
    }
    for (XSSFName nm : toRemove) {
        removeName(nm);
    }
}
Also used : ArrayList(java.util.ArrayList) CTDefinedName(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName)

Example 9 with CTSheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in project poi by apache.

the class XSSFWorkbook method addSheet.

private CTSheet addSheet(String sheetname) {
    CTSheet sheet = workbook.getSheets().addNewSheet();
    sheet.setName(sheetname);
    return sheet;
}
Also used : CTSheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet)

Example 10 with CTSheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet in project poi by apache.

the class XSSFWorkbook method isSheetVeryHidden.

@Override
public boolean isSheetVeryHidden(int sheetIx) {
    validateSheetIndex(sheetIx);
    CTSheet ctSheet = sheets.get(sheetIx).sheet;
    return ctSheet.getState() == STSheetState.VERY_HIDDEN;
}
Also used : CTSheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet)

Aggregations

CTSheet (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet)9 POIXMLDocumentPart (org.apache.poi.POIXMLDocumentPart)2 FileInputStream (java.io.FileInputStream)1 InputStream (java.io.InputStream)1 ArrayList (java.util.ArrayList)1 HashMap (java.util.HashMap)1 ArrayListValuedHashMap (org.apache.commons.collections4.multimap.ArrayListValuedHashMap)1 POIXMLException (org.apache.poi.POIXMLException)1 ReadOnlySharedStringsTable (org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable)1 XSSFSheetXMLHandler (org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler)1 CalculationChain (org.apache.poi.xssf.model.CalculationChain)1 ExternalLinksTable (org.apache.poi.xssf.model.ExternalLinksTable)1 MapInfo (org.apache.poi.xssf.model.MapInfo)1 SharedStringsTable (org.apache.poi.xssf.model.SharedStringsTable)1 StylesTable (org.apache.poi.xssf.model.StylesTable)1 ThemesTable (org.apache.poi.xssf.model.ThemesTable)1 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)1 XmlException (org.apache.xmlbeans.XmlException)1 XmlObject (org.apache.xmlbeans.XmlObject)1 CTDefinedName (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName)1