python写出XLSX

python与xlsxwriter

1.准备阶段：

已准备好要写出的dataframe，worksheet.write_row()可以按行写出数据。（xlsxwriter的计数是从1开始的，行和列都是从1开始）

2.细嚼慢咽

创建Excel文件

workbook= xlsxwriter.Workbook()

创建工作表sheet

worksheet= workbook.add_worksheet()

• 查找字符串中的中文（写出到excel表中，中文比英文长一个单位）
  re.findall(r’([\u2E80-\u9FFF]+)’,str(x))

设置工作表

1.设置列宽

worksheet.set_column(n,n,colwidth)

2.定义格式

先workbook.add_format()占位，然后把属性赋值它，需要用的时候，调用即可。

    title_formatter = workbook.add_format()
    title_formatter.set_border(1)
    title_formatter.set_bg_color('#66CCFF')
    title_formatter.set_align('center')
    title_formatter.set_bold()   

3.按行写出代码

worksheet.write_row(“A1”,数据list,formatter)

3.完整代码

import xlsxwriter
import re
def WriteCreditOn(credit_df,dataset,comp):
    """
    输入：
    credit_df:格式dataframe，贷中最终的审批结果
    dataset：由dataframe表组成的list，单个贷中规则最终命中及权重
    comp:客户名称
    输出：贷中规则宽表
    """
    ####################### 首先做第一个sheet: 贷中最终审批结果_开始 ##########################    
    credit_df = credit_df.fillna("")
    # 1. 创建一个Excel文件
    reportname = os.path.join(comp, comp +"_"+str(time.strftime("%Y%m%d")) + '_贷中规则命中情况.xlsx')
    workbook= xlsxwriter.Workbook(reportname,options={'nan_inf_to_errors': True})#创建一个excel文件
    
    # 2. 创建一个工作表sheet对象
    worksheet= workbook.add_worksheet(u'贷中最终审批结果')#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1    
    
    # 3. 设置列宽
    # 中文字符比英文多一个长度
    # 设置每列的列宽    
    for n in range(credit_df.shape[1]):
        col = credit_df.columns.tolist()[n]
        colwidth = credit_df[col].apply(lambda x:len("".join(re.findall(r'([\u2E80-\u9FFF]+)',str(x))))+len(str(x))+2).max()
        worksheet.set_column(n,n,colwidth)
        
    # 4.定义标题栏格式对象：边框加粗1像素，背景色为蓝色，单元格内容居中、加粗
    title_formatter = workbook.add_format()
    title_formatter.set_border(1)
    title_formatter.set_bg_color('#66CCFF')
    title_formatter.set_align('center')
    title_formatter.set_bold()   
    
    # 4.定义内容格式
    formatter = workbook.add_format()
    formatter.set_border(1)
    formatter.set_align("center")
    
    # 写入数据
    worksheet.write_row('A1',credit_df.columns.tolist(),title_formatter)
    worksheet.write_row('A2',credit_df.iloc[0,],title_formatter)    
    
    for j in range(1,credit_df.shape[0]):
        worksheet.write_row('A{}'.format(j+2),credit_df.iloc[j,],formatter)
        
    ####################### 首先做第一个sheet: 贷中最终审批结果_结束 ##########################
        
    ####################### 再做后续sheet: 贷中明细宽表_开始 ##########################       
    
    for i in range(len(dataset)):     
        dataframe = dataset[i].fillna("")
        # 2. 创建一个工作表sheet对象
        sheetname = "贷中预警全量规则-"+dataframe["Rule_weight"][0][:-4]
        worksheet= workbook.add_worksheet(sheetname)#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
                
        # 3. 设置列宽
        # 中文字符比英文多一个长度
        # 设置每列的列宽    
        for n in range(dataframe.shape[1]):
            col = dataframe.columns.tolist()[n]
            colwidth = dataframe[col].apply(lambda x:len("".join(re.findall(r'([\u2E80-\u9FFF]+)',str(x))))+len(str(x))+2).max()
            worksheet.set_column(n,n,colwidth)        
        
        # 写入数据
        worksheet.write_row('A1',dataframe.columns.tolist(),title_formatter)
        worksheet.write_row('A2',dataframe.iloc[0,],title_formatter)    
        
        for j in range(1,dataframe.shape[0]):
            worksheet.write_row('A{}'.format(j+2),dataframe.iloc[j,],formatter)    
    ####################### 再做后续sheet: 贷中明细宽表_结束 ########################## 

python与openpyxl

1.准备阶段：

已有一个excel，对该excel进行处理

2.细嚼慢咽

导入已有的excel:load_workbook()

pro_file = openpyxl.load_workbook() #导入

查看所有sheetname：get_sheet_names()

sheetnames = pro_file.get_sheet_names() #查看所有sheetname

删除sheet表：del

del pro_file[sheet]

获取某sheet表:get_sheet_by_name

sheet1=pro_file.get_sheet_by_name(“产品列表”)

创建sheet表：create_sheet()

pro_file.create_sheet(“del”,0) #开头创建第一张sheet：del

设置列宽

sheet.column_dimensions[‘A’].width = 14.0

