python Handle excel Data is often needed .
The bag used here is openpyxl
import openpyxl
excel_name = 't1.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
print(ws.cell(row=1, column=1).value)
# or
print(ws.cell(1, 1).value)
This will print the value of the first row and the first column , namely A1 Place the value of the .
openpyxl Are the benefits of , It will use excel Structure to treat table data . As shown in the above code ,ws It's from excel Structured data obtained from . This structured data is neither list, Neither dict, It's about having excel Structured data . Can pass ws.cell(row, column) To access every grid in the table .
The above code can also be transformed in this way :
import openpyxl
excel_name = 't1.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
print(ws['A1'].value)
there A1 It's the value in the first row and the first column , You make it clear excel The column of the form should know A1 What is it? , In the same way and A2,B2... wait .
If you want to traverse this excel What to do ?
you 're right , Still have to for loop ,
import openpyxl
excel_name = 't1.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
# print a column
for r in range(ws.max_row):
print(ws.cell(r+1, 1).value)
Here's a little detail , because excel The value of both row and column in cannot be 0, It's all from 1 Start , So we need to r+1.
If we need to excel Convert a column or row in to python The most convenient list, How to do it? ?
Then write your own function :
import openpyxl
excel_name = 't1.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
def read_excel_by_rc(ws, r=-1, c=-1):
ls = []
if r == -1 and c == -1:
return
if r > 0 and c == -1:
for c in range(ws.max_column):
ls.append(ws.cell(r, c+1).value)
elif r == -1 and c > 0:
for r in range(ws.max_row):
ls.append(ws.cell(r+1, c).value)
else:
print('please input right (r, c) pair!')
return ls
print(read_excel_by_rc(ws, r=1))
print(read_excel_by_rc(ws, c=3))
above .