python的xlwt如何实现数据有效性单元格？

iooiniu

在使用python的xlwt生成excel时，由于需要弄一个数据有效性单元格，类似于下图：

数据有效性单元格

不知道使用什么函数可以做到，求助下大神！！！！(谢谢！！)

iooiniu

#作者：wxw  _ 时间：2018/9/16-14:04
#功能：制作excel表格
# -*- coding: utf-8 -*-
import  xlwt,xlrd,os
from datetime import  datetime
from xlutils.copy import  copy

font=xlwt.Font()
font.bold = True
font.name = 'Times New Roman'
font.height=200
#设置边框
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
#设置居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER

#专用格式
pattern_datetime = xlwt.Pattern()
pattern_datetime.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_datetime.pattern_fore_colour = 15
#分隔符
pattern_separator = xlwt.Pattern()
pattern_separator.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_separator.pattern_fore_colour = 1
#标题和内容
pattern_title = xlwt.Pattern()
pattern_title.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_title.pattern_fore_colour = 26
pattern_range = xlwt.Pattern()
pattern_range.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_range.pattern_fore_colour = 27
pattern_info = xlwt.Pattern()
pattern_info.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_info.pattern_fore_colour = 42  #29 42 52

#style 格式 --使用
style_datetime = xlwt.easyxf('font: name Times New Roman,height 300,bold on', )
style_datetime.pattern = pattern_datetime
style_title = xlwt.easyxf('font: name Times New Roman,height 320,bold on',)
style_title.alignment = alignment
style_title.pattern = pattern_title
style_title.borders=borders

#表头-区间日期统计格式
range_title = xlwt.easyxf('font: name Times New Roman,height 200,bold on',) #10*20
range_title.alignment = alignment
range_title.pattern = pattern_range
range_title.borders=borders

#个股信息格式
style_info = xlwt.easyxf('font: name Times New Roman,height 220,bold on',) #11*20
style_info.alignment = alignment
style_info.pattern = pattern_info
style_info.borders = borders

#分隔符格式
style_separator = xlwt.easyxf('font: name Times New Roman,height 200,bold on',) #10*20
style_separator.alignment = alignment
style_separator.pattern = pattern_separator
style_separator.borders = borders

style_percent = xlwt.easyxf('font: name Times New Roman,height 220',num_format_str='0.00%' )

style_default  = xlwt.easyxf('font: name Times New Roman,' )
style_default.alignment = alignment

sss_a=[['600519', '贵州茅台', '1,559.00', '1,910.00', '1,910.00', '1,245.83', '1,245.83', '14.60亿', '13.72亿', '5.09亿', '3.48亿'],['002594', '比亚迪', '290.00', '338.04', '338.04', '159.67', '159.67', '6.97亿', '6.72亿', '4.91亿', '3.38亿'],['300750', '宁德时代', '265.56', '301.50', '301.50', '135.37', '135.37', '2.87亿', '2.96亿', '2.47亿', '1.95亿'],['000858', '五粮液', '151.29', '178.76', '211.44', '106.33', '106.33', '4,317.60万', '3,946.88万', '2,549.84万', '2,544.36万'],['300688', '创业黑马', '32.23', '39.22', '46.00', '16.43', '15.73', '3,344.58万', '3,165.60万', '2,326.42万', '2,039.61万'], ['300059', '东方财富', '25.63', '31.00', '31.00', '9.87', '9.87', '3,159.72万', '2,555.37万', '1,231.70万', '3,148.34万'], ['000002', '万科A', '8.71', '12.30', '19.95', '6.18', '6.18', '1,856.99万', '2,030.57万', '773.40万', '785.03万'], ['002103', '广博股份', '7.22', '9.94', '9.94', '4.55', '4.55', '1,709.80万', '1,533.38万', '1,341.16万', '1,285.69万'], ['002607', '中公教育', '3.49', '5.10', '6.38', '1.34', '1.34', '423.06万', '405.39万', '377.53万', '328.13万']]
tttt_a=['2023.11.16-2024.11.15','2022.11.16-2024.11.15','11.01-11.15','10.16-11.15','05.16-11.15','2023.11.16-2024.11.15']

#定制第一页表单的行间距
def  ws0_gen(ws0):
    for i in range(0,16):
        if i in [2,4,7,10]:
            ws0.col(i).width = int(256*1)
        elif i in [5,6,8,9,14]:
            ws0.col(i).width = 256*20
        else:
            ws0.col(i).width = 256*15

