OPENPYXL

https://www.jianshu.com/p/b5b36e3bb3ff

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Alignment,Border,Side,Font

    wb = Workbook()#建立excel文件
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))#创建边框对象
    for file_name in names:#开始循环处理文件夹下所有excel文件
        print (file_name)
        ws = wb.create_sheet(file_name.split('.')[0])#创建工作簿名
        ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE#页面方向
        ws.page_setup.paperSize = ws.PAPERSIZE_A4#纸张大小
        ws.page_margins.left = 0.25
        ws.page_margins.rigt = 0
        ws.page_margins.top = 0.2
        ws.page_margins.bottom = 0.2
        ws.page_margins.header = 0
        ws.page_margins.footer = 0#设置页边距
        ws.print_title_rows = '1:2'#设置打印标题为2行，每页开始都打印sheet的前两行

        #print (dffff.tail())
        rows = dataframe_to_rows(duo(file_name))#pandas的dataframe转openpyxl的格式
        for row in rows:#不知道为啥有空行。openpyxl的序号从1开始。delete_rows()
            if len(row) >2:
                ws.append(row)
        ws.column_dimensions['A'].width = 3#设置列宽
        ws.column_dimensions['B'].width = 9
        for i in range(3,ws.max_column):#最大列数目，是整数类型
            ws.column_dimensions[get_column_letter(i)].width = 5
        ws.column_dimensions[get_column_letter(ws.max_column)].width = 6
        for i in list(ws.rows)[0]:#生成器转list才能迭代，第一行
            i.alignment = Alignment(wrap_text=True)#设置文本自动换行
        for row in ws.rows:
            for cell in row:
                cell.border =thin_border#设置边框

        ws.insert_rows(1)#插入标题行
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)#合并标题行
        p_title = file_name.split('.')[0]
        ws['A1'] = p_title
        ws['A1'].alignment = Alignment(horizontal='center')#设置标题居中
        ws['A1'].font = Font(size=20)#设置字号大小

    std=wb.get_sheet_by_name('Sheet')
    wb.remove_sheet(std)#删除默认sheet，保存文件
    wb.save('文件名.xlsx')#excel写入所有sheet