python，openpyxl，读写excel文件

import openpyxl as oxl
from openpyxl.utils import get_column_letter, column_index_from_string
import winreg
import os def main():
# 加载工作簿
desktop_path = get_desktop_path()
wb = oxl.load_workbook(os.path.join(desktop_path(),'aaa.xlsx')) # 加载指定工作表
sheet = wb[wb.sheetnames[1]] # 获取指定单元格内容
c_value = sheet.cell(row=5, column=3).value
c_coordinate = sheet.cell(row=5, column=3).coordinate # 获取最大行和最大列
max_row = sheet.max_row
max_column = sheet.max_column # 列名的字母和数字转换
zm = get_column_letter(3)
sz = column_index_from_string('D') # 遍历区域内的单元格
for my_row in sheet['A1':'C9']:
print('current row is: ' + str(my_row[0].row))
for my_col in my_row:
print(my_col.coordinate + ': ' + my_col.value) # 打印表格
print_sheet(sheet) # 创建新工作簿
creat_workbook() def print_sheet(sheet):
# 生成列序号
a = list(range(1, sheet.max_column + 1))
b = list(map(get_column_letter, a)) # 打印列序号
for i in b:
print('\t' + i, end='')
print(end='\n') # 遍历工作表的所有单元
for my_row in range(0, sheet.max_row): # 逐行遍历
print(str(my_row + 1), end='') # 打印行序号
for my_col in list(sheet.rows)[my_row]: # 逐列遍历
print('\t' + my_col.value, end='') # 打印单元格
print(end='\n') # 回车 # 创建新工作簿
def creat_workbook():
# 创建新工作簿
wb = oxl.Workbook()
# 获取当前工作表
sheet = wb.active
# 更改工作表名称
sheet.title = 'sheet1'
# 创建工作表
wb.create_sheet(title='sheet2', index=0)
wb.create_sheet(title='sheet33', index=2)
# 删除工作表
wb.remove(wb[wb.sheetnames[2]])
# 修改单元格内容
sheet = wb[wb.sheetnames[1]]
for my_row in range(1, 11):
for my_col in range(1, 11):
sheet.cell(row=my_row, column=my_col).value = get_column_letter(my_col) + str(my_row)
# 保存工作簿
wb.save(os.path.join(desktop_path(),'ccc.xlsx')) # 获取当前系统的桌面绝对路径
def desktop_path():
key = winreg.OpenKey(winreg.HKEY_CURRENT_USER,r'Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders')
path = winreg.QueryValueEx(key, "Desktop")[0]
return path if __name__ == '__main__':
main()