1. Excel 2007
1 pip install openpyxl:
C:PythonScripts>pip install openpyxl
2.source code
#coding=utf-8
import openpyxl
def read07Excel(path):
wb = openpyxl.load_workbook(path)
sheet = wb.get_sheet_by_name('Sheet1')
for row in sheet.rows:
for cell in row:
print(cell.value, " ", end="")
print()
def write07Excel(path):
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Sheet1'
value = [["Name", "Age", "Sex", "Hobby"],
["Amy", "25", "Famale", "None"],
["Steven", "32", "Male", "None"]]
for i in range(0, 3):
for j in range(0, len(value[i])):
sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
wb.save(path)
print("write successfully！")
file_2007 = 'data/Data1.xlsx'
write07Excel(file_2007)
read07Excel(file_2007)
openpyxl操作单元格
wb=openpyxl.load_workbook('ttt.xlsx') #open excel file
print(wb.get_sheet_names()) #get all worksheet name
sheet=wb.get_sheet_by_name('Sheet1') #get worksheet
print(sheet.title)
sheet02=wb.get_active_sheet() #get active worksheet
print(sheet02.title)
print(sheet['A1'].value) #get value of A1
print(sheet['A1'].column) #get column number of A1
print(sheet['A1'].row) #get row number of A1
print(sheet.cell(row=1,column=1).value) #get specific row and column value
print(sheet.max_column) #get max column
print(sheet.max_row) #get max row
2. Excel 2003
1.pip install xlrd
pip install xlwt
2.source code
#coding=utf-8
import xlrd
import xlwt
def read03Excel(path):
workbook = xlrd.open_workbook(path)
sheets = workbook.sheet_names()
worksheet = workbook.sheet_by_name(sheets[0])
for i in range(0, worksheet.nrows):
row = worksheet.row(i)
for j in range(0, worksheet.ncols):
print(worksheet.cell_value(i, j), " ", end="")
print()
def write03Excel(path):
wb = xlwt.Workbook()
sheet = wb.add_sheet("Sheet1")
value = [["Name", "Age", "Sex", "Hobby"],
["Amy", "25", "Famale", "None"],
["Steven", "32", "Male", "None"]]
for i in range(0, 3):
for j in range(0, len(value[i])):
sheet.write(i,j,value[i][j])
wb.save(path)
print("write successfully！")
file_2003 = 'data/Data1.xlsx'
write03Excel(file_2003)
read03Excel(file_2003)