获取文件夹下的文件名，并将文件名、文件夹名写入excel中

#获取文件夹下的文件名
#将文件名、文件夹名写入excel中
#通过对话框实现文件或文件夹路径的选择并获得路径

import os
from openpyxl import Workbook,load_workbook
import tkinter as tk
from tkinter import filedialog

def list_dir(path):
    dir_lists = []
    lists = os.listdir(path)
    for li in lists:
        if os.path.isdir(li):
            dir_lists.append(li)
    return dir_lists

def list_file(path):
    file_lists = []
    lists = os.listdir(path)
    for li in lists:
        if os.path.isfile(li):
            filename = os.path.splitext(li)[0]
            fileext = os.path.splitext(li)[1]
            file_lists.append([filename,fileext])
    return file_lists

def write_excel(file_name,file_list,root):
    if os.path.exists(file_name):
        # 打开存在的excel表
        workbook = load_workbook(file_name)
        sheet = workbook.active
    else:
        # 创建新的excel表
        workbook = Workbook()
        sheet = workbook.active
    #print(sheet.max_row)
    if (sheet.max_row == 1 and sheet.max_column == 1):  # 判断是否为空表
        header = ['序号', '文件名', '文件夹名','扩展名','文件全名','文件路径']
        sheet.append(header)
    # print(sheet.max_row)
    no = sheet.max_row
    for list in file_list:
        files = list[0]
        if len(files)>0 :
            dir = list[1]
            for file in files:
                filename = file[0]
                fileext = file[1]
                fullname = filename + fileext
                dir_path =os.path.join(root,dir)
                file_path = os.path.join(dir_path,fullname)
                # 添加一整行
                row_lst = [no, filename, dir,fileext,fullname,file_path]
                sheet.append(row_lst)
                #创建超链接
                sheet.cell(no+1,6).hyperlink = file_path
                no += 1

    #保存
    workbook.save(file_name)

def printHead(headStr):
    num = 40
    tplt = '\n{}'
    print(tplt.format(num * '#'))
    print(headStr)
    print(tplt.format(num * '#'))
    temp = '''请选择路径：
        '''
    print(temp)

def get_select_dir():
    root_tk = tk.Tk()
    root_tk.withdraw()
    folderpath = filedialog.askdirectory()  # 获取选择好的文件夹
    print("文件夹路径：", folderpath)
    return folderpath

def main():
    # 初始化变量
    headStr = '''
                文件名提取程序V2.0
                版 本 号：V2.01
                作   者：yxmichael
                更新时间:2021-6-17
        '''
    printHead(headStr)
    current_path = os.getcwd()
    # root = os.getcwd()
    root = get_select_dir()
    if root:
        #必须要进入路径
        os.chdir(root)
        file_name = '提取文件名.xlsx'
        file_list =[]
        dir_list = list_dir(root)
        for dir in dir_list:
            dir_path = os.path.join(root,dir)
            #print(dir_path)
            os.chdir(dir_path)
            lists = list_file(dir_path)
            #print(lists)
            file_list.append([lists,dir])
            #print(file_list)

        #切换到当前路径
        os.chdir(current_path)
        write_excel(file_name,file_list,root)

        #打开文件
        os.startfile(file_name)

main()