python，openpyxl，读写excel文件 

 1 import openpyxl as oxl
 2 from openpyxl.utils import get_column_letter, column_index_from_string
 3 import winreg
 4 import os
 5 
 6 
 7 def main():
 8     # 加载工作簿
 9     desktop_path = get_desktop_path()
10     wb = oxl.load_workbook(os.path.join(desktop_path(),'aaa.xlsx'))
11 
12     # 加载指定工作表
13     sheet = wb[wb.sheetnames[1]]
14 
15     # 获取指定单元格内容
16     c_value = sheet.cell(row=5, column=3).value
17     c_coordinate = sheet.cell(row=5, column=3).coordinate
18 
19     # 获取最大行和最大列
20     max_row = sheet.max_row
21     max_column = sheet.max_column
22 
23     # 列名的字母和数字转换
24     zm = get_column_letter(3)
25     sz = column_index_from_string('D')
26 
27     # 遍历区域内的单元格
28     for my_row in sheet['A1':'C9']:
29         print('current row is: ' + str(my_row[0].row))
30         for my_col in my_row:
31             print(my_col.coordinate + ': ' + my_col.value)
32 
33     # 打印表格
34     print_sheet(sheet)
35 
36     # 创建新工作簿
37     creat_workbook()
38 
39 
40 def print_sheet(sheet):
41     # 生成列序号
42     a = list(range(1, sheet.max_column + 1))
43     b = list(map(get_column_letter, a))
44 
45     # 打印列序号
46     for i in b:
47         print('\t' + i, end='')
48     print(end='\n')
49 
50     # 遍历工作表的所有单元
51     for my_row in range(0, sheet.max_row):  # 逐行遍历
52         print(str(my_row + 1), end='')  # 打印行序号
53         for my_col in list(sheet.rows)[my_row]:  # 逐列遍历
54             print('\t' + my_col.value, end='')  # 打印单元格
55         print(end='\n')  # 回车
56 
57 
58 # 创建新工作簿
59 def creat_workbook():
60     # 创建新工作簿
61     wb = oxl.Workbook()
62     # 获取当前工作表
63     sheet = wb.active
64     # 更改工作表名称
65     sheet.title = 'sheet1'
66     # 创建工作表
67     wb.create_sheet(title='sheet2', index=0)
68     wb.create_sheet(title='sheet33', index=2)
69     # 删除工作表
70     wb.remove(wb[wb.sheetnames[2]])
71     # 修改单元格内容
72     sheet = wb[wb.sheetnames[1]]
73     for my_row in range(1, 11):
74         for my_col in range(1, 11):
75             sheet.cell(row=my_row, column=my_col).value = get_column_letter(my_col) + str(my_row)
76     # 保存工作簿
77     wb.save(os.path.join(desktop_path(),'ccc.xlsx'))
78 
79 
80 # 获取当前系统的桌面绝对路径
81 def desktop_path():
82     key = winreg.OpenKey(winreg.HKEY_CURRENT_USER,r'Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders')
83     path = winreg.QueryValueEx(key, "Desktop")[0]
84     return path
85 
86 
87 if __name__ == '__main__':
88     main()

 

posted @ 2020-02-06 16:39  xinyangq  阅读(297)  评论(0)    收藏  举报