读取excel文件
使用 openpyxl 模块
openpyxl 官方文档: https://openpyxl.readthedocs.io/en/stable/
import openpyxl
wb = openpyxl.load_workbook('E://manictime//1.xlsx')
从工作簿调取工作表
sheet1 = wb['sheet1']
获取单元格
from openpyxl.utils import get_column_letter
print(sheet1['A1'].value)
name = sheet1['A1']
print('row:'+str(name.row)+' column:'+get_column_letter(name.column)+' '+name.coordinate+' '+name.value)
运行结果:
从数字转换到字母要用到 from openpyxl.utils import get_column_letter
从字母转换到数字要用到 from openpyxl.utils import column_index_from_string
获取表格最大行与列
print(str(sheet1.max_row)+' '+str(get_column_letter(sheet1.max_column)))
运行结果:
切割单元格:
sheet_tuple1 = tuple(sheet1['A1:E5'])
for objectOftuple in sheet_tuple1:
for cell in objectOftuple:
print(cell.coordinate, cell.value)
运行结果:
写入Excel文档
创建并保存excel文档
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet(title='sheet1')
print(ws)
print(wb.sheetnames)
print(ws.title)
wb.save('E://manictime//1_total.xlsx')
运行结果:
删除一个sheet
wb.remove(worksheet=wb['sheet1'])
写入值
ws['A1'] = 'name'
小项目练习
我一直使用ManicTime这个软件,最近发现它可以导出csv文件,记录了应用的使用时间,像下面这个样子:
为了方便使用python进行处理,我把它转换成了xlsx格式
源码
from _pydecimal import Decimal
import openpyxl
import re
def t2s(t):
h, m, s = t.strip().split(":")
return int(h) * 3600 + int(m) * 60 + int(s)
apps = openpyxl.load_workbook('E://manictime//1.xlsx')
apps_sheet = apps['sheet1']
apps_name_column = tuple(apps_sheet['E2': 'E' + str(apps_sheet.max_row)])
apps_time_column = tuple(apps_sheet['D2': 'D' + str(apps_sheet.max_row)])
app_name_time = {'Cent Browser': 0, 'Typora': 0, 'Visual Studio Code': 0, 'HBuilder X': 0, 'PyCharm': 0}
name_index = re.compile(r'E\d+')
time_index = re.compile(r'D\d+')
for row_number in range(0, int(apps_name_column.__len__() - 1)):
mo1 = name_index.search(str(apps_name_column[row_number]))
index1 = mo1.group()
mo2 = time_index.search(str(apps_time_column[row_number]))
index2 = mo2.group()
if apps_sheet[index1].value in app_name_time:
time = t2s(str(apps_sheet[str(index2)].value))/3600
time = Decimal(time).quantize(Decimal('0.00'))
app_name_time[apps_sheet[index1].value] += time
for i in app_name_time.items():
print(i)
输出结果(单位小时制):
对比一下manictime的统计:
好像误差还在能接受的范围内