Difference between revisions of "XLS"

From wiki
Jump to: navigation, search
(Pandas)
(Pandas)
 
Line 100: Line 100:
 
==Pandas==
 
==Pandas==
  
[[Pandas]] has an excel import module read_excel.
+
There is a [[Pandas#Reading_Data|pandas function to read excel files ]] you can use to avoid all this.
  
Using the routine above you can try:
+
Using the routine above you can try to convert it to a pandas dataframe too:
 
;df = pd.DataFrame(xlsx(xlsfile,sheetname))
 
;df = pd.DataFrame(xlsx(xlsfile,sheetname))
 
:Put the sheet into a pandas dataframe
 
:Put the sheet into a pandas dataframe

Latest revision as of 13:50, 29 June 2020


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:

Sample xls.png

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.