This is the 25th day of my participation in the August Genwen Challenge.More challenges in August

Python is one of the more powerful languages, and it’s popular right now. We see a lot of ads about how great programming is, and one of them is about Python manipulating Excel to make it more efficient. This article will teach you how to use Python to manipulate Excel and improve your productivity.

Batch create and save workbooks

App = xw. app (visible = True, add_book = False) for I in range(6): Save (f e:\\file\\test{I}.xlsx') # Save (f e:\\file\\test{I}.xlsx') # save(f e:\\file\\test{I}.xlsx') # App.quit () # Quit ExcelCopy the code

The above code is the most basic operation on the workbook, creating and saving the workbook, and closing the current workbook.

import os
file_path = 'table'
file_list = os.listdir(file_path)
for i in file_list:
    print(i)

Copy the code

List the names of all files and subfolders under the folder. This makes it easy to summarize and view files.

import xlwings as xw app = xw.App(visible = False, Add_book = False) workbook = app.books.open('e:\\table\\ table.xlsx ') worksheets = workbook.sheets range(len(worksheets)): Worksheets [I].name = worksheets[I].name. Replace (' sales ', Save ('e:\\table\\ table 1.xlsx') # Save the workbook app.quit()Copy the code

Batch rename all worksheets in a workbook. The most important function of this is to operate on worksheets.

Import OS import xlwings as xw file_path = 'e:\\table\\ company 'file_list = os.listdir(file_path) app = xw.app (visible = False, add_book = False) for i in file_list: if i.startswith('~$'): continue file_paths = os.path.join(file_path, Workbook = app.books.open(file_paths) # Open workbook to print workbook.api.printout () # Print workbook app.quit()Copy the code

Print worksheets in batches using workbook.api.printout (), which calls the printer

Import xlwings as xW workbook_name = 'e:\ table\ product sales table. XLSX '# add_book = False) header = None all_data = [] workbook = app.books.open(workbook_name) for i in workbook.sheets: Sheet_split = workbook_split.sheets[0] # Select the first worksheet in the target workbook I.api.copy (Before = sheet_split.api) # Copy the current worksheet from the source workbook to the first worksheet Before the target workbook workbook_split.save('{}'.format(i.name)) # Save the target workbook app.quit() with the name of the current worksheet as the file nameCopy the code

The above code splits the workbook, which is also relatively simple. It iterates over the worksheets in the original workbook, then copies them into the new worksheet, and then saves them.

If you want to learn more about python manipulating excel programming source code, you can follow the public account: Poetic Code.