如何使用Python提取每张工作表的列名并输出到新的excel工作簿

Question

我有以下代码尝试循环遍历excel工作簿的每一张工作表，并返回列名，这样我就可以将输出保存到一个新的工作簿中，并在每个工作表中使用相应的选项卡名和列名。

然而，它只返回给我新工作簿中的选项卡名称和列名都不见了，尽管我为它指定了一个循环，有人能告诉我吗？

from openpyxl import load_workbook
from datetime import date
import pandas as pd
from datetime import datetime, date, timedelta
from pandas import ExcelWriter
#import itertools

# Declaring the sheet names within workbook
sheet_names = ['Input_A','Input_B','Input_C','Input_D']
#Open workbook
wb = load_workbook('RS_dataset.xlsx', data_only=True)
writer = ExcelWriter('RS_sr_bs_test.xlsx')
# Logic - Open workbook, get sheet name, get all column names within the sheet
for sheet_name in sheet_names:
# Open workbook and get sheet name
ws1 = wb.get_sheet_by_name(sheet_name)
# Load datasets within workbook
df1 = pd.DataFrame()
# Define year
get_year = date.today().year
# Get the previous month
past = date.today().replace(day=1) - timedelta(days=1)
get_month = past.month
# Get the current quarter
current_date = datetime.now()
current_quarter = round((current_date.month - 1) / 3 + 1)
get_quarter = current_quarter
# Create a list to store column_names
col_names = []
for col_name in ws1.iter_rows(min_row=1, max_row=1, values_only=True):
col_names.append(col_name)

df1.to_excel(writer, sheet_name)
writer.save()

Answer 1

您似乎已经有了工作表名称，并且似乎只使用了一行作为列名，您可以直接从panda读取excel文件并获得所需信息。

df = pd.read_excel('RS_dataset.xlsx', sheet_name=None)
sheet_names = ['Input_A','Input_B','Input_C','Input_D']
for sheet in sheet_names:
if sheet in df.keys():
col_names = df[sheet].columns.to_list()
# you can write the col_names now.