xlwing操作excel

import sys
import os
import tkinter as tk
import tkinter.filedialog as tkf
import xlwings as xw

def getbillfiles(path):
    list =os.listdir(path)
    result = []
    for i in range(0,len(list)):
        temp = os.path.join(path,list[i])
        if os.path.isfile(temp) and os.path.splitext(temp)[1] == '.xlsx':
            result.append(temp)
    return result

if __name__ == '__main__':
    root = tk.Tk()
    root.withdraw()
    folderpath = tkf.askdirectory()
    if folderpath == '':
        sys.exit()

    app = xw.App(visible=False,add_book=False)

    result = [['账号','金额']]
    for file in getbillfiles(folderpath):
        yjzh = "'" + file.split('_')[1]
        wb = app.books.open(fullname=file)
        je = wb.sheets(1).used_range.columns(11).last_cell.value
        wb.close()
        print('账号: %s ,金额: %s' % (yjzh,je))
        result.append([yjzh,je])
    app.quit()
    xw.Book().sheets(1).range('A1').value = result

VBA版本

Option Explicit

Public Sub ExtractAmount()
    Dim folder, sht, dict
    folder = GetFolder()
    If folder = "" Then Exit Sub
     
    Set dict = GetYjzhJeDict(folder)
    Set sht = ThisWorkbook.Worksheets(1)
    WriteData sht, dict
    
End Sub

'写入worksheet
Sub WriteData(sht, dict)
    sht.Cells.ClearContents
    sht.Range("a1") = "账号"
    sht.Range("b1") = "金额"
    sht.Range("a2").Resize(dict.Count, 2) = Excel.Application.Transpose(Array(dict.keys, dict.items))
End Sub


'获取在途账单月结号及金额，返回dict
Function GetYjzhJeDict(path)
    Dim dict, filename, yjzh, je
    Set dict = CreateObject("Scripting.Dictionary")
    
    filename = Dir(path & "\*.xlsx")
    Do While filename <> ""
        yjzh = "'" & Split(filename, "_")(1)
        je = GetValue(path & "\" & filename)
        dict(yjzh) = je
        filename = Dir()
    Loop
        
    Set GetYjzhJeDict = dict
    Exit Function
End Function

'获取K列最后一行数据
Function GetValue(filename)
    Dim wb As Workbook, sht As Worksheet, rng As Range
    Set wb = Excel.Application.Workbooks.Open(filename)
    Set sht = wb.Worksheets(1)
    Set rng = sht.UsedRange.Columns(11)
    GetValue = rng.Cells(rng.Cells.Count).Value
    wb.Close
End Function


'返回选择的目录（单个）
Public Function GetFolder() As String
    Dim fdo
    Set fdo = Excel.Application.FileDialog(msoFileDialogFolderPicker)
    With fdo
        .Title = "请选择文件夹"
    .Show
    If .SelectedItems.Count = 1 Then
        GetFolder = .SelectedItems(1)
        Set fdo = Nothing
        Exit Function
    End If
    End With
    Set fdo = Nothing
    GetFolder = ""
End Function

 

本文地址：https://blog.csdn.net/qq_42678477/article/details/110242895