Excel1的某一列复制到Excel2的某一列
#**************输出结果把ID换成日期********** #把 EXCEL1.xlsx 表里的第一列的数据复制粘贴到 EXCEL2.xlsx 的第一列 from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.writer.excel import ExcelWriter wb = load_workbook ('EXCEL1.xlsx') ws = wb['Sheet1'] sheet = wb.active a = sheet.max_row b=[] i=0 while i < a: i = i+1 print(ws.cell (i,1).value) #(i,x) x表示第几列 b.insert(i, ws.cell (i,1).value)#(i,x) x表示第几列 wbtest = load_workbook ('EXCEL2.xlsx') ws = wbtest['Sheet1'] sheet = wb.active i = 0 while i < a: ws.cell (i+1,1).value = b[i] i = i+1 wbtest.save ('result.xlsx')
版本二:对某个文件夹下的excel文件循环操作
import re import os import pandas as pd import numpy as np from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.writer.excel import ExcelWriter wb = load_workbook ('base1.xlsx') ws = wb['Sheet1'] sheet = wb.active a = sheet.max_row b=[] i=0 while i < a: i = i+1 print(ws.cell (i,1).value) #(i,x) x表示第几列 b.insert(i, ws.cell (i,1).value)#(i,x) x表示第几列 path = 'F:/pycodes/2019RiXin/test/' files = os.listdir(path) files_excel = list(filter(lambda x: x[-5:]=='.xlsx' , files)) #定义一个空列表 data_list = [] #函数re.compile将正则表达式(以字符串书写的)转换为模式对象 num_filter = re.compile(r'\d+') for file in files_excel: wbtest = load_workbook(path + file) ws = wbtest['Sheet1'] sheet = wb.active i = 0 while i < a: ws.cell(i + 1, 1).value = b[i] i = i + 1 wbtest.save('./test/handle/{}'.format(file))