How to use openpyxl to set cell styles in python?

introductory

Save multiple excel forms in pandas, sometimes a lot of content in the cell, the preview is not convenient to view, this time we need to modify the width and height of the cell and line feed display

Here's a quick look at the code

import pandas as pd
import datetime
df = pd.read_excel('test.xlsx')
grouped = (['Sector'])
current_time = ()
current_time = current_time.strftime('%m%d')
print(current_time)
name_list = []
for dtype,group in grouped:
    if "/" in dtype:
        dtype = ("/","-")
    group.to_excel(f"./demand/{dtype}{current_time}.xlsx",index = False)
    name_list.append(f"./demand/{dtype}{current_time}.xlsx")

This code first splits the test form into multiple forms and gets the form name for each department.

def format_xlsx(file_path):
    import os
    from openpyxl import load_workbook
    from  import get_column_letter
    from  import PatternFill, Alignment, Side, Border
    from  import Font, Color, colors, Border, Side, Alignment
    wb = load_workbook(file_path)
    # Open the worksheet
    ws = 
    # Set the line style of cell borders
    border_set = Border(left=Side(style='thin', color=),  # Left border
                    right=Side(style='thin', color=),  # The right frame
                    top=Side(style='thin', color=),  # Upper border
                    bottom=Side(style='thin', color=))  # Lower border
    # Set cell content alignment
    for i in ws:
        for j in i:
#             print(j,type(j))
             = Alignment(horizontal='center', vertical='center',wrapText = True)
    		 = border_set
    # Set cell width and height
    width = 50
    height = 80
#     height = width * (2.2862 / 0.3612)
    for i in range(2, ws.max_row+1):
        ws.row_dimensions[i].height = height
    for i in range(1, ws.max_column+1):
        ws.column_dimensions[get_column_letter(i)].width = width
    (file_path)

Then define a format function to format the sheet page.

ws for worksheet, that is, a single sheet page, nested traversal, i for column traversal of the whole column ["A"], ["B"], ["C"] ... and then row traversal, the result is ["A1"] ["B1"] ["C1"] ... etc. All cells cell.

Here set three parameters, horizontal center "horizontal", vertical center "vertical", and a new line display "wrapText ".

Then there's setting the width and height of the cell

Separate loops to get the maximum length of the cell with data, note: here the openpyxl library loop traversal of the first element of the subscript for the 1 instead of 0, and the line we are from the second line to set up, the reason is that the table header will generally set up a separate eye-catching style in order to differentiate between the body of the table.

Here is a screenshot of the operation

initial table

generator table

summarize

The above is a personal experience, I hope it can give you a reference, and I hope you can support me more.