Excel workbooks are a major source of data collections. Python programming language provides few libraries to perform operations on the excel workbooks, like copying the data from one workbook to another.
Excel工作簿是数据收集的主要来源。 Python编程语言提供了很少的库来对excel工作簿执行操作,例如将数据从一个工作簿复制到另一个工作簿 。
Note: The library mentioned below needs to be installed using pip in the virtual environment.
注意:下面提到的库需要在虚拟环境中使用pip安装。
openpyxl (openpyxl)
https://pypi.org/project/openpyxl/
https://pypi.org/project/openpyxl/
Openpyxl is a python library to read/write Excel files (xlsx, xlsm, xltx, xltm). This library provides an option to read every cell of the workbook and either copies it or modify it by using openpyxl.worksheet.Worksheet.cell() method. This method allows accessing each cell by the row and column as a numerical value.
Openpyxl是用于读取/写入Excel文件(xlsx,xlsm,xltx,xltm)的python库。 该库提供了一个选项,用于读取工作簿的每个单元格,然后使用openpyxl.worksheet.Worksheet.cell()方法对其进行复制或修改。 此方法允许按行和列访问每个单元格作为数值。
The below example, will demonstrate the process of copying the data from a source excel file to the destination file, by row/column.
下面的示例将演示按行/列将数据从源excel文件复制到目标文件的过程 。
Step 1: Consider a source workbook, say source.xlsx and destination workbook, say destination.xlsx. The latter file is empty to where the contents of the former file will be copied. Below is the example of the former file (source.xlsx).
步骤1:考虑一个源工作簿,例如source.xlsx和目标工作簿,例如destination.xlsx。 后一个文件为空,将复制前一个文件的内容。 下面是前一个文件(source.xlsx)的示例。
Step 2: Load the workbooks
步骤2:加载工作簿
from openpyxl import load_workbook
src_wb = load_workbook('source.xlsx')
dest_wb = load_workbook('destination.xlsx')
Step 3: Read the sheets to be copied
步骤3:阅读要复制的图纸
src_sheet = src_wb.get_sheet_by_name('source_sheet')
dest_sheet = dest_wb.get_sheet_by_name('destination')
Step 4: Copy all the rows and columns
步骤4:复制所有行和列
for i in range(1, src_sheet.max_row+1):
for j in range(1, src_sheet.max_column+1):
dest_sheet.cell(row=i, column=j).value = src_sheet.cell(row=i, column=j).value
Step 5: Save the workbooks
步骤5:保存工作簿
src_wb.save('source.xlsx')
dest_wb.save('destination.xlsx')
The contents from the source is now copied and saved in the destination file.
现在,将复制源中的内容并将其保存在目标文件中。
Python代码将数据从一个Excel文件复制到另一个 (Python code to Copy data from one excel file to another)
from openpyxl import load_workbook
src_wb = load_workbook('source.xlsx')
dest_wb = load_workbook('destination.xlsx')
src_sheet = src_wb.get_sheet_by_name('source_sheet')
dest_sheet = dest_wb.get_sheet_by_name('destination')
for i in range(1, src_sheet.max_row+1):
for j in range(1, src_sheet.max_column+1):
dest_sheet.cell(row=i, column=j).value = src_sheet.cell(row=i, column=j).value
src_wb.save('source.xlsx')
dest_wb.save('destination.xlsx')
翻译自: https://www.includehelp.com/python/copy-data-from-one-excel-file-to-another.aspx