Source code for openpyxl.reader.excel

# Copyright (c) 2010-2023 openpyxl

"""Read an xlsx file into Python"""

# Python stdlib imports
from zipfile import ZipFile, ZIP_DEFLATED
from io import BytesIO
import os.path
import warnings

from openpyxl.pivot.table import TableDefinition

# Allow blanket setting of KEEP_VBA for testing
    from ..tests import KEEP_VBA
except ImportError:
    KEEP_VBA = False

# package imports
from openpyxl.utils.exceptions import InvalidFileException
from openpyxl.xml.constants import (
from openpyxl.cell import MergedCell
from openpyxl.comments.comment_sheet import CommentSheet

from .strings import read_string_table, read_rich_text
from .workbook import WorkbookParser
from openpyxl.styles.stylesheet import apply_stylesheet

from openpyxl.packaging.core import DocumentProperties
from openpyxl.packaging.custom import CustomPropertyList
from openpyxl.packaging.manifest import Manifest, Override

from openpyxl.packaging.relationship import (

from openpyxl.worksheet._read_only import ReadOnlyWorksheet
from openpyxl.worksheet._reader import WorksheetReader
from openpyxl.chartsheet import Chartsheet
from openpyxl.worksheet.table import Table
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing

from openpyxl.xml.functions import fromstring

from .drawings import find_images

SUPPORTED_FORMATS = ('.xlsx', '.xlsm', '.xltx', '.xltm')

def _validate_archive(filename):
    Does a first check whether filename is a string or a file-like
    object. If it is a string representing a filename, a check is done
    for supported formats by checking the given file-extension. If the
    file-extension is not in SUPPORTED_FORMATS an InvalidFileException
    will raised. Otherwise the filename (resp. file-like object) will
    forwarded to zipfile.ZipFile returning a ZipFile-Instance.
    is_file_like = hasattr(filename, 'read')
    if not is_file_like:
        file_format = os.path.splitext(filename)[-1].lower()
        if file_format not in SUPPORTED_FORMATS:
            if file_format == '.xls':
                msg = ('openpyxl does not support the old .xls file format, '
                       'please use xlrd to read this file, or convert it to '
                       'the more recent .xlsx file format.')
            elif file_format == '.xlsb':
                msg = ('openpyxl does not support binary format .xlsb, '
                       'please convert this file to .xlsx format if you want '
                       'to open it with openpyxl')
                msg = ('openpyxl does not support %s file format, '
                       'please check you can open '
                       'it with Excel first. '
                       'Supported formats are: %s') % (file_format,
            raise InvalidFileException(msg)

    archive = ZipFile(filename, 'r')
    return archive

def _find_workbook_part(package):
    workbook_types = [XLTM, XLTX, XLSM, XLSX]
    for ct in workbook_types:
        part = package.find(ct)
        if part:
            return part

    # some applications reassign the default for application/xml
    defaults = {p.ContentType for p in package.Default}
    workbook_type = defaults & set(workbook_types)
    if workbook_type:
        return Override("/" + ARC_WORKBOOK, workbook_type.pop())

    raise IOError("File contains no valid workbook part")

[docs]class ExcelReader: """ Read an Excel package and dispatch the contents to the relevant modules """ def __init__(self, fn, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True, rich_text=False): self.archive = _validate_archive(fn) self.valid_files = self.archive.namelist() self.read_only = read_only self.keep_vba = keep_vba self.data_only = data_only self.keep_links = keep_links self.rich_text = rich_text self.shared_strings = []
[docs] def read_manifest(self): src = root = fromstring(src) self.package = Manifest.from_tree(root)
[docs] def read_strings(self): ct = self.package.find(SHARED_STRINGS) reader = read_string_table if self.rich_text: reader = read_rich_text if ct is not None: strings_path = ct.PartName[1:] with,) as src: self.shared_strings = reader(src)
[docs] def read_workbook(self): wb_part = _find_workbook_part(self.package) self.parser = WorkbookParser(self.archive, wb_part.PartName[1:], keep_links=self.keep_links) self.parser.parse() wb = self.parser.wb wb._sheets = [] wb._data_only = self.data_only wb._read_only = self.read_only wb.template = wb_part.ContentType in (XLTX, XLTM) # If are going to preserve the vba then attach a copy of the archive to the # workbook so that is available for the save. if self.keep_vba: wb.vba_archive = ZipFile(BytesIO(), 'a', ZIP_DEFLATED) for name in self.valid_files: wb.vba_archive.writestr(name, if self.read_only: wb._archive = self.archive self.wb = wb
[docs] def read_properties(self): if ARC_CORE in self.valid_files: src = fromstring( = DocumentProperties.from_tree(src)
[docs] def read_custom(self): if ARC_CUSTOM in self.valid_files: src = fromstring( self.wb.custom_doc_props = CustomPropertyList.from_tree(src)
[docs] def read_theme(self): if ARC_THEME in self.valid_files: self.wb.loaded_theme =
[docs] def read_chartsheet(self, sheet, rel): sheet_path = rels_path = get_rels_path(sheet_path) rels = [] if rels_path in self.valid_files: rels = get_dependents(self.archive, rels_path) with, "r") as src: xml = node = fromstring(xml) cs = Chartsheet.from_tree(node) cs._parent = self.wb cs.title = self.wb._add_sheet(cs) drawings = rels.find(SpreadsheetDrawing._rel_type) for rel in drawings: charts, images = find_images(self.archive, for c in charts: cs.add_chart(c)
[docs] def read_worksheets(self): comment_warning = """Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data.""" for sheet, rel in self.parser.find_sheets(): if not in self.valid_files: continue if "chartsheet" in rel.Type: self.read_chartsheet(sheet, rel) continue rels_path = get_rels_path( rels = RelationshipList() if rels_path in self.valid_files: rels = get_dependents(self.archive, rels_path) if self.read_only: ws = ReadOnlyWorksheet(self.wb,,, self.shared_strings) ws.sheet_state = sheet.state self.wb._sheets.append(ws) continue else: fh = ws = self.wb.create_sheet( ws._rels = rels ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only, self.rich_text) ws_parser.bind_all() # assign any comments to cells for r in rels.find(COMMENTS_NS): src = comment_sheet = CommentSheet.from_tree(fromstring(src)) for ref, comment in comment_sheet.comments: try: ws[ref].comment = comment except AttributeError: c = ws[ref] if isinstance(c, MergedCell): warnings.warn(comment_warning.format(ws.title, c.coordinate)) continue # preserve link to VML file if VBA if self.wb.vba_archive and ws.legacy_drawing: ws.legacy_drawing = rels[ws.legacy_drawing].target else: ws.legacy_drawing = None for t in ws_parser.tables: src = xml = fromstring(src) table = Table.from_tree(xml) ws.add_table(table) drawings = rels.find(SpreadsheetDrawing._rel_type) for rel in drawings: charts, images = find_images(self.archive, for c in charts: ws.add_chart(c, c.anchor) for im in images: ws.add_image(im, im.anchor) pivot_rel = rels.find(TableDefinition.rel_type) for r in pivot_rel: pivot_path = r.Target src = tree = fromstring(src) pivot = TableDefinition.from_tree(tree) pivot.cache = self.parser.pivot_caches[pivot.cacheId] ws.add_pivot(pivot) ws.sheet_state = sheet.state
[docs] def read(self): action = "read manifest" try: self.read_manifest() action = "read strings" self.read_strings() action = "read workbook" self.read_workbook() action = "read properties" self.read_properties() action = "read custom properties" self.read_custom() action = "read theme" self.read_theme() action = "read stylesheet" apply_stylesheet(self.archive, self.wb) action = "read worksheets" self.read_worksheets() action = "assign names" self.parser.assign_names() if not self.read_only: self.archive.close() except ValueError as e: raise ValueError( f"Unable to read workbook: could not {action} from {self.archive.filename}.\n" "This is most probably because the workbook source files contain some invalid XML.\n" "Please see the exception for more details." ) from e
[docs]def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True, rich_text=False): """Open the given filename and return the workbook :param filename: the path to open or a file-like object :type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile` :param read_only: optimised for reading, content cannot be edited :type read_only: bool :param keep_vba: preserve vba content (this does NOT mean you can use it) :type keep_vba: bool :param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet :type data_only: bool :param keep_links: whether links to external workbooks should be preserved. The default is True :type keep_links: bool :param rich_text: if set to True openpyxl will preserve any rich text formatting in cells. The default is False :type rich_text: bool :rtype: :class:`openpyxl.workbook.Workbook` .. note:: When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet` and the returned workbook will be read-only. """ reader = ExcelReader(filename, read_only, keep_vba, data_only, keep_links, rich_text) return reader.wb