如何用Pandas逐元素将[1x6]行向量追加至Excel列？

问题背景 #

我正在用Python/Pandas和Numpy自动化处理Excel表格，已经完成读取Excel、转换为数组、矩阵运算步骤，现在得到了6个[1x6]的行向量（P1_sch至P6_sch），希望将它们逐元素追加到Excel的6列中。附上现有代码，需要把运算得到的P1_sch至P6_sch分别存入对应单元格，刚接触Python，求实现方法。

原代码 #

import numpy as np
import openpyxl
s= pd.read_excel('ECOMP TRACKING.xlsx',sheet_name='SCHEDULE') #imprt as dataset
S = s.to_numpy() #converts to ndarray
veh = np.array(S[:,0])[np.newaxis] #gives 1xn matrix
V_sch=(S[:,1:])
e= pd.read_excel('ECOMP TRACKING.xlsx',sheet_name='EBOM')
E= e.to_numpy()
veh1 = np.array(E[:,2])[np.newaxis]  #gives 1xn matrix
#BO=B_0.T
b_1= veh1.size
i=0
while i<b_1:
    if veh1[0][i]==veh[0][0]:
        P1_sch=pd.ExcelWriter np.multiply(V_sch[0][:],E[i][5]*E[i][6])

        i+=1
    elif veh1[0][i]==veh[0][1]:
        P2_sch= np.multiply(V_sch[1][:],E[i][5]*E[i][6])
        i+=1
    elif veh1[0][i]==veh[0][2]:
        P3_sch= np.multiply(V_sch[2][:],E[i][5]*E[i][6])
        i+=1
    elif veh1[0][i]==veh[0][3]:
        P4_sch= np.multiply(V_sch[3][:],E[i][5]*E[i][6])
        i+=1
    elif veh1[0][i]==veh[0][4]:
        P5_sch= np.multiply(V_sch[4][:],E[i][5]*E[i][6])
        i+=1
    elif veh1[0][i]==veh[0][5]:
        P6_sch= np.multiply(V_sch[5][:],E[i][5]*E[i][6])
        i+=1
    else:
        i+=1

修正与解决方案 #

首先修正原代码的语法错误：P1_sch赋值行多写了pd.ExcelWriter，应删除该部分，改为P1_sch = np.multiply(V_sch[0][:], E[i][5]*E[i][6])。

下面提供两种适合新手的写入方式：

方法一：Pandas DataFrame批量写入（推荐）

这种方法简洁高效，适合结构化数据写入：

import numpy as np
import pandas as pd

# 读取数据
s = pd.read_excel('ECOMP TRACKING.xlsx', sheet_name='SCHEDULE')
S = s.to_numpy()
veh = np.array(S[:, 0])[np.newaxis]
V_sch = S[:, 1:]
e = pd.read_excel('ECOMP TRACKING.xlsx', sheet_name='EBOM')
E = e.to_numpy()
veh1 = np.array(E[:, 2])[np.newaxis]

# 初始化结果存储数组（6行6列，对应P1-P6_sch）
results = np.zeros((6, 6))

b_1 = veh1.size
i = 0
while i < b_1:
    if veh1[0][i] == veh[0][0]:
        results[0] = np.multiply(V_sch[0][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][1]:
        results[1] = np.multiply(V_sch[1][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][2]:
        results[2] = np.multiply(V_sch[2][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][3]:
        results[3] = np.multiply(V_sch[3][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][4]:
        results[4] = np.multiply(V_sch[4][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][5]:
        results[5] = np.multiply(V_sch[5][:], E[i][5] * E[i][6])
        i += 1
    else:
        i += 1

# 转换为DataFrame，可自定义列名
result_df = pd.DataFrame(results, columns=['列1', '列2', '列3', '列4', '列5', '列6'])

# 追加写入Excel指定工作表
with pd.ExcelWriter('ECOMP TRACKING.xlsx', mode='a', if_sheet_exists='overlay') as writer:
    # 从原数据下一行开始写入，不保留索引
    result_df.to_excel(writer, sheet_name='SCHEDULE', startrow=len(s)+1, startcol=0, index=False)

关键说明：

• mode='a'启用追加模式，if_sheet_exists='overlay'允许覆盖工作表指定区域
• startrow=len(s)+1确保从原数据的下一行开始写入，避免覆盖原有内容
• 可根据实际表格修改columns的列名，以及startrow、startcol的位置

方法二：openpyxl逐个单元格写入

如果需要精确控制每个单元格位置，可使用openpyxl：

import numpy as np
import openpyxl
import pandas as pd

# 读取数据
s = pd.read_excel('ECOMP TRACKING.xlsx', sheet_name='SCHEDULE')
S = s.to_numpy()
veh = np.array(S[:, 0])[np.newaxis]
V_sch = S[:, 1:]
e = pd.read_excel('ECOMP TRACKING.xlsx', sheet_name='EBOM')
E = e.to_numpy()
veh1 = np.array(E[:, 2])[np.newaxis]

# 初始化结果变量
P1_sch = P2_sch = P3_sch = P4_sch = P5_sch = P6_sch = np.zeros(6)

b_1 = veh1.size
i = 0
while i < b_1:
    if veh1[0][i] == veh[0][0]:
        P1_sch = np.multiply(V_sch[0][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][1]:
        P2_sch = np.multiply(V_sch[1][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][2]:
        P3_sch = np.multiply(V_sch[2][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][3]:
        P4_sch = np.multiply(V_sch[3][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][4]:
        P5_sch = np.multiply(V_sch[4][:], E[i][5] * E[i][6])
        i += 1
    elif veh1[0][i] == veh[0][5]:
        P6_sch = np.multiply(V_sch[5][:], E[i][5] * E[i][6])
        i += 1
    else:
        i += 1

# 打开Excel文件并选择工作表
wb = openpyxl.load_workbook('ECOMP TRACKING.xlsx')
ws = wb['SCHEDULE']

# 定义写入起始行（原数据结束后的下一行）
start_row = len(s) + 2  # 假设表头占1行，原数据从第2行开始

# 写入P1_sch到第1行目标位置
for col_idx, value in enumerate(P1_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

# 依次写入P2-P6_sch
start_row += 1
for col_idx, value in enumerate(P2_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

start_row += 1
for col_idx, value in enumerate(P3_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

start_row += 1
for col_idx, value in enumerate(P4_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

start_row += 1
for col_idx, value in enumerate(P5_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

start_row += 1
for col_idx, value in enumerate(P6_sch, start=1):
    ws.cell(row=start_row, column=col_idx, value=value)

# 保存文件
wb.save('ECOMP TRACKING.xlsx')

关键说明：

• openpyxl中单元格的行、列编号均从1开始
• start_row可根据你的表格结构调整，确保不覆盖原有内容
• 这种方法适合需要精细控制单元格的场景，但代码相对繁琐

内容的提问来源于stack exchange，提问作者javad