1. Add borders to the table
Reference function format_Border (ws, start_row, end_row, start_col, end_col).
package to be introduced
import openpyxl
from openpyxl.styles import Border, Side
from openpyxl.utils.cell import get_column_letter
ws representative :ws=openpyxl.load_workbook(dir_name1)["Sheetname"]
def format_border(ws, start_row, end_row, start_col, end_col):
#internal boundaries Internal border
for row in tuple(ws[start_row:end_row]):
for cell in row[start_col-1:end_col]:
cell.border = set_border('medium', 'medium', 'medium', 'medium')
#left boundary Left border
for cell in [row[start_col-1] for row in ws[start_row:end_row]]:
cell.border = set_border(cell.border.top.style, cell.border.bottom.style, 'medium', cell.border.right.style)
#right boundary Right border
for cell in [row[end_col-1] for row in ws[start_row:end_row]]:
cell.border = set_border(cell.border.top.style, cell.border.bottom.style, cell.border.left.style, 'medium')
#upper boundary Upper border
for cell in ws[start_row][start_col-1:end_col]:
cell.border = set_border('medium', cell.border.bottom.style, cell.border.left.style, cell.border.right.style)
#lower boundary Lower border
for cell in ws[end_row][start_col-1:end_col]:
cell.border = set_border(cell.border.top.style, 'medium', cell.border.left.style, cell.border.right.style)
return ws
#defined boundary style Defined border style
def set_border(t_border, b_border, l_border, r_border, t_color='000000', b_color='000000', l_color='000000', r_color='000000'):
border = Border(top=Side(border_style=t_border, color=t_color),
bottom=Side(border_style=b_border, color=b_color),
left=Side(border_style=l_border, color=l_color),
right=Side(border_style=r_border, color=r_color))
return border
2. Merge Cells.
ws.merge_cells('B3:D3')
3. Set table font and font size.
font = Font(name="song typeface",size=12) # bold whether to bold, italic whether the finger is tilted or not
#get_column_letter () is used to obtain column names such as A、B、C、
for k in range(sheet.max_column):
for l in range(sheet.max_row):
cell = ws[f"{get_column_letter(k + 1)}{str(l + 1)}"]
cell.font = font
4. Set row height.
#set the height of the first row to 25
ws.row_dimensions[1].height = 25
5. Set column width.
#set up A the width of the column is 20. if it needs to be converted to PDF the total width shall not exceed 80
ws.column_dimensions["A"].width=20.0
6. Set all data to be horizontally centered and vertically centered.
for key in list(ws._cells.keys()):
ws._cells[key].alignment = Alignment(wrapText=True,horizontal='center',vertical='center')