Python创建并写入训练数据到xlsx文件

Python创建并写入训练数据到xlsx文件

前言

训练模型后，总是需要测试并保存评估结果，之前一直是将各个数据集和指标对应的结果组成一个字典，直接构造到f-string输出到txt文件中，这样虽然方便，但是却也导致想要使用excel处理数据的时候，来回的复制粘贴。为了爱护双手，爱护指关节，还是应该尝试下更直接的，利用python的第三方库来写入数据到excel的xlsx文件。下面是这个过程的一些记录。

Xlwings&openpyxl

我找了很多帖子，各种库推荐的都有，最开始看到这篇文章：https://zhuanlan.zhihu.com/p/54003662，准备尝试下Xlwings，但是我的简单尝试中发现，这个库子在linux上有点问题，而且官方也没有提在linux上的使用，给的安装方式都仅仅是win和mac的，可见linux上的支持并不是很好。我最终选择使用更为常用的openpyxl来作为处理的工具。

具体过程

关于一些函数的理解，主要参考了这篇文章：https://blog.csdn.net/weixin_43094965/article/details/82226263，在此表示感谢。

先放出我的代码：

# 全局变量，提供一些必要的参数
dataset_list = ['lfsd', 'njud', 'nlpr', 'rgbd135', 'sip', 'ssd', 'stereo', 'dutrgbd']
dataset_num_list = [100, 500, 300, 135, 929, 80, 1000, 400]
metric_list = ['MaxF', 'MeanF', 'MAE', 'SM', 'EM']
xlsx_path = os.path.join(path_config['ckpt_path'], 'records.xlsx')

def pre_mkdir():
    """训练模型之前的必要文件的检查操作"""
    ...
    if not os.path.exists(xlsx_path):
        make_xlsx(xlsx_path)

def make_xlsx(path):
    """
    创建xlsx文件，并向其中写入部分公用数据
    
    :param path: xlsx文件路径，这里要提供完整路径
    """
    
    num_metrics = len(metric_list)
    num_datasets = len(dataset_list)
    
    # 创建一个Workbook对象
    wb = Workbook()
    # 创建一个Sheet对象
    sheet = wb.create_sheet(title="实验结果统计", index=0)

    sheet['A1'] = 'name_dataset'
    sheet['A2'] = 'num_dataset'   
    for i, dataset_name in enumerate(dataset_list):
        if (i * num_metrics + 1) // 26 == 0:
            start_region_idx = f"{chr(ord('A') + (i * num_metrics + 1) % 26)}1"
        else:
            start_region_idx = (f"{chr(ord('A') + (i * num_metrics + 1) // 26 - 1)}"
                                f"{chr(ord('A') + (i * num_metrics + 1) % 26)}1")
        if ((i + 1) * num_metrics) // 26 == 0:
            end_region_idx = f"{chr(ord('A') + ((i + 1) * num_metrics) % 26)}1"
        else:
            end_region_idx = (f"{chr(ord('A') + ((i + 1) * num_metrics) // 26 - 1)}"
                              f"{chr(ord('A') + ((i + 1) * num_metrics) % 26)}1")
        region_idx = f"{start_region_idx}:{end_region_idx}"
        sheet.merge_cells(region_idx)  # 合并一行中的几个单元格
        sheet[start_region_idx] = dataset_name
        
        # 构造第二行数据
        start_region_idx = start_region_idx.replace('1', '2')
        sheet[start_region_idx] = dataset_num_list[i]
    
    # 构造第三行数据
    third_row = ['metrics'] + metric_list * num_datasets
    sheet.append(third_row)
    
    # 最后保存workbook
    wb.save(path)


def write_xlsx(model_name, data):
    """
    向xlsx文件中写入数据
    
    :param model_name: 模型名字
    :param data: 数据信息，包含数据集名字和对应的测试结果
    """
    
    num_metrics = len(metric_list)
    num_datasets = len(dataset_list)
        
    # 必须先得由前面的部分进行xlsx文件的创建，确保前三行OK满足要求，后面的操作都是从第四行开始的
    wb = load_workbook(xlsx_path)
    assert "实验结果统计" in wb.sheetnames, "请确保操作的是使用`make_xlsx`创建的xlsx文件"
    sheet = wb["实验结果统计"]
    num_cols = num_metrics * num_datasets + 1
    idx_insert_row = len(sheet['A']) + 1
    
    sheet.cell(row=idx_insert_row, column=1, value=model_name)
    for dataset_name in data.keys():
        # 遍历每个单元格
        for row in sheet.iter_rows(min_row=1, min_col=2, max_col=num_cols, max_row=1):
            for cell in row:
                if cell.value == dataset_name:
                    for i in range(num_metrics):
                        matric_name = sheet.cell(row=3, column=cell.column + i).value
                        sheet.cell(row=idx_insert_row, column=cell.column + i,
                                   value=data[dataset_name][matric_name])
    wb.save(xlsx_path)

