0

Using various python libraries - Pandas, xlrd, openpyxl - is not possible. I encounter the same error as here : Openpyxl: 'ValueError: Max value is 14' when using load_workbook.

I used pandas.read_excel (engine=openpyxl).

Sample code I tried:

import pandas as pd
import os
from openpyxl import Workbook,load_workbook


download_folder = "path"
excel='sample.xlsx'
xls = pd.read_excel(download_folder+excel, sheet_name='Sheet1',header=1,skiprows=list(range(5)))

#wb = load_workbook(filename = download_folder+excel)

Always returns the same error:

   raise ValueError('Max value is {0}'.format(self.max))
ValueError: Max value is 14
Dametime
  • 581
  • 1
  • 6
  • 23
  • Have you tried using *xlwings*? One of the answers in the SO post that you refer to suggests that *xlwings* can handle this font family issue –  Sep 27 '21 at 11:09
  • yup i tried, problem is that it requires excel to be installed, but that is not a possibility – Dametime Sep 27 '21 at 19:28

2 Answers2

1

I offer this as an answer with a MASSIVE caveat. I was able to make this work for me on macOS. I cannot guarantee that it will work elsewhere or for anyone else's particular Excel file.

The issue is related to a font family value. I do not know why the magic number of 14 comes into play but it is what it is. Furthermore, I do not know what one would do within Excel to cause values above 14 to occur. So here's what I did...

I created a very basic Excel spreadsheet. Excel files are just zip-style archives so I unzipped my .xlsx file. As part of that extract, we see xl/styles.xml and within that we see this extract:-

<fonts count="1" x14ac:knownFonts="1">
        <font>
            <sz val="12" />
            <color theme="1" />
            <name val="Calibri" />
            <family val="2" />
            <scheme val="minor" />
        </font>
    </fonts>

It is the val attribute of the family element which, when greater than 14, causes the problem.

I then modified xl/styles.xml to contain:-

<family val="22" />

Now, if I re-package my extracted data (i.e., re-build the Excel file) and then try to open it in Python using openpyxl I get the exception as observed by @NewGirl

But we can fix it. The process involves extracting (unzipping) the spreadsheet, parsing the xl/styles.xml and looking for an inappropriate value in the family element. We then re-write the XML file and repackage the spreadsheet.

WARNING: If this doesn't work for you it will very likely corrupt your spreadsheet so make sure you take a copy!

Here's the code:-

import tempfile
from openpyxl import load_workbook
import os
import shutil
from lxml import etree


EXCELFILE = '~/Book1.xlsx'
STYLES = 'xl/styles.xml'
FORMAT = 'zip'


with tempfile.TemporaryDirectory() as tdir:
    os.chdir(tdir)
    shutil.unpack_archive(filename=EXCELFILE, format=FORMAT)
    with open(STYLES, 'r') as styles:
        tree = etree.parse(styles)
        for family in tree.xpath('//*[local-name()="fonts"]//*[local-name()="font"]//*[local-name()="family"]'):
            try:
                if int(family.attrib['val']) > 14:
                    family.set('val', '2')
            except Exception:
                pass
    with open(STYLES, 'wb') as styles:
        tree.write(styles)
    shutil.make_archive(base_name=EXCELFILE, format=FORMAT)
    shutil.move(f'{EXCELFILE}.{FORMAT}', EXCELFILE)

load_workbook(EXCELFILE)

The call to load_workbook() is done merely as a check against the modified spreadsheet's validity

1

This problem with bad styles could be solved by cleaning xlsx styles

Here is my code how to do it with pandas and openpyxl https://stackoverflow.com/a/71526058/1731460

pymen
  • 5,737
  • 44
  • 35