邯城往事
来自邯郸社畜的呐喊
目录
表格数据抽取以及生成表格
表格数据抽取以及生成表格
开启新生活
#!/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')