python 利用python处理excel文件_python worksheet

1、相关包的安装

安装openpyxl包指令：

pip install openpyxl
1

2、读取、创建excel文件，遍历内容

wb=openpyxl.load_workbook('1234.xlsx')
1

wb=openpyxl.Workbook()
1

sheet=wb.worksheets[0]
 
sheet=wb['吸入类']
1
2
3

wb.create_sheet(index=0,title='小猪')
 
wb.create_sheet(index=1,title='佩奇')
1
2
3

sheet['A1'].value
1

import openpyxl
 
wb=openpyxl.load_workbook('1234.xlsx')
sheet=wb.worksheets[0]
for row in sheet.iter_rows():
	for cell in row:
		print(cell.coordinate, cell.value)
1
2
3
4
5
6
7

sheet['A1'].value='1111111'
sheet.cell(1,1).value='1111111'
sheet.cell(1,1,'111111')
1
2
3

wb.save('1234.xlsx')
1

3、字体样式调整

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
font = Font(name='微软雅黑', size=10, bold=False, italic=False, vertAlign=None,
                                        underline='none', strike=False, color='FF000000')
1
2
3

sheet['A1'].font=font
sheet.cell(1,1).font=font
1
2

4、填充样式调整

fill = PatternFill(fill_type='darkUp',start_color='FFFF00',end_color='FF0000')
sheet.cell(1,1).fill=fill
1
2

 {‘gray0625’, ‘lightHorizontal’, ‘darkVertical’, ‘darkGray’, ‘darkDown’, ‘solid’, ‘lightTrellis’, ‘darkHorizontal’, ‘gray125’, ‘lightGray’, ‘lightDown’, ‘lightUp’, ‘mediumGray’, ‘darkUp’, ‘darkGrid’, ‘darkTrellis’, ‘lightVertical’, ‘lightGrid’}
1

5、边框样式调整

border = Border(left=Side(border_style='dashDotDot',color='9932CC'),
                right=Side(border_style='dashDotDot',color='121212'),
                top=Side(border_style='dashDotDot',color='8B0A50'),
                bottom=Side(border_style='dashDotDot',color='B3EE3A'),)
sheet.cell(5,4).border=border
1
2
3
4
5

6、对齐样式调整

alignment = Alignment(horizontal='center',
                    vertical='center',
                    text_rotation=0,
                    indent=0)
 
sheet.cell(5,3).alignment=alignment
1
2
3
4
5
6

7、excel内添加链接

sheet.cell(6,3).value='=HYPERLINK("%s","%s")' % ("https://www.baidu.com", "百度一下")
1

sheet.cell(6,3).value='=HYPERLINK("%s","%s")' % ("#A1", "跳转到A1")
1

8、公式添加

sheet['F1']='=SUM(A1:E1)'
1

9、单元格的合并与拆分

sheet.merge_cells('A1:E1')
1

sheet.unmerge_cells('A1:E1')
1

代码汇总：

**# Python操作excel(openpyxl)**

# Python操作excel
#导入openpyxl库
import openpyxl
from openpyxl import Workbook
import datetime
#修改路径
import dir#自定义dir库，需要自己定义，可以忽略此句
dir.dir()

#读取与选择excel数据
import openpyxl
workbook = openpyxl.load_workbook('data.xlsx')#打开data文件
worksheet = workbook.get_sheet_by_name('Sheet1')#选择data.xlsx中的sheet1表格
row1=[row.value for row in list(worksheet.rows)[0]]#选择表格中的第一行单元格的值
print('第1行值:%s'%(row1))
#第1行值 ['a',1]
col2=[col.value for col in list(worksheet.columns)[1]]#选择第二列单元格的值
print('第2列值',col2)
#第2列值 [1,2,3]
cell_1_2=worksheet.cell(row=1,column=2).value#选择表格第一行第二列的值
print('第1行第2列值',cell_1_2)
#第1行第2列值 1
max_row=worksheet.max_row#显示总行数

读写数据
import dir 
dir.dir()
import openpyxl
workbook = openpyxl.load_workbook('data.xlsx')#打开data文件
worksheet = workbook.get_sheet_by_name('Sheet1')#选择data.xlsx中的sheet1表格
# 以上为读取某个表格数据，以下为创建一个新表格，然后写入上面读取的表格数据
from openpyxl import Workbook
wb = Workbook()
ws=wb.active

for i in range(max_row):
    rowi=[row.value for row in list(worksheet.rows)[i]]#循环选择表格中的第i行单元格的值
    for j in range(max_col):
        aij=rowi[j]#循环选择第i+1,j+1单元格的值
        ws.cell(row=i+1, column=j+1,value=aij)#循环写入数据，value后面输入需要写入的值
#         print(aij)
row6=[i.value for i in list(ws.rows)[5]]#选取ws工作表中的第6行数据，赋值给row6
column4=[i.value for i in list(ws.columns)[3]]#选取ws工作表中的第4列数据，赋值给row4
row6
wb.save('hzx1.xlsx')

新建、读取、写入(openpyxl)
#导入openpyxl库
import openpyxl
from openpyxl import Workbook
import datetime
#修改路径
import dir
dir.dir()

# 新建excel，实例化
wb = Workbook()
# 激活 worksheet，ws变为第一张worksheet
ws = wb.active




#3.写入数据
ws['A1'] = 42
# 方式二：附加行，从第一列开始附加(从最下方空白处，最左开始)(可以输入多行)
ws.append([1, 2, 3])
# 方式三：Python 类型会被自动转换
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
#4.创建表（sheet）
# 方式一：插入到最后(default)
ws1 = wb.create_sheet("Mysheet")
# 方式二：插入到最开始的位置
ws2 = wb.create_sheet("Mysheet", )
ws4=wb.create_sheet('New Title')


