#!/usr/bin/env python#encoding: utf-8
importopenpyxlimportcollectionsimportjsonimportcommands#颜色显示
defblue(string):print ("\033[0;34m%s\033[0m" %string)defcyan(string):print ("\033[0;36m%s\033[0m" %string)defgreen(string):print ("\033[0;32m%s\033[0m" %string)defred(string):print ("\033[0;46m%s\033[0m" %string)defwrite_Conf(path,data):
with open(path,'a+') as f:
f.write(data+ "\n")defcmd(args):
status, output=commands.getstatusoutput(args)if status ==0:return0else:return "Command error or something"
defmerge():#查询合并的单元格
m_list =worksheet.merged_cells#判断单元格生成坐标输出到list
merge_all_list =[]for m_area inm_list:#合并单元格的起始行坐标、终止行坐标
r1, r2, c1, c2 =m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_colif (r1 != r2 and c1 !=c2):
row_col= [(x, y) for x in xrange(r1, r2 + 1) for y in xrange(c1, c2 + 1)]
merge_all_list.append(row_col)elif (r1 == r2 and c1 != c2): #or (r1 != r2 and c1 == c2):
col = [(r1, n) for n in xrange(c1, c2 + 1)]
merge_all_list.append(col)elif (r1 != r2 and c1 ==c2):
row= [(m, c1) for m in xrange(r1, r2 + 1)]
merge_all_list.append(row)return merge_all_list,col #返回一个元组(索引0是合并之后的单元格列表,索引1是第一行合并的单元格坐标)
result = cmd("> json.conf")if result ==0:print "Have to empty"
else:printresult
workbook= openpyxl.load_workbook("moban.xlsx") #加载已经存在的excel
name_list =workbook.sheetnames#worksheet = workbook.get_sheet_by_name(name_list[0]) #最新版本已经不能使用这种方法
worksheet =workbook[name_list[0]]#print name_list#print workbook#print worksheet
rows=worksheet.rows
columns=worksheet.columns#如下是最大行数和最大列数
row_number =worksheet.max_row
col_number=worksheet.max_column#定义添加顺序的字典
temp =collections.OrderedDict()for i in xrange(2, worksheet.max_row+1):
install_list=[]
col_merger=[]for j in xrange(1, worksheet.max_column+1):
merge_list=merge()'''merge_list结果就类似如下坐标
[(1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), (1, 14)]
[(3, 1), (4, 1)]
[(2, 2), (2, 3), (3, 2), (3, 3)]
[(4, 1), (5, 1), (6, 1)]
[(8, 2), (8, 3), (8, 4), (9, 2), (9, 3), (9, 4), (10, 2), (10, 3), (10, 4)]'''
#循环合并的单元格的坐标和字典的横纵坐标对比,
#如果存在单元格的list就获取合并单元格中的左上角单元格值,然后跳出
#如果不存在单元格的list就再继续判断是不是大于第五列,大于第五列就是添加list中然后跳出,不然添加的是重复的list
for a, b inenumerate(merge_list[0]):if (i, j) inb:
cell_value_header= worksheet.cell(row=1, column=j).value
cell_value= worksheet.cell(row=merge_list[0][a][0][0], column=merge_list[0][a][0][1]).value
temp[cell_value_header]=cell_valuebreak
else:if j >= merge_list[-1][0][-1]:
cell_value_header= worksheet.cell(row=1, column=merge_list[-1][0][-1]).value
cell_value= worksheet.cell(row=i, column=j).valueif not cell_value isNone:
col_merger.append(cell_value)
temp[cell_value_header]=col_mergerbreak
else:
cell_value_header= worksheet.cell(row=1, column=j).value
cell_value= worksheet.cell(row=i, column=j).value
temp[cell_value_header]=cell_value#print json.dumps(temp)
write_Conf("json.txt", json.dumps(temp))