# coding: utf-8
import re

import pandas as pd
import xlrd
from openpyxl.reader.excel import load_workbook


def open_xls(file_path):
    # 开始处理excel数据
    workbook = xlrd.open_workbook(filename=file_path)
    # 选择工作表
    return workbook.sheet_by_index(0)  # 选择第一个工作表


def save_xls(data, output_file, sheet_name='Sheet1', adjusted=True):
    try:
        # 如果文件已存在，则追加新的 sheet
        with pd.ExcelWriter(output_file, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
            df = pd.DataFrame(data)
            df.to_excel(writer, index=False, sheet_name=sheet_name)
    except FileNotFoundError:
        # 如果文件不存在，创建一个新的文件并写入
        with pd.ExcelWriter(output_file, engine='openpyxl', mode='w') as writer:
            df = pd.DataFrame(data)
            df.to_excel(writer, index=False, sheet_name=sheet_name)

    # 使用 openpyxl 重新加载工作簿
    wb = load_workbook(output_file)
    ws = wb[sheet_name]

    if not adjusted:
        return

        # 自动调整列宽
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                # 判断 cell.value 是否为 None，并尝试转换为字符串计算长度
                if cell.value is not None and len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except (TypeError, AttributeError) as e:
                print(f"Error processing cell {cell.coordinate}: {e}")
                continue
        adjusted_width = max_length + 3
        ws.column_dimensions[column_letter].width = adjusted_width

    wb.save(output_file)


def remove_last_comma(csv_file, skip_rows=2):
    # 创建一个空列表用于存储处理后的行
    cleaned_lines = []

    # 读取原始 CSV 文件并处理行末的逗号
    with open(csv_file, 'r', encoding='utf-8') as file:
        # 跳过指定数量的行
        for _ in range(skip_rows):
            next(file)  # 跳过每一行

        for line in file:
            # 使用正则表达式替换 空格 + 数字 + 引号
            cleaned_line = re.sub(r'(\s\d+)"', r'\1 ', line)  # 去掉空格 + 数字后面的引号
            # 使用正则表达式替换每个逗号前的空格为引号
            cleaned_line = re.sub(r'\s+,\s*"', r'", "', cleaned_line)
            # 去掉末尾的逗号和换行符
            cleaned_line = cleaned_line.rstrip(',\n')
            # 不添加换行符，待会写入时统一处理
            cleaned_lines.append(cleaned_line)

    # 将处理后的数据写入同一个文件
    with open(csv_file, 'w', encoding='utf-8', newline='') as cleaned_file:
        cleaned_file.write('\n'.join(cleaned_lines) + '\n')  # 使用 join 处理换行符
