How to modify the header of .xls file in Python

Today, my colleague said that he had more than 1,000 tables in his hand and asked to change the table in batches. For example, changing the appointment and removal form of personnel cadres to personnel information form is a small matter. It is estimated that it will take a lot of time to change one by one to see if you can write a mini program or something.

The first thing I think about is that this matter is not that easy. The main reason is that .xls is an old format, but it is not easy to change the table. I started to try it. GPT always likes to change the .xlsx format, but the old format does not have much support.

Finally, I found the xlwings component. Finally, I can only change the table header and not lose the format. Considering that there are more than 1,000 files, it is best to traverse and search in one directory, find one to modify one and then save it in another directory. The overall code is as follows:

In order to make the operation simple in the program, create the folder D:/data/ori directly on the D disk as the source directory.

D:/data/mod as the target directory

import os
import xlwings as xw
 
 
def modify_excel_file(file_path, save_path, app):
    """
     Use the incoming Excel application instance app to open the file_path file,
     Modify the content of the first worksheet A1 cell to "People Information Table".
     Then save as save_path file.
     """
    wb = (file_path)
    # Assume that the title is in cell A1 of the first worksheet (if there are merged cells, just modify the upper left corner)    [0].range('A1').value = "People Information Table"
    (save_path)
    ()
 
 
def process_excel_files(input_dir, output_dir):
    """
     Search for all .xls files in the input_dir directory,
     Use Excel to modify and save to the output_dir directory.
     """
    # If the output directory does not exist, create    if not (output_dir):
        (output_dir)
 
    # Start Excel application and run in the background    app = (visible=False)
    try:
        # traverse all files in the directory        for filename in (input_dir):
            if ().endswith('.xls'):
                file_path = (input_dir, filename)
                save_path = (output_dir, filename)
                print(f"Processing：{file_path}")
                modify_excel_file(file_path, save_path, app)
    finally:
        # Exit Excel app        ()
 
 
if __name__ == '__main__':
    input_directory = r'D:\data\ori'
    output_directory = r'D:\data\mod'
    process_excel_files(input_directory, output_directory)
    print("The processing is completed!")
    input("Press any key to exit...")

Then you have to send it to your colleagues. They may not have a python environment, use pyinstaller --onefile

Just generate

Knowledge extension

01_X3_xsl_sort.py

1. Delete 3 files in the current path, output_X3_left_align.xls, output_X4_left_align.xls

2. Modify xls in the current path to

3. Get the first one of the data in each row is X3

4. The data are sorted in the third column

5. Data splicing + add a new row

6. Data deletion of redundant columns

7. Data is left aligned and imported into output_X3_left_align.xls

import xlrd
import os

# List of files to be deletedfiles_to_delete = [
    "./",
    "./output_X3_left_align.xls",
    "./output_X4_left_align.xls"
]

# traverse the file list and delete the filefor file_path in files_to_delete:
    if (file_path):
        (file_path)
        print(f"{file_path} Deleted。")
    else:
        print(f"{file_path} Does not exist。")



folder_path = r'./'

# Get all files in the directoryfile_list = (folder_path)

# traverse file listfor file_name in file_list:
    # Check if the file ends in .xls    if file_name.endswith('.xls'):
        # Construct a new file name        new_file_name = (folder_path, '')
        # Rename the file        ((folder_path, file_name), new_file_name)

data = xlrd.open_workbook('')  # Open xls filetable = ()[0]  # Open the first tablenrows =   # Get the number of rows in the table
# Used to store data that meets the criteriafiltered_data = []

# Loop and filter data row by rowfor i in range(nrows):
    if i == 0:  # Skip the first line        continue
    row_data = table.row_values(i)
    if row_data[0] == 'X3':  # If the first element is equal to 'X3'        filtered_data.append(row_data)

# Sort the data in filtered_data by the third elementfiltered_data_sorted = sorted(filtered_data, key=lambda x: x[2], reverse=True)

# Assume that the new data to be inserted is new_rownew_row = ['area','Application Name','The number of machines has been monitored','Number of database servers','Number of application servers','cpuAverage usage rate(%)','cpuMaximum usage rate(%)','内存Average usage rate(%)','内存Maximum usage rate(%)','系统盘Average usage rate(%)','系统盘Maximum usage rate(%)','数据盘Average usage rate(%)','数据盘Maximum usage rate(%)']

# Insert new data before the first location of filtered_data_sortedfiltered_data_sorted = [new_row] + filtered_data_sorted

# # Output data before sorting# print(filtered_data_sorted)

for inner_list in filtered_data_sorted:
    del inner_list[0]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[2]

for inner_list in filtered_data_sorted:
    del inner_list[3]

for inner_list in filtered_data_sorted:
    del inner_list[4]



# Close the filedata.release_resources()

import xlwt

# Create a new Excel fileoutput_workbook = ()

# Add a worksheetoutput_sheet = output_workbook.add_sheet('Sheet1')

# Set left alignment of cellsstyle = ()
alignment = ()
 = .HORZ_LEFT
 = alignment

# Write the filtered data to a new file, align leftfor i, row in enumerate(filtered_data_sorted):
    for j, value in enumerate(row):
        output_sheet.write(i, j, value, style)

