python - 使用python将范围从多个工作簿复制到主工作簿中的新工作表
问题描述
我有一堆文件,我正在运行一个循环来做一些计算。这个循环已经完成,但我将根据原始文件将结果保存到新文件中。
file1.xlsx
file2.xlsx
运行代码后:
results/file1_results.xlsx
results/file2_results.xlsx
这是文件的循环:
directory = os.getcwd()
for filename in os.listdir(directory):
if filename.endswith(".xlsx"):
wb = load_workbook(filename)
ws = wb.active
max_row = ws.max_row
ws["CX1"] = "Humanas"
for row in range(2, max_row + 1):
ws["CX{}".format(row)] = round((
ws['R{}'.format(row)].value +
ws['U{}'.format(row)].value +
ws['X{}'.format(row)].value +
ws['AA{}'.format(row)].value +
ws['AD{}'.format(row)].value +
ws['AG{}'.format(row)].value +
ws['AJ{}'.format(row)].value +
ws['AM{}'.format(row)].value ) * (50/8))
ws["CY1"] = "Exatas"
for row in range(2, max_row + 1):
ws["CY{}".format(row)] = round((
ws['AP{}'.format(row)].value +
ws['AS{}'.format(row)].value +
ws['AV{}'.format(row)].value +
ws['AY{}'.format(row)].value ) * (50/4))
ws["CZ1"] = "Linguagens"
for row in range(2, max_row + 1):
ws["CZ{}".format(row)] = round((
ws['BB{}'.format(row)].value +
ws['BE{}'.format(row)].value +
ws['BH{}'.format(row)].value +
ws['BK{}'.format(row)].value +
ws['BN{}'.format(row)].value +
ws['BQ{}'.format(row)].value +
ws['BT{}'.format(row)].value +
ws['BW{}'.format(row)].value +
ws['BZ{}'.format(row)].value +
ws['CC{}'.format(row)].value +
ws['CF{}'.format(row)].value +
ws['CI{}'.format(row)].value ) * (50/12))
ws["DA1"] = "Biológicas"
for row in range(2, max_row + 1):
ws["DA{}".format(row)] = round((
ws['CL{}'.format(row)].value +
ws['CO{}'.format(row)].value +
ws['CR{}'.format(row)].value +
ws['CU{}'.format(row)].value ) * (50/4))
wb.save('results/' + os.path.splitext(filename)[0] + '_results.xlsx')
wb.close
else:
continue
数据是一堆假人(0或1)
我需要将结果调整为单个 file.xlsx。我需要获取多个工作表(命名为原始文件或接近此)。我不想合并成一个 ws
我正在尝试复制所有 results.xlsx 的范围并放入新文件中。但没有成功。一个不错的选择是跳过所有文件的创建并将结果直接放入最后一个文件中,但我不知道如何做到这一点。
编辑 1 - 成功将所有结果加入特定工作表中,但现在,我需要清理所有结果以仅获得结果。
dest_wb = Workbook()
from openpyxl import Workbook
from openpyxl import load_workbook
for root, dir, filenames in os.walk(path):
for file in filenames:
file_name = file.split('.')[0]
file_path = os.path.abspath(os.path.join(root, file))
# Create new sheet in destination Workbook
dest_wb.create_sheet(file_name)
dest_ws = dest_wb[file_name]
source_wb = load_workbook(file_path)
source_sheet = source_wb.active
for row in source_sheet.rows:
for cell in row:
dest_ws[cell.coordinate] = cell.value
dest_wb.save("results/report.xlsx")
解决方案
这样做的结果:
dados = pd.read_excel("results/report.xlsx", sheet_name=None)
df = pd.concat(dados[frame] for frame in dados.keys())
lista_colunas = [7, 10, 101, 102, 103, 104]
filtro = df.columns[lista_colunas]
final_df = df[filtro]
grouped_df = final_df.groupby(final_df.columns[1])
salas = grouped_df.groups.keys()
writer = pd.ExcelWriter('results/resultado.xlsx', engine='xlsxwriter')
for sala in salas:
splitdf = grouped_df.get_group(sala)
splitdf.to_excel(writer, sheet_name=str(sala))
writer.save()