CODEWITHSUNDEEP
pythonopenpyxlxlsm
abhiram subramanya
abhiram subramanya

Reputation: 67

Issue while opening .xlsm file using openpyxl

I am currently using openpyxl to add new sheet into .xlsm file, with (read_only=False)

eg.

    wb = openpyxl.load_workbook("Input_file.xlsm", read_only=False, keep_vba=True)
    sheet = wb.create_sheet('Source_Data')
    final_row = dataframe_to_rows(final_df)
    #  code to add final row to sheet Source_Data
    wb.save("Input_file.xlsm")
    wb.close()


 Below is the error message

~\AppData\Local\Programs\Python\Python39\lib\zipfile.py in getinfo(self, name)
   1427         info = self.NameToInfo.get(name)
   1428         if info is None:
-> 1429             raise KeyError(
   1430                 'There is no item named %r in the archive' % name)
   1431 

KeyError: "There is no item named 'xl/drawings/NULL' in the archive"

Upvotes: 0

Views: 806

Answers (2)

Alex L
Alex L

Reputation: 4241

this fixes it if you are happy with monkeypatching openpyxl:

from openpyxl import load_workbook
import openpyxl.reader.drawings
import openpyxl.reader.excel
from openpyxl.reader.drawings import (
    fromstring,
    SpreadsheetDrawing,
    get_rel,
    get_rels_path,
    get_dependents,
    ChartSpace,
    read_chart,
    PILImage,
    IMAGE_NS,
    Image,
    BytesIO,
    warn,
)

# monkey patching to fix the issue with the openpyxl library
# where it doesn't allow hidden images
# here: https://openpyxl.readthedocs.io/en/3.1.2/api/openpyxl.reader.drawings.html#openpyxl.reader.drawings.find_images
def new_find_images(archive, path):
    """
    Our monkey patched version of the openpyxl.reader.drawings.find_images

    Given the path to a drawing file extract charts and images

    Ignore errors due to unsupported parts of DrawingML
    Also ignore hidden images (our monkey patch)
    """

    src = archive.read(path)
    tree = fromstring(src)
    try:
        drawing = SpreadsheetDrawing.from_tree(tree)
    except TypeError:
        warn(
            "DrawingML support is incomplete and limited to charts and images only. Shapes and drawings will be lost."
        )
        return [], []

    rels_path = get_rels_path(path)
    print(path, rels_path)
    deps = []
    if rels_path in archive.namelist():
        deps = get_dependents(archive, rels_path)

    charts = []
    for rel in drawing._chart_rels:
        try:
            cs = get_rel(archive, deps, rel.id, ChartSpace)
        except TypeError as e:
            warn(f"Unable to read chart {rel.id} from {path} {e}")
            continue
        chart = read_chart(cs)
        chart.anchor = rel.anchor
        charts.append(chart)

    images = []
    if not PILImage:  # Pillow not installed, drop images
        return charts, images

    for rel in drawing._blip_rels:
        dep = deps[rel.embed]
        if dep.target == "xl/drawings/NULL":
            # new line to skip hidden images
            msg = "Hidden images are not supported so the image is being dropped"
            warn(msg)
            continue
        if dep.Type == IMAGE_NS:
            try:
                print(dep.target)
                image = Image(BytesIO(archive.read(dep.target)))
            except OSError:
                msg = "The image {0} will be removed because it cannot be read".format(
                    dep.target
                )
                warn(msg)
                continue
            if image.format.upper() == "WMF":  # cannot save
                msg = "{0} image format is not supported so the image is being dropped".format(
                    image.format
                )
                warn(msg)
                continue
            image.anchor = rel.anchor
            images.append(image)
    return charts, images


# apply monkey patched function
openpyxl.reader.drawings.find_images = new_find_images
openpyxl.reader.excel.find_images = new_find_images  # type: ignore[attr-defined]

kwargs = {} # enter yours
wb = load_workbook("Input_file.xlsm", **kwargs)

Upvotes: 0

Hazkel
Hazkel

Reputation: 121

I had the exact same problem. I managed to solve it easily (I don't understand exactly why but it worked). Apparently there were some images pasted in some sheets that were HIDDEN.

you need to find and delete them. (maybe making them visible will do the trick as well - I didn't check) There are two ways to so. Select each sheet in your workbook. Then:

option1: press F5 -> special -> Objects This will highlight all hidden images on sheet

option2: goto "home" tab -> press "Find & Select" -> press "Selection Pane" You can see all hidden images - and delete them

Upvotes: 0

Related Questions