python中关于excle的处理方法

1.Excle文档

一个 Excel 电子表格文档称为一个工作簿，一个工作簿保存在扩展名为.xlsx 的文件中。每个工作簿可以包含多个表（也称为工作表）。
用户当前查看的表（或关闭 Excel 前最后查看的表），称为活动表。每个表都有一些列（地址是从 A 开始的字母）和一些行（地址是从 1 开始的数字）。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。

2.安装openpyxl模块

(venv) C:\Users\Administrator\PycharmProjects\pythonProject16>pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 250.0/250.0 kB 151.9 kB/s eta 0:00:00
Collecting et-xmlfile (from openpyxl)
  Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2

3.import openpyxl

A B C
1 4/5/2015 1:34:02 PM Apples 73
2 4/5/2015 3:41:23 AM Cherries 85
3 4/6/2015 12:46:51 PM Pears 14
4 4/8/2015 8:59:43 AM Oranges 52
5 4/10/2015 2:07:00 AM Apples 152
6 4/10/2015 6:10:37 PM Bananas 23
7 4/10/2015 2:40:46 AM Strawberries 98

用 openpyxl 模块打开 Excel 文档

import openpyxl
wb = openpyxl.load_workbook("examples.xlsx")
print(type(wb))

<class 'openpyxl.workbook.workbook.Workbook'>

从工作簿中取得工作表

sheet = wb.sheetnames #获取表
print([x for x in sheet])
print(wb.active) #查看活动的表

在指定的sheet上工作

sheet = wb['Sheet3']
sheet.dimensions  #获取sheet的大小

打印cell的属性值

print(sheet['A3'].value)
C = sheet['A3']
C.row  .#打印sheet的行
C.column    #打印sheet的lie
C.coordinate    #返回的是单元格的坐标
sheet.cell(row=i, column=2).value

列字母和数字之间的转换

openpyxl.utils.get_column_letter(2)
'B'
openpyxl.utils.column_index_from_string("D")
4

打印每一个单元格的内容

通过for 循环

x = tuple(sheet["A2":"D8"])
for i in x:
	for j in i:
		print(j.coordinate,  j.value)
	print("end of", i[1].row, "row")

打印每一列或者每一行的内容

1.行很好取值

for i in sheet[1]:
     print(i)

2.列取值：
第一种方法

lieshu = 0   列初始化
want_column = 3   想打印哪一类
for i in sheet.columns:
	lieshu += 1
	# print(i,lieshu)
	if lieshu == want_column:
		for x in i:
			print(x.value, end="\t")

第二种方法

n = 3
for i  in tuple(sheet.columns)[n-1]:
	print(j.coordinate,i.value, end="\t")

3.遍历工作表

for sheet in wb:
     print(sheet.title)

写入 Excel 文档

调用 openpyxl.Workbook()函数，创建一个新的空 Workbook 对象。在交互式环境中输入以下代码：
wb = openpyxl.Workbook()

# wb.create_sheet("first",1)
print(wb.sheetnames)
sheet = wb['Sheet']
print(sheet.title)

這提供了使用行和列表示法對儲存格的訪問：

d = ws.cell(row=4, column=2, value=10)

访问多个cell

cell_range = ws[‘A1’:‘C2’]

行或列的範圍可以類似地獲得

 colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]

Worksheet.iter_rows()

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...    for cell in row:
...        print(cell)

Worksheet.iter_cols()

for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
...     for cell in col:
...         print(cell)

wb = Workbook()
>>> ws = wb.active
>>> treeData = [["Type", "Leaf Color", "Height"], ["Maple", "Red", 549], ["Oak", "Green", 783], ["Pine", "Green", 1204]]

Worksheet.append()

for row in treeData:
...     ws.append(row)

將標題加粗以使其更加突出，創建一個並將其應用於標題行中的所有單元格

 from openpyxl.styles import Font
 ft = Font(bold=True)
>>> for row in ws["A1:C1"]:
...     for cell in row:
...         cell.font = ft

for row in ws[1]:
	print(row)
	# for cell in row:
	row.font = ft

制作图表

from openpyxl.chart import BarChart, Series, Reference

chart = BarChart()
chart.type = "col"
chart.title = "Tree Height"
chart.y_axis.tiltle = "Height(cm)"
chart.x_axis.title = "Tree type"
chart.legend = None
data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1)
chart.add_data(data)
chart.set_categories(categories)
ws.add_chart(chart, "E1")
wb.save("TreeData.xlsx")

設置合併儲存格的樣式

 from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
>>> from openpyxl import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws.merge_cells('B2:F4')
>>>
>>> top_left_cell = ws['B2']
>>> top_left_cell.value = "My Cell"
>>>
>>> thin = Side(border_style="thin", color="000000")
>>> double = Side(border_style="double", color="ff0000")

top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
>>> top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
>>> top_left_cell.fill = fill = GradientFill(stop=("000000", "FFFFFF"))
>>> top_left_cell.font  = Font(b=True, color="FF0000")
>>> top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
>>>
>>> wb.save("styled.xlsx")

插入和刪除行和列，移動儲存格範圍

openpyxl.worksheet.worksheet.Worksheet.insert_rows()
openpyxl.worksheet.worksheet.Worksheet.insert_cols()
openpyxl.worksheet.worksheet.Worksheet.delete_rows()
openpyxl.worksheet.worksheet.Worksheet.delete_cols()

#从第6行开始数3行进行删除
ws.delete_cols(6, 3)

#从第7行下插入一行
 ws.insert_rows(7)

单元格的移动

ws.move_range("D4:F10", rows=-1, cols=2)
#这将使区域中的单元格向上移动一行，向右移动两列。单元格将覆盖任何现有的单元格。

ws.move_range("G4:H10", rows=1, cols=1, translate=True)
#这将把范围内公式中的相对引用移动一行和一列，原来单元格中的计算公式引用的单元格也同步移动一行和一列。