wordexceltools

WordExcelTools

An efficient toolkit for handling Excel files and generating Word documents, leveraging the power of python-docx and openpyxl.

Install

pip install wordexceltools

Main Functions

Common

 1: common_append_file(file_path: str, content: str)
 2: common_capitalize_all_words(text: str)
 3: common_capitalize_first_letter(text: str)
 4: common_clean_text(text: str)
 5: common_contains_digit(text: str)
 6: common_copy_file(source: str, file_type: str, suffix: str)
 7: common_count_files_in_folders(directory: str)
 8: common_count_frequency(items: list)
 9: common_count_func_exec_time(func: Any)
10: common_date_diff(start_date: str, end_date: str)
11: common_execute_command(command: str)
12: common_func_retry(max_retries: int, delay: int)
13: common_generate_md5(text: str)
14: common_generate_random_string(length: int)
15: common_generate_unique_id()
16: common_get_current_date()
17: common_get_current_time()
18: common_get_file_encoding(file_path: str)
19: common_get_files_path(folder_path: Any, recursive: Any, include_files: Any)
20: common_inerate_dict(dict_data: dict)
21: common_int_to_roman(number: int)
22: common_iterate_list(lists: list)
23: common_iterate_list_idx(lists: list)
24: common_iterate_set(sets: set)
25: common_iterate_tuple(tuples: tuple)
26: common_list_to_string(lst: list, separator: str)
27: common_make_dir(path: str)
28: common_make_file(path: str)
29: common_now_time()
30: common_read_file(file_path: str)
31: common_remove_dir(path: str)
32: common_remove_dir_recursive(path: str)
33: common_remove_duplicates(lists: Any)
34: common_remove_file(path: str)
35: common_reverse_string(text: str)
36: common_sort_dict_by_value(data: dict, reverse: bool)
37: common_string_to_list(text: str, separator: str)
38: common_validate_email(email: str)
39: common_validate_phone(phone: str)
40: common_verify_file_exist(path: str)
41: common_verify_folder_empty(folder_path: str)
42: common_verify_folder_exist(path: str)
43: common_verify_license_count()
44: common_verify_license_date(license_time: str)
45: common_verify_license_expiry(license_count: Any, license_date: Any, count: int)
46: common_verify_positive_integer(value: Any)
47: common_verify_positive_integer(value: Any)
48: common_write_file(file_path: str, content: str)

Excel

  1: excel_add_sheet(source_excel: Any, sheet_name: Any)
  2: excel_copy_file(source_excel: Any, target_excel: Any)
  3: excel_count_keywords_in_columns(sheet: Any, column_identifiers: Any, keywords: Any)
  4: excel_count_sheet_columns_with_prefix_suffix(sheet: Any, column_prefix: Any, column_suffix: Any)
  5: excel_delete_file(target_excel: Any)
  6: excel_delete_sheet_by_identifiers(source_excel: Any, sheet_identifiers: Any)
  7: excel_delete_sheet_columns(sheet: Any, target_column_index: Any, number_of_columns: Any)
  8: excel_delete_sheet_columns_by_identifiers(sheet: Any, column_identifiers: Any)
  9: excel_delete_sheet_rows(sheet: Any, target_row_index: Any, number_of_rows: Any)
 10: excel_delete_sheet_rows_by_keyword(sheet: Any, target_column: Any, keyword: Any)
 11: excel_fill_sheet_cells(sheet: Any, ranges_to_fill: Any, fill_value: Any)
 12: excel_get_sheet(workbook: Any, sheet_identifiers: Any)
 13: excel_get_sheet_cell_date_value(sheet: Any, row: Any, column: Any)
 14: excel_get_sheet_cell_value(sheet: Any, row: Any, column: Any)
 15: excel_get_sheet_column_keyword_count(sheet: Any, column_identifiers: Any, keyword: Any)
 16: excel_get_sheet_column_len(sheet: Any, column_identifiers: Any)
 17: excel_get_sheet_column_str_custom(sheet: Any, column_identifiers: Any, separator: Any, end_mark: Any)
 18: excel_get_sheet_column_values(sheet: Any, column_identifiers: Any, include_header: Any)
 19: excel_get_sheet_row_col_count(sheet: Any)
 20: excel_get_sheet_row_or_col_max(sheet: Any, identifiers: Any, mode: Any)
 21: excel_get_sheet_row_values(sheet: Any, row_identifiers: Any, include_index: Any)
 22: excel_get_sheets_index_name(workbook: Any)
 23: excel_get_sheets_targets_counts(list_sheets: Any, column_prefix: Any, column_suffix: Any)
 24: excel_get_workbook(source_excel: Any)
 25: excel_insert_sheet_columns(sheet: Any, target_column_index: Any, number_of_columns: Any)
 26: excel_insert_sheet_rows(sheet: Any, target_row_index: Any, number_of_rows: Any)
 27: excel_merge_sheet_cells(sheet: Any, ranges_to_merge: Any)
 28: excel_replace_sheet_content(sheet: Any, target_text: Any, replacement_text: Any, rows: Any, cols: Any)
 29: excel_save_workbook(workbook: Any, output_file: Any)
 30: excel_set_sheet_alignment(sheet: Any, ranges: Any, horizontal: Any, vertical: Any)
 31: excel_set_sheet_bold(sheet: Any, ranges: Any, bold: Any)
 32: excel_set_sheet_cell_value(sheet: Any, row: Any, column: Any, value: Any)
 33: excel_set_sheet_fill_color(sheet: Any, ranges: Any, color_hex: Any)
 34: excel_set_sheet_font_color(sheet: Any, ranges: Any, color_hex: Any)
 35: excel_set_sheet_font_size(sheet: Any, ranges: Any, font_size: Any)
 36: excel_set_sheet_freeze_panes(sheet: Any, cell_reference: Any)
 37: excel_unmerge_sheet_cells(sheet: Any, ranges_to_unmerge: Any)

