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?

[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]

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

[PhilMorefield · Accepted solution]

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.

[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.

[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?

[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?

[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]

Thanks Jason.  I'll give this a try.

[PaulHuffman]

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