import openpyxl from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils import get_column_letter from datetime import datetime, date import re import os class DataCleaner: def __init__(self): # Name mapping for technicians (from Skill.txt and project rules) self.kythuat_map = { 'trần dũng': 'Trần Dũng', 'trần dung': 'Trần Dũng', 'trần thanh dung': 'Trần Thanh Dũng', 'nguyễn văn tuân': 'Nguyễn Văn Tuân', 'nguyễn văn tuấn': 'Nguyễn Văn Tuấn', 'nguyên hưu lợi': 'Nguyễn Hữu Lợi', 'nguyên hữu lợi': 'Nguyễn Hữu Lợi', 'nguyễn hưu lợi': 'Nguyễn Hữu Lợi', 'nguyễn hữu lợi': 'Nguyễn Hữu Lợi', 'nguyên hữu lợi ( kt cắm tại nha máy làm)': 'Nguyễn Hữu Lợi', 'nguyễn hưu lợi ( kt căm tai nha máy làm)': 'Nguyễn Hữu Lợi', 'nông đưc trường': 'Nông Đức Trường', 'nông đức trường': 'Nông Đức Trường', 'đỗ quang thuỷ': 'Đỗ Quang Thủy', 'đỗ quang thủy': 'Đỗ Quang Thủy', 'đõ quang thuỷ': 'Đỗ Quang Thủy', 'nguyễn văn tùng': 'Nguyễn Văn Tùng', 'đoàn thanh nhân': 'Đoàn Thanh Nhân', 'dương tấn hiền': 'Dương Tấn Hiền', 'dươg tấn hiền': 'Dương Tấn Hiền', 'trịnh hữu hải': 'Trịnh Hữu Hải', 'phan văn hiếu': 'Phan Văn Hiếu', 'lê văn hoa': 'Lê Văn Hoa', 'bùi văn tuấn': 'Bùi Văn Tuấn', 'phạm thanh hoàn': 'Phạm Thanh Hoàn', 'lê xuân tương': 'Lê Xuân Tương', 'lê xuân tuong': 'Lê Xuân Tương', 'hồ tiến sĩ': 'Hồ Tiến Sĩ', 'ngọc dương': 'Ngọc Dương', 'trần văn thưởng': 'Trần Văn Thưởng', 'nguyễn văn hoan': 'Nguyễn Văn Hoan', 'hoàng phong': 'Hoàng Phong', 'hữu nhựt': 'Hữu Nhựt', 'xuân tương': 'Xuân Tương', 'thanh hoàn': 'Thanh Hoàn', 'phú vĩnh': 'Phú Vĩnh', 'văn lịnh': 'Văn Lịnh', 'hữu nghị': 'Hữu Nghị', 'đông (sơn nước)': 'Đông (Sơn Nước)', 'đông , thưởng': 'Đông , Thưởng', 'thưởng': 'Thưởng', } def parse_date(self, val): """Converts Excel cell date values to DD/MM/YYYY string format""" if val is None: return None if isinstance(val, datetime): return val.strftime("%d/%m/%Y") if isinstance(val, date): return val.strftime("%d/%m/%Y") if isinstance(val, (int, float)): try: base = datetime(1899, 12, 30) d = base + __import__('datetime').timedelta(days=int(val)) return d.strftime("%d/%m/%Y") except: return str(int(val)) s = str(val).strip() if re.match(r'^\d{2}/\d{2}/\d{4}$', s): return s m = re.match(r'^(\d{4})-(\d{2})-(\d{2})', s) if m: return f"{m.group(3)}/{m.group(2)}/{m.group(1)}" return s def normalize_kythuat(self, name): """Clean and normalize technician names""" if name is None: return None s = str(name).strip() if not s: return None parts = re.split(r'[,+]+', s) normalized_parts = [] for part in parts: part = part.strip() if not part: continue part = re.sub(r'\s+', ' ', part) part_clean = re.sub(r'\(.*?\)', '', part).strip() if not part_clean: normalized_parts.append(part.strip()) continue part_clean = re.sub(r'kt\s+c[ăa]m\s+t[aii].*$', '', part_clean, flags=re.IGNORECASE).strip() if not part_clean: continue words = part_clean.split() normalized_words = [] for w in words: if w.upper() == w and len(w) > 1: normalized_words.append(w) else: normalized_words.append(w[0].upper() + w[1:] if len(w) > 1 else w.upper()) normalized_parts.append(' '.join(normalized_words)) if not normalized_parts: return None return ' , '.join(normalized_parts) def apply_kythuat_map(self, name): if name is None: return None name_lower = name.lower().strip() if name_lower in self.kythuat_map: return self.kythuat_map[name_lower] return name def clean_file(self, input_path, output_path): """Cleans a raw report file and writes a standardized output file""" wb_in = openpyxl.load_workbook(input_path) ws_in = wb_in.active data_rows = [] current_nha_may = None # In typical report templates, header is at row 9, subheader at row 10, data starts from row 12 # Let's dynamically find where data starts. # We look for the first row after the headers that is not empty. start_row = 12 for r in range(start_row, ws_in.max_row + 1): stt = ws_in.cell(r, 1).value nha_may = ws_in.cell(r, 2).value ma_pt = ws_in.cell(r, 3).value ma_nm = ws_in.cell(r, 4).value khach_hang = ws_in.cell(r, 5).value ngay_nhan = ws_in.cell(r, 6).value ngay_ht = ws_in.cell(r, 7).value loai_moi = ws_in.cell(r, 8).value loai_nhan = ws_in.cell(r, 9).value loai_bo = ws_in.cell(r, 10).value so_luong = ws_in.cell(r, 11).value ky_thuat = ws_in.cell(r, 12).value trang_thai_dk = ws_in.cell(r, 13).value trang_thai_ht = ws_in.cell(r, 14).value trang_thai_dl = ws_in.cell(r, 15).value co_dh = ws_in.cell(r, 16).value ghi_chu = ws_in.cell(r, 18).value # Skip empty rows if (stt is None and nha_may is None and ma_pt is None and ma_nm is None and khach_hang is None and ngay_nhan is None and ngay_ht is None and loai_moi is None and loai_nhan is None and loai_bo is None and so_luong is None and ky_thuat is None and ghi_chu is None): continue if nha_may is not None and str(nha_may).strip(): current_nha_may = str(nha_may).strip() # Merge type loai_mau = None sp = None loai_moi_str = str(loai_moi).strip() if loai_moi is not None else '' loai_nhan_str = str(loai_nhan).strip() if loai_nhan is not None else '' loai_bo_str = str(loai_bo).strip() if loai_bo is not None else '' if loai_moi_str.upper() == 'X': loai_mau = 'Mới' elif loai_nhan_str.upper() == 'X': loai_mau = 'Nhân' elif loai_bo_str and loai_bo_str.upper() != 'X': loai_mau = 'Bộ' sp = loai_bo_str # Merge status trang_thai = None tt_dk_str = str(trang_thai_dk).strip() if trang_thai_dk is not None else '' tt_ht_str = str(trang_thai_ht).strip() if trang_thai_ht is not None else '' tt_dl_str = str(trang_thai_dl).strip() if trang_thai_dl is not None else '' if tt_dl_str.upper() == 'X': trang_thai = 'DK' # O: KH đã ký -> DK (Đã Ký) elif tt_ht_str.upper() == 'X': trang_thai = 'HT' # N: Đã hoàn thành -> HT (Hoàn Thành) elif tt_dk_str.upper() == 'X': trang_thai = 'ĐL' # M: Đang làm -> ĐL (Đang làm) # Normalize technician ky_thuat_clean = self.normalize_kythuat(ky_thuat) if ky_thuat else None ky_thuat_mapped = self.apply_kythuat_map(ky_thuat_clean) if ky_thuat_clean else ky_thuat_clean # Format dates ngay_nhan_clean = self.parse_date(ngay_nhan) ngay_ht_clean = self.parse_date(ngay_ht) # Fill factory nha_may_final = str(nha_may).strip() if nha_may is not None and str(nha_may).strip() else current_nha_may co_dh_final = None if co_dh is not None and str(co_dh).strip().upper() == 'X': co_dh_final = 'X' # Format quantity sl_final = so_luong if sl_final is not None: if isinstance(sl_final, str): try: sl_final = int(float(sl_final.replace(',', '').strip())) except: pass else: try: sl_final = int(sl_final) except: pass stt_final = stt if stt is not None: try: stt_final = int(float(str(stt))) except: pass ma_pt_final = str(ma_pt).strip() if ma_pt is not None else None if ma_pt_final in ('None', ''): ma_pt_final = None ma_nm_final = str(ma_nm).strip() if ma_nm is not None else None if ma_nm_final in ('None', ''): ma_nm_final = None ghi_chu_final = str(ghi_chu).strip() if ghi_chu is not None else None if ghi_chu_final in ('None', ''): ghi_chu_final = None data_rows.append([ stt_final, nha_may_final, ma_pt_final, ma_nm_final, khach_hang, ngay_nhan_clean, ngay_ht_clean, loai_mau, sp, sl_final, ky_thuat_mapped, trang_thai, co_dh_final, ghi_chu_final ]) # Write to clean file os.makedirs(os.path.dirname(output_path), exist_ok=True) wb_out = openpyxl.Workbook() ws_out = wb_out.active ws_out.title = "Làm sạch" headers = ["Stt", "Nhà máy", "Mã PT/KH", "Mã NM", "Khách hàng", "Ngày nhận", "Ngày HT", "Loại mẫu", "SP", "Số lượng", "Kỹ thuật", "Trạng thái", "Có ĐH", "Ghi chú"] header_font = Font(bold=True, size=11) header_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid") header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) for col_idx, h in enumerate(headers, 1): cell = ws_out.cell(1, col_idx, h) cell.font = header_font cell.fill = header_fill cell.alignment = header_alignment cell.border = thin_border for row_idx, row_data in enumerate(data_rows, 2): for col_idx, val in enumerate(row_data, 1): cell = ws_out.cell(row_idx, col_idx, val) cell.border = thin_border if col_idx == 1: cell.alignment = Alignment(horizontal='center') elif col_idx == 10: cell.alignment = Alignment(horizontal='center') col_widths = [5, 18, 18, 20, 18, 14, 14, 10, 25, 8, 30, 10, 8, 40] for i, w in enumerate(col_widths, 1): ws_out.column_dimensions[get_column_letter(i)].width = w wb_out.save(output_path) print(f"Data cleaned successfully and saved to: {output_path}") if __name__ == "__main__": import sys if len(sys.argv) < 3: print("Usage: python data_cleaner.py ") else: cleaner = DataCleaner() cleaner.clean_file(sys.argv[1], sys.argv[2])