xlwt以格式生成xls文件分析

哈哈 阅读:372 2021-05-26 14:28:16 评论:0

参考: IT虾米网
IT虾米网

import xlrd, xlwt 
import re 
import os 
 
 
wbs = {} 
SPLIT_NUM = 6 
FN = 'yeah.xls' 
SN = '' 
 
header_style = xlwt.easyxf('font:bold on, height 280;align: horz center; border: top thin, right thin, bottom thin, left thin;') 
header_height = xlwt.easyxf('font:height 500;') 
 
title_style = xlwt.easyxf('font:bold on, height 220;border: top thin, right thin, bottom thin, left thin;') 
title_height = xlwt.easyxf('font:height 500;') 
 
cell_style = xlwt.easyxf('font:height 200;border: top thin, right thin, bottom thin, left thin;') 
cell_height = xlwt.easyxf('font:height 400;') 
 
CHARW = 650        
 
with xlrd.open_workbook(FN) as book: 
    if SN: 
        sht = book.sheet_by_name(SN) 
    else: 
        sht = book.sheets()[0] 
    columns = [e.value for e in sht.row(0)] 
    for r in range(1, sht.nrows): 
        row = [e.value for e in sht.row(r)] 
        name = row[SPLIT_NUM] 
        sfz = row[1] 
        xm = row[2] 
        yh = row[7] 
        jtdm = row[3] 
        if name not in wbs: 
            wb = xlwt.Workbook() 
            ws = wb.add_sheet(name) 
            wbs[name] = {'wb':wb, 'ws':ws, 'r':4} 
            ws.write_merge(0, 0, 0, 4, '领卡签收表清单', header_style) 
            ws.write_merge(1, 1, 0, 1, '银行网点名称', header_style) 
            ws.write_merge(1, 1, 2, 4, yh, header_style) 
            ws.write_merge(2, 2, 0, 1, '集体名称', header_style) 
            ws.write_merge(2, 2, 2, 4, name, header_style) 
            ws.write_merge(3, 3, 0, 1, '集体代码', header_style) 
            ws.write_merge(3, 3, 2, 4, jtdm, header_style) 
            for r in range(4): 
                ws.row(r).set_style(header_height) 
            for col, e  in enumerate([ 
                ('序号', CHARW*3), ('姓名', CHARW*5), 
                ('身份证号码', CHARW*11),('领卡人联系电话', CHARW*8), 
                ('领卡人签名', CHARW*6)]): 
                label = e[0] 
                width = e[1] 
                ws.write(4, col, label, title_style) 
                ws.col(col).width = width 
            ws.row(4).set_style(title_height)       
        else: 
            ws = wbs[name]['ws'] 
        wbs[name]['r'] += 1 
        cr = wbs[name]['r'] 
        ws.write(cr, 0, cr-4, cell_style) 
        ws.write(cr, 1, xm, cell_style) 
        ws.write(cr, 2, sfz, cell_style) 
        ws.write(cr, 3, '', cell_style) 
        ws.write(cr, 4, '', cell_style) 
        ws.row(cr).set_style(cell_height) 
    for k, v in wbs.items(): 
        v['wb'].save(k+'.xls') 
        print('成功:'+k+'.xls') 
 

声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
关注我们

一个IT知识分享的公众号