parsing excel documents with python [closed]

I want to parse excel document to lists in Python. Is there a python library which is helpful for this action? And what functions are relevant in that library?

Solution 1:

You're best bet for parsing Excel files would be the xlrd library. The python-excel.org site has links and examples for xlrd and related python excel libraries, including a pdf document that has some good examples of using xlrd. Of course, there are also lots of related xlrd questions on StackOverflow that might be of use.

One caveat with the xlrd library is that it will only work with xls (Excel 2003 and earlier versions of excel) file formats and not the more recent xlsx file format. There is a newer library openpyxl for dealing with the xlsx, but I have never used it.

UPDATE: As per John's comment, the xlrd library now supports both xls and xlsx file formats.

Hope that helps.

Solution 2:

The pandas library has a quick and easy way to read excel. If it's mostly just data and nothing too complicated it'll work:

import pandas as pd
ex_data = pd.read_excel('excel_file.xlsx')

It reads it into a pandas DataFrame, which is handy for data munging, etc.

To go to a list:

ex_data['column1_name'].values.tolist()

If you have multiple tables and things in each worksheet then you may want to use another library such as xlrd or openpyxl.

Solution 3:

openpyxl is a great library and supports read/write to 2010 xlsx files.

sample parsing code

from openpyxl import load_workbook
wb = load_workbook('Book1.xlsx')
ws = wb.active
for row in ws.iter_rows():
   for cell in row:
     print cell.value

sample writing code

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active
ws1.title = "range names"

for row in range(1, 40):
    ws1.append(range(600))
wb.save(filename = dest_filename)

you can read more here: https://openpyxl.readthedocs.io/en/stable/index.html

Solution 4:

xlrd is great for simple tasks, but if you need to work with any of Excel's deeper functionality (macros, advanced plotting, etc), and you are working on a windows machine, you can use the pywin32 library to control the win32com layer. This provides access to just about everything that can be controlled via macros / Visual Basic.