python操作excel文件（使用openpyxl）

某日开会中，收到老友的需求：把一个带263个工作表(sheet)的excel文件（工作簿）里的每一个工作表(sheet)都做一些固定的格式化操作。

总的来说就是263个sheets的原始格式都如图一所示，唯一的不同点是有些sheet如图所示只有三行有效数据，有些sheets有303行数据，各个 sheet的有效数据行不一样。

总体需求是：把每个sheet都转换成图二。

图一：

图二：

 

作为程度员第一步肯定是分解一下需求

• 在每个sheet的最前面插入一列，标上序号
• 每个sheet的第一行第一列放“单位名称”
• 每个sheet的第一行第二列开始直到表格最后一列放上单位实际名称，并合并单元格
• 每个sheet的最后一行后增加一行
• 每个sheet的最后增加的这一行的第一列放“单位意见”
• 每个sheet 的最后增加的这一行的第二列开始直到表格最后一列放“单位盖章签字栏”
• 给sheet里的表格有效部分加上边框（边框黑色则可，对边框宽度无明确需求）

作为一位以开会谋生的过气程序员，分析完需求就被拉去开会了。还好同事sky帮忙找了个openpyxl库写下了第一版（按初始客户需求）的程序，等过气程序员回来后按客户更新的需求迭代发布(再一次证明了客户总是不能第一时间知道自己需要什么，所以我们要拥抱变化):-)

基于python 3 的原代码如下：

 

附件 openpyxl网站：https://openpyxl.readthedocs.io/en/stable/

 

import openpyxl
from openpyxl.styles import Border, Side, colors, Alignment

ii=0
#打开当前目录下的文件1.xlsx
wb = openpyxl.load_workbook('1.xlsx')

#遍历当前工作簿(excel文件)里的所有工作表(sheets)
#对每个sheet都做如下一系列操作
for sheet in wb.worksheets:

    ii = ii + 1
    print("正在转换第", ii, "个sheet", sheet.title)
    
    #在最前面插入一列
    sheet.insert_cols(idx=1) 
    
    #把第一行的每个有效数据往前挪一个单元格
    sheet.cell(1, 1).value='单位名称:'
    sheet.cell(1, 2).value=sheet.cell(1, 3).value
    sheet.cell(1, 3).value=''
    #全并第一行的单元格(从第2列到第10列)
    sheet.merge_cells(start_row=1, start_column=2, end_row=1, end_column=10)
    
    #第二行，第一列单元格式写上"序号"，并做左右左对齐，上下中间对其，自动换行文字
    sheet.cell(2, 1).value='序号'
    alignment1 = Alignment(horizontal='left',vertical='center',wrap_text=True)
    sheet.cell(2, 1).alignment=alignment1
    
    #合并当前sheet的最后一行的单元格式(从第2列到第7列)，既表格中的“总计”行
    sheet.merge_cells(start_row=sheet.max_row, start_column=2, end_row=sheet.max_row, end_column=7)
    
    #当前表格的最后一行的下一行的第一列写上“单位意见”
    sheet.cell(sheet.max_row + 1, 1).value='单位意见'
    
    #合并当前sheet的最后一行的单元格式(从第2列到最后一列)，既表格中的上一步新增加的“单位意见”行
    sheet.merge_cells(start_row=sheet.max_row, start_column=2, end_row=sheet.max_row, end_column=sheet.max_column)
    
    #设置最后一行高度为70
    sheet.row_dimensions[sheet.max_row].height = 70
    #设置B列的宽度为20
    sheet.column_dimensions['B'].width = 20
    
    #最后一行，第一列和第二列单元格做左右中间对齐，上下中间对其，自动换行文字
    alignment = Alignment(horizontal='center',vertical='center',wrap_text=True)
    sheet.cell(sheet.max_row, 1).alignment=alignment
    sheet.cell(sheet.max_row, 2).alignment=alignment
    

    #当前表格的最后一行（注意这是上面新增加的一行）的第2列写上“单位盖章签字栏”，并格式化
    sheet.cell(sheet.max_row, 2).value = "   经核对，以上数据无误。\n\n单位负责人签字：            单位名称（盖章）"
    
    #定义边框为黑色瘦边框，上下左右同时定义为黑色瘦边框
    border = Border(left=Side(border_style='thin',color='000000'),
                    right=Side(border_style='thin',color='000000'),
                    top=Side(border_style='thin',color='000000'),
                    bottom=Side(border_style='thin',color='000000'),)
                    
    #遍历每个单元格，加上4周边框                
    for _row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, max_col=10):
        for _cell in _row:
            _cell.border = border
    
    #从第3行开始到倒数第三行(注意这里range里是不包括index_end_row这一行的)加上序号1,2,3,4....n    ，并把单元格格式设置对左右中间对齐，上下中间对其，自动换行文字
    index_end_row = sheet.max_row - 1
    print(index_end_row)
    for i in range(3, index_end_row):
        j = i - 2
        print("j=",j)
        sheet.cell(i,1).value = j 
        sheet.cell(i,1).alignment=alignment
        
#将转换后的excel工作簿存成文件out.xlsx
wb.save('out.xlsx')