Examples with ExternalLinksTable org.apache.poi.xssf.model.ExternalLinksTable used on opensource projects

Example 1 with ExternalLinksTable

use of org.apache.poi.xssf.model.ExternalLinksTable in project poi by apache.

the class BaseXSSFEvaluationWorkbook method getExternalSheet.

@Override
public ExternalSheet getExternalSheet(String firstSheetName, String lastSheetName, int externalWorkbookNumber) {
    String workbookName;
    if (externalWorkbookNumber > 0) {
        // External reference - reference is 1 based, link table is 0 based
        int linkNumber = externalWorkbookNumber - 1;
        ExternalLinksTable linkTable = _uBook.getExternalLinksTable().get(linkNumber);
        workbookName = linkTable.getLinkedFileName();
    } else {
        // Internal reference
        workbookName = null;
    }
    if (lastSheetName == null || firstSheetName.equals(lastSheetName)) {
        return new ExternalSheet(workbookName, firstSheetName);
    } else {
        return new ExternalSheetRange(workbookName, firstSheetName, lastSheetName);
    }
}

Example 2 with ExternalLinksTable

use of org.apache.poi.xssf.model.ExternalLinksTable in project poi by apache.

the class BaseXSSFEvaluationWorkbook method resolveBookIndex.

private int resolveBookIndex(String bookName) {
    // Strip the [] wrapper, if still present
    if (bookName.startsWith("[") && bookName.endsWith("]")) {
        bookName = bookName.substring(1, bookName.length() - 2);
    }
    // Is it already in numeric form?
    try {
        return Integer.parseInt(bookName);
    } catch (NumberFormatException e) {
    }
    // Look up an External Link Table for this name
    List<ExternalLinksTable> tables = _uBook.getExternalLinksTable();
    int index = findExternalLinkIndex(bookName, tables);
    if (index != -1)
        return index;
    // Is it an absolute file reference?
    if (bookName.startsWith("'file:///") && bookName.endsWith("'")) {
        String relBookName = bookName.substring(bookName.lastIndexOf('/') + 1);
        // Trailing '
        relBookName = relBookName.substring(0, relBookName.length() - 1);
        // Try with this name
        index = findExternalLinkIndex(relBookName, tables);
        if (index != -1)
            return index;
        // If we get here, it's got no associated proper links yet
        // So, add the missing reference and return
        // Note - this is really rather nasty...
        ExternalLinksTable fakeLinkTable = new FakeExternalLinksTable(relBookName);
        tables.add(fakeLinkTable);
        // 1 based results, 0 = current workbook
        return tables.size();
    }
    // Not properly referenced
    throw new RuntimeException("Book not linked for filename " + bookName);
}

Example 3 with ExternalLinksTable

use of org.apache.poi.xssf.model.ExternalLinksTable in project carina by qaprosoft.

the class XLSParser method parseCellLinks.

