python Excel操作

读取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)

输出结果（单位小时制）：