Extract from Excel ... in Mac

Question

Hi I'd like to be able to extract data from an Excel file. As opposed to "export," an extract would open an Excel file in some manner, read all cells in a certain row-column range, and write the contents of those cells into a .txt file. The contents of the range are either numeric or text so there are no conversion problems to work through.

I'd prefer doing it without having to go into Excel and using save-as or copy-paste.

My environment is OS X Yosemite, and I am happy to try Python or bash to do this. Currently using Excel 2011 to do this, though later versions of Office for Mac are available.

Thanks!

Answer 1

openpyxl is a Python library to read and manipulate .xlsx files. Sample usage is given on its documentation page:

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = 'empty_book.xlsx')
>>> sheet_ranges = wb['range names']
>>> print(sheet_ranges['D18'].value)
3

Example

from openpyxl import load_workbook

wb = load_workbook(filename = 'sample.xlsx')
ws = wb['Sheet1']

with open('sample.txt', 'w') as f:
    myrange = ws['A1':'C4']
    for c1, c2, c3 in myrange:
        print(c1.value, c2.value, c3.value, file=f)

This worksheet named Sheet1 produces a text file with this output:

1 5 Nine
Two Six 1923-01-07 00:00:00
3 7 Tiger
40 8 Lion