#定制第一页表头
def  ws0_title_gen(ws0):
    ws0.write_merge(0,1,0,0,"股票代码",style_title) # 合并第1列的 第1行和第2行
    ws0.write_merge(0,1,1,1,"股票名称",style_title) # 合并第2列的 第1行和第2行
    ws0.write_merge(0,1,3,3,"现价(元)",style_title) # 合并第4列的 第1行和第2行
    ws0.write_merge(0,0,5,6,"区间最高价(元)",style_title) # 合并第1行的 第6列和第7列
    ws0.write_merge(0,0,8,9,"区间最低价(元)",style_title) # 合并第1行的 第9列和第10列
    ws0.write_merge(0,0,11,14,"区间平均成交量(股)",style_title) # 合并第一行的 第12列和第15列

def  ws0_title_rangetime(ws0,r_num,arry):
    ws0.write(r_num, 5,arry[0],range_title)
    ws0.write(r_num, 6,arry[1],range_title)
    ws0.write(r_num, 8,arry[0],range_title)
    ws0.write(r_num, 9,arry[1],range_title)
    ws0.write(r_num, 11,arry[2],range_title)
    ws0.write(r_num, 12,arry[3],range_title)
    ws0.write(r_num, 13,arry[4],range_title)
    ws0.write(r_num, 14,arry[5],range_title)

def  ws0_info(ws0,r_num,arry):
    for i in range(len(arry)):
        for j in range(len(arry[i])):
            if  j < 2:
                ws0.write(r_num+i,j,arry[i][j],style_info)
            elif j == 2:
                ws0.write(r_num+i,j+1,arry[i][j],style_info)
            elif j in [3,4]:
                ws0.write(r_num+i,j+2,arry[i][j],style_info)
            elif j in [5,6]:
                ws0.write(r_num+i,j+3,arry[i][j],style_info)
            else :
                ws0.write(r_num+i,j+4,arry[i][j],style_info)



#制作表格
wb = xlwt.Workbook()
ws_0 = wb.add_sheet("基本数据")
ws0_gen(ws_0)
#定制表头&时间
ws0_title_gen(ws_0)
ws0_title_rangetime(ws_0,1,tttt_a)
ws0_info(ws_0,2,sss_a)
wb.save('stock_database_collect.xls')

#rwork = xlrd.open_workbook("stock_database_collect_change.xls",formatting_info = True)
#rwb_ws0 =   rwork.sheet_by_index(0)
#nrows0= rwb_ws0.nrows
##开始修改
#rwb = copy(rwork)
#ws_0 =   rwb.get_sheet(0)
#
##定制表头&时间
#ws0_title_gen(nrows0,ws_0)
#os.remove("stock_database_collect_change.xls")
#rwb.save("stock_database_collect_change.xls")



def  ws0_hyzj_gen(ws0,r_num,c_num,arry):
    for i in  range(len(arry)):
        for j in range(len(arry[i])):
            if(i<=2):
                #print(i+c_num,"xxxxx",arry[i][j])
                ws0.write(r_num+j, i+c_num, arry[i][j],style_title_asc)
            else:
                #print(i+c_num,"yyyyy",arry[i][j])
                ws0.write(r_num+j, i+c_num, arry[i][j],style_title_desc)

def  ws0_hqzx_gen(ws0,r_num,arry):
    for i in  range(len(arry)):
        if i == 0:
            ws0.write(r_num + 4, 20, arry[i], style_default)
        else:
            for j in range(len(arry[i])):
                if(i<=2):
                    ws0.write(r_num+6+i-1, j+15, arry[i][j],style_default)
                else :
                    ws0.write(r_num+(i-3), j+15, arry[i][j],style_default)

#定制第二页表单的行间距
def  ws1_gen(ws1):
    for i in range(26):
        if(i==11):
            ws1.col(i).width = 256*15
        else :
            ws1.col(i).width = 256*11
#定制第二页表头
def ws1_title_gen(r_num,ws1):
    ws1.write(r_num+1,0,"领涨股",style_title)
    ws1.write(r_num+1,1,"最新价",style_title)
    ws1.write(r_num+1,2,"涨幅",style_title)
    ws1.write(r_num+1,3,"所属板块",style_title)
    ws1.write(r_num+1,4,"领涨股",style_title)
    ws1.write(r_num+1,5,"最新价",style_title)
    ws1.write(r_num+1,6,"涨幅",style_title)
    ws1.write(r_num+1,7,"所属板块",style_title)
    ws1.write(r_num+1,8,"领涨股",style_title)
    ws1.write(r_num+1,9,"最新价",style_title)
    ws1.write(r_num+1,10,"涨幅",style_title)
    ws1.write(r_num+1,11,"所属板块",style_title)
    ws1.write(r_num+1,12,"领跌股",style_title)
    ws1.write(r_num+1,13,"最新价",style_title)
    ws1.write(r_num+1,14,"跌幅",style_title)
    ws1.write(r_num+1,15,"所属板块",style_title)
    ws1.write(r_num+1,16,"领跌股",style_title)
    ws1.write(r_num+1,17,"最新价",style_title)
    ws1.write(r_num+1,18,"跌幅",style_title)
    ws1.write(r_num+1,19,"所属板块",style_title)
    ws1.write(r_num+1,20,"领跌股",style_title)
    ws1.write(r_num+1,21,"最新价",style_title)
    ws1.write(r_num+1,22,"跌幅",style_title)
    ws1.write(r_num+1,23,"所属板块",style_title)


