import openpyxl
from openpyxl.utils import get_column_letter

打开工作簿

workbook = openpyxl.load_workbook(“C:\Users\B000090\Desktop\python 数据\python 2.0(1)(2).xlsx”)

选择工作表

sheets = workbook.sheetnames
ws_dataupload = workbook[“dataupload”]
ws_rawdata_original = workbook[“origin”]

清除数据

for row in ws_dataupload[“A2:Z1000000”]:
for cell in row:
cell.value = None

显示消息框

print(“Dataupload field cleared!”)
print(“Start to combine project budget data, pls wait!”)

初始化变量

dataupload_row_index = 2

遍历原始数据工作表

for i in range(2, 201):
if ws_rawdata_original.cell(i, 1).value == “0” or ws_rawdata_original.cell(i, 1).value == None or ws_rawdata_original.cell(i, 1).value == “”:
continue
else:

处理2023年的数据

for month_index in range(1, 13):
for column_index in range(1, 5):
ws_dataupload.cell(dataupload_row_index, column_index).value = ws_rawdata_original.cell(i, column_index).value
ws_dataupload.cell(dataupload_row_index, 5).value = “2023”

if ws_rawdata_original.cell(i, 1).value == “Biosion_USA”:
ws_dataupload.cell(dataupload_row_index, 6).value = “USD”
elif ws_rawdata_original.cell(i, 1).value == “Biosion_AUS”:
ws_dataupload.cell(dataupload_row_index, 6).value = “AUD”
else:
ws_dataupload.cell(dataupload_row_index, 6).value = “CNY”

ws_dataupload.cell(dataupload_row_index, 7).value = ws_rawdata_original.cell(1, 4 + month_index).value
ws_dataupload.cell(dataupload_row_index, 8).value = ws_rawdata_original.cell(i, 4 + month_index).value

dataupload_row_index += 1

处理2024年的数据

for month_index in range(1, 13):
for column_index in range(1, 5):
ws_dataupload.cell(dataupload_row_index, column_index).value = ws_rawdata_original.cell(i, column_index).value
ws_dataupload.cell(dataupload_row_index, 5).value = “2024”

if ws_rawdata_original.cell(i, 1).value == “Biosion_USA”:
ws_dataupload.cell(dataupload_row_index, 6).value = “USD”
elif ws_rawdata_original.cell(i, 1).value == “Biosion_AUS”:
ws_dataupload.cell(dataupload_row_index, 6).value = “AUD”
else:
ws_dataupload.cell(dataupload_row_index, 6).value = “CNY”

ws_dataupload.cell(dataupload_row_index, 7).value = ws_rawdata_original.cell(1, 18 + month_index).value
ws_dataupload.cell(dataupload_row_index, 8).value = ws_rawdata_original.cell(i, 18 + month_index).value

dataupload_row_index += 1

print(“Data Combination Finished!”)