组件选型
- 直接从网上找到的选型结论
- 单从读写的性能上考虑,win32com的性能是最好的,xlwings其次。
- 综合考虑,xlwings的表现最佳,正如其名,xlwings——Make Excel Fly!
- 从操作来看,openpyxl功能最强大,但读写性能过于糟糕,尤其是写大表时会占用大量内存。
- 本次只是做些简单的工具所有使用openpyxl 最方便。
Openpyxl
官网:https://openpyxl.readthedocs.io/en/stable/index.html
安装
pip install openpyxl
Sample code:
from openpyxl import Workbook
wb = Workbook()
# grab the active worksheet
ws = wb.active
# Data can be assigned directly to cells
ws['A1'] = 42
# Rows can also be appended
ws.append([1, 2, 3])
# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()
# Save the file
wb.save("sample.xlsx")
常用命令
# 常用模块
import json
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Alignment, PatternFill, Font
from openpyxl.styles.colors import RED, YELLOW, BLUE, BLACK
from openpyxl.utils import get_column_letter
# 1.加载已有excel
wb = load_workbook("file_name")
# 2.输出所有sheet 名称
print json.dumps(wb.get_sheet_names(), ensure_ascii=False)
# 3.通过sheet name 获取 sheet 对象<