Pkg.XLSB — API for parsing Microsoft Excel XLSB files

Overview

The Pkg.XLSB module contains the API for parsing Microsoft Excel XLSB (binary) files.

Parsing an XLSB File

The following code example demonstrates how to parse an XLSB file and list its sheets, defined names and shared strings:

from Pro.Core import *
from Pkg.XLSB import *

def parseXLSB(fname):
    c = createContainerFromFile(fname)
    if c.isNull():
        return
    obj = XLSBObject()
    if not obj.Load(c):
        return
    obj.SetEntries(obj.RetrieveEntries())
    # list all sheets
    for sheet in obj.GetSheets():
        print("sheet:", sheet["name"], sheet["type"], sheet["state"])
    # list defined names
    for name, formula in obj.GetDefinedNames():
        print("name:", name, "=", formula)
    # list shared strings
    for s in obj.GetSharedStrings():
        print("string:", s)

Detecting Macro or Hidden Sheets

The following code example demonstrates how to detect Excel 4.0 macro sheets or hidden sheets:

from Pro.Core import *
from Pkg.XLSB import *

def inspectXLSB(fname):
    c = createContainerFromFile(fname)
    if c.isNull():
        return
    obj = XLSBObject()
    if not obj.Load(c):
        return
    obj.SetEntries(obj.RetrieveEntries())
    if obj.HasMacroSheets():
        print("warning: workbook contains macro sheets")
    if obj.HasHiddenSheets():
        print("warning: workbook contains hidden sheets")

Module API

Pkg.XLSB module API.

Classes:

XLSBObject()

This class represents an Excel XLSB (binary) file.

class XLSBObject

Bases: Pro.Zip.ZipObject

This class represents an Excel XLSB (binary) file.

Methods:

CreateSpreadsheetWorkspace()	Creates a Silicon spreadsheet workspace that can be used for macro emulation.
GetContentTypes()	Retrieves the [Content_Types].xml file from the XLSB package.
GetDefinedNames()	Retrieves all defined names in the workbook.
GetFile(name)	Retrieves a file from the XLSB package.
GetSharedStrings()	Retrieves the shared strings used in the workbook.
GetSharedStringsPart()	Retrieves the binary shared strings part from the XLSB package.
GetSheets()	Retrieves information about all sheets in the workbook.
GetStylesPart()	Retrieves the binary styles part from the XLSB package.
GetTypes()	Retrieves a mapping from MIME content types to their associated file names within the XLSB package.
GetWorkbookPart()	Retrieves the binary workbook part (xl/workbook.bin) from the XLSB package.
GetWorkbookRels()	Retrieves the relationships XML file associated with the workbook.
GetWorksheetPart(idx)	Retrieves the binary worksheet part for the given index (1-based).
GetWorksheetRels(idx)	Retrieves the relationships part for the worksheet at the given index (1-based).
GetXMLFile(fname)	Retrieves and parses an XML file from the XLSB package.
GetXMLFromFile(c)	Parses an XML file from a given container.
HasHiddenSheets()	Checks whether the workbook contains any hidden or very-hidden sheets.
HasMacroSheets()	Checks whether the workbook contains any Excel 4.0 macro sheets.

CreateSpreadsheetWorkspace() → Pro.SiliconSpreadsheet.SiliconSpreadsheetWorkspace

Creates a Silicon spreadsheet workspace that can be used for macro emulation.

Returns

Returns the workspace.

Return type

SiliconSpreadsheetWorkspace

GetContentTypes() → Optional[Pro.Core.NTXml]

Retrieves the [Content_Types].xml file from the XLSB package.

Returns

Returns an XML object if successful; otherwise returns None.

Return type

Optional[NTXml]

See also GetTypes().

GetDefinedNames() → List[Tuple[str, str]]

Retrieves all defined names in the workbook.

Returns

Returns a list of tuples containing defined names and their associated formulas.

Return type

List[Tuple[str, str]]

GetFile(name: str) → Pro.Core.NTContainer

Retrieves a file from the XLSB package.

Parameters

name (str) – The name of the file within the XLSB package to retrieve.

Returns

Returns a container for the specified file within the XLSB package.

Return type

NTContainer

GetSharedStrings() → List[str]

Retrieves the shared strings used in the workbook.

Returns

Returns a list of shared strings.

Return type

List[str]

GetSharedStringsPart() → Pro.Core.NTContainer

Retrieves the binary shared strings part from the XLSB package.

Returns

Returns a container for the shared strings part.

Return type

NTContainer

See also GetSharedStrings().

GetSheets() → List[Dict[str, str]]

Retrieves information about all sheets in the workbook.

Each entry contains the keys name, type (e.g. worksheet or macrosheet), loc, rId and state (visible, hidden or veryhidden).

Returns

Returns a list of dictionaries, each containing information about a sheet.

Return type

List[Dict[str, str]]

See also HasMacroSheets() and HasHiddenSheets().

GetStylesPart() → Pro.Core.NTContainer

Retrieves the binary styles part from the XLSB package.

Returns

Returns a container for the styles part.

Return type

NTContainer

GetTypes() → Dict[str, str]

Retrieves a mapping from MIME content types to their associated file names within the XLSB package.

Returns

Returns a dictionary mapping MIME content types to file names.

Return type

Dict[str, str]

See also GetContentTypes().

GetWorkbookPart() → Pro.Core.NTContainer

Retrieves the binary workbook part (xl/workbook.bin) from the XLSB package.

Returns

Returns a container for the workbook part.

Return type

NTContainer

See also GetWorkbookRels().

GetWorkbookRels() → Optional[Pro.Core.NTXml]

Retrieves the relationships XML file associated with the workbook.

Returns

Returns an XML object if successful; otherwise returns None.

Return type

Optional[NTXml]

See also GetWorkbookPart().

GetWorksheetPart(idx: int) → Pro.Core.NTContainer

Retrieves the binary worksheet part for the given index (1-based).

Parameters

idx (int) – The 1-based worksheet index.

Returns

Returns a container for the worksheet part.

Return type

NTContainer

See also GetWorksheetRels().

GetWorksheetRels(idx: int) → Pro.Core.NTContainer

Retrieves the relationships part for the worksheet at the given index (1-based).

Parameters

idx (int) – The 1-based worksheet index.

Returns

Returns a container for the worksheet relationships part.

Return type

NTContainer

See also GetWorksheetPart().

GetXMLFile(fname: str) → Optional[Pro.Core.NTXml]

Retrieves and parses an XML file from the XLSB package.

Parameters

fname (str) – The name of the XML file within the XLSB package to retrieve.

Returns

Returns an XML object if successful; otherwise returns None.

Return type

Optional[NTXml]

GetXMLFromFile(c: Pro.Core.NTContainer) → Optional[Pro.Core.NTXml]

Parses an XML file from a given container.

Parameters

c (NTContainer) – The container representing the file from which to extract XML content.

Returns

Returns an XML object if successful; otherwise returns None.

Return type

Optional[NTXml]

HasHiddenSheets() → bool

Checks whether the workbook contains any hidden or very-hidden sheets.

Returns

Returns True if at least one sheet is hidden; otherwise returns False.

Return type

bool

See also GetSheets().

HasMacroSheets() → bool

Checks whether the workbook contains any Excel 4.0 macro sheets.

Returns

Returns True if at least one sheet is a macro sheet; otherwise returns False.

Return type

bool

See also GetSheets().