这里主要写了两个函数，一个是make_xlsx另一个是write_xlsx，接下来主要说明其中的一些关键内容。

需要明确一点，对于操作xlsx文件来说，实际上就是针对其中的各个位置（cell）赋值的过程。所以实际上这里的操作很大部分就是在处理其中的单元格位置。

对于索引单元格有三种方法：

• 一种是直接使用excel中的索引方式[字母+数字]，这里的索引可以利用f-string等字符串操作方式来构造特定的位置坐标，例如：sheet['A1'] = 'name_dataset'
• 一种是使用指定行列的方式来索引：matric_name = sheet.cell(row=3, column=cell.column + i).value
• 通过遍历的形式来索引，openpyxl提供了迭代器方法来遍历行或者列，例如：

  for row in sheet.iter_rows(min_row=1, min_col=2, max_col=num_cols, max_row=1):
          for cell in row:
  

对单元格赋值的亦是可以如是操作：

• 第一点差不多
• 第二点可有两种形式：
  • sheet.cell(row=3, column=cell.column + i).value = new_value
  • sheet.cell(row=idx_insert_row, column=cell.column + i, value=new_value)
• 第三种可以如下形式，这里的cell的修改，实际上会改变最终的sheet中的值，可以认为是一个引用：

      for row in sheet.iter_rows(min_row=3, min_col=2, max_col=num_cols, max_row=3):
          for cell in row:
              cell.value = value
  

---

我们创建新的xlsx文件，需要先在表格中插入一些数据，主要是如下的类似形式：

可以看到，这里面涉及到了单个单元格的处理，也涉及到了数个单元格合并后的处理。但是对于一行有规律的数据，若要直接挨个插入，这有点麻烦。openpyxl提供了直接插入一行的方法.append()，所以，对于可以很容易构造成一个完整列表的数据，我们是可以直接借助该方法插入到表格中的。在make_xlsx()中，我使用了这样的方式构造了第三行的数据：

    # 构造第三行数据
    third_row = ['metrics'] + metric_list * num_datasets
    sheet.append(third_row)

但是对于第一行的数据，我需要合并单元格操作，这里主要使用了如下代码：

        if (i * num_metrics + 1) // 26 == 0:
            start_region_idx = f"{chr(ord('A') + (i * num_metrics + 1) % 26)}1"
        else:
            start_region_idx = (f"{chr(ord('A') + (i * num_metrics + 1) // 26 - 1)}"
                                f"{chr(ord('A') + (i * num_metrics + 1) % 26)}1")
        if ((i + 1) * num_metrics) // 26 == 0:
            end_region_idx = f"{chr(ord('A') + ((i + 1) * num_metrics) % 26)}1"
        else:
            end_region_idx = (f"{chr(ord('A') + ((i + 1) * num_metrics) // 26 - 1)}"
                              f"{chr(ord('A') + ((i + 1) * num_metrics) % 26)}1")
        region_idx = f"{start_region_idx}:{end_region_idx}"
        sheet.merge_cells(region_idx)  # 合并一行中的几个单元格
        sheet[start_region_idx] = dataset_name

根据参考文章，这里可以使用merge_cells()，根据文档：

def merge_cells(self,
                range_string: Any = None,
                start_row: Any = None,
                start_column: Any = None,
                end_row: Any = None,
                end_column: Any = None) -> None

可以知道，这里是可以使用范围字符串，即我这里实际在make_xlsx()中使用的，也可以使用起始行列索引来指定合并范围。

实际上，使用行列索引来指定应该是更灵活的，可以看到，我这里因为使用了范围字符串，导致对于列索引超出Z的列，还得考虑进一步的“进位”，无形中造成了不必要的构造。

对于合并的单元格，若要向其中添加数据，则应该使用该区域的左上角的单元格坐标。

参考链接

• https://blog.csdn.net/weixin_43094965/article/details/82226263
• https://zhuanlan.zhihu.com/p/54003662