I wanted to know how to read an entire column without iterating from an excel sheet using win32com client for python.
Add a comment
|
3 Answers
You can read an entire column without iterating from a sheet using the Range collection. You should never use Cells if performance is any concern. Python uses the win32com module to interact with the Excel COM library. Whenever you use Python and COM (Excel, PowerPoint, Acess, ADODB, etc.) one of your biggest performance constraints will be IO between COM and Python. With the Range method you only make one COM method call while with Cells you make one for each row. This would also be faster if you were doing the same in VBA or .NET
In the following test I created a worksheet with 10 random characters in cells A1 through A2000. I then extracted these values into lists using both Range and Cells.
import win32com.client
app = win32com.client.Dispatch("Excel.Application")
s = app.ActiveWorkbook.Sheets(1)
def GetValuesByCells():
startTime = time.time()
vals = [s.Cells(r,1).Value for r in range(1,2001)]
return time.time() - startTime
def GetValuesByRange():
startTime = time.time()
vals = [v[0] for v in s.Range('A1:A2000').Value]
return time.time() - startTime
>>> GetValuesByRange()
0.03600001335144043
>>> GetValuesByCells()
5.27400016784668
In this case Range is 2 orders of magnitude faster (146x) faster than Cells. Note that the Range method returns a 2D list where each inner list is a row. The list iteration transposes vals into a 2D list where the inner list is a column.
4 Comments
John Y
OK, I'm upvoting this even though, in its current form, I don't really consider it an answer. To me, this is a very long comment. Why I think it still deserves an upvote is that it is ultimately more useful and more helpful than either of the other answers presented so far (despite yuvi's being accepted already). The code snippet shown here clearly comes the closest to illustrating how to "read an entire column without iterating ... using
win32com".
Michael David Watson
I just looked back on this answer and modified it to answer the original question
yuvi
I wonder how well it does against
xlrd. If the differences are miniscule, then xlrd would be a clear winnerJohn Y
@yuvi: I haven't tested extensively myself, but I think it depends to a large extent on the nature of the data and the nature of what you're trying to do. The bigger and more complicated the workbook, the bigger the advantage Excel will have, in sheer loading time alone. The more you can rely on Excel itself to do the heavy lifting (via its ranges and its calculation engine), the bigger the advantage Excel will have. Just make sure you make as few and as productive COM calls as possible.
Have you looked into the openpyxl library? From the documentation:
from openpyxl import load_workbook
wb = load_workbook(filename='file.xlsx')
ws = wb.get_sheet_by_name(name='Sheet1')
columns = ws.columns()
There's also support for iterators and other goodies.
Comments
The fastest way would be to use the built in Range functionality through the win32com.client API. However, I'm not a big fan of it. I think the API is confusing and badly documented, and using it isn't very pythonic (but that's just me).
If efficiency is not an issue for you, you can use the excellent xlrd library. Like so:
import xlrd
book = xlrd.open_workbooks('Book1')
sheet = book.sheet_by_name('Sheet1')
sheel.col(1)
sheet.col(2)
# and so on...
That gives you the cell objects. To get pure values, use sheet.col_values (and there are a few other methods that are real nice to work with).
Just remember that xlrd stand for "excel read", so if you want to write to an excel file you need a different library called "xlwt" (which is also pretty good, though less so than xlrd in my opinion).
14 Comments
Nischal Hp
Yeah i did try writing this piece of code and was thinking , python can be used to write as less as possible so i was just wondering if there was anything that would just return me a list with the required column values without me having to write the iteration part.
yuvi
You can use the xlrd library, wait a second I'll add an example
yuvi
There. hope that helps!
John Y
Conceptually, I think OP is looking for a way to retrieve an entire range "in one go", like retrieving a result set using
SELECT from a database. That is, any "iterating" that must be done during retrieval is handled before it gets to Python. In the database case, the SQL engine may be iterating under the covers, but all you see is a single "return value", that happens to contain multiple values within it. So for Excel, OP is looking to specify a range, then grab all the values "at once" into, say, a tuple. This may or may not be possible; I don't know COM well enough.John Y
All that being said, I don't see why iteration needs to be avoided in the first place. Perhaps it's because accessing Excel via COM cell by cell is notoriously slow. Operating on an Excel range is vastly faster than operating on one cell at a time. But reading the file directly via
xlrd (and not involving COM at all) is usually fast enough. |