使用Python+pandas实现Excel转设备可读格式化文本文件

现有基础代码 #

import pandas as pd 
import numpy as np

#file_name = input("Please input a file to read. It should have a name like File.xlsm\n")
file_name = "file.xlsm"

# maybe add a part where if it fails you ask the user again 

read_file = pd.read_excel(file_name, sheet_name = 0, header = 0, index_col = 0, usecols = [" Name", "Comment", "Price", "category", "data to change"], skiprows = 15) # sheet is equal to 0 by default os it will do the first one 

#print("\n")
#print(read_file)

# search fe 
#Fruit Name | Comment | Price | Category | Aisle# / data  

#for index, row in read_file.iterrows():  trying to find if healthy or unhealthy or to remove row
#    if cell = Dgiit\

#read_file["Fruit Name"] = read_file["Fruit Name"].str.lower() #broken. tring to get name in to paranthees and all lower case. APPLE -> "apple" 

#drop_val = #!digital / supply    
#read_file = read_file[~read_file['A'].isin(drop_val)] ! ( unhealty * | *Healthy )

# saving to a text file 
read_file.to_csv('input2.txt', sep = '\t', line_terminator = ';\n') # saves data frame to tab seperated text file. need to find out how to have semi colons at the end. 

待实现核心规则 #

• 行过滤与命令前缀：读取category列内容，单元格包含healthy关键词的行开头添加HEALTHY前缀，包含unhealthy关键词的行开头添加UNHEALTHY前缀，两个关键词都不匹配的行直接删除
• 特殊字段解析：处理data to change列中SHELF+数字格式的内容，提取数字后映射为预设表中的物理地址、货架排数信息
• 输出格式规范：严格按照[命令] "[商品名]" "[货架地址]"; // [Comment列注释内容]结构输出，解决原有to_csv输出tab分隔错位、不符合设备读取规则的问题
• 预留Price字段处理入口，后续可针对有效行单独处理价格，生成输出文件的分段内容

预期输出示例 #

HEALTHY "bannana" "Aisle#-storename" ; // the comment I need from the comment box //(the number comes from data that needs to be manipulated tab, it has some exess info and things i need to conver)
HEALTHY "orange" "Aisle#-storename"; // what came first the color or the fruit. is the fruit named after the color or the color after the fruit
UNHEALTHY "cupcake" "Aisle#-storename"; // not good for you but maybe for the sould 
UNHEALTHY "pizza" "Aisle#-storename";

完整实现代码 #

import pandas as pd
import numpy as np
import re

# -------------------------- 配置项 可根据实际情况修改 --------------------------
FILE_NAME = "file.xlsm"
OUTPUT_FILE = "input2.txt"
# 货架编号与物理地址映射表，根据实际业务表修改
SHELF_ADDRESS_MAP = {
    323: "A1-storename",
    324: "A2-storename",
    325: "B1-storename"
}
# Excel读取参数
EXCEL_READ_PARAMS = {
    "sheet_name": 0,
    "header": 0,
    "index_col": 0,
    "usecols": [" Name", "Comment", "Price", "category", "data to change"],
    "skiprows": 15
}
# -----------------------------------------------------------------------------

def parse_shelf_address(shelf_raw):
    """解析SHELF开头的字段，提取数字映射为物理地址"""
    if pd.isna(shelf_raw):
        return ""
    shelf_str = str(shelf_raw).strip()
    match_res = re.search(r'SHELF(\d+)', shelf_str, flags=re.IGNORECASE)
    if not match_res:
        return ""
    shelf_num = int(match_res.group(1))
    return SHELF_ADDRESS_MAP.get(shelf_num, f"UNKNOWN_SHELF_{shelf_num}")

# 1. 读取Excel数据
df = pd.read_excel(FILE_NAME, **EXCEL_READ_PARAMS)

# 2. 数据清洗与行过滤
# 处理category列，统一转字符串、去空格、转小写做匹配
df["category"] = df["category"].astype(str).str.strip().str.lower()
# 过滤掉既不含healthy也不含unhealthy的行
df = df[df["category"].str.contains("healthy|unhealthy", na=False)].copy()
# 生成命令前缀列
df["command"] = np.where(df["category"].str.contains("healthy"), "HEALTHY", "UNHEALTHY")

# 3. 各字段格式化处理
# 商品名：去空格、转小写
df["item_name"] = df[" Name"].astype(str).str.strip().str.lower()
# 解析货架地址
df["shelf_address"] = df["data to change"].apply(parse_shelf_address)
# 注释字段处理：空值转为空字符串
df["comment"] = df["Comment"].fillna("").astype(str).str.strip()

# 4. 逐行生成符合格式的输出内容，放弃to_csv避免格式错位
output_lines = []
for _, row in df.iterrows():
    # 基础格式拼接
    base_part = f'{row["command"]} "{row["item_name"]}" "{row["shelf_address"]}";'
    # 有注释则拼接注释部分，无注释则直接保留基础部分
    if row["comment"]:
        line = f'{base_part} // {row["comment"]}'
    else:
        line = base_part
    output_lines.append(line)

    # --------------------------
    # 后续Price字段处理逻辑可在此处添加，生成对应分段内容
    # price_process(row["Price"])
    # --------------------------

# 5. 写入文件
with open(OUTPUT_FILE, "w", encoding="utf-8") as f:
    f.write("\n".join(output_lines))

关键逻辑说明 #

• 匹配逻辑统一对category列做小写转换，避免单元格内容大小写不一致（比如Healthy、HEALTHY）导致的匹配遗漏问题
• 货架地址解析通过独立映射表维护，后续货架编号对应规则变动时，仅需修改SHELF_ADDRESS_MAP配置即可，不需要改动核心处理逻辑
• 完全抛弃to_csv的自动格式化逻辑，手动按设备要求拼接每行字符串，从根源上避免tab分隔、字段错位、多余符号的问题
• 代码中预留了Price字段的处理位置，后续添加价格分段逻辑时不需要调整现有数据清洗、过滤的流程

内容的提问来源于stack exchange，提问作者Programmy-Boi