表格数据抽取以及生成表格

Published on with 0 views and 0 comments


开启新生活

#!/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')

标题:表格数据抽取以及生成表格
作者:cuijianzhe
地址:https://cuijianzhe.github.io/articles/2020/06/02/1591098366985.html