public static XLSChildTable parseCellLinks(Cell cell, Workbook wb, Sheet sheet) {
    if (cell == null)
        return null;
    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        if (cell.getCellFormula().contains("#This Row")) {
            if (cell.getCellFormula().contains("!")) {
                // Parse link to the cell with table name in the external doc([2]!Table1[[#This Row],[Header6]])
                List<String> paths = Arrays.asList(cell.getCellFormula().split("!"));
                int externalLinkNumber = Integer.valueOf(paths.get(0).replaceAll("\\D+", "")) - 1;
                String tableName = paths.get(1).split("\\[")[0];
                if (wb instanceof XSSFWorkbook) {
                    ExternalLinksTable link = ((XSSFWorkbook) wb).getExternalLinksTable().get(externalLinkNumber);
                    File file = new File(XLSCache.getWorkbookPath(wb));
                    XSSFWorkbook childWb = (XSSFWorkbook) XLSCache.getWorkbook(file.getParent() + "/" + link.getLinkedFileName());
                    if (childWb == null)
                        throw new DataLoadingException(String.format("WorkBook '%s' doesn't exist!", link.getLinkedFileName()));
                    for (int i = 0; i < childWb.getNumberOfSheets(); i++) {
                        XSSFSheet childSheet = childWb.getSheetAt(i);
                        for (XSSFTable table : childSheet.getTables()) {
                            if (table.getName().equals(tableName)) {
                                return createChildTable(childSheet, cell.getRowIndex());
                            }
                        }
                    }
                } else {
                    throw new DataLoadingException("Unsupported format. External links supports only for .xlsx documents.");
                }
            } else {
                // Parse link to the cell with table name in the same doc(=Table1[[#This Row],[Header6]])
                List<String> paths = Arrays.asList(cell.getCellFormula().replace("=", "").split("\\["));
                if (wb instanceof XSSFWorkbook) {
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        XSSFSheet childSheet = (XSSFSheet) wb.getSheetAt(i);
                        for (XSSFTable table : childSheet.getTables()) {
                            if (table.getName().equals(paths.get(0))) {
                                return createChildTable(childSheet, cell.getRowIndex());
                            }
                        }
                    }
                } else {
                    throw new DataLoadingException("Unsupported format. Links with table name supports only for .xlsx documents.");
                }
            }
        } else {
            String cellValue = cell.getCellFormula().replace("=", "").replace("[", "").replace("]", "!").replace("'", "");
            List<String> paths = Arrays.asList(cellValue.split("!"));
            int rowNumber = 0;
            Sheet childSheet = null;
            switch(paths.size()) {
                // Parse link to the cell in the same sheet(=A4)
                case 1:
                    rowNumber = Integer.valueOf(paths.get(0).replaceAll("\\D+", "")) - 1;
                    return createChildTable(sheet, rowNumber);
                // Parse link to the cell in another sheet in the same doc(=SheetName!A4)
                case 2:
                    childSheet = wb.getSheet(paths.get(0));
                    if (childSheet == null)
                        throw new DataLoadingException(String.format("Sheet '%s' doesn't exist!", paths.get(0)));
                    rowNumber = Integer.valueOf(paths.get(1).replaceAll("\\D+", "")) - 1;
                    return createChildTable(childSheet, rowNumber);
                // Parse link to the cell in another doc(=[2]SheetName!A4)
                case 3:
                    if (wb instanceof XSSFWorkbook) {
                        ExternalLinksTable link = ((XSSFWorkbook) wb).getExternalLinksTable().get(Integer.valueOf(paths.get(0)) - 1);
                        File file = new File(XLSCache.getWorkbookPath(wb));
                        XSSFWorkbook childWb = (XSSFWorkbook) XLSCache.getWorkbook(file.getParent() + "/" + link.getLinkedFileName());
                        if (childWb == null)
                            throw new DataLoadingException(String.format("WorkBook '%s' doesn't exist!", paths.get(0)));
                        childSheet = childWb.getSheet(paths.get(1));
                        if (childSheet == null)
                            throw new DataLoadingException(String.format("Sheet '%s' doesn't exist!", paths.get(0)));
                        rowNumber = Integer.valueOf(paths.get(2).replaceAll("\\D+", "")) - 1;
                        return createChildTable(childSheet, rowNumber);
                    } else {
                        throw new DataLoadingException("Unsupported format. External links supports only for .xlsx documents.");
                    }
                default:
                    return null;
            }
        }
    }
    return null;
}

Example 4 with ExternalLinksTable

use of org.apache.poi.xssf.model.ExternalLinksTable in project poi by apache.

the class XSSFWorkbook method onDocumentRead.

