本文章借鉴学习于 https://blog.csdn.net/songyanli_good/article/details/122282661
win7,wps,python2.7
上代码
if __name__ == "__main__":
workbook = xlsxwriter.Workbook('defined_test.xlsx')
worksheet1 = workbook.add_worksheet("请在此页填写你的内容")
worksheet2 = workbook.add_worksheet("示例")
worksheet3 = workbook.add_worksheet("导入规则")
worksheet4 = workbook.add_worksheet()
# sheet4设置成隐藏
# worksheet4.hide()
data = {
"进博会系统": ["进博会系统1", "进博会系统2", "进博会系统3", "进博会系统4", "进博会系统5"],
"供服系统": ["供服系统1", "供服系统2", "供服系统3"],
"测试系统": ["测试系统1", "测试系统2"],
"其它": []
}
col = ["A", "B", "C", "D", "E", "F", "G", "H", "I"]
key_data = list(data.keys())
print("key_data :", key_data)
for i in range(len(key_data)):
worksheet4.write(0, i, key_data[i])
key_value = data[key_data[i]]
_len_key_value = len(key_value)
for j in range(_len_key_value):
worksheet4.write(j+1, i, key_value[j])
# print(_len_key_value)
_tmp_max = 2
if _len_key_value < 2:
_tmp_max = 2
else:
_tmp_max = _len_key_value + 1
print('请在此页填写你的内容!{}'.format(key_data[i]))
print('=Sheet4!${}$2:${}${}'.format(col[i], col[i], _tmp_max))
workbook.define_name('请在此页填写你的内容!{}'.format(key_data[i]), '=Sheet4!${}$2:${}${}'.format(
col[i], col[i], _tmp_max))
# 100行 增加下拉数据
for v in range(1, 100):
v = str(v)
worksheet1.data_validation("A" + v, {'validate': 'list',
'value': '=Sheet4!$A$1:$D$1'})
worksheet1.data_validation("B" + v, {'validate': 'list',
'source': '=INDIRECT(CONCATENATE($A'+v+'))'})
workbook.close()
pass