import openpyxl
from openpyxl.styles import numbers
import re
import os
import csv

# 查找当前csv文件
excelfiles1 = [f for f in os.listdir('.') if not f.startswith("~$") and f.endswith(".csv")]

# 创建xlsx空白文件
wb1 = openpyxl.Workbook()
ws1 = wb1.active

# 修改默认工作表的标题为"站位表"
ws1.title = "站位表"

# 将csv文件内容填充到xlsx文件
with open(excelfiles1[0], 'r', encoding='gbk') as f:
    reader = csv.reader(f)
    for row in reader:
        ws1.append(row)

# 存储为xlsx文件
outfile = "modified_" + excelfiles1[0].replace('.csv', '.xlsx')
wb1.save(outfile)

# 加载工作簿
wb = openpyxl.load_workbook(outfile)
ws = wb.active

# 1. 将D列转换为文本格式，并将该列中的'00'替换为'-'
for row in range(1, ws.max_row + 1):
    cell = ws[f"D{row}"]
    if cell.value is not None:
        # 先将值转换为字符串
        cell_value = str(cell.value)
        # 将'00'替换为'-'
        cell_value = cell_value.replace('00', '-')
        # 设置为文本格式
        cell.value = cell_value
        cell.number_format = numbers.FORMAT_TEXT
    else:
        # 如果为空值，设为空字符串并保持文本格式
        cell.value = ""
        cell.number_format = numbers.FORMAT_TEXT

# 2. 将E列中的'L'替换为'1'，'R'替换为'2'，空值替换为'1'
for row in range(1, ws.max_row + 1):
    cell = ws[f"E{row}"]
    if cell.value == "L":
        cell.value = "1"
    elif cell.value == "R":
        cell.value = "2"
    elif cell.value is None or cell.value == "":
        cell.value = "1"
    # 确保E列为文本格式
    cell.number_format = numbers.FORMAT_TEXT

# 3. 将F列格式设置为数值格式，保留4位小数点
for row in range(1, ws.max_row + 1):
    cell = ws[f"F{row}"]
    if cell.value is not None:
        if isinstance(cell.value, (int, float)):
            # 已经是数字类型
            cell.number_format = "0.0000"
        elif isinstance(cell.value, str) and cell.value.strip():
            # 如果是字符串且非空，尝试转换为浮点数
            try:
                cell.value = float(cell.value.strip())
                cell.number_format = "0.0000"
            except ValueError:
                # 如果不是数字，保持原样
                pass

# 4. 在H列设置为常规格式，输入公式'=D1&"-"&E1'
# 首先检查H列是否存在（第8列）
if ws.max_column < 8:  # H列是第8列
    # 插入列使H列存在
    ws.insert_cols(8)

# 设置H列公式
for row in range(1, ws.max_row + 1):
    cell = ws[f"H{row}"]
    # 输入公式
    cell.value = f'=D{row}&"-"&E{row}'
    # 设置为常规格式
    cell.number_format = 'General'

# 创建站位查询工作表
ws2 = wb.create_sheet(title='站位查询')
ws2['A1'] = 'SN编码'
ws2['B1'] = '子件编码'
ws2['C1'] = '站位'

# 填充查询公式
for i in range(2,200):
    cell2 = ws2[f"B{i}"]
    cell3 = ws2[f"C{i}"]
    cell2.value = f'=IFERROR(VALUE(TRIM(MID(SUBSTITUTE(A{i},"|",REPT(" ",100)),1,100))),"")'
    cell3.value = f'=IF(LEN(B{i})=10,VLOOKUP(B{i},站位表!F:H,3,0),"")'
    cell2.number_format = 'General'
    cell3.number_format = 'General'

# 保存工作簿
wb.save(outfile)