Python 处理 excle 文件

最新推荐文章于 2024-09-04 08:33:58 发布

band210094

最新推荐文章于 2024-09-04 08:33:58 发布

阅读量240

点赞数

CC 4.0 BY-SA版权

分类专栏： Python

本文链接：https://blog.youkuaiyun.com/band210094/article/details/97620461

Python 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个使用Python脚本同步两个Excel文件数据的过程。脚本从一个Excel文件中读取ECR编号，并在另一个文件中查找对应的发布日期、逾期日期和计划实施日期，然后将这些信息写回到第一个文件中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
print("hello world")
import openpyxl
import os
import numpy as np
import operator
os.chdir('E:\\Eclipse\stock')
Current_work_directory = os.getcwd()
print (Current_work_directory)

# open ECR_Master_List.xlsx
wb_ecr_master_list = openpyxl.load_workbook('ECR_Master_List.xlsx')
type(wb_ecr_master_list)
print(wb_ecr_master_list.sheetnames)
ws_ecr_master_list = wb_ecr_master_list['EC list-Y2019']

# open PDM.xlsx
wb_pdm = openpyxl.load_workbook('PDM.xlsx')
type(wb_pdm)
print(wb_pdm.sheetnames)
ws_pdm= wb_pdm['PDM']

#get ecr release date

# get the ecr release date from pdm
def get_ecr_release_date(ws_ecr_master_list,ws_pdm):
for i in range(1,500,1):
cell_name_ecr_number = 'A' + str(i)
cell_name_ecr_release_date = 'F' + str(i)
# print (ws_ecr_master_list[cell_name_ecr_number].value)
if ws_ecr_master_list[cell_name_ecr_number] != None:
for j in range(1,20000,1):
cell_name_ecr_number_pdm = 'A' + str(j)
cell_name_ecr_release_date_pdm = 'W' + str(j)
if ws_ecr_master_list[cell_name_ecr_number].value == ws_pdm[cell_name_ecr_number_pdm].value:
ws_ecr_master_list[cell_name_ecr_release_date].value = ws_pdm[cell_name_ecr_release_date_pdm].value

# get ECR overdue date
def get_ecr_overdue_date(ws_ecr_master_list,ws_pdm):
for i in range(1,500,1):
cell_name_ecr_number = 'A' + str(i)
cell_name_ecr_overdue_date = 'G' + str(i)
# print (ws_ecr_master_list[cell_name_ecr_number].value)
if ws_ecr_master_list[cell_name_ecr_number] != None:
for j in range(1,20000,1):
cell_name_ecr_number_pdm = 'A' + str(j)
cell_name_ecr_overdue_date_pdm = 'N' + str(j)
if ws_ecr_master_list[cell_name_ecr_number].value == ws_pdm[cell_name_ecr_number_pdm].value:
ws_ecr_master_list[cell_name_ecr_overdue_date].value = ws_pdm[cell_name_ecr_overdue_date_pdm].value

# get ECR planned implement date
def get_ecr_planned_implement_date(ws_ecr_master_list,ws_pdm):
for i in range(1,500,1):
cell_name_ecr_number = 'A' + str(i)
cell_name_ecr_planned_implement_date = 'H' + str(i)
ecr_implement_date = []
# print (ws_ecr_master_list[cell_name_ecr_number].value)
if ws_ecr_master_list[cell_name_ecr_number] != None:
for j in range(1,20000,1):
cell_name_ecr_number_pdm = 'A' + str(j)
cell_name_ecr_alternative_date_pdm = 'H' + str(j)
if ws_ecr_master_list[cell_name_ecr_number].value == ws_pdm[cell_name_ecr_number_pdm].value:
ecr_implement_date.append(ws_pdm[cell_name_ecr_alternative_date_pdm].value)
# print(ecr_implement_date)
# print(get_max_value_from_list(ecr_implement_date))
ws_ecr_master_list[cell_name_ecr_planned_implement_date] = get_max_value_from_list(ecr_implement_date)

# get the max value from list
def get_max_value_from_list(list):
max_value = ''
for i in range(0,len(list),1):
if operator.lt(max_value,list[i]):
max_value = list[i]
return max_value

#value =['2019-01-02','2019-01-01']

#print(get_max_value_from_list(value))

get_ecr_release_date(ws_ecr_master_list,ws_pdm)
get_ecr_overdue_date(ws_ecr_master_list,ws_pdm)
get_ecr_planned_implement_date(ws_ecr_master_list,ws_pdm)

#save excel file

wb_ecr_master_list.save('ECR_Master_List.xlsx')

# deactive
#for i in range(1,1000,1):
# cell_name_ecr_number = 'A' + str(i)
# cell_name_ecr_release_date = 'F' + str(i)
#print (cell_name)
# if ws_ecr_master_list[cell_name_ecr_number].value != None:
# print(ws_ecr_master_list[cell_name_ecr_number].value)
# ws_ecr_master_list[cell_name_ecr_release_date] = 'PDM_ECR_release date'
# print(ws_ecr_master_list[cell_name_ecr_release_date].value)

#for i in range(1,1000,1):
# cell_name_ecr_number_pdm = 'A' + str(i)
# cell_name_ecr_release_date_pdm = 'W' + str(i)
#print (cell_name)
# if ws_pdm[cell_name_ecr_number_pdm].value != None:
# print(ws_pdm[cell_name_ecr_number_pdm].value)
# print(ws_pdm[cell_name_ecr_release_date_pdm].value)