在Python中逐表和逐列追加多个Excel文件

Question

请建议如何逐个工作表和逐列附加多个 Excel 文件，其中所有文件在 python3 中具有完全相同的工作表和列名称。

                 **Input**                                **Output**

File name: abc.xlsx       File name: xyz.xlsx       File name: appended.xlsx

Sheet: One                Sheet: One                Sheet: One

Column: A & B             Column: A & B             Column: A & B

Rows: 100                 Rows: 100                 Rows: **200**

.                         .                         .

.                         .                         .

.                         .                         .

.                         .                         .

.                         .                         .

.                         .                         .

Sheet: Ten                Sheet: Ten                Sheet: Ten

Column: A & B             Column: A & B             Column: A & B

Rows: 100                 Rows: 100                 Rows: **200**

Answer 1

您可以使用 pythonopenpyxl模块来实现此目的。from openpyxl import load_workbook我不知道为什么你需要第三个文件。您可以将一个文件附加到另一个文件。首先，读取一个包含以下内容的文件：first_file_wb = load_workbook(/file_path, read_only=True) # wb stands for workbookfor sheet_name in first_file_wb.sheetnames: # You can iterate through sheets with sheetnames&nbsp; &nbsp; first_file_ws = first_file_wb[sheet_name]&nbsp; &nbsp; # ws stands for worksheet&nbsp; &nbsp; # after this you have access to specific sheet.&nbsp; &nbsp; for row in first_file_ws.iter_rows():&nbsp; &nbsp; &nbsp; &nbsp; for cell in row:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; print('%s: cell.value=%s' % (cell, cell.value)) # You can write this values to a dict sheet by sheet.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ...操作这些值后，您可以将其附加到其他文件。second_file_wb = load_workbook(/file_path)for sheet_name in second_file_wb.sheetnames:&nbsp; &nbsp; second_file_ws = first_file_wb[sheet_name]&nbsp; &nbsp; # ws stands for worksheet&nbsp; &nbsp; last_row = second_file_ws.max_row # This will give you last row number of the file&nbsp; &nbsp; # At this point, you can append the values after that row.&nbsp; &nbsp; for key,value in your_dict_from_first_file.items():&nbsp; &nbsp; &nbsp; &nbsp; # I assume key is a counter to values but if it is not, you can create a counter here. Just be sure <key+max> gives first available row to append.&nbsp; &nbsp; &nbsp; &nbsp; second_file_ws.cell(row=key+max, column={column from dict}, value={value})&nbsp; &nbsp; &nbsp; &nbsp; # or&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; second_file_ws.cell(row=key+max, column={column_number_from_dict}).value = value