对齐方式、自动换行，加边框，添加超链接

（见完整代码）

3.完整代码

import openpyxl
from openpyxl import Workbook
# 导入字体、边框、颜色以及对齐方式相关库
from openpyxl.styles import Font, Border, Side, PatternFill, Color, colors, Alignment
import time
import os
import pandas as pd

def dict_output(prd_document_file,hit_info,comp):
    """
    文档说明：生成数据字典
    输入：
    prd_document_file：产品文档xlsx的文件名，第一页是“产品列表”sheet，后面的sheet是对应产品的英文名+版本号，例如“ApplyLoanStrV2.0”
    hit_info：测试的产品、版本、对应的文件
    comp:公司名
    输出：生成测试的数据字典
    """
    pro_file = openpyxl.load_workbook(os.path.join("辅助文档", prd_document_file))
    keepsheet,index_row,noting = keepsheets(prd_document_file,hit_info)    
    sheetnames = pro_file.get_sheet_names() #查看所有sheetname
    
    ################### 删除没测试产品字典 ################### 
    lst = []
    for sheet in sheetnames:
        if sheet.lower() not in keepsheet:
            lst.append(sheet)
            del pro_file[sheet] #删除不需要的sheet表

    ################### 处理“产品列表”sheet页 ###################
    ## 原始产品列表
    sheet1=pro_file.get_sheet_by_name("产品列表")    
    max_row=sheet1.max_row#最大行数
    max_column=sheet1.max_column#最大列数
            
    ## 生成空sheet页：del，将原始产品列表进行处理到del中，最后删除原始“产品列表”，将del更名为“产品列表”
    pro_file.create_sheet("del",0)
    sheet = pro_file.get_sheet_by_name("del")   
    
    ## 设置del页格式：列宽，首行加粗
    ### 设置列宽          
    sheet.column_dimensions['A'].width = 14.0
    sheet.column_dimensions['B'].width = 32.0
    sheet.column_dimensions['C'].width = 23.0
    sheet.column_dimensions['D'].width = 6.0
    sheet.column_dimensions['E'].width = 10.0
    sheet.column_dimensions['F'].width = 12.0
    sheet.column_dimensions['G'].width = 65.0
    sheet.column_dimensions['H'].width = 100.0    
    
    ### 水平居中
    align = Alignment(horizontal='center', vertical='center',wrap_text=True)
    
    
    ### 加边框，首先设置边框四个方向的线条种类
    left, right, top, bottom = [Side(style='thin', color='000000')] * 4
    # 再将各方向线条作为参数传入Border方法
    borders = Border(left=left, right=right, top=top, bottom=bottom)
    
    ### 首行加粗
    sheet['A1'].font = Font(name='Times New Roman', bold=True)
    sheet['B1'].font = Font(name='Times New Roman', bold=True)
    sheet['C1'].font = Font(name='Times New Roman', bold=True)
    sheet['D1'].font = Font(name='Times New Roman', bold=True)
    sheet['E1'].font = Font(name='Times New Roman', bold=True)
    sheet['F1'].font = Font(name='Times New Roman', bold=True)
    sheet['G1'].font = Font(name='Times New Roman', bold=True)
    sheet['H1'].font = Font(name='Times New Roman', bold=True)
    sheet['I1'].font = Font(name='Times New Roman', bold=True)       

    ### 给sheet添加超链接 
    for l,m in enumerate(index_row):
        for n in range(97,97+max_column):
            # l是sheet1
            n=chr(n)#ASCII字符
            i='%s%d'%(n,m)#单元格编号
            j='%s%d'%(n,l+1)
            cell1=sheet1[i].value#获取sheet1单元格数据
            if n=='c' and l>0:
                link = sheet1['%s%d'%("d",m)].value + sheet1['%s%d'%("e",m)].value
                sheet[j].value='=HYPERLINK("#{}!A1","{}")'.format(link,cell1) #赋值到sheet单元格
                sheet[j].font = Font(underline='single', color='0563C1') #添加超链接格式
                sheet[j].alignment = align
                sheet[j].border = borders                
            else:
                sheet[j].value=cell1 #赋值到sheet单元格
                sheet[j].alignment = align
                sheet[j].border = borders  
    ### 给sheet说明列设置对齐方式
    for describ_h in range(max_row): 
        sheet["I{}".format(describ_h+2)].alignment = Alignment(horizontal='left', vertical='center',wrap_text=True) 
                          
    ### 如果测试了“申请信息评估”、“稳定性指数”则增加说明           
    if len(noting)>0:
        sheet['%s%d'%("C",l+3)].value='=HYPERLINK("#{}!A1","{}")'.format("字段说明表",noting)
        sheet['%s%d'%("C",l+3)].font = Font(underline='single', color='0563C1')  #添加超链接格式            
            
    sheet.delete_cols(1)        
    del pro_file["产品列表"]
    sheet.title = "产品列表"
    
    ######################### 保存处理好的文件生成本地xlsx #################################
    save_path = os.path.join(comp,'详细匹配数据及字典', comp +"_"+str(time.strftime("%Y%m%d")) + '_测试产品数据字典.xlsx')
    pro_file.save(save_path)