蓝桥杯基础-007-Letter_Pattern_Row-Column

本文介绍了一种字符矩阵生成算法,该算法通过特定规律生成字母矩阵,并提供了两种不同的实现思路及对应的Java代码示例。

题意

样例输入
5 7
样例输出
ABCDEFG
BABCDEF
CBABCDE
DCBABCD
EDCBABC


找规律

规律一 

1. 列+行-1大小的一维数组 + 行 个一维数组 + 对列整体进行平移sub_row个单位

2.左下角与右上方对称(I.row+row-column -> 情况2下越界;II.row <-> column 情况1 2通用)

3.截取输出 linesum*lettersum的矩阵

情况1)row<column

情况2)row>=column


规律一代码

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		char[] Alphabet = new char[26];
		int letter = (int)'A';
		for(int i = 0;i < 26;i++,letter++){
			Alphabet[i] = (char)letter;
		}
		Scanner keyin = new Scanner(System.in);
		int linesnum = keyin.nextInt();
		int letternum = keyin.nextInt();
		char[][] AlphabetOut = new char[linesnum][letternum+linesnum-1];
		
		for(int row = 0;row < linesnum;row++){
			for (int cnt_column = 0; row+cnt_column < AlphabetOut[0].length; cnt_column++) {
				AlphabetOut[row][row+cnt_column] = Alphabet[cnt_column%letternum];
			}
		}
		for (int row = 1; row < AlphabetOut.length; row++) {
			for (int column = 0; column < row; column++) {
				AlphabetOut[row][column] = AlphabetOut[column][row];
			}
		}
		for (int i = 0; i < AlphabetOut.length; i++) {
			for (int j = 0; j < letternum; j++) {
				System.out.print(AlphabetOut[i][j]);
			}
			System.out.println("");
		}
	}
}


规律二(标答规律)

1.26*26初始化

1)思路一

i)思路同规律一中,下一行 'A' 起始sub平移row个单位 -> Alphabet[row][row+cnt_column]=(char)((int)'A'+column) && row+cnt_column<26

ii).思路同规律一中对称II -> row <-> column

规律二代码1

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		char[][] Alphabet = new char[26][26];
		for (int row = 0; row < Alphabet.length; row++) {
			for (int cnt_column = 0; row+cnt_column < Alphabet[0].length; cnt_column++) {
				Alphabet[row][row+cnt_column] = (char)((int)'A'+cnt_column);
			}
		} 

		for (int row = 0; row < Alphabet.length; row++) {
			for (int column = 0; column < Alphabet[0].length; column++) {
				System.out.print(Alphabet[row][column]);
			}
			System.out.println("");
		}
		
		for (int row = 0; row < Alphabet.length; row++) {
			for (int column = 0; column < row; column++) {
				Alphabet[row][column] = Alphabet[column][row];
			}
		}

		Scanner keyin = new Scanner(System.in);
		int row_sum = keyin.nextInt();
		int column_sum = keyin.nextInt();
		for (int row = 0; row < row_sum; row++) {
			for (int column = 0; column < column_sum; column++) {
				System.out.print(Alphabet[row][column]);
			}
			System.out.println("");
		}
	}

}

2)思路二

->下标规律

规律二代码2

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		Scanner keyin = new Scanner(System.in);
		int row = keyin.nextInt();
		int column = keyin.nextInt();
		for (int i = 0; i < row; i++) {
			for (int j = 0; j < column; j++) {
				System.out.print((char)((int)'A'+Math.abs(i-j)));
			}
			System.out.println("");
		}
	}

}
3)思路三

->先向右 "赋值++" ,再向左 "++赋值" 

i)外循环(行)

ii)两个内循环

-1>边 列sub+1 (),边  'A'++

-2>边 行sub-1('A'左移),边++'A' (即先++再赋值)

历程

一看到这题

