Python操作Excel-对Excel数据重新排版

@Python对Excel表格重新排版-操作Excel

import xlwt

class Cnumber:
    cdict = {}
    gdict = {}
    xdict = {}

    def __init__(self):
        self.cdict = {1: u'', 2: u'拾', 3: u'佰', 4: u'仟'}
        self.xdict = {1: u'元', 2: u'万', 3: u'亿', 4: u'兆'}  # 数字标识符
        self.gdict = {0: u'零', 1: u'壹', 2: u'贰', 3: u'叁', 4: u'肆', 5: u'伍', 6: u'陆', 7: u'柒', 8: u'捌', 9: u'玖'}

    @staticmethod
    def csplit(cdata):  # 拆分函数，将整数字符串拆分成[亿，万，仟]的list
        g = len(cdata) % 4
        csdata = []
        lx = len(cdata) - 1
        if g > 0:
            csdata.append(cdata[0:g])
        k = g
        while k <= lx:
            csdata.append(cdata[k:k + 4])
            k += 4
        return csdata

    def cschange(self, cki):  # 对[亿，万，仟]的list中每个字符串分组进行大写化再合并
        lenki = len(cki)

        lk = lenki
        chk = u''
        for i in range(lenki):
            if int(cki[i]) == 0:
                if i < lenki - 1:
                    if int(cki[i + 1]) != 0:
                        chk = chk + self.gdict[int(cki[i])]
            else:
                chk = chk + self.gdict[int(cki[i])] + self.cdict[lk]
            lk -= 1
        return chk

    def cwchange(self, data):
        cdata = str(data).split('.')

        cki = cdata[0]
        ckj = cdata[1]

        chk = u''
        cski = self.csplit(cki)  # 分解字符数组[亿，万，仟]三组List:['0000','0000','0000']
        ikl = len(cski)  # 获取拆分后的List长度
        # 大写合并
        for i in range(ikl):
            if self.cschange(cski[i]) == '':  # 有可能一个字符串全是0的情况
                chk = chk + self.cschange(cski[i])  # 此时不需要将数字标识符引入
            else:
                chk = chk + self.cschange(cski[i]) + self.xdict[ikl - i]  # 合并：前字符串大写+当前字符串大写+标识符
        # 处理小数部分
        lenkj = len(ckj)
        if lenkj == 1:  # 若小数只有1位
            if int(ckj[0]) == 0:
                chk = chk + u'整'
            else:
                chk = chk + self.gdict[int(ckj[0])] + u'角整'
        else:  # 若小数有两位的四种情况
            if int(ckj[0]) == 0 and int(ckj[1]) != 0:
                chk = chk + u'零' + self.gdict[int(ckj[1])] + u'分'
            elif int(ckj[0]) == 0 and int(ckj[1]) == 0:
                chk = chk + u'整'
            elif int(ckj[0]) != 0 and int(ckj[1]) != 0:
                chk = chk + self.gdict[int(ckj[0])] + u'角' + self.gdict[int(ckj[1])] + u'分'
            else:
                chk = chk + self.gdict[int(ckj[0])] + u'角整'
        return chk
