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
上一篇: Paddle.fluid之loader
下一篇: 使用Perl生成随机密码