Welcome, guest | Sign In | My Account | Store | Cart

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

2 comments

Slawomir Gontarek 5 years, 6 months ago  # | flag

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

Alfred Wang (author) 5 years, 6 months ago  # | flag

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.

Created by Alfred Wang on Wed, 16 Dec 2015 (MIT)
Python recipes (4591)
Alfred Wang's recipes (2)

Required Modules

  • (none specified)

Other Information and Tasks