使用openpyxl处理Excel文件实例

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
import re

# 正则表达式用来处理列名
regx = r'(?<=\()(.*?)(?=[（，\[])'
regx2 = r'(?<=[A-Z、])(.*)'

wb = openpyxl.load_workbook('result.xlsx')
ws = wb.active  # 选中默认工作簿

ft = Font(bold=True, size=10)
al = Alignment(horizontal='center', vertical='center', wrap_text=True)
thin_border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))

# 处理第一行
row1 = ws.iter_rows(min_row=1, max_row=1, min_col=2, max_col=ws.max_column)
for row in row1:
    for cell in row:
        if cell.value is not None:
            cell.value = re.findall(regx, cell.value)[0]
            cell.font = ft
            cell.alignment = al

# 处理第二行
row2 = ws.iter_rows(min_row=2, max_row=2, min_col=2, max_col=ws.max_column)
for row in row2:
    for cell in row:
        if cell.value is not None:
            cell.value = re.findall(regx2, cell.value)[0]
            cell.font = ft
            cell.alignment = al

# 处理汇总行
ws['A3'].value = '全市'
row3 = ws.iter_rows(min_row=3, max_row=3, min_col=2, max_col=ws.max_column)
for row in row3:
    for cell in row:
        cell.value = '=SUM(%s4:%s%d)' % (cell.column, cell.column, ws.max_row)


# 处理全部内容
all_rows = ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column)
for row in all_rows:
    for cell in row:
        cell.border = thin_border
        cell.alignment = al


# 设置首行行高
ws.row_dimensions[1].height = 60
ws.row_dimensions[2].height = 46
for r in range(3, ws.max_row+1):
    ws.row_dimensions[r].height = 30

# 设置列宽
for c in range(2, ws.max_column):
    ws.column_dimensions[openpyxl.utils.get_column_letter(c)].width = 5

# 设置页面
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE    # 页面横向
ws.page_setup.fitToHeight = False
ws.sheet_properties.pageSetUpPr.fitToPage = True    # 自适应页面宽度
ws.page_margins = openpyxl.worksheet.page.PageMargins(top=1.97, header=1.57)  # 设置页边距(单位:英寸)

# 设置页眉标题
ws.oddHeader.center.text = '2018年第三季度安阳市平安建设公众安全感和政法机关执法满意度调查进度汇总表-' + wb.sheetnames[0]
ws.oddHeader.center.size = 22
ws.oddHeader.center.font = "黑体,Bold"

# 页面右下角添加签名内容
ws.cell(row=ws.max_row+4, column=ws.max_column-6).value = '县区监督人员签名:'
ws.cell(row=ws.max_row, column=ws.max_column-6).alignment = Alignment(horizontal='right')
ws.cell(row=ws.max_row, column=ws.max_column-6).font = Font(size=16, bold=True)
ws.cell(row=ws.max_row+2, column=ws.max_column-6).value = '日期:'
ws.cell(row=ws.max_row, column=ws.max_column-6).alignment = Alignment(horizontal='right')
ws.cell(row=ws.max_row, column=ws.max_column-6).font = Font(size=16, bold=True)

wb.save('new_result.xlsx')