# -*- coding: utf-8 -*- """ Làm sạch báo cáo mua hàng và so sánh trung bình ngày của 5 tháng trước đó. Tự động tìm kiếm file excel mới nhất và lưu kết quả theo đúng tháng hiện tại. """ import openpyxl from openpyxl.styles import Font, Alignment from openpyxl.utils import get_column_letter import os import re import sys sys.stdout.reconfigure(encoding='utf-8') BASE_DIR = os.path.dirname(os.path.abspath(__file__)) OUTPUT_DIR = os.path.join(BASE_DIR, "Bao_cao") os.makedirs(OUTPUT_DIR, exist_ok=True) # Số ngày chuẩn trong các tháng năm 2026 DAYS_IN_MONTH = { 1: 31, 2: 28, 3: 31, 4: 30, 5: 31, 6: 30, 7: 31, 8: 31, 9: 30, 10: 31, 11: 30, 12: 31 } def get_days_for_month_header(header_text): """Xác định số ngày tương ứng từ tiêu đề cột.""" header_clean = str(header_text or "").replace("\n", " ").strip() # 1. Kiểm tra nếu là kỳ hiện tại dạng "Từ X-> Y" range_match = re.search(r"(?:Từ|tu)\s*(\d+)\s*->\s*(\d+)", header_clean, re.IGNORECASE) if range_match: start_day = int(range_match.group(1)) end_day = int(range_match.group(2)) return end_day - start_day + 1 # 2. Kiểm tra nếu là tháng cố định dạng "Tháng MM" hoặc "T MM" month_match = re.search(r"(?:Tháng|T)\s*(\d+)", header_clean, re.IGNORECASE) if month_match: m = int(month_match.group(1)) return DAYS_IN_MONTH.get(m, 30) return 30 # Default fallback def find_latest_excel(): excel_files = [f for f in os.listdir(BASE_DIR) if f.startswith("MUA HÀNG") and f.endswith(".xlsx") and not f.startswith("~$")] if not excel_files: excel_files = [f for f in os.listdir(BASE_DIR) if f.startswith("MUA HANG") and f.endswith(".xlsx") and not f.startswith("~$")] if excel_files: excel_files.sort(key=lambda x: os.path.getmtime(os.path.join(BASE_DIR, x)), reverse=True) return os.path.join(BASE_DIR, excel_files[0]) return os.path.join(BASE_DIR, "MUA HÀNG THÁNG 8-26.xlsx") # fallback def clean_and_compare(): file_path = find_latest_excel() basename = os.path.basename(file_path) print(f"Bắt đầu xử lý & so sánh file Excel: {basename}") wb = openpyxl.load_workbook(file_path, data_only=True) # Lấy sheet FILE GỐC target_sheet = None for name in wb.sheetnames: if "FILE GỐC" in name.upper() or "FILE GOC" in name.upper(): target_sheet = name break if not target_sheet: target_sheet = max(wb.sheetnames, key=lambda s: wb[s].max_row) # Xóa sheet phụ for name in list(wb.sheetnames): if name != target_sheet: del wb[name] ws = wb[target_sheet] max_row = ws.max_row original_max_col = ws.max_column # Phân tích thông tin kỳ hiện tại (cột cuối) để lấy tháng last_header = ws.cell(4, original_max_col).value last_header_clean = str(last_header or "").replace("\n", " ").strip() month_match = re.search(r"(?:Tháng|T)\s*(\d+)", last_header_clean, re.IGNORECASE) month = int(month_match.group(1)) if month_match else 8 # Làm sạch các header hiện có (cột 4 đến cuối) for c in range(4, original_max_col + 1): cell = ws.cell(4, c) val = str(cell.value or "").replace("\n", " ").strip() if val and "(Khối lượng" not in val: cell.value = f"{val} (Khối lượng Kg)" # Xác định cột tính toán: current_col = original_max_col current_header = ws.cell(4, current_col).value current_days = get_days_for_month_header(current_header) print(f"Cột hiện tại: Cột {current_col} ({current_header}) -> Số ngày tính toán: {current_days}") # 5 tháng trước đó sẽ là 5 cột liền trước cột hiện tại preceding_cols = list(range(current_col - 5, current_col)) preceding_days_list = [] preceding_headers = [] for c in preceding_cols: header = ws.cell(4, c).value days = get_days_for_month_header(header) preceding_days_list.append(days) preceding_headers.append(header) total_preceding_days = sum(preceding_days_list) print(f"5 tháng trước đó: Cột {preceding_cols} ({preceding_headers}) -> Tổng số ngày: {total_preceding_days} ngày ({preceding_days_list})") # Thêm tiêu đề cho 4 cột mới new_headers = [ "TB Ngày 5T Trước (Kg)", "TB Ngày Hiện Tại (Kg)", "So Sánh (Kg)", "So Sánh (%)" ] header_font = Font(name="Times New Roman", size=10, bold=True) data_font = Font(name="Times New Roman", size=10) for idx, h_text in enumerate(new_headers): c_idx = original_max_col + 1 + idx cell = ws.cell(4, c_idx) cell.value = h_text cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.column_dimensions[get_column_letter(c_idx)].width = 18 # Định dạng cột cũ & điền công thức cho các dòng dữ liệu for r in range(5, max_row + 1): is_total_row = False row_label = str(ws.cell(r, 3).value or "").strip() if "tổng cộng" in row_label.lower() or "tổng" in row_label.lower() or r == max_row: is_total_row = True # Làm sạch và ép kiểu cột dữ liệu cũ sang số nguyên for c in range(1, original_max_col + 1): cell = ws.cell(r, c) cell.font = data_font if c >= 4: cell.alignment = Alignment(horizontal="right") if cell.value is not None: try: cell.value = int(float(str(cell.value).replace(",", "").strip())) except ValueError: pass # Đọc dữ liệu số để tính toán preceding_sum = 0 for c in preceding_cols: val = ws.cell(r, c).value preceding_sum += int(val or 0) current_val = int(ws.cell(r, current_col).value or 0) # Tính toán giá trị trung bình ngày tb_truoc = round(preceding_sum / total_preceding_days) tb_hientai = round(current_val / current_days) chenh_lech_kg = tb_hientai - tb_truoc if tb_truoc > 0: chenh_lech_pct = (chenh_lech_kg / tb_truoc) else: chenh_lech_pct = 1.0 if tb_hientai > 0 else 0.0 # Ghi vào 4 cột mới c_tb_truoc = ws.cell(r, original_max_col + 1) c_tb_truoc.value = tb_truoc c_tb_truoc.font = Font(name="Times New Roman", size=10, bold=is_total_row) c_tb_truoc.alignment = Alignment(horizontal="right") c_tb_truoc.number_format = "#,##0" c_tb_hientai = ws.cell(r, original_max_col + 2) c_tb_hientai.value = tb_hientai c_tb_hientai.font = Font(name="Times New Roman", size=10, bold=is_total_row) c_tb_hientai.alignment = Alignment(horizontal="right") c_tb_hientai.number_format = "#,##0" c_diff_kg = ws.cell(r, original_max_col + 3) c_diff_kg.value = chenh_lech_kg c_diff_kg.font = Font(name="Times New Roman", size=10, bold=is_total_row) c_diff_kg.alignment = Alignment(horizontal="right") c_diff_kg.number_format = "+#_##0;-#_##0;0" c_diff_pct = ws.cell(r, original_max_col + 4) c_diff_pct.value = chenh_lech_pct c_diff_pct.font = Font(name="Times New Roman", size=10, bold=True) c_diff_pct.alignment = Alignment(horizontal="right") c_diff_pct.number_format = "+0.0%;-0.0%;0.0%" # Thiết lập độ rộng cho các cột dữ liệu ban đầu for c in range(1, original_max_col + 1): col_letter = get_column_letter(c) if c == 1: ws.column_dimensions[col_letter].width = 5 elif c == 2: ws.column_dimensions[col_letter].width = 14 elif c == 3: ws.column_dimensions[col_letter].width = 50 elif 4 <= c < original_max_col: ws.column_dimensions[col_letter].width = 14 elif c == original_max_col: ws.column_dimensions[col_letter].width = 18 # Lưu file kết quả out_name = f"MUA_HANG_T{month}_DA-LAM-SACH_SO-SANH.xlsx" out_path = os.path.join(OUTPUT_DIR, out_name) wb.save(out_path) print(f"Đã lưu kết quả tại: {out_path}") print(f"Tổng số dòng xử lý: {max_row - 4}") if __name__ == "__main__": clean_and_compare()