python读写excel,xlsx 模块选择
# 各种模块介绍
# 跨平台模块:
xlrd、xlwt、xlsxwriter、pandas、pyexcel-xls
- 读最好用pyexcel-xls
- 写最好用xlsxwriter
写数据时,pandas速度最快,xlwt其次,xlsxwriter最慢,但是xlsxwriter支持xlsx和样式等,最方便易用。
# windows平台专用模块xlwings
xlwings比较特殊,它连接windows上的excel程序,跟excel通信,必须安装了Excel才能用,也只能windows上用。其他的大部分模块都是直接读写文件,不需要安装excel。xlwings优点是可以读写,可以执行vba程序(如自动下拉计算等),对打开的excel直接操作(就仿佛直接用鼠标在点)。
# pyexcel-xls
GitHub上有一个pyexcel_xls,直接把整个xls文件读成一个字典,sheet名作为key,里面的表是二维数组,用起来非常方便。用了它可以不用xlrd。如果想使用xlrd,可以看下面。
data_dict = pyexcel_xls.get_data('test.xlsx') #get an OrderedDict
sheet1_2d_data = data_dict['sheet1'] # get sheet data, 2d array
first_sheet_2d_data = next(iter(data_dict.values())) # get data_dict's first sheet data
1
2
3
2
3
# xlsxwriter
官方文档:https://xlsxwriter.readthedocs.io/index.html
用法示例:
import xlsxwriter, datetime
def main():
workbook = xlsxwriter.Workbook('测试.xlsx')
worksheet = workbook.add_worksheet('sheet1')
#写字符串,居中对齐
center_format = workbook.add_format()
center_format.set_align('center')
worksheet.write_row('A1', ['日期', '价格'], center_format)
#写日期
date_format = workbook.add_format()
date_format.set_num_format('yyyy/mm/dd')
date_format.set_align('center')
worksheet.write(1, 0, datetime.date(2020, 7, 7), date_format)
#写float,设置小数显示位数
float_4_format = workbook.add_format()
float_4_format.set_num_format('0.0000')
float_4_format.set_align('center')
worksheet.write(1, 1, 1.23, float_4_format)
#设置宽度
worksheet.set_column(0, 0, 12)
worksheet.set_column(1, 1, 25)
workbook.close()
if __name__ == '__main__':
main()
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
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
# xlwings
# 使用当前已存在的excel app实例,防止冲突
打开多个excel app时可能会冲突,比如多个app不能打开同一个excel文件。
app = xlwings.apps.active
if not app:
app = xlwings.App(visible=True, add_book=False)
print('create new excel app')
app.visible = True
app.display_alerts = False #设置为False后修改文件关闭不会保存提醒
app.books.open('xxx.xlsx') #这样会使用现有的excel app实例,不会创建新的导致冲突
1
2
3
4
5
6
7
2
3
4
5
6
7
# range概念
xlwings中,range是区域性的概念,cell就是1*1的range,但是并没有cell这个东西,都是range
另外获取全部数据时,有个函数叫used_range,这个东西如果你表格中左上角没用,那它是会忽略左上角的,并不会从A1开始。
想获取从A1到所有的右下角的数据,可以对sheet切片取值实现:
def get_xlwings_total_range(sheet:xlwings.Sheet) -> xlwings.Range:
'return range from A1 to all used range'
return sheet[:sheet.used_range.last_cell.row, :sheet.used_range.last_cell.column]
1
2
3
2
3
获取某个cell对应的列的cell(used,不是最上面),这个方法很僵硬:
def get_xlwings_cell_col(cell:xlwings.Range):
'Given a cell is a 1x1 Range, return the whole column range.'
sheet = cell.sheet
for col in sheet.used_range.columns:
if col.column == cell.column:
return col
1
2
3
4
5
6
2
3
4
5
6
# xlrd使用示例
建议用上面的pyexcel-xls,更好用。
import xlrd, datetime
def main():
file_name = 'test.xls'
book = xlrd.open_workbook(file_name)
sheet = book.sheets()[0]
nrows = sheet.nrows # 有内容的总行数
for i in range(nrows):
if i<3: #前3列跳过
continue
row = sheet.row_values(i)
# acc_name = str(row[0]).strip() # 读取第一列的字符串
# total_value = float(row[1]) # 读取第二列的浮点数
# 读取第3列日期
cell = sheet.cell(i, 3) # type, <class 'xlrd.sheet.Cell'>
py_date = get_xls_cell_date(cell)
def get_date_by_str(date_str:str):
spliter = re.sub('\d', '',date_str)
if not spliter:
res_date = datetime.date(int(date_str[:4]), int(date_str[4:6]), int(date_str[6:]))
return res_date
spliter = spliter[0]
ymd = date_str.split(spliter)[:3]
res_date = datetime.date(int(ymd[0]), int(ymd[1]), int(ymd[2]))
return res_date
def get_xls_cell_date(cell):
if cell.ctype == 3:
ms_date_number = cell.value
year, month, day, hour, minute, second = xlrd.xldate_as_tuple(ms_date_number, xlrd.Book.datemode)
res_date = datetime.date(year, month, day)
return res_date
else:
date_str = str(cell.value)
return get_date_by_str(date_str)
if __name__ == '__main__':
main()
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
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
xlrd 读取日期时间格式参考:https://blog.csdn.net/alvin__yang/article/details/51199307
编辑 (opens new window)
上次更新: 2021/10/11, 12:43:58