Discussion :

[Dingobec]

OK mais pas grave si je perds la mise en forme, j'aurai voulu quand même avoir une solution en Python

Merci.

[fred1599]

Voici au cas où une correction,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
import logging
from pathlib import Path
from typing import Iterator, Set
 
import pandas as pd
 
INPUT_DIR = Path(r"C:\test2")
OUTPUT_FILENAME = "Collecte2026.xlsx"
INFO_SHEET_NAME = "Info"
SHEET_NAME_MAX_LENGTH = 31
DEFAULT_SHEET_NAME = "Sheet"
INVALID_SHEET_CHARS = set(r"[]:*?/\\")
LOGGER = logging.getLogger(__name__)
 
 
def _is_input_excel(file_path: Path) -> bool:
    return file_path.suffix.lower() in (".xls", ".xlsx")
 
 
def _iter_input_files(input_dir: Path, output_filename: str) -> Iterator[Path]:
    for file_path in input_dir.iterdir():
        is_output_file = file_path.name.lower() == output_filename.lower()
        if _is_input_excel(file_path) and not is_output_file:
            yield file_path
 
 
def _sanitize_sheet_base_name(file_path: Path) -> str:
    sanitized_name = "".join(
        "_" if character in INVALID_SHEET_CHARS else character
        for character in file_path.stem
    ).strip()
    if not sanitized_name:
        return DEFAULT_SHEET_NAME
    return sanitized_name
 
 
def _build_unique_sheet_name(file_path: Path, used_sheet_names: Set[str]) -> str:
    base_name = _sanitize_sheet_base_name(file_path)
    candidate = base_name[:SHEET_NAME_MAX_LENGTH]
 
    if candidate.casefold() not in used_sheet_names:
        used_sheet_names.add(candidate.casefold())
        return candidate
 
    suffix_index = 2
    while True:
        suffix = f"_{suffix_index}"
        base_slice_size = SHEET_NAME_MAX_LENGTH - len(suffix)
        unique_candidate = f"{base_name[:base_slice_size]}{suffix}"
        normalized_candidate = unique_candidate.casefold()
        if normalized_candidate not in used_sheet_names:
            used_sheet_names.add(normalized_candidate)
            return unique_candidate
        suffix_index += 1
 
 
def _write_input_sheet(
    writer: pd.ExcelWriter,
    file_path: Path,
    sheet_name: str,
) -> None:
    data_frame = pd.read_excel(file_path)
    data_frame.to_excel(writer, sheet_name=sheet_name, index=False)  # pragma: no mutate
 
 
def _write_info_sheet(writer: pd.ExcelWriter) -> None:
    pd.DataFrame(
        {"message": ["Aucun fichier Excel exploitable trouve dans le dossier."]},
    ).to_excel(writer, sheet_name=INFO_SHEET_NAME, index=False)  # pragma: no mutate
 
 
def main() -> None:
    logging.basicConfig(level=logging.INFO, format="%(message)s")
    INPUT_DIR.mkdir(parents=True, exist_ok=True)
    output_file = INPUT_DIR / OUTPUT_FILENAME
    written_sheets = 0
    used_sheet_names: Set[str] = set()
 
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        for file_path in _iter_input_files(INPUT_DIR, OUTPUT_FILENAME):
            try:
                sheet_name = _build_unique_sheet_name(file_path, used_sheet_names)
                _write_input_sheet(writer, file_path, sheet_name)
            except Exception as error:
                LOGGER.error("Erreur avec %s : %s", file_path.name, error)
            else:
                written_sheets += 1  # pragma: no mutate
                LOGGER.info("Ajoute : %s", file_path.name)
 
        if written_sheets == 0:
            _write_info_sheet(writer)
            LOGGER.info("Aucun fichier valide a fusionner, onglet Info cree.")
 
    LOGGER.info("Fusion terminee")
 
 
if __name__ == "__main__":
    main()

[Dingobec]

Super, cela fonctionne bien, je récupère tous les onglets

Une dernière chose si je veux supprimer le début de l'affichage standard pour chaque fichier "COLLECTE DES RESTOS DU COEUR A" pour ne garder que le nom du magasin dans chaque onglet. Je mets ça comment et où?

Merci

[fred1599]

Je ferai de cette manière, mais pas sûr d'avoir compris précisément,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
import logging
from pathlib import Path
from typing import Iterator, Set
 
import pandas as pd
 
INPUT_DIR = Path(r"C:\test2")
OUTPUT_FILENAME = "Collecte2026.xlsx"
INFO_SHEET_NAME = "Info"
SHEET_NAME_MAX_LENGTH = 31
DEFAULT_SHEET_NAME = "Sheet"
INVALID_SHEET_CHARS = set(r"[]:*?/\\")
SHEET_NAME_PREFIX_TO_REMOVE = "COLLECTE DES RESTOS DU COEUR A "
LOGGER = logging.getLogger(__name__)
 
 
def _is_input_excel(file_path: Path) -> bool:
    return file_path.suffix.lower() in (".xls", ".xlsx")
 
 
