用Python3写的Excel汇总程序

代码如下:

import os
import tkinter.messagebox as tkMessageBox
import sys
import win32com.client
import dateutil.parser
import win32process
import win32api
import win32con

def close_excel_by_force(excel):
    # Get the window's process id's
    hwnd = excel.Hwnd
    t, p = win32process.GetWindowThreadProcessId(hwnd)
    # Ask window nicely to close
    try:
        handle = win32api.OpenProcess(win32con.PROCESS_TERMINATE, 0, p)
        if handle:
            win32api.TerminateProcess(handle, 0)
            win32api.CloseHandle(handle)
    except:
        pass


def create_total_page(tgroup, wb, month, qty_small, qty_big):
    has_total_page = False
    page_name = '汇总'
    total_sheet = None
    for sheet in wb.Sheets:
        if sheet.Name == page_name:
            has_total_page = True
            break

    if not has_total_page:
        wb.Worksheets.Add().Name = page_name
        total_sheet = wb.Worksheets(page_name)
        # Add Title
        total_sheet.Cells(1, 1).Value = '月份'
        total_sheet.Cells(1, 2).Value = '日期'
        total_sheet.Cells(1, 3).Value = '散办数量'
        total_sheet.Cells(1, 4).Value = '大办数量'
        total_sheet.Cells(1, 5).Value = '总件数'
        xrange = total_sheet.Range(total_sheet.Cells(1, 1), total_sheet.Cells(1, 5))
        xrange.ColumnWidth = 15
        xrange.HorizontalAlignment = -4108
        xrange.Borders.LineStyle = 1
        xrange.Interior.ColorIndex = 44
    else:
        total_sheet = wb.Worksheets(page_name)
        last_row = total_sheet.usedrange.rows.count + 1
        print('last row:' + str(last_row))
        total_sheet.Cells(last_row, 1).Value = month
        total_sheet.Cells(last_row, 2).Value = str(tgroup[0]) + '~' + str(tgroup[1])
        total_sheet.Cells(last_row, 3).Value = qty_small
        total_sheet.Cells(last_row, 4).Value = qty_big
        total_sheet.Cells(last_row, 5).Value = qty_small + qty_big
        xrange = total_sheet.Range(total_sheet.Cells(last_row, 1), total_sheet.Cells(last_row, 5))
        xrange.ColumnWidth = 15
        xrange.HorizontalAlignment = -4108
        xrange.Borders.LineStyle = 1


def create_summary(tgroup, wb, ws, row, month, qty_small, qty_big):
    ws.Rows(row).Insert(1)
    ws.Rows(row).Interior.ColorIndex = 6
    ws.Cells(row, 1).Value = '散办数量 :'
    ws.Cells(row, 2).Value = qty_small

    ws.Rows(row).Insert(1)
    ws.Rows(row).Interior.ColorIndex = 6
    ws.Cells(row, 1).Value = '大办数量 :'
    ws.Cells(row, 2).Value = qty_big

    # Total qty
    ws.Rows(row).Insert(1)
    ws.Rows(row).Interior.ColorIndex = 6
    ws.Cells(row, 1).Value = str(month) + '/' + str(tgroup[0]) + '~' + str(month) + '/' + str(tgroup[1])
    ws.Cells(row, 2).Value = qty_small + qty_big
    create_total_page(tgroup, wb, month, qty_small, qty_big)


folder = './dest/'
title = 'Excel Process Tool'
allFiles = os.listdir(folder)
if len(allFiles) <= 0:
    tkMessageBox.showerror(title, "Error, No .xlsx Excel File")
    sys.exit()

for file in allFiles:
    try:
        fileName = folder + file
        excel = win32com.client.Dispatch("Excel.Application")

        absPath = os.path.abspath(fileName)
        print(absPath)
        wb = excel.Workbooks.Open(absPath)
        ws = wb.Worksheets(1)
        rows = ws.UsedRange.Rows.Count
        columns = ws.UsedRange.Columns.Count
        dateColumn = -1
        qtyColumn = -1

        for i in range(1, columns + 1):
            if ws.Cells(1, i).Value == '預計走辦期Lịch dự tính xuất mẫu':
                '預計走辦期Lịch dự tính xuất mẫu'
                dateColumn = i
                print(ws.Cells(1, dateColumn).Value)
            if ws.Cells(1, i).value == '數量Số lượng':
                qtyColumn = i
                print(ws.Cells(1, qtyColumn).Value)

        if dateColumn == -1:
            tkMessageBox.showerror(title, "無 '預計走辦期Lịch dự tính xuất mẫu' 櫚位")
            sys.exit()
        if qtyColumn == -1:
            tkMessageBox.showerror(title, "無 '數量Số lượng' 櫚位")
            sys.exit()

        # Define data range
        dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(rows, columns))

        # Order By Date
        dataRange.Sort(Key1=ws.Cells(1, dateColumn), Order1=1, Orientation=1)

        # loop data rows
        thresholds = [[1, 10], [11, 20], [21, 31]]
        globalGroup = []
        currentGroup = []
        globalMonth = -1
        currentMonth = -1
        qty_small = 0
        qty_big = 0
        i = 1
        rows += 1
        while i < rows:
            i += 1
            is_last_record = (i == rows)
            if is_last_record:
                create_summary(globalGroup, wb, ws, i, globalMonth, qty_small, qty_big)
            else:
                qty = ws.Cells(i, qtyColumn).Value
                if qty == "" or qty is None:
                    continue
                dateValue = ws.Cells(i, dateColumn).Value
                if dateValue == "" or dateValue is None:
                    continue
                date = dateutil.parser.parse(str(dateValue))
                print(date)
                month = date.month
                day = date.day

                # Handle month
                currentMonth = month
                if globalMonth < 0:
                    globalMonth = month

                # Handle day
                for tempgroup in thresholds:
                    if tempgroup[0] <= day <= tempgroup[1]:
                        currentGroup = tempgroup
                        if len(globalGroup) <= 0:
                            globalGroup = tempgroup
                        break
                if globalMonth != currentMonth or globalGroup != currentGroup:
                    # Write Summary
                    create_summary(globalGroup, wb, ws, i, globalMonth, qty_small, qty_big)

                    globalGroup = currentGroup
                    globalMonth = currentMonth
                    rows += 3
                    # Clear qty
                    qty_small = 0
                    qty_big = 0
                else:
                    # Sum qty
                    if qty == "":
                        continue
                    if qty > 10:
                        qty_big += qty
                    else:
                        qty_small += qty
        wb.Save()
        tkMessageBox.showinfo(title, "完成!")
    except Exception as e:
        tkMessageBox.showinfo(title, e)
        print(e)
    finally:
        close_excel_by_force(excel)
        # pyinstaller --hiddenimport win32timezone -F --icon="C:\Users\Phoenix\Downloads\Pink_Flower.ico" Control.py