python openpyxl合并单元格_简单使用openpyxl读取合并单元格输出json

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