class write_excel:
    def __init__(self, file_name):
        self.file_name = file_name
        self.workbook = xlwt.Workbook()
        self.worksheet = self.workbook.add_sheet("打印")
    def set_width_height(self, col ):
        row = 0
        #设置行高
        tall_style1 = xlwt.easyxf('font:height 618')
        self.worksheet.row(row).set_style(tall_style1)
        self.worksheet.row(row+17).set_style(tall_style1)

        tall_style2 = xlwt.easyxf('font:height 404')
        for i in range(row+1, row+10):
            self.worksheet.row(i).set_style(tall_style2)
        for i in range(row+18, row+27):
            self.worksheet.row(i).set_style(tall_style2)
        #设置列宽
        width_list = [4403, 1322, 3726, 1457, 5009, 9113]
        for i in range(6):
            self.worksheet.col(col+i).width = width_list[i]
    def set_style(self, borders = xlwt.Borders(), font_bold = False,font_height = 20*12, horz = 0x01, vert = 0x01):
        #设置字体
        font = xlwt.Font()
        font.bold = font_bold
        font.height = font_height
        #设置单元格对齐方式
        alignment = xlwt.Alignment()
        alignment.horz = horz
        alignment.vert = vert
        '''
        borders = xlwt.Borders()
        borders.top = 1
        borders.bottom = 1
        borders.left = 1
        borders.right = 1
        '''
        style = xlwt.XFStyle()
        style.font = font
        style.alignment = alignment
        style.borders = borders
        return style
    def get_upper_Roman(self, number):
        pt = Cnumber()
        number_str = str(number)
        if number_str == '0':
            result = '零'
        elif '.' in number_str:
            result = pt.cwchange(number_str)
        else:
            number_str += '.00'
            result = pt.cwchange(number_str)
        return result
    def add_data(self, data, row = 0,):
        cols = data[0]
        name = data[1]
        mgroup = data[2]
        date_start = data[3]
        date_end = data[4]
        sale = data[5]
        cost = data[6]
        rent = data[7]
        good = data[8]
        remain = data[9]
        print(remain)
        self.set_width_height(cols)

        style1 = self.set_style(font_bold=True,font_height= 20*18, horz=0x02, vert=0x01)
        self.worksheet.write_merge(row, row, cols, cols+5, "客户返款结算单", style1)
        self.worksheet.write_merge(row+17, row+17, cols,cols+5, "客户返款结算单", style1)

        #左上角的格式
        borders = xlwt.Borders()
        borders.top = 5
        borders.left = 5
        style2 = self.set_style(borders = borders)
        self.worksheet.write(row+1, cols, "商户姓名：", style2)
        self.worksheet.write(row+18, cols, '商户姓名：', style2)
        #上边格式
        borders = xlwt.Borders()
        borders.top = 5
        borders.left = 1
        borders.right = 1
        style3 = self.set_style(borders = borders)
        self.worksheet.write_merge(row+1, row+1, cols+1, cols+3, name, style3)
        self.worksheet.write_merge(row+18, row+18, cols+1, cols+3, name, style3)
        self.worksheet.write(row+1, cols+4, "区域号", style3)
        self.worksheet.write(row+18, cols+4, "区域号", style3)
        #右上角格式
        borders = xlwt.Borders()
        borders.top = 5
        borders.right = 5
        borders.left = 1
        style4 = self.set_style(borders = borders)
        self.worksheet.write(row+1, cols+5, mgroup, style4)
        self.worksheet.write(row+18, cols+5, mgroup, style4)
        #左边格式
        borders = xlwt.Borders()
        borders.left = 5
        borders.top = 1
        borders.bottom = 1
        borders.right = 1
        style5 = self.set_style(borders = borders)
        content = ['结算日期', '销售金额', '扣费金额', '扣租金额', '扣除货款']
        for i in range(len(content)):
            self.worksheet.write(row+2+i, cols, content[i], style5)
            self.worksheet.write(row+19+i, cols, content[i], style5)
        #内部格式
        borders = xlwt.Borders()
        borders.top = 1
        borders.bottom = 1
        borders.left = 1
        borders.right = 1
        style6 = self.set_style(borders = borders)
        self.worksheet.write_merge(row+2, row+2, cols+1, cols+3, date_start, style6)
        self.worksheet.write_merge(row+19, row+19, cols+1, cols+3, date_start, style6)
        self.worksheet.write(row+2, cols+4, "至", style6)
        self.worksheet.write(row+19, cols+4, "至", style6)
        content2 = [sale, cost, rent, good]
        for i in range(4):
            self.worksheet.write(row+3+i, cols+1, "小写", style6)
            self.worksheet.write(row+3+i, cols+2, str(content2[i]), style6)
            self.worksheet.write(row+3+i, cols+3, "元", style6)
            self.worksheet.write(row+3+i, cols+4, "大写", style6)
            self.worksheet.write(row + 20 + i, cols + 1, "小写", style6)
            self.worksheet.write(row + 20 + i, cols + 2, str(content2[i]), style6)
            self.worksheet.write(row + 20 + i, cols + 3, "元", style6)
            self.worksheet.write(row + 20 + i, cols + 4, "大写", style6)
        #右边格式
        borders = xlwt.Borders()
        borders.top = 1
        borders.bottom = 1
        borders.left = 1
        borders.right = 5
        style7 = self.set_style(borders = borders)
        content3 = [date_end, sale, cost, rent, good]
        for i in range(len(content3)):
            if i == 0:
                self.worksheet.write(row+2+i, cols+5, content3[i], style7)
                self.worksheet.write(row+19+i, cols+5, content3[i], style7)
            else:
                self.worksheet.write(row+2+i, cols+5, self.get_upper_Roman(content3[i]), style7)
                self.worksheet.write(row+19+i, cols+5, self.get_upper_Roman(content3[i]),style7)
        #左下角
        borders = xlwt.Borders()
        borders.top = 1
        borders.left = 5
        borders.right = 1
        borders.bottom = 5
        style8 = self.set_style(borders = borders)
        self.worksheet.write(row+7, cols, "剩余返款", style8)
        self.worksheet.write(row+24, cols, "剩余返款", style8)
        #下边
        borders = xlwt.Borders()
        borders.top = 1
        borders.bottom = 5
        borders.left = 1
        borders.right = 1
        style9 = self.set_style(borders = borders)
        content4 = ['小写', remain, '元', '大写']
        for i in range(len(content4)):
            self.worksheet.write(row+7, cols+1+i, str(content4[i]), style9)
            self.worksheet.write(row+24, cols+1+i, str(content4[i]), style9)
        #右下角
        borders = xlwt.Borders()
        borders.top = 1
        borders.bottom = 5
        borders.left = 1
        borders.right = 5
        style10 = self.set_style(borders = borders)
        self.worksheet.write(row+7, cols+5, self.get_upper_Roman(remain), style10)
        self.worksheet.write(row+24, cols+5, self.get_upper_Roman(remain), style10)
        #方框外部
        style11 = self.set_style()
        self.worksheet.write(row+9, cols, "核算人员签字", style11)
        self.worksheet.write(row+26,cols, "核算人员签字", style11)
        self.worksheet.write(row+9, cols+4, "经理签字", style11)
        self.worksheet.write(row+26, cols+4, "经理签字", style11)
    def save(self):
        self.workbook.save(self.file_name)

if __name__ == "__main__":
    data = [
        [0, "XXX", '2XXX', '2020.03.26', '2020.04.25', 20000, 200, 100, 100, 19600],
        [6, 'XXX', '2XXX', '2020.03.26', '2020.04.25', 20000, 200, 100, 100,19600],
        [12, 'XXX', '2XX', '2020.03.26', '2020.04.25', 50000, 200, 100, 100, 49600],
        [18, 'XXX', 'XXX', '2020.03.26', '2020.04.25', 890000, 0, 100, 100, 49600],
    ]
    file_name = "changshi.xls"
    p = write_excel(file_name)
    for n in range(len(data)):
        print(data[n])
        p.add_data(data[n])
    p.save()