def _iter_input_files(input_dir: Path, output_filename: str) -> Iterator[Path]:
    for file_path in input_dir.iterdir():
        is_output_file = file_path.name.lower() == output_filename.lower()
        if _is_input_excel(file_path) and not is_output_file:
            yield file_path
 
 
def _sanitize_sheet_base_name(file_path: Path) -> str:
    base_name = file_path.stem.strip()
    if base_name.casefold().startswith(SHEET_NAME_PREFIX_TO_REMOVE.casefold()):
        base_name = base_name[len(SHEET_NAME_PREFIX_TO_REMOVE):].strip(" -_")
 
    sanitized_name = "".join(
        "_" if character in INVALID_SHEET_CHARS else character
        for character in base_name
    ).strip()
    if not sanitized_name:
        return DEFAULT_SHEET_NAME
    return sanitized_name
 
 
def _normalize_sheet_name(sheet_name: str) -> str:
    return sheet_name.casefold()
 
 
def _is_sheet_name_available(sheet_name: str, used_sheet_names: Set[str]) -> bool:
    return _normalize_sheet_name(sheet_name) not in used_sheet_names
 
 
def _reserve_sheet_name(sheet_name: str, used_sheet_names: Set[str]) -> str:
    used_sheet_names.add(_normalize_sheet_name(sheet_name))
    return sheet_name
 
 
def _build_suffixed_sheet_name(base_name: str, suffix_index: int) -> str:
    suffix = f"_{suffix_index}"
    base_name_slice = base_name[: SHEET_NAME_MAX_LENGTH - len(suffix)]
    return "".join((base_name_slice, suffix))
 
 
def _build_unique_sheet_name(file_path: Path, used_sheet_names: Set[str]) -> str:
    base_name = _sanitize_sheet_base_name(file_path)
    candidate = base_name[:SHEET_NAME_MAX_LENGTH]
 
    if _is_sheet_name_available(candidate, used_sheet_names):
        return _reserve_sheet_name(candidate, used_sheet_names)
 
    suffix_index = 2
    while True:
        unique_candidate = _build_suffixed_sheet_name(base_name, suffix_index)
        if _is_sheet_name_available(unique_candidate, used_sheet_names):
            return _reserve_sheet_name(unique_candidate, used_sheet_names)
        suffix_index += 1
 
 
def _write_input_sheet(
    writer: pd.ExcelWriter,
    file_path: Path,
    sheet_name: str,
) -> None:
    data_frame = pd.read_excel(file_path)
    data_frame.to_excel(writer, sheet_name=sheet_name, index=False)  # pragma: no mutate
 
 
def _write_info_sheet(writer: pd.ExcelWriter) -> None:
    pd.DataFrame(
        {"message": ["Aucun fichier Excel exploitable trouve dans le dossier."]},
    ).to_excel(writer, sheet_name=INFO_SHEET_NAME, index=False)  # pragma: no mutate
 
 
def _write_single_input_file(
    writer: pd.ExcelWriter,
    file_path: Path,
    used_sheet_names: Set[str],
) -> None:
    sheet_name = _build_unique_sheet_name(file_path, used_sheet_names)
    _write_input_sheet(writer, file_path, sheet_name)
 
 
def _try_write_single_input_file(
    writer: pd.ExcelWriter,
    file_path: Path,
    used_sheet_names: Set[str],
) -> bool:
    try:
        _write_single_input_file(writer, file_path, used_sheet_names)
    except Exception as error:
        LOGGER.error("Erreur avec %s : %s", file_path.name, error)
        return False
    LOGGER.info("Ajoute : %s", file_path.name)
    return True
 
 
def main() -> None:
    logging.basicConfig(level=logging.INFO, format="%(message)s")
    INPUT_DIR.mkdir(parents=True, exist_ok=True)
    output_file = INPUT_DIR / OUTPUT_FILENAME
    written_sheets = 0
    used_sheet_names: Set[str] = set()
 
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        for file_path in _iter_input_files(INPUT_DIR, OUTPUT_FILENAME):
            if _try_write_single_input_file(writer, file_path, used_sheet_names):
                written_sheets += 1  # pragma: no mutate
 
        if written_sheets == 0:
            _write_info_sheet(writer)
            LOGGER.info("Aucun fichier valide a fusionner, onglet Info cree.")
 
    LOGGER.info("Fusion terminee")
 
 
if __name__ == "__main__":
    main()

[Dingobec]

C'est tout à fait ça, Merci beaucoup à tous.
J'ai appris beaucoup

[Dingobec]

A Fred1599,

Merci pour ta solution, maintenant je voudrai avoir la solution pour collecte des restos du coeur A mais aussi à restos du coeur au. Ceci afin d'éliminer ces 2 chaines de caractères des onglets . Merci

[umfred]

Essai de comprendre le code de Fred1599, tu devrais pouvoir apporter la modification toi-même (puisqu'il traite déjà "COLLECTE DES RESTOS DU COEUR A ")