#!/usr/bin/env python#Encoding:utf-8ImportOPENPYXLImportCollectionsImportJSONImportcommands#Color DisplaydefBlue (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)ifStatus = =0:return0Else: return "Command error or something"defmerge ():#query a merged cellM_list =Worksheet.merged_cells#Judging cell generated coordinates output to listMerge_all_list = [] forM_areainchm_list:#start line coordinates, terminating line coordinates for merged cellsR1, R2, c1, c2 =M_area.min_row, M_area.max_row, M_area.min_col, M_area.max_colif(R1! = R2 andC1! =C2): Row_col= [(x, Y) forXinchXrange (R1, R2 + 1) forYinchXrange (C1, C2 + 1)] Merge_all_list.append (Row_col)elif(R1 = = R2 andC1! = C2):#or (r1! = R2 and C1 = = C2):col = [(R1, N) forNinchXrange (C1, C2 + 1)] Merge_all_list.append (COL)elif(R1! = R2 andC1 = =C2): Row= [(M, C1) forMinchXrange (R1, R2 + 1)] Merge_all_list.append (ROW)returnMerge_all_list,col#returns a tuple (index 0 is the merged cell sorting, and index 1 is the first row of merged cell coordinates)result = cmd ("> Json.conf")ifresult = =0:Print "Has to empty"Else: PrintResultworkbook= Openpyxl.load_workbook ("moban.xlsx")#load an already existing 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#The following is the maximum number of rows and columnsRow_number =Worksheet.max_rowcol_number=Worksheet.max_column#A dictionary that defines the order of additionstemp =collections. Ordereddict () forIinchXrange (2, worksheet.max_row+1): Install_list=[] Col_merger= [] forJinchXrange (1, worksheet.max_column+1): Merge_list=merge ()" "merge_list results are similar in coordinates [(1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), )] [(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), (, 2), (+ 3), (+, 4)]" " #The coordinates of the merged cell and the horizontal ordinate of the dictionary are compared, #If a list of cells is present, gets the upper-left cell value in the merged cell, and then jumps out #if the list of cells does not exist then continue to judge whether it is greater than the fifth column, greater than the fifth column is to add the list and then jump out, or add a duplicate list forA, binchEnumerate (merge_list[0]):if(I, J)inchB: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: ifJ >= 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 notCell_value isNone: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))
Simple use of OPENPYXL to read merged cell output JSON