yunwei-tools►Python

Python3操作excel 

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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2020/1/17 19:55
# @Author  : Ropon
# @File    : excel_test.py

# 读excel
# pip3 install xlrd
# import xlrd

# excelName = r"./excel/1-12月.xls"
# book = xlrd.open_workbook(excelName)
# 获取所有sheet页的名字
# sheetNames = book.sheet_names()
# print(sheetNames)
# # 根据sheet索引获取表格sheet
# sheet = book.sheet_by_index(8)
# # 根据sheet名字获取表格sheet
# sheet2 = book.sheet_by_name("9月")
# 获取当前sheet的名称
# print(sheet.name)

# 获取指定行和列的数据 cell(行索引, 列索引)
# r1 = sheet.cell(0, 0).value
# r2 = sheet.cell(1, 0).value
# r3 = sheet.cell(1, 1).value
# r4 = sheet.cell(1, 2).value
# print(r1, r2, r3, r4)

# 获取单元格数据的类型
# ctype_text = {
#     XL_CELL_EMPTY: 'empty', 0
#     XL_CELL_TEXT: 'text', 1
#     XL_CELL_NUMBER: 'number', 2
#     XL_CELL_DATE: 'xldate', 3
#     XL_CELL_BOOLEAN: 'bool', 4
#     XL_CELL_ERROR: 'error', 5
#     XL_CELL_BLANK: 'blank', 6
# }
# cType = sheet.cell(100, 10).ctype
# print(cType)

# 获取excel有多少行和列
# rows = sheet.nrows
# cols = sheet.ncols
# print(rows, cols)

# 根据索引获取某一行所有数据
# rowData = sheet.row_values(0)
# print(rowData)

# 根据索引获取某一列所有数据
# colData = sheet.col_values(2)
# print(colData)

# 写excel
# pip3 install xlwt
import xlwt
# 只能写不能读

# 创建样式字体红色并加粗
style = "font:colour_index red, bold on;"
styleBoldRed = xlwt.easyxf(style)

# 新建一个excel
book = xlwt.Workbook()
# 添加sheet页
sheet = book.add_sheet("技术部")
# 将数据写入指定行和列
sheet.write(0, 0, "one", styleBoldRed)
sheet.write(0, 1, "two")
sheet.write(1, 1, "three")
sheet.write(1, 2, "four")
# 保存
book.save("writeTest.xls")

# 修改excel
# pip3 install xlutils3
# import xlrd
# from xlutils3.copy import copy
#
# excelName = "./excel/1-12月.xls"
# book = xlrd.open_workbook(excelName)
# writeBook = copy(book)
#
# sheet = book.sheet_by_name("9月")
# r1 = sheet.cell(2, 2).value
# print(r1)
# data = r1 + "测试写入"
# # 查看类的成员
# # print(dir(writeBook))
#
# # 根据sheet索引获取表格sheet
# writeSheet = writeBook.get_sheet(8)
# writeSheet.write(2, 16, data)
# writeBook.save("1-12月_ok.xls")

# openpyxl 读excel 不支持xls
# import openpyxl

# excelName = r"./excel/1-12月1.xlsx"
# book = openpyxl.load_workbook(excelName)
# 获取所有sheet页的名字
# sheetNames = book.get_sheet_names()
# print(sheetNames)
# 根据sheet索引获取表格sheet
# sheet = book.worksheets[8]
# 根据sheet名字获取表格sheet
# sheet2 = book.get_sheet_by_name("9月")
# 获取当前sheet的名称
# print(sheet.title)

# 获取excel有多少行和列
# rows = sheet.max_row
# cols = sheet.max_column
# print(rows, cols)

# 获取每一行的内容，这是一个生成器，有每一行的数据 每一行数据有一个元素类型包裹
# sheet.rows

# 获取每一列的内容，这是一个生成器，有每一列的数据 每一列数据有一个元素类型包裹
# sheet.columns

# 根据索引获取第一行所有数据
# for cell in list(sheet.rows)[0]:
#     print(cell.value)

# 根据索引获取某一列所有数据
# for cell in list(sheet.columns)[1]:
#     print(cell.value)

# 获取指定行和列的数据 cell(行索引, 列索引) 索引从1开始
# r1 = sheet.cell(1, 1).value
# r2 = sheet.cell(2, 1).value
# r3 = sheet.cell(2, 2).value
# r4 = sheet.cell(2, 3).value
# print(r1, r2, r3, r4)

# 获取第一行第一列（A）单元格数据
# print(sheet["A1"].value)

# openpyxl 写excel 不支持xls
# import openpyxl
#
# # 新建一个excel
# book = openpyxl.Workbook()
# # 添加sheet页
# sheet = book.create_sheet("技术部")
# # 获取当前活跃页 默认第一工作页
# # sheet = book.active
#
# # 将数据写入指定行和列 索引从1开始
# sheet.cell(1, 1, "one")
# sheet.cell(1, 2, "two")
# sheet.cell(2, 2, "three")
# sheet.cell(2, 3, "four")
# # 保存
# book.save("writeTest2.xlsx")