XLS
Python XLS parser using standard modules. xlsx files are basically just a set of compressed (zipped) .XML pages.
A simple workbook with 3 sheets having the same content like this:
Consists of the following .xml files:
Filename | Remarks |
---|---|
./[Content_Types].xml | |
./docProps/core.xml | |
./docProps/app.xml | |
./xl/workbook.xml | Holds per sheet a node like '<sheet name="Test2" sheetId="2" state="visible" r:id="rId3"/>' |
./xl/sharedStrings.xml | Strings that appear in more sheets, references from the sheet by indexnumber. |
./xl/styles.xml | |
./xl/worksheets/sheet2.xml | Content of sheet2 |
./xl/worksheets/sheet1.xml | Content of sheet1 |
./xl/worksheets/sheet3.xml | Content of sheet3 |
./xl/_rels/workbook.xml.rels | |
./_rels/.rels |
Found this on stackoverflow where it has been removed [1]. It was announced as very very basic but it works very well and it only uses modules installed by default (at least in Ubuntu 18.04). You can find the original solution at the end of this page.
The routine returns a list of dicts like { <columname> : <cellvalue> [, .. ] }
Our solution supporting workbooks with and without sharedstrings and fetches sheet content by the sheet name. Using 're' to find the column, this may be slower than the original approach but we have not found that in the test-files used.
def xlsx(fname,sheet):
import zipfile
from xml.etree.ElementTree import iterparse
import re
z = zipfile.ZipFile(fname)
if 'xl/sharedStrings.xml' in z.namelist():
# Get shared strings
strings = [element.text for event, element
in iterparse(z.open('xl/sharedStrings.xml'))
if element.tag.endswith('}t')]
# Get the sheets available
sheets = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))
if element.tag.endswith('}sheet') }
# Just to see what is in, comment out for real use
for s in sheets:
print('SheetID: '+sheets[s]+' Sheetname: '+s)
rows = []
row = {}
value = ''
if sheet in sheets:
sheetfile = 'xl/worksheets/sheet'+sheets[sheet]+'.xml'
#print(sheet,sheetfile)
for event, element in iterparse(z.open(sheetfile)):
# get value or index to shared strings
if element.tag.endswith('}v') or element.tag.endswith('}t'):
value = element.text
# If value is a shared string, use value as an index
if element.tag.endswith('}c'):
if element.attrib.get('t') == 's':
value = strings[int(value)]
# split the row/col information so that the row leter(s) can be separate
letter = re.sub('\d','',element.attrib['r'])
row[letter] = value
value = ''
if element.tag.endswith('}row'):
rows.append(row)
row = {}
return rows
Pandas
There is a pandas function to read excel files you can use to avoid all this.
Using the routine above you can try to convert it to a pandas dataframe too:
- df = pd.DataFrame(xlsx(xlsfile,sheetname))
- Put the sheet into a pandas dataframe
- df.columns = df.iloc[1]
- Set the column names as the first row in the sheet.