还以为是 -(>数组循环右移 ?

欸,搞了半天发现没那么难-(>数组循环赋值+row column对称(column<row时) ?

最后得了个70分

什么鬼,,

只考虑了row < column 的情况 -(1>只对每行先赋了lettersum个值然后右移

然后导致越界(对称I搞的鬼)-(2>换 对称II ->直接row column对调啊 不就行了

没系,再来(ง •_•)ง

木有错了啊。->【即:规律一】

还是  70分 

”怎么可以这么简单;一个找规律……搞得我……(但是我还是……打死不看参考答案,话说,你们Code的怎么全都差不多)明天早上起来更blog,更blog可比一晚上刷完6集神盾局带劲,嘿嘿。 “-> 【即:规律二】

欸 没事啦,当积累经验啊哈哈哈~~






import tkinter as tk from tkinter import filedialog, ttk, messagebox import openpyxl from openpyxl.utils import get_column_letter, column_index_from_string from openpyxl.styles import Font, Border, PatternFill, Alignment, Protection, GradientFill import threading import os import logging from datetime import datetime import time import sys # 配置日志 logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') class ExcelDiffApp: def __init__(self, root): self.root = root self.root.title("Excel差分工具") self.root.geometry("800x500") self.root.configure(bg="#f0f0f0") # 设置网格布局 self.root.grid_rowconfigure(0, weight=1) self.root.grid_columnconfigure(0, weight=1) # 创建主框架 self.main_frame = ttk.Frame(self.root, padding=20) self.main_frame.grid(row=0, column=0, sticky="nsew") self.main_frame.grid_rowconfigure(0, weight=1) self.main_frame.grid_columnconfigure(0, weight=1) # 文件路径变量 self.new_file_path = tk.StringVar() self.old_file_path = tk.StringVar() # 创建界面组件 self.create_widgets() def create_widgets(self): logging.debug("创建界面组件") # 标题 title_label = ttk.Label( self.main_frame, text="Excel差分工具", font=("Arial", 16, "bold"), foreground="#2c3e50" ) title_label.grid(row=0, column=0, columnspan=3, pady=(0, 20)) # 文件选择区域 - 新文件 file_frame = ttk.LabelFrame(self.main_frame, text="文件选择", padding=10) file_frame.grid(row=1, column=0, sticky="ew", padx=10, pady=5) file_frame.columnconfigure(1, weight=1) ttk.Label(file_frame, text="新版本Excel:").grid(row=0, column=0, sticky="w", padx=5, pady=5) new_entry = ttk.Entry(file_frame, textvariable=self.new_file_path, width=40) new_entry.grid(row=0, column=1, sticky="ew", padx=5, pady=5) ttk.Button(file_frame, text="浏览", command=self.select_new_file, width=8).grid(row=0, column=2, padx=5, pady=5) # 文件选择区域 - 旧文件 ttk.Label(file_frame, text="旧版本Excel:").grid(row=1, column=0, sticky="w", padx=5, pady=5) old_entry = ttk.Entry(file_frame, textvariable=self.old_file_path, width=40) old_entry.grid(row=1, column=1, sticky="ew", padx=5, pady=5) ttk.Button(file_frame, text="浏览", command=self.select_old_file, width=8).grid(row=1, column=2, padx=5, pady=5) # 开始按钮 button_frame = ttk.Frame(self.main_frame) button_frame.grid(row=2, column=0, pady=20) self.start_btn = ttk.Button( button_frame, text="开始差分", command=self.start_process, width=20, style="Accent.TButton" ) self.start_btn.pack() # 进度条区域 progress_frame = ttk.LabelFrame(self.main_frame, text="处理进度", padding=10) progress_frame.grid(row=3, column=0, sticky="ew", padx=10, pady=5) progress_frame.columnconfigure(0, weight=1) # 进度条 self.progress = ttk.Progressbar( progress_frame, orient="horizontal", length=600, mode="determinate" ) self.progress.grid(row=0, column=0, sticky="ew", padx=5, pady=5) # 百分比标签 self.progress_percent = ttk.Label( progress_frame, text="0%", font=("Arial", 10), anchor="center" ) self.progress_percent.grid(row=1, column=0, pady=(0, 5)) # 状态标签 self.status_label = ttk.Label( progress_frame, text="就绪", font=("Arial", 10), anchor="center", foreground="#27ae60" ) self.status_label.grid(row=2, column=0) # 详情标签 self.detail_label = ttk.Label( progress_frame, text="", font=("Arial", 9), anchor="center", foreground="#7f8c8d", wraplength=550 ) self.detail_label.grid(row=3, column=0, pady=(5, 0)) # 结果路径标签 self.result_label = ttk.Label( self.main_frame, text="", font=("Arial", 9), anchor="center", foreground="#2980b9", wraplength=550 ) self.result_label.grid(row=4, column=0, pady=(10, 0)) # 添加样式 self.style = ttk.Style() self.style.configure("Accent.TButton", foreground="white", background="#3498db") self.style.map("Accent.TButton", background=[("active", "#2980b9"), ("disabled", "#bdc3c7")]) logging.debug("界面组件创建完成") def select_new_file(self): logging.debug("选择新版本文件") file = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")]) if file: self.new_file_path.set(file) logging.debug(f"新版本文件路径: {file}") def select_old_file(self): logging.debug("选择旧版本文件") file = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")]) if file: self.old_file_path.set(file) logging.debug(f"旧版本文件路径: {file}") def get_last_column(self, ws): """获取工作表中最后一列有内容的列号""" max_col = 0 for row in ws.iter_rows(): for cell in row: if cell.value is not None: max_col = max(max_col, cell.column) return max_col def get_last_row(self, ws): """获取工作表中最后一行有内容的行号""" max_row = 0 for row in ws.iter_rows(): for cell in row: if cell.value is not None: max_row = max(max_row, cell.row) return max_row def copy_cell_style(self, source_cell, target_cell): """安全复制单元格样式""" try: # 复制字体样式 if source_cell.font: target_cell.font = Font( name=source_cell.font.name, size=source_cell.font.size, bold=source_cell.font.bold, italic=source_cell.font.italic, underline=source_cell.font.underline, strike=source_cell.font.strike, color=source_cell.font.color ) # 复制边框样式 if source_cell.border: target_cell.border = Border( left=source_cell.border.left, right=source_cell.border.right, top=source_cell.border.top, bottom=source_cell.border.bottom, diagonal=source_cell.border.diagonal, diagonal_direction=source_cell.border.diagonal_direction, outline=source_cell.border.outline, vertical=source_cell.border.vertical, horizontal=source_cell.border.horizontal ) # 复制填充样式 if source_cell.fill: # 处理 PatternFill if hasattr(source_cell.fill, 'patternType'): target_cell.fill = PatternFill( patternType=source_cell.fill.patternType, fgColor=source_cell.fill.fgColor, bgColor=source_cell.fill.bgColor ) # 处理 GradientFill elif hasattr(source_cell.fill, 'type'): target_cell.fill = GradientFill( degree=source_cell.fill.degree, stop=source_cell.fill.stop, type=source_cell.fill.type ) # 处理简单颜色 elif isinstance(source_cell.fill, openpyxl.styles.colors.Color): target_cell.fill = PatternFill(patternType="solid", fgColor=source_cell.fill) # 复制其他样式属性 target_cell.number_format = source_cell.number_format if source_cell.alignment: target_cell.alignment = Alignment( horizontal=source_cell.alignment.horizontal, vertical=source_cell.alignment.vertical, textRotation=source_cell.alignment.textRotation, wrapText=source_cell.alignment.wrapText, shrinkToFit=source_cell.alignment.shrinkToFit, indent=source_cell.alignment.indent, relativeIndent=source_cell.alignment.relativeIndent, justifyLastLine=source_cell.alignment.justifyLastLine, readingOrder=source_cell.alignment.readingOrder ) if source_cell.protection: target_cell.protection = Protection( locked=source_cell.protection.locked, hidden=source_cell.protection.hidden ) except Exception as e: logging.warning(f"样式复制失败: {str(e)}") def get_first_row(self, ws): """获取工作表中第一行有内容的行号""" min_row = None for row in ws.iter_rows(): for cell in row: if cell.value is not None: return cell.row return 1 # 如果没有找到有内容的行,默认为第1行 def get_first_column(self, ws): """获取工作表中第一列有内容的列号""" min_col = None for col in ws.iter_cols(): for cell in col: if cell.value is not None: return cell.column return 1 # 如果没有找到有内容的列,默认为第1列 def process_sheet(self, ws_new, ws_old, sheet_name, total_sheets, current_idx): try: # 获取行列信息 last_col_new = self.get_last_column(ws_new) first_col_new = self.get_first_column(ws_new) last_row_new = self.get_last_row(ws_new) first_row_new = self.get_first_row(ws_new) last_col_old = self.get_last_column(ws_old) first_col_old = self.get_first_column(ws_old) last_row_old = self.get_last_row(ws_old) first_row_old = self.get_first_row(ws_old) # 计算实际数据列数(新增) data_cols_new = last_col_new - first_col_new + 1 data_cols_old = last_col_old - first_col_old + 1 # 1. 复制旧数据 start_col = last_col_new + 3 # 原始数据后空3列放置旧数据 self.detail_label.config(text=f"正在复制旧数据...") # 计算总单元格数 total_cells = (last_row_old - first_row_old + 1) * data_cols_old processed_cells = 0 # 只复制有数据的区域 for row_idx in range(first_row_old, last_row_old + 1): for col_idx in range(first_col_old, last_col_old + 1): cell = ws_old.cell(row=row_idx, column=col_idx) if cell.value is None: continue new_col = start_col + (col_idx - first_col_old) new_cell = ws_new.cell(row=row_idx, column=new_col, value=cell.value) self.copy_cell_style(cell, new_cell) # 更新进度 processed_cells += 1 if processed_cells % 100 == 0 or processed_cells == total_cells: sheet_progress = int((processed_cells / total_cells) * 50) total_progress = int((current_idx + (sheet_progress / 100.0)) / total_sheets * 100) self.update_progress(total_progress, f"复制数据: {processed_cells}/{total_cells}单元格") # 复制列宽 for col in range(first_col_old, last_col_old + 1): col_letter = get_column_letter(col) new_col = start_col + (col - first_col_old) new_col_letter = get_column_letter(new_col) ws_new.column_dimensions[new_col_letter].width = ws_old.column_dimensions[col_letter].width # 2. 生成比较公式 compare_start_col = start_col + data_cols_old + 2 # 旧数据后空2列放置比较公式 self.detail_label.config(text=f"正在生成比较公式...") # 计算总单元格数(只处理有数据的区域) formula_cells = (last_row_new - first_row_new + 1) * data_cols_new processed_formulas = 0 # 只处理有数据的区域 for row_idx in range(first_row_new, last_row_new + 1): for col_idx in range(first_col_new, first_col_new + data_cols_new): # 使用data_cols_new定义范围 # 只处理新文件中有内容的单元格或对应旧文件中有内容的单元格 new_cell = ws_new.cell(row=row_idx, column=col_idx) old_col = start_col + (col_idx - first_col_new) old_cell = ws_new.cell(row=row_idx, column=old_col) # 如果新旧单元格都为空,跳过不处理 if new_cell.value is None and (old_col > ws_new.max_column or old_cell.value is None): continue # 计算列字母引用 old_col_letter = get_column_letter(old_col) new_col_letter = get_column_letter(col_idx) # 创建公式 formula = ( f'=IF(AND({new_col_letter}{row_idx}<>"",{old_col_letter}{row_idx}<>""),' f'IF({new_col_letter}{row_idx}={old_col_letter}{row_idx},"无变更","变更"),' f'IF({new_col_letter}{row_idx}<>"","新增",' f'IF({old_col_letter}{row_idx}<>"","删除","无变更")))' ) # 插入公式 target_col = compare_start_col + (col_idx - first_col_new) target_cell = ws_new.cell(row=row_idx, column=target_col, value=formula) # 复制格式(使用新文件中的格式) source_cell = ws_new.cell(row=row_idx, column=col_idx) self.copy_cell_style(source_cell, target_cell) # 更新进度 processed_formulas += 1 if processed_formulas % 100 == 0 or processed_formulas == formula_cells: sheet_progress = int((processed_formulas / formula_cells) * 40) + 50 total_progress = int((current_idx + (sheet_progress / 100.0)) / total_sheets * 100) self.update_progress(total_progress, f"生成公式: {processed_formulas}/{formula_cells}单元格") # 3. 生成统计列 - 修复部分 n_col = compare_start_col + data_cols_new # 放在比较公式区域之后 self.detail_label.config(text=f"正在生成统计列...") # 计算实际需要统计的列数(有内容的列) actual_count_cols = 0 for col_idx in range(compare_start_col, compare_start_col + data_cols_new): col_letter = get_column_letter(col_idx) # 检查该列是否有内容(从第2行开始) for row_idx in range(first_row_new + 1, last_row_new + 1): if ws_new.cell(row=row_idx, column=col_idx).value is not None: actual_count_cols += 1 break # 生成统计公式 for row_idx in range(first_row_new + 1, last_row_new + 1): # 从第2行开始 # 跳过完全空的行 row_has_data = False for col_idx in range(first_col_new, last_col_new + 1): if ws_new.cell(row=row_idx, column=col_idx).value is not None: row_has_data = True break if not row_has_data: continue # 动态生成统计公式 formula_parts = [] for col_idx in range(compare_start_col, compare_start_col + data_cols_new): col_letter = get_column_letter(col_idx) formula_parts.append(f'{col_letter}{row_idx}="无变更"') # 只有有内容的列才包含在统计中 if formula_parts: formula = f'=IF(AND({",".join(formula_parts)}), "无", "有")' else: formula = '"无数据"' # 没有可统计的列 target_cell = ws_new.cell(row=row_idx, column=n_col, value=formula) # 复制格式 if last_col_new > 0: source_cell = ws_new.cell(row=row_idx, column=last_col_new) self.copy_cell_style(source_cell, target_cell) # 更新进度 - 当前工作表完成 progress = int((current_idx + 1) / total_sheets * 100) self.update_progress(progress, f"工作表 {sheet_name} 处理完成") except Exception as e: logging.error(f"处理工作表 {sheet_name} 时出错: {str(e)}", exc_info=True) self.status_label.config(text="错误", foreground="#e74c3c") self.detail_label.config(text=f"处理工作表 {sheet_name} 时出错: {str(e)}", foreground="#e74c3c") messagebox.showerror("处理错误", f"处理工作表 {sheet_name} 时出错:\n{str(e)}") def update_progress(self, progress, detail=""): """更新进度条和状态标签""" self.progress["value"] = progress self.progress_percent.config(text=f"{progress}%") self.detail_label.config(text=detail) self.root.update_idletasks() def start_process(self): logging.debug("开始处理流程") new_file = self.new_file_path.get() old_file = self.old_file_path.get() if not all([new_file, old_file]): messagebox.showerror("错误", "请先选择新旧版本文件") return try: self.start_btn.config(state=tk.DISABLED) self.status_label.config(text="正在处理...", foreground="orange") self.update_progress(0, "准备开始...") self.result_label.config(text="") # 加载工作簿 logging.debug(f"加载新文件: {new_file}") self.update_progress(1, "加载新文件...") wb_new = openpyxl.load_workbook(new_file) logging.debug(f"加载旧文件: {old_file}") self.update_progress(3, "加载旧文件...") wb_old = openpyxl.load_workbook(old_file) # 获取共同存在的sheet self.update_progress(5, "查找共同工作表...") sheets = [sheet for sheet in wb_new.sheetnames if sheet in wb_old.sheetnames] total_sheets = len(sheets) logging.debug(f"找到共同工作表: {sheets}, 数量: {total_sheets}") if total_sheets == 0: messagebox.showerror("错误", "新旧Excel文件没有共同的工作表") self.reset_ui() return def worker(): try: start_time = time.time() for idx, sheet_name in enumerate(sheets): logging.debug(f"开始处理工作表 {sheet_name} (第 {idx+1}/{total_sheets} 个)") ws_new = wb_new[sheet_name] ws_old = wb_old[sheet_name] # 更新状态 sheet_progress = int(idx / total_sheets * 100) self.update_progress(sheet_progress, f"开始处理工作表: {sheet_name}") self.process_sheet(ws_new, ws_old, sheet_name, total_sheets, idx) # 保存结果 self.update_progress(95, "正在保存结果...") output_dir = os.path.dirname(new_file) or os.getcwd() timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") output_path = os.path.join(output_dir, f"diff_result_{timestamp}.xlsx") logging.debug(f"保存结果到: {output_path}") wb_new.save(output_path) # 计算处理时间 elapsed = time.time() - start_time self.root.after(0, lambda: self.show_completion(output_path, elapsed)) except Exception as e: logging.error(f"处理过程中出错: {str(e)}", exc_info=True) self.root.after(0, lambda: messagebox.showerror("处理错误", f"处理过程中出错:\n{str(e)}")) self.root.after(0, lambda: self.reset_ui()) # 启动处理线程 threading.Thread(target=worker, daemon=True).start() except Exception as e: messagebox.showerror("错误", f"加载文件失败: {str(e)}") self.reset_ui() logging.debug("处理流程结束") def reset_ui(self): logging.debug("重置UI状态") self.start_btn.config(state=tk.NORMAL) self.status_label.config(text="就绪", foreground="#27ae60") self.detail_label.config(text="") def show_completion(self, output_path, elapsed_time): logging.debug(f"处理完成,结果保存至: {output_path}") self.update_progress(100, "处理完成!") self.status_label.config(text="完成", foreground="#27ae60") self.detail_label.config(text=f"处理耗时: {elapsed_time:.2f}秒") self.result_label.config(text=f"结果保存至: {output_path}") self.start_btn.config(state=tk.NORMAL) messagebox.showinfo("完成", f"差分处理完成!\n耗时: {elapsed_time:.2f}秒\n结果已保存至:\n{output_path}") if __name__ == "__main__": root = tk.Tk() # Windows系统设置应用感观 if sys.platform.startswith('win'): from ctypes import windll windll.shcore.SetProcessDpiAwareness(1) # 启用高DPI支持 app = ExcelDiffApp(root) root.mainloop() 这个统计公式还是有问题的,1、单元格很多大于100个时,公式很大很乱。2、比较公式的填充因为有一些单元格为空,那么改比较公式单元格就为空,但是统计公式并没有进行优化,仍然判定了,所以需要进行修改
最新发布
11-19
class EmptyCellDetector: """空单元格检测器 - 增强版:添加规则7""" def __init__(self, color_detector): self.color_detector = color_detector # 固定列位置(列索引) self.COLUMN_MAPPING = { "A": 1, # A列 "B": 2, # B列 "C": 3, # C列 "D": 4, # D列 "E": 5, # E列 "F": 6, # F列 "G": 7, # G列 "H": 8, # H列 "I": 9, # I列 "J": 10, # J列 "K": 11, # K列 "L": 12, # L列 (差分種別) "M": 13, # M列 (变更内容) "N": 14, # N列 (判断列) "O": 15, # O列 (判断理由) "P": 16, # P列 (变更背景) "Q": 17 # Q列 (备注) } self.HEADER_ROW = 3 # 表头固定在第三行 self.DATA_START_ROW = 4 # 数据从第四行开始 self.SPEC_PREFIX = "仕様書No.:" # I列排除条件前缀 def detect_empty_cells(self, file_path, checksheet_base_path=None): """ 检测指定列的空单元格 返回: (missing_data, marked_file_path) - missing_data: 缺失数据列表 [{'row': 行号, 'col': 列号, 'col_name': 列名, 'message': 错误信息}, ...] - marked_file_path: 标记后的文件路径(如果有缺失数据) """ missing_data = [] marked_file_path = None try: logger.info(f"开始检测空单元格: {file_path}") # 加载SCL文件 scl_wb = openpyxl.load_workbook(file_path) scl_sheet = scl_wb.active logger.info(f"工作表加载成功: {scl_sheet.title}, 总行数: {scl_sheet.max_row}") # 检查是否有足够的数据行 if scl_sheet.max_row < self.DATA_START_ROW: logger.info("文件没有数据行,跳过检测") return missing_data, None # 获取文件名用于错误信息 file_name = os.path.basename(file_path) # 规则1: 检查A、B、C、K列的单元格是否为空 required_columns = ["A", "B", "C", "K"] for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): for col_letter in required_columns: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 数据缺失 (规则1)" }) logger.warning(f"行 {row_idx} 列 {col_letter} 数据缺失 (规则1)") # 规则5: 检查L-Q列是否为空(备注列表头可为空) required_cols = ["L", "M", "N", "O", "P", "Q"] for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): for col_letter in required_cols: col_idx = self.COLUMN_MAPPING[col_letter] # 备注列的表头可以为空,但数据需要检查 if col_letter == "Q" and row_idx == self.HEADER_ROW: continue cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 数据缺失 (规则5)" }) logger.warning(f"行 {row_idx} 列 {col_letter} 数据缺失 (规则5)") # 规则6: 当文件名后缀为.docx时,检查整个文件的F列(排除I列前缀为"仕様書No.:"的行) file_name_cell = scl_sheet.cell(row=1, column=1) if file_name_cell.value and str(file_name_cell.value).lower().endswith('.docx'): logger.info(f"文件后缀为.docx,检查F列数据: {file_name}") for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): # 检查I列是否有排除前缀 i_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["I"]) i_value = str(i_cell.value).strip() if i_cell.value else "" # 如果I列以指定前缀开头,则跳过该行 if i_value.startswith(self.SPEC_PREFIX): continue # 检查F列是否为空 col_idx = self.COLUMN_MAPPING["F"] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': "F", 'message': f"{file_name}: 行 {row_idx} 列 F 数据缺失 (规则6: .docx文件, 排除条件:{self.SPEC_PREFIX})" }) logger.warning(f"行 {row_idx} 列 F 数据缺失 (规则6: .docx文件, 排除条件:{self.SPEC_PREFIX})") # 规则2-4: 基于L列的值检查其他列 for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): l_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["L"]) l_value = str(l_cell.value).strip() if l_cell.value else "" # 规则2: 当L列为"変更"时,检查D、E、I、J列 if l_value == "変更": # 应有数据的列 required_cols = ["D", "E", "I", "J"] for col_letter in required_cols: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 数据缺失 (规则2: L列='変更')" }) logger.warning(f"行 {row_idx} 列 {col_letter} 数据缺失 (规则2: L列='変更')") # 规则3: 当L列为"削除"时,检查D、I列应有数据,E、J列应无数据 elif l_value == "削除": # 应有数据的列 required_cols = ["D", "I"] for col_letter in required_cols: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 数据缺失 (规则3: L列='削除')" }) logger.warning(f"行 {row_idx} 列 {col_letter} 数据缺失 (规则3: L列='削除')") # 应无数据的列 empty_cols = ["E", "J"] for col_letter in empty_cols: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is not None and str(cell.value).strip() != "": # 标记单元格为红色(不应有数据但有数据) self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFF0000") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 不应有数据 (规则3: L列='削除')" }) logger.warning(f"行 {row_idx} 列 {col_letter} 不应有数据 (规则3: L列='削除')") # 规则4: 当L列为"追加"时,检查E、J列应有数据,D、I列应无数据 elif l_value == "追加": # 应有数据的列 required_cols = ["E", "J"] for col_letter in required_cols: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is None or str(cell.value).strip() == "": # 标记单元格为黄色 self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFFFF00") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 数据缺失 (规则4: L列='追加')" }) logger.warning(f"行 {row_idx} 列 {col_letter} 数据缺失 (规则4: L列='追加')") # 应无数据的列 empty_cols = ["D", "I"] for col_letter in empty_cols: col_idx = self.COLUMN_MAPPING[col_letter] cell = scl_sheet.cell(row_idx, col_idx) if cell.value is not None and str(cell.value).strip() != "": # 标记单元格为红色(不应有数据但有数据) self.highlight_cell(scl_sheet, row_idx, col_idx, "FFFF0000") missing_data.append({ 'row': row_idx, 'col': col_idx, 'col_name': col_letter, 'message': f"{file_name}: 行 {row_idx} 列 {col_letter} 不应有数据 (规则4: L列='追加')" }) logger.warning(f"行 {row_idx} 列 {col_letter} 不应有数据 (规则4: L列='追加')") # 规则7: 变更背景检查 if checksheet_base_path: background_errors = self.check_background_rule7( scl_sheet, file_name, checksheet_base_path ) missing_data.extend(background_errors) # 如果有缺失数据,保存标记后的文件 if missing_data: marked_file_path = file_path.replace(".xlsx", "_marked.xlsx") scl_wb.save(marked_file_path) logger.warning(f"检测到空单元格,文件已标记并保存为: {marked_file_path}") return missing_data, marked_file_path except Exception as e: error_msg = f"空单元格检测失败: {str(e)}" logger.exception(f"空单元格检测失败: {file_path} - {str(e)}") return [{'row': 0, 'col': 0, 'col_name': '错误', 'message': error_msg}], None def highlight_cell(self, sheet, row, col, color="FFFF0000"): """为单元格设置背景色""" try: fill = PatternFill(start_color=color, end_color=color, fill_type="solid") sheet.cell(row=row, column=col).fill = fill return True except Exception as e: logger.error(f"设置单元格颜色失败: {str(e)}") return False def extract_folder_name(self, file_name): """从文件名中提取文件夹名 (SCL_和.xlsx之间的部分)""" pattern = r'SCL_(.*?)\.xlsx' match = re.search(pattern, file_name) if match: return match.group(1) return None def read_checksheet_data(self, checksheet_path): """从CheckSheet文件中读取数据(第8行开始,B、C列)""" try: logger.info(f"读取CheckSheet文件: {checksheet_path}") # 加载CheckSheet文件 wb = openpyxl.load_workbook(checksheet_path) sheet = wb.active # 表头在第7行 header_row = 7 # 数据从第8行开始 data_start_row = 8 # 存储读取到的数据 data = [] # 遍历数据行 for row_idx in range(data_start_row, sheet.max_row + 1): # 读取B列数据(合并后的主数据) b_cell = sheet.cell(row=row_idx, column=2) b_value = str(b_cell.value).strip() if b_cell.value else "" # 如果B列为空,停止读取 if not b_value: break # 读取C列数据 c_cell = sheet.cell(row=row_idx, column=3) c_value = str(c_cell.value).strip() if c_cell.value else "" # 只添加非空数据 if b_value or c_value: data.append((b_value, c_value)) logger.info(f"从CheckSheet读取到 {len(data)} 条数据") return data except Exception as e: logger.error(f"读取CheckSheet文件失败: {str(e)}") return [] def check_background_rule7(self, scl_sheet, file_name, checksheet_base_path): """检查变更背景是否符合规则7""" errors = [] try: logger.info(f"开始检查规则7: {file_name}") # 提取文件夹名 folder_name = self.extract_folder_name(file_name) if not folder_name: logger.warning(f"无法从文件名提取文件夹名: {file_name}") return [] # 构建CheckSheet文件路径 checksheet_dir = os.path.join(checksheet_base_path, folder_name) if not os.path.exists(checksheet_dir): logger.warning(f"文件夹不存在: {checksheet_dir}") return [] # 查找CheckSheet文件 checksheet_files = [f for f in os.listdir(checksheet_dir) if f.startswith("CheckSheet_") and f.endswith((".xlsx", ".xls"))] if not checksheet_files: logger.warning(f"未找到CheckSheet文件 in {checksheet_dir}") return [] # 使用第一个找到的CheckSheet文件 checksheet_file = checksheet_files[0] checksheet_full_path = os.path.join(checksheet_dir, checksheet_file) logger.info(f"使用CheckSheet文件: {checksheet_full_path}") # 读取CheckSheet数据 - 使用修复后的方法 checksheet_data = self.read_checksheet_data(checksheet_full_path) if not checksheet_data: logger.warning(f"CheckSheet文件无有效数据: {checksheet_full_path}") return [] # 打印读取到的sheet名数据 logger.info(f"读取到的CheckSheet数据: {checksheet_data}") # 提取所有sheet名(来自CheckSheet的B列和C列) sheet_names = set() for b_value, c_value in checksheet_data: if b_value: sheet_names.add(b_value) if c_value: sheet_names.add(c_value) logger.info(f"提取到 {len(sheet_names)} 个唯一sheet名") # 创建变更背景错误列表 background_errors = [] # 遍历C列,查找匹配的sheet名 for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): c_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["C"]) c_value = str(c_cell.value).strip() if c_cell.value is not None else "" # 检查是否在sheet名列表中 if c_value and c_value in sheet_names: # 检查P列(变更背景) p_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["P"]) p_value = str(p_cell.value).strip() if p_cell.value is not None else "" # 检查是否为空或为"-" if not p_value or p_value == "-": error_msg = f"{file_name}: 行 {row_idx} - C列值 '{c_value}' 匹配, 但变更背景为空或'-' (规则7)" background_errors.append({ 'row': row_idx, 'col': self.COLUMN_MAPPING["P"], 'col_name': "P", 'message': error_msg }) logger.warning(error_msg) # 标记错误单元格 for error in background_errors: self.highlight_cell(scl_sheet, error['row'], error['col'], "FFFFFF00") return background_errors except Exception as e: error_msg = f"检查规则7失败: {str(e)}" logger.exception(error_msg) return [{ 'row': 0, 'col': 0, 'col_name': '错误', 'message': f"{file_name}: {error_msg} (规则7)" }] 还有一个类没有更新为加强版
08-07
def create_column_letter_map(self): """创建列号到列字母的映射""" column_letter_map = {} # 生成A-Z列 for i in range(1, 27): column_letter_map[i] = chr(64 + i) # 生成AA-AZ列 for i in range(1, 27): column_letter_map[26 + i] = f"A{chr(64 + i)}" # 生成BA-BZ列 for i in range(1, 27): column_letter_map[52 + i] = f"B{chr(64 + i)}" # 生成CA-CZ列 for i in range(1, 27): column_letter_map[78 + i] = f"C{chr(64 + i)}" # 添加已知的特殊列 column_letter_map.update({ 16: "P", 23: "W", 27: "AA", 30: "AD", 34: "AH", 37: "AK", 42: "AP", 45: "AS", 50: "AX", 53: "BA", 57: "BE", 60: "BH", 62: "BL", 65: "BO", 71: "BS", 74: "BV", 78: "BZ", 85: "CG" }) return column_letter_map def process_file(self): """处理文件 - 根据操作模式执行不同处理流程""" operation_mode = self.operation_mode.get() # 重置结果 self.result_text.config(state=tk.NORMAL) self.result_text.delete(1.0, tk.END) self.result_text.insert(tk.END, "开始处理...\n") self.result_text.see(tk.END) self.result_text.config(state=tk.DISABLED) self.status_var.set("开始处理文件...") self.root.update() try: # 每次处理前重新初始化日志系统 self.setup_logger() # 记录处理开始信息 self.logger.info("=" * 50) self.logger.info(f"开始处理: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") self.logger.info(f"操作模式: {operation_mode}") self.logger.info("=" * 50) # 更新UI显示当前日志文件 self.status_var.set(f"当前日志: {os.path.basename(self.current_log_file)}") # 获取公共配置参数 scl_folder = self.scl_folder_var.get() prefix = self.prefix_var.get() # 根据操作模式执行不同处理流程 if operation_mode == "stats": self.process_stats_mode(scl_folder, prefix) elif operation_mode == "empty_check": self.process_empty_check_mode(scl_folder, prefix) else: messagebox.showerror("错误", f"未知操作模式: {operation_mode}") self.logger.error(f"未知操作模式: {operation_mode}") except Exception as e: error_msg = f"处理文件时出错: {str(e)}" self.logger.exception(f"处理文件时出错: {str(e)}") messagebox.showerror("错误", error_msg) self.status_var.set(f"错误: {str(e)}") # 更新结果文本框 self.result_text.config(state=tk.NORMAL) self.result_text.insert(tk.END, f"\n错误: {error_msg}\n") self.result_text.see(tk.END) self.result_text.config(state=tk.DISABLED) def process_stats_mode(self, scl_folder, prefix): """处理统计模式:扫描统计表E列获取SCL文件名""" # 检查输入文件是否存在 input_file = self.input_path_var.get() if not input_file: messagebox.showwarning("警告", "请先选择统计表") self.logger.warning("未选择统计表") return if not os.path.exists(input_file): messagebox.showerror("错误", f"统计表文件不存在: {input_file}") self.logger.error(f"统计表文件不存在: {input_file}") return # 使用openpyxl加载工作簿(保留格式) wb = openpyxl.load_workbook(input_file) sheet = wb.active self.logger.info(f"工作簿加载成功, 工作表: {sheet.title}") # 扫描E列(第5列) total_rows = sheet.max_row processed_count = 0 found_files = 0 problem_files = 0 self.logger.info(f"开始扫描E列, 总行数: {total_rows}") start_time = time.time() # 初始化关键列统计字典 key_columns = [16, 23, 27, 30, 34, 37, 42, 45, 50, 53, 57, 60, 62, 65, 71, 74, 78, 85] column_stats = {col: defaultdict(int) for col in key_columns} # 创建列号到列字母的映射(用于日志) column_letter_map = self.create_column_letter_map() for row_idx in range(1, total_rows + 1): # 更新进度 progress = (row_idx / total_rows) * 100 self.progress_var.set(progress) self.progress_label.config(text=f"{progress:.1f}%") self.status_var.set(f"处理行: {row_idx}/{total_rows}") self.root.update() cell = sheet.cell(row=row_idx, column=5) cell_value = str(cell.value) if cell.value else "" # 检查是否包含前缀的文件名 if prefix in cell_value: # 改进的正则表达式:匹配包含空格的文件名 # 匹配模式:以prefix开头,后跟任意字符(包括空格),直到文件扩展名 pattern = re.compile( fr'{prefix}[^\s]*\s*[^\s]*\.(?:xlsx|xls|xlsm)', re.IGNORECASE ) file_names = pattern.findall(cell_value) self.logger.info(f"行 {row_idx}: 找到文件: {', '.join(file_names)}") result_lines = [] file_has_problems = False # 标记当前行是否有问题文件 for file_name in file_names: # 在SCL文件夹及其子目录中查找文件(保留文件名中的空格) file_path = self.find_single_scl_file(scl_folder, file_name) # 检查文件是否存在 if not file_path or not os.path.exists(file_path): result_lines.append(f"{file_name}: 文件不存在") self.logger.warning(f"文件不存在: {file_name}") # 标记文件不存在的单元格为紫色 self.highlight_cell(sheet, row_idx, 5, "FF800080") file_has_problems = True problem_files += 1 continue # 执行统计处理 results, color_report, missing_data = self.stats_processor.process_file(file_path) # 如果有数据缺失 if missing_data: file_has_problems = True problem_files += 1 result_lines.append(f"{file_name}: 数据缺失!") for item in missing_data: result_lines.append(f" - {item['message']}") self.logger.warning(item['message']) else: result_lines.append(f"{file_name}: 处理完成") # 将结果写入主Excel文件的不同列 for rule_name, result_str in results.items(): target_col = self.stats_processor.RULE_MAPPING.get(rule_name) if target_col: target_cell = sheet.cell(row=row_idx, column=target_col) target_cell.value = result_str found_files += 1 # 在文件处理完成后,收集关键列数据 for col in key_columns: cell = sheet.cell(row=row_idx, column=col) cell_value = str(cell.value) if cell.value else "" # 跳过空值和错误信息 if not cell_value or cell_value.startswith("错误"): continue # 解析单元格值(格式为"值,计数") for line in cell_value.split('\n'): parts = line.split(',') if len(parts) >= 2: try: value = parts[0].strip() count = int(parts[1].strip()) column_stats[col][value] += count except ValueError: # 记录解析错误 self.logger.warning( f"行 {row_idx} 列 {column_letter_map.get(col, f'列{col}')} " f"无法解析值: {line}" ) # 如果该行有文件存在问题,将E列单元格标红 if file_has_problems: self.highlight_cell(sheet, row_idx, 5) self.logger.info(f"行 {row_idx} E列单元格标记为红色(存在问题)") # 更新结果文本框 self.result_text.config(state=tk.NORMAL) self.result_text.insert( tk.END, f"行 {row_idx} 处理结果:\n" + "\n".join(result_lines) + "\n\n" ) self.result_text.see(tk.END) self.result_text.config(state=tk.DISABLED) processed_count += 1 # 保存修改后的Excel文件 output_path = input_file.replace(".xlsx", "_processed.xlsx") wb.save(output_path) self.logger.info(f"结果已保存到: {output_path}") # 记录关键列统计结果到日志和UI self.log_key_column_stats(column_stats, column_letter_map) elapsed_time = time.time() - start_time status_msg = f"统计处理完成! 处理了 {processed_count} 个文件项, 耗时 {elapsed_time:.2f} 秒" if problem_files > 0: status_msg += f", {problem_files} 个文件存在问题" self.status_var.set(status_msg) self.logger.info(status_msg) # 更新结果文本框 self.result_text.config(state=tk.NORMAL) self.result_text.insert( tk.END, f"\n{status_msg}\n" f"结果已保存到: {output_path}\n" ) self.result_text.see(tk.END) self.result_text.config(state=tk.DISABLED) messagebox.showinfo("完成", status_msg) 关于关键列的处理,每个根据计数的值,由大到小进行排列记录
08-19
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值