I constantly need to read an excel file. However, it seems there is no built in functions in xlwings to automatically read all cells without telling xlwings the range you want to read. So I wrote this function to achieve this.
Python, 59 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | """
Created on Tue Dec 15 20:30:51 2015
@author: awang
"""
def ExcelReadtoList():
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
F = filedialog.askopenfilename()
import xlwings as xw
wb = xw.Workbook(F)
Results={}
Result={}
endRow=500
endCol=100
sheetNo=1
while True:
try:
carrier=xw.Sheet(sheetNo).name
Results[carrier]=xw.Range(sheetNo,(1,1),(endRow,endCol)).value
maxCol=1
for i in range(0,endRow):
countCol=endCol-1
for j in range(endCol-1,-1,-1):
if Results[carrier][i][j]!=None:
break
else:
countCol-=1
if maxCol<countCol:
maxCol=countCol
maxRow=1
for i in range(0,endCol):
countRow=endRow-1
for j in range(endRow-1,-1,-1):
if Results[carrier][j][i]!=None:
break
else:
countRow-=1
if maxRow<countRow:
maxRow=countRow
Result[carrier]=xw.Range(sheetNo,(1,1),(maxRow+1,maxCol+1)).value
sheetNo+=1
except:
wb.close()
print('Completed!\nBe noted maximum of %d rows and %d columns have been tested!!!' % (endRow,endCol))
return Result
|
I constantly need to read an excel file. However, it seems there is no built in functions in xlwings to automatically read all cells without telling xlwings the range you want to read. So I wrote this function to achieve this. I just started python programming, so the code might look not quite concise.
Tags: excel
Hello, I have a question to your code. This function read all Excel file or read actual open file or I must write name of this read file? I am waiting on your answer and your help on this matter
Hi Slawomir, when you run this code, it'll pop up a window asking you to select the excel file you want to read, one excel file at a time. Hope this answers your question.