def  ws1_ggzf_gen(ws1,r_num,arry0,arry1):
    #print(arry0)
    #print(arry1)
    for i in  range(0,12):
        for j in range(0,37):
                if(i%4==0):
                    ws1.write(r_num + j, i, arry0[((37*(i//4)+j)*3)], style_title_asc)
                elif i%4==1:
                    ws1.write(r_num + j, i, arry0[1+((37*(i//4)+j)*3)], style_title_asc)
                elif i%4==2:
                    ws1.write(r_num + j, i, arry0[2+((37*(i//4)+j)*3)], style_title_asc)
                elif i%4==3:
                    ws1.write(r_num + j, i, arry1[j+(37*(i//3-1))], style_title_asc)
def  ws1_ggdf_gen(ws1,r_num,arry0,arry1):
    #print(arry0)
    #print(arry1)
    for i in  range(0,12):
        for j in range(0,37):
                if(i%4==0):
                    ws1.write(r_num + j, 12+i, arry0[((37*(i//4)+j)*3)], style_title_desc)
                elif i%4==1:
                    ws1.write(r_num + j, 12+i, arry0[1+((37*(i//4)+j)*3)], style_title_desc)
                elif i%4==2:
                    ws1.write(r_num + j, 12+i, arry0[2+((37*(i//4)+j)*3)], style_title_desc)
                elif i%4==3:
                    ws1.write(r_num + j, 12+i, arry1[j+(37*(i//3-1))], style_title_desc)



#定制第三页表单的行间距
def  ws2_gen(ws2):
    for i in range(26):
        if(i==2 or i==5 or i==9 or i==13 or i==17):
            ws2.col(i).width = 256*2
        elif(i==0 or i==3 or i==6  or i==10 or i==14 or i==18):
            ws2.col(i).width = 256*14
        elif(i==10):
            ws2.col(i).width = 256*15
        else:
            ws2.col(i).width = 256*11

#定制第三页表头
def ws2_title_gen(r_num,ws2):
    ws2.write(r_num+1,0,"个股净流入",style_title)
    ws2.write(r_num+1,1,"金额",style_title)
    ws2.write(r_num+1,3,"个股净流出",style_title)
    ws2.write(r_num+1,4,"金额",style_title)
    ws2.write(r_num+1,6,"连续上涨",style_title)
    ws2.write(r_num+1,7,"天数",style_title)
    ws2.write(r_num+1,8,"幅度",style_title)
    ws2.write(r_num+1,10,"连续下跌",style_title)
    ws2.write(r_num+1,11,"天数",style_title)
    ws2.write(r_num+1,12,"幅度",style_title)
    ws2.write(r_num+1,14,"量价齐升",style_title)
    ws2.write(r_num+1,15,"天数",style_title)
    ws2.write(r_num+1,16,"幅度",style_title)
    ws2.write(r_num+1,18,"量价齐跌",style_title)
    ws2.write(r_num+1,19,"天数",style_title)
    ws2.write(r_num+1,20,"幅度",style_title)

def  ws2_wr_gen(ws2,r_num,c_num,arry,style):
    #print(len(arry))
    #print(arry)
    for i in  range(0,len(arry)):
        if(i==37):
            break
        else:
            ws2.write(r_num + i, c_num, arry[i], style)


#定制时间标题
def  ws_datetime_gen(ws,r_num,c_num,date_time):
    ws.row(r_num).set_style(style_datetime)
    ws.write(r_num,c_num,date_time,style_datetime)



#首次Excel制作
def  first_wb_gen(date_time,hyzj_arry,gnzj_arry,hqzx_arry,\
                  ggzf_arry,ggdf_arry,ggzjlr_arry,lxzd_arry,ljzd_arry):
    #制作表格
    wb = xlwt.Workbook()
    ws_0 = wb.add_sheet("行业概念")
    ws_1 = wb.add_sheet("个股情况")
    ws_2 = wb.add_sheet("资金量")

    ws0_gen(ws_0)
    ws1_gen(ws_1)
    ws2_gen(ws_2)
    #定制表头&时间
    ws0_title_gen(0,ws_0)
    ws1_title_gen(0,ws_1)
    ws2_title_gen(0,ws_2)
    ws_datetime_gen(ws_0,0,9,date_time)
    ws_datetime_gen(ws_1,0,11,date_time)
    ws_datetime_gen(ws_2,0,10,date_time)

    #开始写入第一页:
    ws0_hyzj_gen(ws_0,2,0,hyzj_arry)
    ws0_hyzj_gen(ws_0,2,7,gnzj_arry)
    ws0_hqzx_gen(ws_0,2,hqzx_arry)

    #开始写入第二页:
    ws1_ggzf_gen(ws_1,2,ggzf_arry[0],ggzf_arry[1])
    ws1_ggdf_gen(ws_1,2,ggdf_arry[0],ggdf_arry[1])

    #开始写入第三页:
    #print(len(ljzd_arry[0]))
    #print(ljzd_arry[0])
    #print(len(ljzd_arry[3]))
    #print(ljzd_arry[3])
    ws2_wr_gen(ws_2,2,0,ggzjlr_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,2,1,ggzjlr_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,2,3,ggzjlr_arry[2],style_title_desc)
    ws2_wr_gen(ws_2,2,4,ggzjlr_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,2,6,lxzd_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,2,7,lxzd_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,2,8,lxzd_arry[2],style_title_asc)
    ws2_wr_gen(ws_2,2,10,lxzd_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,2,11,lxzd_arry[4],style_title_desc)
    ws2_wr_gen(ws_2,2,12,lxzd_arry[5],style_title_desc)
    ws2_wr_gen(ws_2,2,14,ljzd_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,2,15,ljzd_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,2,16,ljzd_arry[2],style_title_asc)
    ws2_wr_gen(ws_2,2,18,ljzd_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,2,19,ljzd_arry[4],style_title_desc)
    ws2_wr_gen(ws_2,2,20,ljzd_arry[5],style_title_desc)

    wb.save('stock_database_collect.xls')

def  change_wb_gen(date_time,hyzj_arry,gnzj_arry,hqzx_arry,\
                    ggzf_arry,ggdf_arry,ggzjlr_arry,lxzd_arry,ljzd_arry):
    rwork = xlrd.open_workbook("stock_database_collect_change.xls",formatting_info = True)
    rwb_ws0 =   rwork.sheet_by_index(0)
    rwb_ws1 =   rwork.sheet_by_index(1)
    rwb_ws2 =   rwork.sheet_by_index(2)
    nrows0= rwb_ws0.nrows
    nrows1= rwb_ws1.nrows
    nrows2= rwb_ws2.nrows

    #开始修改
    rwb = copy(rwork)
    ws_0 =   rwb.get_sheet(0)
    ws_1 =   rwb.get_sheet(1)
    ws_2 =   rwb.get_sheet(2)

    #定制表头&时间
    ws0_title_gen(nrows0,ws_0)
    ws1_title_gen(nrows1,ws_1)
    ws2_title_gen(nrows2,ws_2)
    ws_datetime_gen(ws_0,nrows0,9,date_time)
    ws_datetime_gen(ws_1,nrows1,11,date_time)
    ws_datetime_gen(ws_2,nrows2,10,date_time)
   
    #开始写入第一页:
    ws0_hyzj_gen(ws_0,nrows0+2,0,hyzj_arry)
    ws0_hyzj_gen(ws_0,nrows0+2,7,gnzj_arry)
    ws0_hqzx_gen(ws_0,nrows0+2,hqzx_arry)

    #开始写入第二页:
    ws1_ggzf_gen(ws_1,nrows1+2,ggzf_arry[0],ggzf_arry[1])
    ws1_ggdf_gen(ws_1,nrows1+2,ggdf_arry[0],ggdf_arry[1])

    #开始写入第三页:
    ws2_wr_gen(ws_2,nrows2+2,0,ggzjlr_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,1,ggzjlr_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,3,ggzjlr_arry[2],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,4,ggzjlr_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,6,lxzd_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,7,lxzd_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,8,lxzd_arry[2],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,10,lxzd_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,11,lxzd_arry[4],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,12,lxzd_arry[5],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,14,ljzd_arry[0],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,15,ljzd_arry[1],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,16,ljzd_arry[2],style_title_asc)
    ws2_wr_gen(ws_2,nrows2+2,18,ljzd_arry[3],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,19,ljzd_arry[4],style_title_desc)
    ws2_wr_gen(ws_2,nrows2+2,20,ljzd_arry[5],style_title_desc)

    os.remove("stock_database_collect_change.xls")
    rwb.save("stock_database_collect_change.xls")