@Override
protected void onDocumentRead() throws IOException {
    try {
        WorkbookDocument doc = WorkbookDocument.Factory.parse(getPackagePart().getInputStream(), DEFAULT_XML_OPTIONS);
        this.workbook = doc.getWorkbook();
        ThemesTable theme = null;
        Map<String, XSSFSheet> shIdMap = new HashMap<String, XSSFSheet>();
        Map<String, ExternalLinksTable> elIdMap = new HashMap<String, ExternalLinksTable>();
        for (RelationPart rp : getRelationParts()) {
            POIXMLDocumentPart p = rp.getDocumentPart();
            if (p instanceof SharedStringsTable) {
                sharedStringSource = (SharedStringsTable) p;
            } else if (p instanceof StylesTable) {
                stylesSource = (StylesTable) p;
            } else if (p instanceof ThemesTable) {
                theme = (ThemesTable) p;
            } else if (p instanceof CalculationChain) {
                calcChain = (CalculationChain) p;
            } else if (p instanceof MapInfo) {
                mapInfo = (MapInfo) p;
            } else if (p instanceof XSSFSheet) {
                shIdMap.put(rp.getRelationship().getId(), (XSSFSheet) p);
            } else if (p instanceof ExternalLinksTable) {
                elIdMap.put(rp.getRelationship().getId(), (ExternalLinksTable) p);
            }
        }
        boolean packageReadOnly = (getPackage().getPackageAccess() == PackageAccess.READ);
        if (stylesSource == null) {
            // Create Styles if it is missing
            if (packageReadOnly) {
                stylesSource = new StylesTable();
            } else {
                stylesSource = (StylesTable) createRelationship(XSSFRelation.STYLES, XSSFFactory.getInstance());
            }
        }
        stylesSource.setWorkbook(this);
        stylesSource.setTheme(theme);
        if (sharedStringSource == null) {
            // Create SST if it is missing
            if (packageReadOnly) {
                sharedStringSource = new SharedStringsTable();
            } else {
                sharedStringSource = (SharedStringsTable) createRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.getInstance());
            }
        }
        // Load individual sheets. The order of sheets is defined by the order
        //  of CTSheet elements in the workbook
        sheets = new ArrayList<XSSFSheet>(shIdMap.size());
        for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) {
            parseSheet(shIdMap, ctSheet);
        }
        // Load the external links tables. Their order is defined by the order 
        //  of CTExternalReference elements in the workbook
        externalLinks = new ArrayList<ExternalLinksTable>(elIdMap.size());
        if (this.workbook.isSetExternalReferences()) {
            for (CTExternalReference er : this.workbook.getExternalReferences().getExternalReferenceArray()) {
                ExternalLinksTable el = elIdMap.get(er.getId());
                if (el == null) {
                    logger.log(POILogger.WARN, "ExternalLinksTable with r:id " + er.getId() + " was defined, but didn't exist in package, skipping");
                    continue;
                }
                externalLinks.add(el);
            }
        }
        // Process the named ranges
        reprocessNamedRanges();
    } catch (XmlException e) {
        throw new POIXMLException(e);
    }
}

Example 5 with ExternalLinksTable

use of org.apache.poi.xssf.model.ExternalLinksTable in project poi by apache.

the class BaseXSSFEvaluationWorkbook method getExternalName.

@Override
public ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber) {
    if (externalWorkbookNumber > 0) {
        // External reference - reference is 1 based, link table is 0 based
        int linkNumber = externalWorkbookNumber - 1;
        ExternalLinksTable linkTable = _uBook.getExternalLinksTable().get(linkNumber);
        for (org.apache.poi.ss.usermodel.Name name : linkTable.getDefinedNames()) {
            if (name.getNameName().equals(nameName)) {
                // HSSF returns one sheet higher than normal, and various bits
                //  of the code assume that. So, make us match that behaviour!
                int nameSheetIndex = name.getSheetIndex() + 1;
                // Should support XSSF stuff lookups
                return new ExternalName(nameName, -1, nameSheetIndex);
            }
        }
        throw new IllegalArgumentException("Name '" + nameName + "' not found in " + "reference to " + linkTable.getLinkedFileName());
    } else {
        // Internal reference
        int nameIdx = _uBook.getNameIndex(nameName);
        // TODO Is this right?
        return new ExternalName(nameName, nameIdx, 0);
    }
}