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

#!/usr/bin/env python# encoding: utf-8import openpyxlimport collectionsimport jsonimport commands#颜色显示def blue(string): ???print ("\033[0;34m%s\033[0m" % string)def cyan(string): ???print ("\033[0;36m%s\033[0m" % string)def green(string): ???print ("\033[0;32m%s\033[0m" % string)def red(string): ???print ("\033[0;46m%s\033[0m" % string)def write_Conf(path,data): ???with open(path, ‘a+‘) as f: ???????f.write(data + "\n")def cmd(args): ???status, output = commands.getstatusoutput(args) ???if status == 0: ???????return 0 ???else: ???????return "Command error or something"def merge(): ???#查询合并的单元格 ???m_list = worksheet.merged_cells ???#判断单元格生成坐标输出到list ???merge_all_list = [] ???for m_area in m_list: ???????# 合并单元格的起始行坐标、终止行坐标 ???????r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col ???????if (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: ???print resultworkbook = openpyxl.load_workbook("moban.xlsx") ?#加载已经存在的excelname_list = workbook.sheetnames# worksheet = workbook.get_sheet_by_name(name_list[0]) ?#最新版本已经不能使用这种方法worksheet = workbook[name_list[0]]# print name_list# print workbook# print worksheetrows = worksheet.rowscolumns = worksheet.columns#如下是最大行数和最大列数row_number = worksheet.max_rowcol_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 in enumerate(merge_list[0]): ???????????if (i, j) in b: ???????????????????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_value ???????????????????break ???????????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).value ???????????????????if not cell_value is None: ???????????????????????col_merger.append(cell_value) ???????????????????temp[cell_value_header] = col_merger ???????????????????break ???????????????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))