python表格对齐_Python对Excel表格数据重新排版

利用Python操作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()