学步园

# -*- 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 姓名

结果：