nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Working with Excel 2013, python

PaulHuffman

I'm interested in trying some data manipulation and retrieval from Excel spreadsheets. I've been trying out the xlrd, xlwt, and xlutils packages from http://www.python-excel.org/ with some success, but it seems I can't make these packages work with .xlsx files, unless I go to Excel and save them as Excel 97-03 formats, .xls. Are there updates to these packages for Excel 2013?

Vind meer berichten getagd met

Accepted answers

PhilMorefield

It's not a quick-and-easy solution to your problem, but if you've installed pywin32 and some version of Excel, you just use the COM interface to Excel.

Open PythonWin
Click "Tools" and select "COM Makepy utility"
Look for something like "Microsoft Excel 12.0 Object Library" and click "OK"

Now you can access Excel directly through a Python script like so:

import win32com.client xl = win32com.client.Dispatch('Excel.Application') xl.Visible = 1 wb = xl.Workbooks.Add()  datasheet = wb.Worksheets.Add() datasheet.Name = 'New name for my worksheet' datasheet.Cells(1, 1).Value = 'This is cell A1' datasheet.Cells(2, 2).Value = 'This is cell B2'  wb.SaveAs('C:\\Temp\\example.xlsx') wb.Close(SaveChanges=1) xl.Quit() del xl

The documentation for accessing Excel this way is scattered, but I've scripted the processing and creating of hundreds of spreadsheets and Excel charts this way.

All comments

LyleShakespear

Thanks Jason. I'll give this a try.

JasonWhite

The easiest way I have done it personally is to use the readline() method, split that line into a list, return the new one by indexing out the first column and then joining and writing the line out to a new file.

try: with open(fin, 'r') as inf:

with open(fout, 'w') as outf:

for line in inf:

lineToCheck = line #variable the for loop to hold the line from fin

correctedLine = body(lineToCheck) # corrected line String

outf.write(correctedLine)

...and the function to deal with it...

def body(lineCheck😞

'''Parses through the lines of the csv removing the first column.'''

tempLine = lineCheck.split(',') # creates a list from the parsed input line

tempLine = tempLine[1:] # drops the "0" position in the list and returns the rest

returnString = ','.join(tempLine) # rejoins the line

return returnString # returns it back to main script

LyleShakespear

I know this post was done forever ago but it has been helpful to me. However, I now need to be able to delete the first column in the spreadsheet but I haven't been able to find code for that. Can you point me in the right direction?

PaulHuffman

Here's a post with some useful examples: http://mail.python.org/pipermail/python-win32/2011-August/011738.html

PaulHuffman

Hey, great. After I installed pywin32, win32com was available to IDLE as well. I was able to read a value from a Excel 2013 file with a simple test script:

# Test to see if ws32com can read a Excel 2013 spreadsheet
# testcomread.py
# import the Dispatch library, get a reference to an Excel instance
from win32com.client import Dispatch
import os
xlApp = Dispatch("Excel.Application")
# Set the Excel file name, working directory and path

old_file_name = 'Excel2013.xlsx'

working_dir = r"C:\Users\Paul\My Documents\pyscripts\Office" + os.sep

old_file_path = os.path.join(working_dir, old_file_name)

xlBook = xlApp.Workbooks.Open(old_file_path)
 
# make Excel visible (1 is True, 0 is False)
xlApp.Visible=1

# get a reference to the first sheet
xlSheet = xlBook.Sheets(1)

# extract the value from a cell
a_value = xlSheet.Cells(1,1).Value

print a_value

Why couldn't I have found win32com when I started?

Can win32com run in a Linux install of Python?

ChrisSnyder

I also use the COM method to interact with Excel... Works great assuming you have Excel and PythonWin installed.

It's nice to couple the data access cursors (a tuple of the field values) with a sheet's .Range() write method - fastest way I have found to write native Excel tables.... row by row instead of cell by cell.

PhilMorefield

It's not a quick-and-easy solution to your problem, but if you've installed pywin32 and some version of Excel, you just use the COM interface to Excel.

Open PythonWin
Click "Tools" and select "COM Makepy utility"
Look for something like "Microsoft Excel 12.0 Object Library" and click "OK"

Now you can access Excel directly through a Python script like so:

import win32com.client xl = win32com.client.Dispatch('Excel.Application') xl.Visible = 1 wb = xl.Workbooks.Add()  datasheet = wb.Worksheets.Add() datasheet.Name = 'New name for my worksheet' datasheet.Cells(1, 1).Value = 'This is cell A1' datasheet.Cells(2, 2).Value = 'This is cell B2'  wb.SaveAs('C:\\Temp\\example.xlsx') wb.Close(SaveChanges=1) xl.Quit() del xl

The documentation for accessing Excel this way is scattered, but I've scripted the processing and creating of hundreds of spreadsheets and Excel charts this way.

PaulHuffman

I got openpyxl to work with Excel 2013 files.

from openpyxl import load_workbook
wb = load_workbook(filename = r'Excel2013.xlsx')
sheet_ranges = wb.get_sheet_by_name(name = 'Sheet1')
print sheet_ranges.cell('C3').value # C3

returns, prints the value in cell C3.

However, I can't get openpyxl to work with Excel 2003 xls files. wb = load_workbook(filename = r'Excel2003.xls') blows up. I thought openpyxl was supposed to work with both. No it says right there at the top of the library web page "A Python library to read/write Excel 2007 xlsx/xlsm "

I'm still waiting for a upgrade from Microsoft that fixes more problems than it causes.

Here's a Google group that supports openpyxl for those of you that are interested in trying it: http://https://groups.google.com/forum/#!forum/openpyxl-users

PaulHuffman

I don't know why ez_setup.py would install setuptools-0.9.7 for me, but I downloaded setuptools.-0.9.8.tar.gz from https://pypi.python.org/packages/source/s/setuptools, unpacked it myself, opened cmd and changed directory to the unpacked folder, ran "python setup.py install" and everything installed without a problem.

Then I used cmd, changed directory to the unpacked openpyxl folder, ran "python setup.py install" and it looked like openpyxl installed without problems.

Now back to testing openpyxl on Excel 2013 files.

PaulHuffman

Today I thought I'd try adding an environmental variable %PYTHON_PATH% and append it to %PATH%, but I got the same messages from both of the steps above.

PaulHuffman

It doesn't look promising. The openpyxl webpage http://openpyxl.readthedocs.org/en/latest/#getting-the-source mentions only Excel 2007 xlsx files.

Plus I'm having difficulty installing openpyxl in Windows and Python 2.7. With cmd python setup.py install, I got "No Module named setuptools" (see attached image) [ATTACH=CONFIG]26177[/ATTACH]

Then I tried to install setuptools 0.9.7 https://pypi.python.org/pypi/setuptools/0.9.7#windows but on running ez_setup.py I got another error:

Downloading https://pypi.python.org/packages/source/s/setuptools/setuptools-0.9.7.tar.gz
Extracting in c:\users\paul\appdata\local\temp\tmptm3fqf
Now working in c:\users\paul\appdata\local\temp\tmptm3fqf\setuptools-0.9.7
Installing Setuptools
Something went wrong during the installation.
See the error message above.

Traceback (most recent call last):
  File "C:\Users\Paul\Documents\pyscripts\ez_setup.py", line 264, in <module>
    sys.exit(main())
SystemExit: 2

openpyxlcap.PNG

PaulHuffman

People at SO say that xlrd currently works with the .xlsx extension, http://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python but that may not include the Excel 2013 files.

But there is openpyxl https://bitbucket.org/ericgazoni/openpyxl/ that I will try tomorrow.

PaulHuffman

There's another simple example at http://codingtutorials.co.uk/python-excel-xlrd-xlwt/ If I run it on xls files the print.sheet_value doesn't seem to print anything. Wait, yes it works, I just happened to hit an empty cell. But when I try to read a xlsx file, it really blows up.

There's a example on this site to create a workbook, and that runs, creates a xls file with a bunch of numbers in it.

PaulHuffman

Double check your xlrd version and test on a simple xlsx file.

I'm using xlrd 0.9.2. Is there revision beyond that?

PaulHuffman

When I tried the simple file opening routine on page 7 it seemed d to work with xls files:

from mmap import mmap,ACCESS_READ
from xlrd import open_workbook
print open_workbook('simple.xls')
with open('simple.xls','rb') as f:
    print open_workbook(
        file_contents=mmap(f.fileno(),0,access=ACCESS_READ)
        )
aString = open('simple.xls','rb').read()
print open_workbook(file_contents=aString)

>>>
<xlrd.Book object at 0x02950390>
<xlrd.Book object at 0x029211D0>
<xlrd.Book object at 0x029C19B0>
>>>

But seemed to blow up when I tried a xlsx file:
Traceback (most recent call last):
File "C:\Users\Paul\Documents\pyscripts\Office\open.py", line 3, in <module>
    print open_workbook('simple.xlsx')
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 429, in open_workbook
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 1545, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 1539, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
XLRDError: Unsupported format, or corrupt file: Expected BOF record; found 'PK\x03\x04\x14\x00\x06\x00'

MathewCoyle

A possible answer for the error you are getting on SO.

http://stackoverflow.com/questions/16504975/error-unsupported-format-or-corrupt-file-expected-bof-record

Double check your xlrd version and test on a simple xlsx file.

PaulHuffman

I'm using Python 2.7.2.

When I try the simple example on page 10 with an xlsx file in the same folder as the script, I get a lot of red returned:

from xlrd import open_workbook,XL_CELL_TEXT
book = open_workbook('simple.xlsx')
sheet = book.sheet_by_index(1)
cell = sheet.cell(0,0)
print cell
print cell.value
print cell.ctype==XL_CELL_TEXT
for i in range(sheet.ncols):
    print sheet.cell_type(1,i),sheet.cell_value(1,i)

Traceback (most recent call last):
File "C:\Users\Paul\Documents\pyscripts\Office\cell_access.py", line 2, in <module>
    book = open_workbook('simple.xlsx')
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 429, in open_workbook
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 1545, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\__init__.py", line 1539, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
XLRDError: Unsupported format, or corrupt file: Expected BOF record; found 'PK\x03\x04\x14\x00\x06\x00'

If I try the script on an xls file, I get a lot less red. But I thought this worked for we on xls files last week.

Traceback (most recent call last):
File "C:\Users\Paul\Documents\pyscripts\Office\cell_access.py", line 4, in <module>
    cell = sheet.cell(0,0)
File "C:\Python27\ArcGIS10.1\lib\site-packages\xlrd\sheet.py", line 255, in cell
    self._cell_types[rowx][colx],
IndexError: list index out of range

MathewCoyle

What version of python are you using? 2.7 should be able to open xlsx files fine.

Edit: xlrd 0.8.0+ support xlsx.

If you are asking about writing to xlsx you'd need to find another module currently. openpyxl seems preferred.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.