# Save the fileoutput_workbook.save('output_X3_left_align.xls')

02_X4_xsl_sort.py

import xlrd

data = xlrd.open_workbook('')  # Open xls filetable = ()[0]  # Open the first tablenrows =   # Get the number of rows in the table
# Used to store data that meets the criteriafiltered_data = []

# Loop and filter data row by rowfor i in range(nrows):
    if i == 0:  # Skip the first line        continue
    row_data = table.row_values(i)
    if row_data[0] == 'X4':  # If the first element is equal to 'X4'        filtered_data.append(row_data)


# Sort the data in filtered_data by the third elementfiltered_data_sorted = sorted(filtered_data, key=lambda x: x[2], reverse=True)

# Assume that the new data to be inserted is new_rownew_row = ['area','Application Name','The number of machines has been monitored','Number of database servers','Number of application servers','cpuAverage usage rate(%)','cpuMaximum usage rate(%)','内存Average usage rate(%)','内存Maximum usage rate(%)','系统盘Average usage rate(%)','系统盘Maximum usage rate(%)','数据盘Average usage rate(%)','数据盘Maximum usage rate(%)']

# Insert new data before the first location of filtered_data_sortedfiltered_data_sorted = [new_row] + filtered_data_sorted

# # Output data before sorting# print(filtered_data_sorted)

for inner_list in filtered_data_sorted:
    del inner_list[0]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[1]

for inner_list in filtered_data_sorted:
    del inner_list[2]

for inner_list in filtered_data_sorted:
    del inner_list[3]

for inner_list in filtered_data_sorted:
    del inner_list[4]

# Close the filedata.release_resources()

import xlwt

# Create a new Excel fileoutput_workbook = ()

# Add a worksheetoutput_sheet = output_workbook.add_sheet('Sheet1')


# Set left alignment of cellsstyle = ()
alignment = ()
 = .HORZ_LEFT
 = alignment

# Write the filtered data to a new file, align leftfor i, row in enumerate(filtered_data_sorted):
    for j, value in enumerate(row):
        output_sheet.write(i, j, value, style)

# Save the fileoutput_workbook.save('output_X4_left_align.xls')

03_X3_max.py

import xlrd

data = xlrd.open_workbook('')  # Open xls filetable = ()[0]  # Open the first tablenrows =   # Get the number of rows in the table
# Used to store data that meets the criteriafiltered_data = []

# Loop and filter data row by rowfor i in range(nrows):
    row_data = table.row_values(i)
    if row_data[0] == 'X3':  # If the first element is equal to 'x3'        filtered_data.append(row_data)

# Sort the data in filtered_data by the third elementfiltered_data_sorted = sorted(filtered_data, key=lambda x: x[2], reverse=True)

# Replace null with 0data_with_zeros = [[0 if cell == 'null' else cell for cell in row] for row in filtered_data_sorted]

# Sort the data in filtered_data by the 7th element cpu maximum usage rate (%)data_with_zeros_sorted = sorted(data_with_zeros, key=lambda x: x[6], reverse=True)


first_sublist = data_with_zeros_sorted[0]

sixth_element_of_first_sublist = first_sublist[6]

print("X3areacpuMaximum usage rate(%)：",sixth_element_of_first_sublist)


data_with_zeros_sorted8 = sorted(data_with_zeros, key=lambda x: x[8], reverse=True)

first_sublist2 = data_with_zeros_sorted8[0]

sixth_element_of_first_sublist2 = first_sublist2[8]

print("X3areacpuMaximum usage rate(%)：",sixth_element_of_first_sublist2)

# Close the filedata.release_resources()

04_X4_max.py

import xlrd

data = xlrd.open_workbook('')  # Open xls filetable = ()[0]  # Open the first tablenrows =   # Get the number of rows in the table
# Used to store data that meets the criteriafiltered_data = []

# Loop and filter data row by rowfor i in range(nrows):
    row_data = table.row_values(i)
    if row_data[0] == 'X4':  # If the first element is equal to 'X4'        filtered_data.append(row_data)

# Sort the data in filtered_data by the third elementfiltered_data_sorted = sorted(filtered_data, key=lambda x: x[2], reverse=True)

# Replace null with 0data_with_zeros = [[0 if cell == 'null' else cell for cell in row] for row in filtered_data_sorted]


data_with_zeros_sorted = sorted(data_with_zeros, key=lambda x: x[6], reverse=True)


first_sublist = data_with_zeros_sorted[0]

sixth_element_of_first_sublist = first_sublist[6]

print("X4areacpuMaximum usage rate(%)：",sixth_element_of_first_sublist)


data_with_zeros_sorted8 = sorted(data_with_zeros, key=lambda x: x[8], reverse=True)

first_sublist2 = data_with_zeros_sorted8[0]

sixth_element_of_first_sublist2 = first_sublist2[8]

print("X4areacpuMaximum usage rate(%)：",sixth_element_of_first_sublist2)

# Close the filedata.release_resources()

import subprocess

(["python", "01_X3_xsl_sort.py"])
(["python", "02_X4_xsl_sort.py"])
(["python", "03_X3_max.py"])
(["python", "04_X4_max.py"])

This is the article about how Python can modify the .xls file header. For more information about Python's content to modify the xls file, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!