Word

 1: word_add_custom_heading(document: Any, level: Any, text: Any)
 2: word_add_custom_paragraph(document: Any, text: Any, style: Any)
 3: word_add_custom_table(document: Any, row_count: Any, column_count: Any, style: Any, font_size: Any, spacing_before: Any)
 4: word_add_figure_caption(document: Any, caption_text: Any)
 5: word_add_indented_paragraph(document: Any, text: Any)
 6: word_add_ordered_list(document: Any, items: Any, index_style: Any, indentation: Any)
 7: word_add_table_caption(document: Any, caption_text: Any)
 8: word_docxinitial(doc: Any, text_font_type: Any, text_font_size: Any, text_font_line_spacing: Any, text_header: Any, text_footer: Any)
 9: word_get_section_number(paragraph_text: Any)
10: word_get_section_number(paragraph_text: Any)
11: word_get_table_cell_text(table: Any, row_index: Any, column_index: Any)
12: word_merge_cells(table: Any, start_row: Any, start_column: Any, end_row: Any, end_column: Any)
13: word_merge_cells_by_column(table: Any, column_index: Any)
14: word_set_number_to_roman(number: Any)
15: word_set_table_alignment(table: Any, start_row: Any, start_column: Any, end_row: Any, end_column: Any, orizontal_alignment: Any, vertical_alignment: Any)
16: word_set_table_cell_text(table: Any, row_index: Any, column_index: Any, text: Any)
17: word_set_table_style(table: Any, font_size: Any, spacing_before: Any)
18: word_setsectionformat(doc: Any)
19: word_table_is_valid(table: Any, value: str)

Example

source_excel = "./test1.xlsx"
copy_excel = "./test2.xlsx"
# Test 1
excel_add_sheet(source_excel, "test1")

# Test 2
excel_copy_file(source_excel, copy_excel)

# Test 22
wb_copy = excel_get_workbook(copy_excel)

# Test 10
sheet_copy_1 = excel_get_sheet(wb_copy, 0)

# Test 3
print(excel_count_sheet_columns_with_prefix_suffix(sheet_copy_1, "平台", "结果"))

# Test 5
excel_delete_sheet_by_identifiers(copy_excel, 1)
workbook = excel_get_workbook(source_excel)

# Test 6
excel_delete_sheet_columns(sheet_copy_1, 2, 2)

# Test 7
excel_delete_sheet_columns_by_identifiers(sheet_copy_1, 1)

# Test 8
excel_delete_sheet_rows(sheet_copy_1, 2, 3)

# Test 9
excel_fill_sheet_cells(sheet_copy_1, (2, 4, 5, 4), 1)

# Test 11
print(excel_get_sheet_cell_date_value(sheet_copy_1, 10, 1))

# Test 12
print(excel_get_sheet_cell_value(sheet_copy_1, 10, 1))

# Test 13
print(excel_get_sheet_column_keyword_count(sheet_copy_1, 6, "XXXX"))

# Test 14
print(excel_get_sheet_column_len(sheet_copy_1, 6))

# Test 15
print(excel_get_sheet_column_str_custom(sheet_copy_1, 3))

# Test 16
print(excel_get_sheet_column_values(sheet_copy_1, 3))

# Test 17
print(excel_get_sheet_row_col_count(sheet_copy_1))

# Test 18
print(excel_get_sheet_row_or_col_max(sheet_copy_1, 1, mode='row'))

# Test 19
print(excel_get_sheet_row_values(sheet_copy_1, 2))

# Test 20
print(excel_get_sheets_index_name(wb_copy))

# Test 21
print(excel_get_sheets_targets_counts(sheet_copy_1, "平台", "结果"))

# Test 23
print(excel_insert_sheet_columns(sheet_copy_1, 4, 2))

# Test 24
print(excel_insert_sheet_rows(sheet_copy_1, 4, 2))

# Test 25
print(excel_merge_sheet_cells(sheet_copy_1, (2, 4, 5, 4)))

# Test 26
print(excel_replace_sheet_content(sheet_copy_1, "XXXX", "QWE", cols=6))

# Test 28
print(excel_set_sheet_alignment(sheet_copy_1, (2, 4, 5, 4), horizontal="left", vertical="center"))

# Test 29
print(excel_set_sheet_bold(sheet_copy_1, (2, 4, 5, 4)))

# Test 30
print(excel_set_sheet_cell_value(sheet_copy_1, 4, 1, "QQ"))

# Test 31
print(excel_set_sheet_fill_color(sheet_copy_1, (2, 4, 5, 4),  "FFFF00"))

# Test 32
print(excel_set_sheet_font_color(sheet_copy_1, (2, 4, 5, 4),  "FF0000"))

# Test 33
print(excel_set_sheet_font_size(sheet_copy_1, (2, 4, 5, 4),  16))

# Test 34
print(excel_set_sheet_freeze_panes(sheet_copy_1, "C2"))

# Test 34
print(excel_unmerge_sheet_cells(sheet_copy_1, (2, 4, 5, 4)))

# Test 27
print(excel_save_workbook(wb_copy))

# Test 4
excel_delete_file(copy_excel)