英文:
Open all Excel files in one folder and add sheets
问题
我正在尝试打开一个文件夹中的所有Excel (.xlsx)文件并添加三个工作表(命名为M1、M2和M3)。然后将其保存在现有的Excel文件中(非强制性)。你能帮忙吗?谢谢。
from openpyxl import load_workbook
import os
folder_path = r'C:\Users\alex\mob' # 替换为你的文件夹路径
for filename in os.listdir(folder_path):
if filename.endswith('.xlsx'):
file_path = os.path.join(folder_path, filename)
wb = load_workbook(file_path)
wb.create_sheet('M1')
wb.create_sheet('M2')
wb.create_sheet('M3')
wb.save(file_path)
这段代码可以用于一个文件夹中的所有.xlsx文件。
英文:
I am trying to open all Excel (.xlsx) files in one folder and add three sheets (named M1, M2 and M3). Then save it in the existing Excel file (not mandatory). Can you please help? Thanks.
from openpyxl import load_workbook
wb2 = load_workbook(r'C:\Users\alex\mob0.xlsx')
wb2.create_sheet('M1')
wb2.create_sheet('M2')
wb2.create_sheet('M3')
wb2.save(r'C:\Users\alex\mob0.xlsx')
This works for each Excel file, but I want to iterate/loop/do it for all files in one folder. All files are .xlsx
答案1
得分: 1
你可以使用 glob
来列出特定扩展名的所有文件在给定目录中:
import glob
from openpyxl import load_workbook
files = glob.glob("/some/random/location/*.xlsx")
for file in files:
wb2 = load_workbook(file)
wb2.create_sheet('M1')
wb2.create_sheet('M2')
wb2.create_sheet('M3')
wb2.save(file)
glob.glob()
的作用是返回与特定搜索匹配的所有文件的数组。在当前搜索中,你正在查找所有具有 .xlsx
扩展名的文件。
请注意,这只查看扩展名,而不是文件的内容,所以如果你有一个简单的 test.txt
文件,将其重命名为 test.xlsx
,你的程序可能会崩溃。
英文:
You can use glob
to list all files ending with specific extensions in a given directory:
import glob
from openpyxl import load_workbook
files = glob.glob("/some/random/location/*.xlsx")
for file in files:
wb2 = load_workbook(file)
wb2.create_sheet('M1')
wb2.create_sheet('M2')
wb2.create_sheet('M3')
wb2.save(file)
What glob.glob()
does is - it returns an array of all files matching a specific search. In the current search, you are looking for all files
with the .xlsx
extension.
Note that this only looks at the extension, not the contents of the file, so if you have a simple test.txt
file and rename it to test.xlsx
, your program will likely crash.
答案2
得分: 0
这回答了我的问题!非常感谢。
为了让我的计算机读取文件夹,我将目录更改为:
files = glob.glob(r'C:\Users\alex\mob\*.xlsx')
英文:
This answered my question! Thank you very much.
In order for my computer to read the folder, I changed the directory to:
files = glob.glob(r'C:\Users\alex\mob\*.xlsx')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论