Simon Shi的小站

人工智能，机器学习， 强化学习，大模型，自动驾驶

0%

Python 之 表格数据 填充

Posted on Edited on In ,

图片写入表格xlsx

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
from openpyxl.styles import Alignment

import os
import shutil
import numpy as np

def GetImgNameByEverDir(dir, property):
    filename_with_path = []
    filename = []
    file_dir = []
    for root, dirs, files in os.walk(dir):
        print(property)
        files = sorted(files)
        print('---root---', root)
        print('---property---', files)
        for file in files:
            print(os.path.splitext(file))
            if os.path.splitext(file)[1] in property:
                filename_with_path.append( os.path.join(root, file))
                filename.append(file)
                file_dir.append(root+'/')
    # print(filename_with_path)
    return np.array(filename), np.array(filename_with_path), file_dir

class Generate:
    def __init__(self, xls_path, file_dir=None, prompt=''):
        self.file_name = xls_path
        self.file_dir = file_dir
        self.prompt = prompt

    def save_one_dir_imgs_to_excel(self, bsize=4, property=['.png','.jpg', '.JPG']):
        wb = Workbook()
        try:
            ws = wb['Sheet']
            wb.remove(ws)
        except:
            print("No sheet named 'Sheet'")

        ws = wb.active
        print(self.file_dir)
        ff, fp, dd = GetImgNameByEverDir(self.file_dir, property)
        ff = ff.reshape([-1, bsize])
        fp = fp.reshape([-1, bsize])
        print(ff)
        print(fp)
        print(dd)
        ws = wb.create_sheet(title="result")
        alignment_center = Alignment(horizontal='center', vertical='center')
        ws.row_dimensions[1].height = 20
        ws.cell(row=1, column=1, value="Prompt").alignment = alignment_center
        ws.cell(row=1, column=2, value=self.prompt)

        # ws.row_dimensions[1].height = 20
        ws.row_dimensions[2].height = 20
        ws.cell(row=2, column=1, value="图片").alignment = alignment_center
        ws.cell(row=2, column=2, value="Gen1").alignment = alignment_center
        ws.cell(row=2, column=3, value="Gen2").alignment = alignment_center
        ws.cell(row=2, column=4, value="Gen3").alignment = alignment_center
        ws.cell(row=2, column=5, value="打分").alignment = alignment_center

        for kk in range(len(fp)):
            ws.row_dimensions[kk+3].height = 300
            ws.column_dimensions["A"].width = 40
            ws.column_dimensions["B"].width = 50
            ws.column_dimensions["C"].width = 50
            ws.column_dimensions["D"].width = 50
            # ws.cell(row=kk+2, column=3, value=fp[kk]).alignment = alignment_center
            # ws.cell(row=kk+2, column=2, value=dd[kk]).alignment = alignment_center

        row_start = 2
        for l in range(len(fp)):
            for i in range(bsize):
                img = Image(fp[l][i])
                _from = AnchorMarker(col=i, colOff=50000, row=l+row_start, rowOff=50000)
                to = AnchorMarker(col=i+1, colOff=-50000, row=l+row_start+1, rowOff=-50000)
                img.anchor = TwoCellAnchor("twoCell", _from, to)
                ws.add_image(img)

        wb.save(self.file_name)
        print("保存成功")
        return self.file_name


import argparse
if __name__ == "__main__":
    argparse = argparse.ArgumentParser()
    argparse.add_argument('--dir', type=str, default='outputs/20241204-193022-87693-web', help='outputs/20241204-193022-87693-web')
    args = argparse.parse_args()

    # format_img_names("/mnt/c/Users/simon/Pictures/女")
    # xls = os.path.join(args.dir, "result.xlsx")
    # ge = Generate(os.path.basename(xls), args.dir, prompt="this is a testthis is a testthis is a testthis is a testthis is a testthis is a testthis is a testthis is a test")
    # ge.save_one_dir_imgs_to_excel(property=['.png','.jpg', '.JPG'])

    xlsx_to_image('result.xlsx', 'result.png', 4, 4)

文件名格式化（format_img_names）

1
2
3
4
5
6
7
8
9
10
import re
def format_img_names(dir_path, replacement='_'):
    for root, dirs, files in os.walk(dir_path):
        print(root)
        for file in files:
            sanitized = re.sub(r'[\\/:*?"<>|\r\n]+', replacement, file)
            sanitized = sanitized.strip(' _')
            sanitized = re.sub(r'[-\s]+', '_', sanitized)
            print(file, '---->', sanitized)
            os.rename(os.path.join(root, file), os.path.join(root, sanitized))

xlsx_to_image (支持文字，不支持嵌入的图片)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import pandas as pd
import matplotlib.pyplot as plt
def xlsx_to_image(input_file, output_image, n_rows, m_cols):
    df = pd.read_excel(input_file)
    print(df)
    sub_data = df.iloc[:n_rows, :m_cols]


    plt.figure(figsize=(10, 6))

    table = plt.table(cellText=sub_data.values,
                  colLabels=sub_data.columns,
                  rowLabels=sub_data.index,
                  cellLoc='center',
                  loc='center')
    table.auto_set_font_size(False)
    table.set_fontsize(12)
    table.scale(1, 1.5)

    plt.axis('off')
    plt.savefig(output_image)