隔壁站import os
import sys
import time
from openpyxl import Workbook
from openpyxl.styles import Alignment,Font,colors
'''
源码写的不规整，顺手想起啥写了啥，有需要的可以自己看看思路规整一下
'''
def getFilePath():# 获取当前目录
return os.path.split(os.path.realpath(__file__))[0]
fls = []
def showFiles(dir_path:str):
"""遍历获取文件路径，添加到fls
Args:
dir_path (str): 根目录路径
"""
dir_or_files = os.listdir(dir_path)
for item in dir_or_files:
p = os.path.join(dir_path,item)
if os.path.isdir(p):
fls.append(p)
showFiles(p)
elif os.path.isfile(p):
fp = os.path.join(dir_path,item)
fls.append(fp)
def writeToExcel(d:list,fpath:str ):
"""写入数据到excel
Args:
d (list): 要写入的数据，是个二维数组[rows:list]
fpath (str, optional): exce输出路径.
Returns:
_type_: str:xlsx保存路径
"""
wb = Workbook()# 创建workbook对象，写入模式
ws = wb['Sheet']
wb.remove(ws)#删除默认生成的sheet页
sheetName = "文件汇总"#新建sheet页名称
ws = wb.create_sheet(sheetName)
for row in d:
ws.append(row)
for i,r in enumerate(ws):
if i !=0:
ws.cell(row=i+1,column=2).hyperlink=ws.cell(row=i+1,column=3).value
ws.cell(row=i+1,column=2).style = "Hyperlink"
elif i==0:
t = ws[1]
style = Font(name='黑体',color=colors.BLACK,bold=True,size=18)
for c in t:
c.alignment = Alignment(horizontal='center', vertical='center')
c.font = style
# ws.delete_cols(3)#这个作用就是删除文件路径
# filePath 是指保存的 excel 文件路径
filePath = "文件统计_{}.xlsx".format(timeNow('Y'))
filePath = fpath +'\\'+ filePath
wb.save(filePath)
return filePath
def timeNow(tp = 'A'):
"""[获取当前时间返回]
Returns:
[string]: [YYYY-MM-DD HH:MM:SS]
"""
from time import strftime
now = None
if tp == 'A':
now = strftime("%Y-%m-%d %H:%M:%S")
elif tp == 'Y':
now = strftime("%Y-%m-%d-%H-%M-%S")
return '{}'.format(now)
fu = 0
def lsToRow(s:str,ls:list,fu):#将数据转化为二维数组，便于写入
tls = []
for i in ls:
fname = i.split('\\')[-1]
t = []
t.append(fname)
t.append(i)
sm = addSerialNum(i,s)
t.insert(0,sm)
sx = judge(i)
t.append(sx)
iinfo = getFileInfo(i)
for j in iinfo:
t.append(j)
tls.append(t)
return tls
def addSerialNum(fp:str,root:str):#判断文件层级
s_num = ''
t = fp.split(root+'\\')[1]
n = t.count('\\')
s_num = '{}'.format(n)
return s_num
def tstampToDate(timeStamp:int):#转化时间戳
timeArray = time.localtime(timeStamp)
otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
# 2013--10--10 23:40:00
return otherStyleTime
def getFileInfo(fpath:str):#获取文件信息
statinfo = os.stat(fpath)
file_size = statinfo.st_size/1024
create_time = tstampToDate(statinfo.st_ctime)
modify_time = tstampToDate(statinfo.st_mtime)
return file_size,create_time,modify_time
def judge(fp):#判断文件夹还是文件
if os.path.isdir(fp):
return '<文件夹>'
elif os.path.isfile(fp):
return '<文件>'
#-------------------------
info = '''
*********************************
* 遍历文件夹生成excel统计表 *
* 创建时间：2022-3-25 *
* 更新时间：2022-3-26 *
*********************************
'''
print(info)
s = input('#>:请输入文件夹路径,回车确认:\t')
# s = getFilePath()
showFiles(s)
rl = lsToRow(s,fls,fu)
rl.insert(0,['层级','文件名称','文件路径','属性','大小(K)','创建时间','上次修改'])
o = input('#>:请输入统计表输出路径(默认同输入路径):\t')
#防止空输入导致出错
if o !='':
o = o
else:
o = s
fp = writeToExcel(rl,o)
print('#>:处理完毕！')
print('#>:文件保存在: {}'.format(fp))
print('#>:按任意键退出')
input()#按任意键退出
以下为VBA源码
Public iFileSys As Object
Sub 遍历文件夹()
Cells.Delete '清除表格所有数据
Columns("B:B").NumberFormatLocal = "@"
Columns("F:G").NumberFormatLocal = "yyyy-mm-dd hh:mm:ss"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
iPath = .SelectedItems(1)
End If
End With
If iPath = "False" Or Len(iPath) = 0 Then Exit Sub '所选文件夹为空，结束脚本
ReDim arr(1 To 7, 1 To 1)
arr(1, 1) = "层级"
arr(2, 1) = "文件名"
arr(3, 1) = "完整路径（包含超链接）"
arr(4, 1) = "类型"
arr(5, 1) = "文件大小（KB）"
arr(6, 1) = "创建时间"
arr(7, 1) = "修改时间"
Set iFileSys = CreateObject("Scripting.FileSystemObject")
Call GetFolderFile(iPath, arr, 0)
arr = TransposeArray(arr)
ActiveSheet.Range("A1").Resize(UBound(arr), 7) = arr
For i = 2 To UBound(arr)
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=Cells(i, 3)
Next
ActiveSheet.Rows.AutoFit
ActiveSheet.Columns.AutoFit
MsgBox "Done."
End Sub
Private Sub GetFolderFile(ByVal nPath As String, arr As Variant, TreeNum As Long)
On Error Resume Next
Set iFolder = iFileSys.GetFolder(nPath)
Set sFolder = iFolder.SubFolders
Set iFile = iFolder.Files
Call AddList(iFolder, arr, TreeNum)
For Each gFile In iFile
Call AddList(gFile, arr, TreeNum)
Next
'递归遍历所有子文件夹
For Each nFolder In sFolder
Call GetFolderFile(nFolder.Path, arr, TreeNum + 1)
Next
On Error GoTo 0
End Sub
Private Sub AddList(ByVal obj As Object, arr As Variant, TreeNum As Long)
On Error Resume Next
ub = UBound(arr, 2) + 1
ReDim Preserve arr(1 To 7, 1 To ub)
arr(1, ub) = TreeNum '层级
arr(2, ub) = CStr(IIf(Len(obj.Name) = 0, "\", obj.Name)) '文件名
arr(3, ub) = obj.Path '文件路径
arr(4, ub) = obj.Type '文件类型
arr(5, ub) = Format(obj.Size / 1024, "#,##0.00") '文件大小(KB)
arr(6, ub) = Format(obj.DateCreated, "yyyy-mm-dd hh:mm:ss") '创建时间
arr(7, ub) = Format(obj.DateLastModified, "yyyy-mm-dd hh:mm:ss") '修改时间
On Error GoTo 0
End Sub
Function TransposeArray(arrA) As Variant
Dim aRes()
If IsArray(arrA) Then
ReDim aRes(LBound(arrA, 2) To UBound(arrA, 2), LBound(arrA, 1) To UBound(arrA, 1))
For i = LBound(arrA, 1) To UBound(arrA, 1)
For j = LBound(arrA, 2) To UBound(arrA, 2)
aRes(j, i) = arrA(i, j)
Next
Next
TransposeArray = aRes
End If
End Function
