# -*- coding: utf-8 -*-
"""Kiem chung 2 file Men/Thu (glob de tranh lech Unicode) + kiem 3 file."""
import glob, os, unicodedata, zipfile, openpyxl

DEST = r'C:\Claw 2\Bao cao - Hanh chinh\Bao cao lam mau'
print('=== ten file that trong thu muc (repr) ===')
for f in sorted(os.listdir(DEST)):
    if '40' in f and f.lower().endswith(('.xlsx', '.xls')):
        print('  ', repr(f), os.path.getsize(os.path.join(DEST, f)))

files = sorted(set(glob.glob(os.path.join(DEST, '*40*.xlsx'))))
print()
print('=== kiem chung ===')
for p in files:
    b = os.path.basename(p)
    print('-', unicodedata.normalize('NFC', b), '|', os.path.getsize(p), 'byte | zip:', zipfile.is_zipfile(p))
    try:
        wb = openpyxl.load_workbook(p, read_only=True, data_only=True)
        print('   sheets:', wb.sheetnames, '(%d)' % len(wb.sheetnames))
        for sn in wb.sheetnames[:3]:
            ws = wb[sn]
            n = last = 0
            first = None
            for i, row in enumerate(ws.iter_rows(values_only=True), start=1):
                if any(c is not None and str(c).strip() != '' for c in row):
                    n += 1
                    last = i
                    if first is None and i > 1:
                        first = i
            print('    - %-28s dong co du lieu: %4d | dong cuoi: %4d' % (sn, n, last))
        wb.close()
    except Exception as e:
        print('   loi:', e)