简单使用openpyxl读取合并单元格输出json
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了简单使用openpyxl读取合并单元格输出json,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3030字,纯文字阅读大概需要5分钟。
内容图文
![简单使用openpyxl读取合并单元格输出json](/upload/InfoBanner/zyjiaocheng/1132/f6997ee1835748b7badd8d2acde72c05.jpg)
![技术分享图片](/upload/getfiles/default/2022/11/6/20221106065134484.jpg)
# !/usr/bin/env python # encoding: utf-8 import openpyxl import collections import json import 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 result 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中然后跳出,不然添加的是重复的listfor 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 breakelse: 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 ifnot cell_value is None: col_merger.append(cell_value) temp[cell_value_header] = col_merger breakelse: 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))
原文:https://www.cnblogs.com/y-x-h/p/9151968.html
内容总结
以上是互联网集市为您收集整理的简单使用openpyxl读取合并单元格输出json全部内容,希望文章能够帮你解决简单使用openpyxl读取合并单元格输出json所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。