# -*- coding: utf-8 -*-
"""
Python3.2 operate Excel_2007
"""
import sys
import datetime
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
from openpyxl import load_workbook
def read_excel():
""" Read an existing workbook
"""
wb = load_workbook(filename = r'f:/empty_book.xlsx')
sheet_ranges = wb.get_sheet_by_name(name = 'range names')
print(sheet_ranges.cell('D18').value) # D18
def write_excel():
""" Write a workbook
"""
wb = Workbook()
dest_filename = r'f:/empty_book.xlsx'
ws = wb.worksheets[0]
ws.title = "range names"
for col_idx in range(1, 40):
col = get_column_letter(col_idx)
for row in range(1, 600):
ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)
ws = wb.create_sheet()
ws.title = 'Pi'
ws.cell('F5').value = 3.14
wb.save(filename = dest_filename)
def number_formats():
""" Using number formats
"""
wb = Workbook()
ws = wb.worksheets[0]
# set date using a Python datetime
ws.cell('A1').value = datetime.datetime(2010, 7, 21)
print(ws.cell('A1').style.number_format.format_code) # returns 'yyyy-mm-dd'
# set percentage using a string followed by the percent sign
ws.cell('B1').value = '3.14%'
print(ws.cell('B1').value) # returns 0.031400000000000004
print(ws.cell('B1').style.number_format.format_code) # returns '0%'
def read_excel_file(file=r'F:\testData\test.xlsx', name= None) :
""" 考勤表解析
"""
list = []
wb = load_workbook(file, use_iterators=True)
for ws in wb.worksheets:
for row in ws.iter_rows():
map = {}
for cell in row:
# print(cell)
cell_value = cell[3]
if cell_value:
if cell[1] == "D" : # name
map['name'] = cell_value
if cell[1] == "E" : # date
cell_value = str(cell_value)[0:-9]
map['date'] = cell_value
if cell[1] == "O" : # coming time
map['coming_time'] = cell_value
if cell[1] == "P" : # leaving time
map['leaving_time'] = cell_value
if cell[1] == "Q" : # work status
map['work_status'] = cell_value
list.append(map)
__format_print(list, name)
def __format_print(list=None, name= None):
""" 格式化打印结果
"""
if name:
for item in list:
if name in item.values():
print("%s\t%s\t%s\t%s\t%s" % (
item.get("name"), item.get("date"), item.get("coming_time"), item.get("leaving_time"), item.get("work_status")
))
def __get_file_name() :
""" 获取共享目录下记录考勤的excel文件
"""
shared_directory = r'//192.168.102.91/考勤/'
now = datetime.datetime.now()
year = str(now.year)
month = str(now.month)
shared_directory += str(year) + "年/" + year + "." + month +"月份考勤.xlsx"
return shared_directory
if __name__== "__main__":
if len(sys.argv) > 1:
name = sys.argv[1]
read_excel_file(file = __get_file_name(), name= name)
调用:
python F:/python/TestPython/_openpyxl.py 姓名
结果: