开启新生活
#!/usr/bin/python # -*- coding: utf-8 -*- # @Time : 2020/6/2 18:36 # @Author : cuijianzhe # @File : biaoge.py # @Software: PyCharm import xlrd import xlwt import time import os name = input('请输入表格名称:') path = name + '.xls' date = time.strftime('%Y-%m-%d',time.localtime()) workbook = xlrd.open_workbook(path) #列出所有表头 Data_sheet = workbook.sheets()[1] #表头参数 # print(Data_sheet.name) rowNum = Data_sheet.nrows #行数 ---419 # print(rowNum) colNum = Data_sheet.ncols #列数 --9 # print(colNum) ''' 提取单元格所有内容 ''' row_sum = Data_sheet.ncols #有效列数 # print(Data_sheet.cell_value(0,row_sum-1)) #列名称 # for r in range(0,row_sum): # print(Data_sheet.cell_value(0,r)) def get_projectName(): sites_list = [] i = 1 # 从1开始跳过表头数据 try: for s in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, s)) == '项目名称': while i <= rowNum: if Data_sheet.cell_value(i, s) != "": sites_list.append(Data_sheet.cell_value(i, s)) i += 1 except: pass # print(sites_list) return sites_list def get_PO(): #获取订单号 PO_list = [] i = 1 try: for PO in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, PO)) == '订单号': while i <= rowNum-1: #rowNum 列数 if Data_sheet.cell_value(i, PO) != "": PO_list.append(Data_sheet.cell_value(i, PO)) i += 1 except Exception as error: print('get错误','error:{}'.format(error)) # print(PO_list) return (PO_list) def get_place(): place_list = [] i = 1 try: for P in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, P)) == '安装地点': while i <= rowNum-1: #rowNum 列数 if Data_sheet.cell_value(i, P) != "": place_list.append(Data_sheet.cell_value(i, P)) i += 1 except Exception as error: print('get错误','error:{}'.format(error)) return place_list def get_goods(): G_list = [] i = 1 try: for G in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, G)) == '商品名称': while i <= rowNum - 1: # rowNum 列数 if Data_sheet.cell_value(i, G) != "": G_list.append(Data_sheet.cell_value(i, G)) i += 1 except Exception as error: print('get错误', 'error:{}'.format(error)) # print(G_list) return G_list def get_buyid(): #获取买房协议号 id_list = [] i = 1 try: for G in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, G)) == '买方协议号': while i <= rowNum - 1: # rowNum 列数 if Data_sheet.cell_value(i, G) != "": id_list.append(Data_sheet.cell_value(i, G)) i += 1 except Exception as error: print('get错误', 'error:{}'.format(error)) return id_list def get_huaweiid(): #华为合同号 hw_list = [] i = 1 try: for hw in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, hw)) == '华为合同号': while i <= rowNum - 1: # rowNum 列数 if Data_sheet.cell_value(i, hw) != "": hw_list.append(Data_sheet.cell_value(i, hw)) i += 1 except Exception as error: print('get错误', 'error:{}'.format(error)) return hw_list def get_proid(): #项目编号 pr_list = [] i = 1 try: for pr in range(Data_sheet.ncols): if (Data_sheet.cell_value(0, pr)) == '项目编号': while i <= rowNum - 1: # rowNum 列数 if Data_sheet.cell_value(i, pr) != "": pr_list.append(Data_sheet.cell_value(i, pr)) i += 1 except Exception as error: print('get错误', 'error:{}'.format(error)) return pr_list def create_xls(project,place,goods,PO,hwid,buyid,prid): #project,place,PO,hwid,buyid,prid,buyid #创建表格(行，列) workbook = xlwt.Workbook(encoding = 'utf-8') #设置工作表 worksheet = workbook.add_sheet('初验证书') # 为样式创建字体 font = xlwt.Font() # 字体类型 # font.name = 'name Times New Roman' # 字体颜色 font.colour_index = 0 # 字体大小，11为字号，20为衡量单位 font.height = 20 * 22 # 字体加粗 font.bold = True # 下划线 # font.underline = True # 斜体字 # font.italic = True ''' 设置字体颜色方案：红色字体 ''' font2 = xlwt.Font() font2.colour_index = 2 font2.height = 20 * 9 # 设置单元格对齐方式 alignment = xlwt.Alignment() # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐) alignment.horz = 0x02 # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐) alignment.vert = 0x01 # 设置自动换行 alignment.wrap = 1 #设置固定内容签字盖章部分 alignment1 = xlwt.Alignment() alignment1.horz = 0x01 alignment1.vert = 0x01 # 设置边框 (右边框是蓝色) borders = xlwt.Borders() # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7 # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13 # 颜色4 时所需要的右边框颜色 borders.left = 1 borders.right = 2 borders.top = 1 borders.bottom = 1 borders.left_colour = 0 borders.right_colour = 4 borders.top_colour = 0 borders.bottom_colour = 0 #设置边框，全为黑色 borders1 = xlwt.Borders() borders1.left = 1 borders1.right = 2 borders1.top = 2 borders1.bottom = 1 borders1.left_colour = 0 borders1.right_colour = 0 borders1.top_colour = 0 borders1.bottom_colour = 0 ''' 设置边框，边框为白色 ''' borders2 = xlwt.Borders() borders2.left = 1 borders2.right = 2 borders2.top = 1 borders2.bottom = 1 borders2.left_colour = 9 borders2.right_colour = 4 borders2.top_colour = 9 borders2.bottom_colour = 9 ''' 设置边框：右边为蓝色4号，其他部位为白色 ''' borders3 = xlwt.Borders() borders3.left = 1 borders3.right = 2 borders3.top = 1 borders3.bottom = 1 borders3.left_colour = 9 borders3.right_colour = 9 borders3.top_colour = 9 borders3.bottom_colour = 9 ''' 设置边框：右边下边为蓝色，上边框为黑色 ''' borders4 = xlwt.Borders() borders4.left = 1 borders4.right = 2 borders4.top = 1 borders4.bottom = 2 borders4.left_colour = 0 borders4.right_colour = 4 borders4.top_colour = 0 borders4.bottom_colour = 4 # 初始化样式 style = xlwt.XFStyle() style.alignment = alignment #居中 style.borders = borders style1 = xlwt.XFStyle() #此类型是加粗字体、居中、右边框蓝色 style1.font = font #加粗 style1.borders = borders style1.alignment = alignment style2 = xlwt.XFStyle() #设置边框周围全黑 style2.borders = borders1 style2.alignment = alignment style3 = xlwt.XFStyle() # 右边有蓝色线条，其他部位为白色 左对齐 style3.alignment = alignment1 style3.borders = borders2 style4 = xlwt.XFStyle() # 全为白色 左对齐 style4.alignment = alignment1 style4.borders = borders3 style5 = xlwt.XFStyle() # 居中无边框 style5.alignment = alignment style5.borders = borders3 style6 = xlwt.XFStyle() # 居中 右边边框 style6.alignment = alignment style6.borders = borders2 style7 = xlwt.XFStyle() # 红色字体，左对齐 style7.alignment = alignment1 style7.borders = borders4 style7.font = font2 # 设置文字模式 # borders.num_format_str = '#,##0.00' #设置行表内容 worksheet.write(1,0,'工程名称',style2) worksheet.write(1,1, '{}'.format(project),style2) worksheet.write(2,0, '安装地点', style2) worksheet.write(2,1, '{}'.format(place),style2) worksheet.write(3, 0, '建设单位', style2) worksheet.write(4, 0, '设备验收内容', style2) # worksheet.write(5, 0, '验收意见', style2) #设置列表内容 worksheet.write(1,2, '到货日期', style2) worksheet.write(1,3, ' ', style) worksheet.write(2,2, '初验日期', style2) worksheet.write(2,3, ' ', style) #单元格合并 worksheet.write_merge(0,0,0,3,'初验证书' ,style1) #(0,0行，0,3列) worksheet.write_merge(3,3,1,3,'中国联合网络通信有限公司北京市分公司',style ) worksheet.write_merge(4,4,1,3,'{}'.format(goods),style) worksheet.write_merge(5, 6, 0, 0, '验收意见', style2) worksheet.write_merge(5, 5, 1, 3, ' 通过验收，双方同意签署初验证书。', style) # 买方订单号：get_PO 卖方合同：get_hwid 买方项目编号：get_proid 买方合同号：get_buyid worksheet.write_merge(6, 6, 1, 3,'卖方合同号: {}\n买方合同号：{}\n买方项目编号：{}\n买方订单号:{}\n客户名称：中国联合网络通信有限公司北京市分公司 '.format(hwid,buyid,prid,PO), style) worksheet.write_merge(7, 7, 0, 3, ' ', style3) # worksheet.write_merge(8, 8, 0, 1, '卖方（供方）（盖章）\t\t\t\t\t\t 建设单位（盖章）\t\t\t\t\n（签字）\t\t\t\t （签字）\t\t\t\t \n \t\t\t年\t\t\t月\t\t\t日 \t\t\t年\t\t\t月\t\t\t日 ',style2) worksheet.write_merge(8, 8, 0, 1, ' 卖方（供方）（盖章）', style4) worksheet.write_merge(8, 8, 2, 3, '建设单位（盖章）', style3) worksheet.write_merge(9, 9, 0, 1, '（签字）\t\t\t\t', style4) worksheet.write_merge(9, 9, 2, 3, '（签字）\t\t\t\t', style3) worksheet.write_merge(10, 10, 0, 1, ' \t\t年 月 日', style5) worksheet.write_merge(10, 10, 2, 3, ' \t\t年 月 日', style6) worksheet.write_merge(11, 11, 0, 3, ' 注：本初验证书适用于设备采购合同中的上线验收证书、初验证书以及验收合格证书。', style7) ''' 设置列宽: 默认字体0的1/256为衡量单位。其创建时使用的默认宽度为2960，即11个字符0的宽度。 width = 256 * 20 # 256为衡量单位，20表示20个字符宽度 ''' worksheet.col(0).width = 256 * 14 # Set the column width worksheet.col(1).width = 256 * 26 # Set the column width worksheet.col(2).width = 256 * 16 # Set the column width worksheet.col(3).width = 256 * 24 # Set the column width # worksheet.col(0).width = 8888 # Set the column width ''' #设置行单元格高度 height的值为表格中实际值得20倍，例如 ： worksheet.row(0).height = 1695 对应表格中的行高为 84.75 ''' worksheet.row(0).height_mismatch = True worksheet.row(0).height = 1695 worksheet.row(1).height_mismatch = True worksheet.row(1).height = 1320 worksheet.row(2).height_mismatch = True worksheet.row(2).height = 585 worksheet.row(3).height_mismatch = True worksheet.row(3).height = 585 worksheet.row(4).height_mismatch = True worksheet.row(4).height = 4610 worksheet.row(5).height_mismatch = True worksheet.row(5).height = 3195 worksheet.row(6).height_mismatch = True worksheet.row(6).height = 1320 worksheet.row(7).height_mismatch = True worksheet.row(7).height = 690 worksheet.row(8).height_mismatch = True worksheet.row(8).height = 800 worksheet.row(9).height_mismatch = True worksheet.row(9).height = 800 worksheet.row(10).height_mismatch = True worksheet.row(10).height = 800 worksheet.row(11).height_mismatch = True worksheet.row(11).height = 255 #保存表格 workbook.save('./' + '表格目录/' + '{}.xls'.format(PO)) #hwid,buyid,prid,PO def main(): for i in range(0,rowNum-1): project = get_projectName()[i] place = get_place()[i] goods = get_goods()[i] site_name = get_PO()[i] hwid = get_huaweiid()[i] buyid = get_buyid()[i] prid = get_proid()[i] create_xls(project,place,goods,site_name,hwid,buyid,prid) if __name__ == '__main__': if os.path.exists('表格目录'): main() else: os.mkdir('表格目录') main()
# coding:utf-8 import xlwt import time i = 0 book = xlwt.Workbook(encoding='utf-8') sheet = book.add_sheet('sheet1', cell_overwrite_ok=True) # 如果出现报错：Exception: Attempt to overwrite cell: sheetname='sheet1' rowx=0 colx=0 # 需要加上：cell_overwrite_ok=True) # 这是因为重复操作一个单元格导致的 while i < 64: # 为样式创建字体 font = xlwt.Font() # 字体类型 font.name = 'name Times New Roman' # 字体颜色 font.colour_index = i # 字体大小，11为字号，20为衡量单位 font.height = 20 * 11 # 字体加粗 font.bold = False # 下划线 font.underline = True # 斜体字 font.italic = True # 设置单元格对齐方式 alignment = xlwt.Alignment() # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐) alignment.horz = 0x02 # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐) alignment.vert = 0x01 # 设置自动换行 alignment.wrap = 1 # 设置边框 borders = xlwt.Borders() # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7 # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13 borders.left = 1 borders.right = 2 borders.top = 3 borders.bottom = 4 borders.left_colour = i borders.right_colour = i borders.top_colour = i borders.bottom_colour = i # 设置列宽，一个中文等于两个英文等于两个字符，11为字符数，256为衡量单位 sheet.col(1).width = 11 * 256 # 设置背景颜色 pattern = xlwt.Pattern() # 设置背景颜色的模式 pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 背景颜色 pattern.pattern_fore_colour = i # 初始化样式 style0 = xlwt.XFStyle() style0.font = font style1 = xlwt.XFStyle() style1.pattern = pattern style2 = xlwt.XFStyle() style2.alignment = alignment style3 = xlwt.XFStyle() style3.borders = borders # 设置文字模式 font.num_format_str = '#,##0.00' sheet.write(i, 0, u'字体', style0) sheet.write(i, 1, u'背景', style1) sheet.write(i, 2, u'对齐方式', style2) sheet.write(i, 3, u'边框', style3) # 合并单元格，合并第2行到第4行的第4列到第5列 sheet.write_merge(2, 4, 4, 5, u'合并') i = i + 1 book.save('test_file' + time.strftime("%Y%m%d%H%M%S") + '.xls')
邯城往事