欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

xlwing操作excel

程序员文章站 2022-04-01 13:05:11
import sysimport osimport tkinter as tkimport tkinter.filedialog as tkfimport xlwings as xwdef getbillfiles(path): list =os.listdir(path) result = [] for i in range(0,len(list)): temp = os.path.join(path,list[i]) if os.pa....
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

相关标签: Python