# 显示所有表名
print(wb.sheetnames)


#5.选择表（sheet），sheet 名称可以作为 key 进行索引
ws3 = wb["New Title”]#选择wb中名为new title的工作表
#或者
ws4 = wb.get_sheet_by_name("New Title")


# 遍历所有表
for sheet in wb:
	print(sheet.title)


#访问单元格
c = ws['A4']#方法一
# 方法二：row 行；column 列
d = ws.cell(row=4, column=2, value=10)#写入数据，value后面输入需要写入的值
# 方法三：只要访问就创建
for i in range(1,101):
    for j in range(1,101):
        ws.cell(row=i, column=j,value=f'{i}{j}')#循环写入数据，value后面输入需要写入的值
wb.save('hzx.xlsx')

根据
max_row=worksheet.max_row#显示总行数
max_col=worksheet.max_column
#3（即行数）
print(max_row,max_col)
for i in range(max_row):
    rowi=[row.value for row in list(worksheet.rows)[i]]#循环选择表格中的第i行单元格的值
    for j in range(max_col):
        aij=rowi[j]#循环选择第i+1,j+1单元格的值
        ws.cell(row=i+1, column=j+1,value=aij)#循环写入数据，value后面输入需要写入的值
#         print(aij)


#2）多个单元格访问
From openpyxl import Workbook
wb = Workbook()
ws=wb.active()
row6=[i.value for i in list(ws.rows)[5]]#选取ws工作表中的第6行数据，赋值给row6
column4=[i.value for i in list(ws.columns)[3]]#选取ws工作表中的第4列数据，赋值给row4


cell_range = ws['A1':'C2']
colC = ws['C']
# 通过行(列)
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
# 通过指定范围(行 行)
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
	for cell in row:
		print(cell)
# 遍历所有 方法一
ws = wb.active
tuple(ws.rows)
# 遍历所有 方法二
tuple(ws.columns)
8.保存数据
wb.save('文件名称.xlsx’)#保存工作薄
改变sheet标签按钮颜色
ws.sheet_properties.tabColor = "1072BA" # 色值为RGB16进制值
# 获得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)







import openpyxl
import d
d.d()
wb=openpyxl.Workbook()
wb=openpyxl.load_workbook('hzx.xlsx')#或者打开已经存在的excel
sheet=wb.worksheets[0]#wb中取第1张表
for row in sheet.iter_rows():
	for cell in row:
		print(cell.coordinate, cell.value)#循环打印单元格名称和单元格里面的值
#下面为在单元格中输入值，3种方法
sheet['A1'].value='1111111'
sheet.cell(1,1).value='1111111'
sheet.cell(1,1,'111111')

wb.create_sheet(index=0,title='小猪')#创建一个名称为小猪的工作表，位于index=0的位置
wb.create_sheet(index=1,title='佩奇')#创建一个名称为佩奇的工作表，位于index=1的位置


 #查询数据（按照excel格式打印）
for row in sheet.iter_rows(min_row=1,max_col=3,max_row=3):  
	print(row)
#方法二，遍历数据（按照行读取每个值，依次打印为一列）
for row in ws1.rows:
    for cell in row:
        print(cell.value,end=“\t”)#可以去掉end参数
（4）根据数字得到字母，根据字母得到数字
#导入安装包
from openpyxl.utils import get_column_letter,column_index_from_string
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4
删除工作表
# 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]
（6）矩阵置换
rows = [
['Number', 'data1', 'data2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10]]
list(zip(*rows))
输出如下格式：
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]
10.设置单元格风格
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment#导入设置字体格式，填充，边框，居中的函数
# 下面设置字体
font = Font(name='微软雅黑', size=10, bold=False, italic=False, vertAlign=None,
     underline='none', strike=False, color='FF000000')
sheet['A1'].font=font
sheet.cell(1,1).font=font
#下面的代码指定了，，字体颜色。直接使用cell的属性，将Font对象赋值给它。
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
sheet['A1'].font = bold_itatic_24_font
# 下面设置填充
fill = PatternFill(fill_type='darkUp',start_color='FFFF00',end_color='FF0000')
sheet.cell(1,1).fill=fill
# 下面设置边框
border = Border(left=Side(border_style='dashDotDot',color='9932CC'),
                right=Side(border_style='dashDotDot',color='121212'),
                top=Side(border_style='dashDotDot',color='8B0A50'),
                bottom=Side(border_style='dashDotDot',color='B3EE3A'),)
sheet.cell(5,4).border=border
# 下列为设置5行3列单元格格式为居中

alignment = Alignment(horizontal='center',
                    vertical='center',
                    text_rotation=0,
                    indent=0)
sheet.cell(5,3).alignment=alignment

#也是直接使用cell的属性，这里指定垂直居中和水平居中。除了center，还可以使用等等参数
# 设置B1中的数据垂直居中和水平居中
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
# 下面插入超链接
sheet.cell(6,5).value='=HYPERLINK("%s","%s")' % ("https://www.baidu.com","百度一下")#插入超链接到6行5列
sheet.cell(6,3).value='=HYPERLINK("%s","%s")' % ("#A1", "跳转到A1")#超链接
sheet['F1']='=SUM(A1:E1)'#公式
sheet.merge_cells('A1:E1')#合并
sheet.unmerge_cells('A1:E1')#拆分,拆分后，值回到A1位置




（4）设置行高和列宽
# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30




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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254