python 导表---xls笔记

最新推荐文章于 2023-09-22 14:50:22 发布

原创最新推荐文章于 2023-09-22 14:50:22 发布 · 576 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python #xls

本文介绍了使用Python的ConfigParser、xlrd库处理Excel文件的方法，包括读取xls文件、解析json、数据唯一性与排序检查、自定义数据类型转换等操作。通过示例代码展示了如何解析xls文件并将其转换为json格式。

idx:

[角色.xls]
角色属性:role
美术:art
动作:action

cfg = configparser.ConfigParser()

cfg.read("idx","utf-8")

for i in cfg.sections():
for j in cfg[i]:
xls_tasks[cfg[i][j]].append([i, j])

i : 角色.xls

j : 角色属性

cfg[i][j] :role

解析json：

with open(os.path.join("tmp", ".org", k), encoding="utf-8") as f:

json_outputs[k] = json.load(f)#解析json

得 {'asset':'R001','anger':100}

另用 with open(os.path.join("tmp", ".org", k), encoding="utf-8") as f:

f.write("ssasas")

如果文件不存在，则自动创建

#functools.partial用法
        # def add(a, b):
        #     return a + b
        # add(4, 2)
        # 6
        # plus3 = functools.partial(add, 3)
        # plus5 = functools.partial(add, 5)
        # plus3(4)
        # 7

#takewhile(lambda x: x < 5, [1, 3, 6, 7, 1])
    #当函数返回True时，收集元素到循环器。一旦函数返回False，则停止。1, 3
    #isinstance(x, str)
    #判断x是否是str类或者object是变量

#os.path.getmtime(path) 文件最后修改时间 exists 判断文件是否存在，ture
#json_outputs[k].extend(parse(xls, sheet))添加数据类型

json_outputs[k].extend(parse(xls, sheet))#extend 使[a,b] 中的元素展开append进去

--------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------

------------------------------------------------------------

--------------------------------------------

python导表代码如下：

#!/usr/bin/env python3
# xls

import collections
import configparser
import functools
import itertools
import json
import logging
import os
import pprint
import re
import shelve
import sys
import time
import traceback

import xlrd

class EvalCache(dict):
    #重写字典类 相当于“2+3+5”的value可eval（value）计算
    def __missing__(self, k):
        code = compile(k, k, "eval")
        self[k] = code
        return code

_eval_cache = EvalCache()
_eval = eval
def eval(source, globals=None, locals=None):
    """patch built-in function `eval`, this new eval is faster"""
    return _eval(_eval_cache[source], globals, locals)

class Default(dict):
    def __missing__(self, key):
        return key

eval_env = Default()
        #functools.partial用法
        # def add(a, b):
        #     return a + b
        # add(4, 2)
        # 6
        # plus3 = functools.partial(add, 3)
        # plus5 = functools.partial(add, 5)
        # plus3(4)
        # 7
dump_sorted = functools.partial(json.dumps, ensure_ascii=False,
                                separators = (",", ": "),
                                sort_keys=True, indent=4)

dump_raw = functools.partial(json.dumps, ensure_ascii=False,
                             separators = (",", ": "), indent=4)

# global progress status

##xls结构
progress = collections.OrderedDict.fromkeys(["xls", "sheet", "column", "row", "output"])

types_recorder = collections.defaultdict(dict)

class ListDefaultDict(collections.OrderedDict):
    def __missing__(self, k):
        self[k] = []
        return self[k]

xls_tasks = ListDefaultDict()
uniq_tasks = ListDefaultDict()
sort_tasks = ListDefaultDict()
json_outputs = ListDefaultDict()
ref_file_tasks = {}
ref_file_not_exist = {}

workbooks_mtimes = collections.Counter()

log_level = logging.DEBUG
log_level = logging.INFO

logging.basicConfig(
    level=log_level,
    format="%(levelname)s:%(message)s",
    )

##查列名的唯一
def check(prefix=""):
    """check
    uniq_tasks
    sort_tasks
    ref_file_tasks
    """
    #logging.info("check uniq_tasks:")
    for pos, lst in uniq_tasks.items():
        logging.debug((pos, lst))
        if len(frozenset(lst)) != len(lst):
            repeat_list = []
            for i in lst:
                if lst.count(i) != 1 and i not in repeat_list:
                    repeat_list.append(i)
            show_repeat = ""
            if repeat_list:
                show_repeat = ": " + ", ".join(str(i) for i in repeat_list)
            logging.error("数据唯一检查失败: "+pos+show_repeat)
            raise RuntimeError

#logging.info("check sort_tasks:")
    for pos, lst in sort_tasks.items():
        logging.debug((pos, lst))
        if sorted(lst) != list(lst):
            logging.error("数据排序检查失败: "+pos+": "+str(lst))
            raise RuntimeError

#logging.info("check ref_file_tasks:")
    passes = []
    for cell, f in ref_file_tasks.items():
        if not os.path.exists(os.path.join("Resources", f)):
            ref_file_not_exist[cell] = f
            logging.warning("指定文件检查失败: "+"{}: {} {}".format(cell, f,  "文件不存在"))

cellname_match = re.compile(r"([A-Z]+)(\d+)").match

##生列号
def cellname_to_index(name, _A2Z="ABCDEFGHIJKLMNOPQRSTUVWXYZ"):
    """
    >>> cellname_to_index("A1")
    (0, 0)
    >>> cellname_to_index("Z1")
    (25, 0)
    >>> cellname_to_index("ZZ1")
    (701, 0)
    >>> cellname_to_index("AAA111")
    (702, 110)
    """
    col, row = cellname_match(name).groups()
    base = len(_A2Z)
    n = m = 0
    for i in map(_A2Z.index, col):
        n = m + i
        m = (n + 1) * base
    return n, int(row) - 1

rc_key_match = re.compile(r"([a-z_]+)(\d*)").match

def mess(raw):
    w0 = []
    w1 = []
    random_type = "random_simple"

for unit in filter(None, (_.strip() for _ in raw.split(","))):
        rw = []
        keys = unit.split(":")
        l = len(keys)
        if l == 3 or l == 5:
            k = keys[0]
            i1, i2 = rc_key_match(k).groups()
            i3 = int(keys[1])

rw.append(i1)
            if i2:
                rw.append(int(i2))
            rw.append(i3)
            if l == 5:
                i4 = int(keys[2])
                i5 = int(keys[3])
                rw.append(i4)
                rw.append(i5)
        else:
            logging.error("mess type input error")
            raise RuntimeError

il = keys[-1]
        if il[-1] == "%":
            w0.append(rw)
            w1.append(float(il[:-1]) / 100)
            assert random_type == "random_simple"
        else:
            w0.append(rw)
            w1.append(float(il))
            random_type = "random_weight"

return [random_type, w0, w1]

def mess_fixed(raw):
    rewards = []
    for unit in filter(None, (_.strip() for _ in raw.split(","))):
        rw = []
        keys = unit.split(":")
        l = len(keys)
        assert l == 2, keys

k = keys[0]
        i1, i2 = rc_key_match(k).groups()
        i3 = int(keys[1])

for l in rewards:
            if l[0] == i1:
                if not i2 or l[1] == int(i2):
                    logging.error("same keys here:")
                    raise RuntimeError

rw.append(i1)
        if i2:
            rw.append(int(i2))
        rw.append(i3)
        rewards.append(rw)

return rewards

list_fmt = "[{}]".format
dict_fmt = "{{{}}}".format

def list_all_same_type(type):
    def _conv(lst):
        if isinstance(lst, list):
            return [_conv(i) for i in lst]
        return type(lst)
    return lambda raw: _conv(eval(list_fmt(raw), None, eval_env))

def BuffVO(v):
    if isinstance(v, list) and len(v) > 5:
        logging.error("too many parameters here:")
        raise RuntimeError
    l = list(map(float, [v, 0, 0, 0, 0] if not isinstance(v, list) else (v + [0, 0, 0, 0])[:5]))
    l[4] = int(l[4])
    return l

def Dialog(v):
    lst = re.findall("(\d+)\s*:\s*\[([\s\S]*?)\]", v)
    c = []
    if lst:
        for k, v in lst:
            if c.count(k):
                logging.error("same keys here")
                raise RuntimeError
            else:
                c.append(k)
    item_dict = dict(lst)
    if item_dict:
        for k, v in item_dict.items():
            item_list = [x.strip() for x in v.split(",")]
            assert len(item_list) == 3, item_list
            str_list = [_.strip() for _ in item_list[2].split("|")]
            assert len(str_list) == 2, str_list
            item_dict[k] = []
            item_dict[k].extend([int(item_list[0]), item_list[1], str_list[0], str_list[1]])
    return item_dict

def compound_items(v):
    items = []
    item_dict = ListDefaultDict()
    item_raws =  v.split(",")
    for item_raw in item_raws:
        item_raw = item_raw.strip()
        if item_raw:
            item_raw_array = item_raw.split("*")
            if len(item_raw_array) == 1:
                item_id = int(item_raw_array[0])
                item_count = 1
            else:
                item_id = int(item_raw_array[0])
                item_count = int(item_raw_array[1])
            if item_dict.get(item_id):
                item_dict[item_id] += item_count
            else:
                item_dict[item_id] = item_count
    for k,v in item_dict.items():
        items.append([k, v])
        
    return  items

find_key = re.compile(r',?(?P<key>\w+):').findall

def check_dict_key(v):
    key_list = find_key(v)
    if len(frozenset(key_list)) != len(key_list):
        logging.warning("same keys here:"+
        "{} -> {} -> {}{}:\n".format(progress["xls"], progress["sheet"],progress["column"],progress["row"])+v)
        raise RuntimeError
    return eval(dict_fmt(v), None, eval_env)

def condition(v):
    d = check_dict_key(v)
    assert len(d) == 1
    return [ str(list(d.keys())[0]) , int(list(d.values())[0]) ]

def foo(v):
    result_list = []
    l = re.findall(',?\s*lv\s*:\s*(\d+)\s*,?', v)
    c = len(l)
    if c > 1:
        logging.error("lv require too much:"+v)
        raise RuntimeError
    elif c == 1:
        tmp = {}
        tmp['lv'] = int(l[0])
        result_list.append(tmp)

if v.count('lv') > c:
        logging.error("input error:"+v)
        raise RuntimeError
    
    l = re.findall(',?\s*item\s*:\s*\[\s*(\d+)\s*,\s*(\d+)\s*\]\s*,?', v)
    if l:
        item_list = {}
        for item_id, item_amount in l:
            item_id = int(item_id)
            item_amount = int(item_amount)
            if item_list.get(item_id):
                item_list[item_id] += item_amount
            else:
                item_list[item_id] = item_amount
        for k, kk in item_list.items():
            tmp = {}
            tmp['item'] = [k, kk]
            result_list.append(tmp)
      
    if v.count('item') > len(l):
        logging.error("input error:"+v)
        raise RuntimeError
    return result_list

def list_dict(v):
    split_raws = v.split(",")
    tmp = ""
    dict_raws = []
    for i in split_raws:
        if tmp:
            i = tmp+','+i
        try:
            d = eval(dict_fmt(i), None, eval_env)
            if d:
                dict_raws.append(d)
            tmp = ""
        except:
            if tmp:
                logging.error("input error:"+repr(i))
                raise RuntimeError
            tmp = i
            continue
    if tmp:
        logging.error("input error:"+repr(tmp))
        raise RuntimeError
    #pprint.pprint(repr(dict_raws))

items = {}
    pop_list = []
    lv_tag = False
    for i,d in dict(enumerate(dict_raws)).items():
        for k,l in d.items():
            if k == 'lv':
                if lv_tag:
                    logging.error("lv require too much:"+repr(d))
                    raise RuntimeError
                else:
                    lv_tag = True
            elif k == 'item':
                if not isinstance(l, list) or len(l) != 2:
                    logging.error("input error:"+repr(d))
                    raise RuntimeError
                if items.get(l[0]) != None:
                    dict_raws[items[l[0]]]['item'][1] += l[1]
                    pop_list.insert(0,i)
                else:
                    items[l[0]] = i
    for i in pop_list:
        dict_raws.pop(i)
    #pprint.pprint(repr(items))

return  dict_raws

def type_bool(Val): 
    if type(Val) == bool:
        return  bool(Val)
    elif type(Val) == str:
        if Val.lower() == "true":
            return  True
        elif Val.lower() == "false":
            return  False
        else:
            raise "Error Boolean Type"
    else:
        raise "Error Boolean Type"

def datetime(v):
    dt = {}
    dt["date"] = [v[0], v[1], v[2]]
    dt["time"] = [v[3], v[4], v[5]]
    return dt
    
types = {
    # my custom formated list and dict
    "int": int,
    "float": float,
    "str": str,
    "bool": type_bool,
    "mess": mess,
    "mess_fixed": mess_fixed,
    "list_dict": list_dict,
    "list_int": lambda raw: [int(i) for i in eval(list_fmt(raw))],
    "list_int2": lambda raw: [[int(i) for i in l] for l in eval(list_fmt(raw))],
    "list_float": lambda raw: [float(i) for i in eval(list_fmt(raw))],
    "list_float2": lambda raw: [[float(i) for i in l] for l in eval(list_fmt(raw))],
    "list_str": lambda raw: [str(i) for i in eval(list_fmt(raw), None, eval_env)],
    "list_str2": lambda raw: eval(list_fmt(raw), None, eval_env),
    "dict_str_int": lambda raw: {str(k): int(v) for k, v in check_dict_key(raw).items()},
    "dict_str_float": lambda raw: {str(k): float(v) for k, v in check_dict_key(raw).items()},
    "dict_str_str": lambda raw: {str(k): str(v) for k, v in check_dict_key(raw).items()},
    "dict_int_int": lambda raw: {int(k): int(v) for k, v in check_dict_key(raw).items()},
    "dict_int_float": lambda raw: {int(k): float(v) for k, v in check_dict_key(raw).items()},
    "dict_int_str": lambda raw: {int(k): str(v) for k, v in check_dict_key(raw).items()},
    "dict_int_list_int": lambda raw: {int(k): [int(i) for i in v] for k, v in check_dict_key(raw).items()},
    "dict_str_list_int": lambda raw: {str(k): [int(i) for i in v] for k, v in check_dict_key(raw).items()},
    "BuffVO": lambda raw: dict((k, BuffVO(v)) for k, v in check_dict_key(raw).items()),
    "compound_items":compound_items,
    "Dialog": Dialog,
    "condition": condition,
    "datetime": datetime,
}

def note_text_to_attr(text):
    """type and test and ...
    type: int, float, str, bool, list, dict, time, mess
    test: any python statement
    ...
    """
    attr = {}
    for line in filter(None, (_.strip() for _ in text.split("\n"))):
        token = line.split(":", 1)
        if len(token) != 2:
            continue
        k, v = (_.strip() for _ in token)
        attr[k] = v
    return attr

def get_custom_attrs(sheet):
    """cells' custom attrubutes
    range will be used: row 2 - end
    """
    cell_note_map = sheet.cell_note_map
    o = {}
    for k, v in cell_note_map.items():
        if k[0] == 0:  # ignore row 1, already parsed in get_keys_attrs
            continue
        txt = v.text
        out = note_text_to_attr(txt)
        logging.debug(
            "note_text_to_attr_{}({!r}) = {}".format(xlrd.cellname(*k), txt, out))
        o[k] = out
    return o

START_ROW = 2
def get_keys_attrs(sheet):
    """keys and attrs
    keys come from row 1, a normal string is ok
    attrs live with these keys, note text
    """
    progress["row"] = START_ROW + 1
    #takewhile(lambda x: x < 5, [1, 3, 6, 7, 1])
    #当函数返回True时，收集元素到循环器。一旦函数返回False，则停止。1, 3
    #isinstance(x, str)   
    #判断x是否是str类或者object是变量
    keys = list(itertools.takewhile(lambda x: isinstance(x, str) and x,
                                    sheet.row_values(START_ROW)))
    assert keys, progress
    assert len(keys) == len(set(keys)), keys

attrs = []
    cell_note_map = sheet.cell_note_map#初步猜测是每个属性的note中的东西
    #print ("cell_note_map:",cell_note_map)
    #  enumerate 函数用于遍历序列中的元素以及它们的下标：
    # >>> for i,j in enumerate(('a','b','c')):
    #  print i,j
    for colx, key in enumerate(keys):
        colname = xlrd.colname(colx)
        progress["column"] = colname
        note = cell_note_map.get((START_ROW, colx))
        txt = note.text
        out = note_text_to_attr(txt)
        logging.debug(
            "note_text_to_attr_{}({!r}) = {}".format(colname, txt, out))
        assert "type" in out
        attrs.append(out)
    return keys, attrs#返回sheet中属性名称和数据类型

def apply_attrs(values, attrs, rowx):
    """convert and check cell.value
    if error occurs, set progress["error"] and break
    """
    fmt = "{} -> {} -> {}".format
    o = []
    colx = 0
    for x, attr in zip(values, attrs):
        colname = xlrd.colname(colx)
        progress["column"] = colname
        abs_colname = fmt(progress["xls"], progress["sheet"], colname)

if attr:
            #
            x = types[attr["type"]](x)
            #
            _test = attr.get("test")
            if _test:
                assert eval(_test, None, locals()), _test
            #
            if attr.get("uniq"):
                uniq_tasks[abs_colname].append(x)
            #
            if attr.get("sort"):
                sort_tasks[abs_colname].append(x)
            #
            _ref = attr.get("ref")
            if _ref and x:
                ref = [i.strip() for i in _ref.split(":", 1)]
                ref_dir = ref[0].format(x=x)
                ref_file = x if len(ref) == 1 else ref[1].format(x=x)
                abs_cellname = fmt(progress["xls"], progress["sheet"],
                                   xlrd.cellname(rowx, colx))
                ref_file_tasks[abs_cellname] = os.path.relpath(os.path.join(ref_dir, ref_file).lstrip(r"\/"))

o.append(x)
        colx += 1

return o

def combine(keys, attrs, rows_values):
    """
    pprint.pprint(keys)
    pprint.pprint(attrs)
    pprint.pprint(rows_values)
    """
    o = []
    for rowx, values in enumerate(rows_values, START_ROW + 1):
        progress["row"] = rowx + 1   # human readable
        v = apply_attrs(values, attrs, rowx)
        o.append(dict(zip(keys, v)))
    types_recorder[progress["output"]] = dict(zip(keys, (i["type"] for i in attrs)))
    return o

def filter_cell_value(type, value, datemode=0):
    if type == xlrd.XL_CELL_NUMBER:
        if value.is_integer():
            value = int(value)
    elif type == xlrd.XL_CELL_DATE:
        value = xlrd.xldate_as_tuple(value, datemode)
    elif type == xlrd.XL_CELL_BOOLEAN:
        value = bool(value)
    else:
        value = value.strip()
    return value

def parse_sheet(sheet):
    keys, attrs = get_keys_attrs(sheet)
    rows_values = []
    for i in range(START_ROW + 1, sheet.nrows):#nrows总行数
        if sheet.row_values(i)[0] != "":
            rows_values.append(list(map(filter_cell_value,
                                        sheet.row_types(i),
                                        sheet.row_values(i))))
        else:
            break
    return combine(keys, attrs, rows_values)

parsing_tag = True
def main():
    from patch import patch
    cfg = configparser.ConfigParser()
    cfg.read("idx", "utf-8")#读取配置文件，【】内的为section，类似key-value
    #sections()get [] in filename
    for i in cfg.sections():
        for j in cfg[i]:
            xls_tasks[cfg[i][j]].append([i, j])
    def getmtime(dir="", file=""):
        path = os.path.join(dir, file)
        return os.path.getmtime(path) if os.path.exists(path) else 0
        #os.path.getmtime(path) 文件最后修改时间 exists 判断文件是否存在，ture
    olders = {
        k
        for k, v in xls_tasks.items()
        if getmtime("tmp", k) > max(getmtime("xls", i) for i, _ in v)
    }# 如果.xls时间小于tem中最后修改的时间 则保存k 即已经解析过的
    #print (olders)
    for k in olders:
        with open(os.path.join("tmp", ".org", k), encoding="utf-8") as f:
            json_outputs[k] = json.load(f)#解析json
        xls_tasks.pop(k)

if os.path.exists("types_recorder"):
        with open("types_recorder", encoding="utf-8") as f:
            types_recorder.update(json.load(f))#得到每个sheet的属性的数据类型
    if os.path.exists("ref_file_not_exist"):
        with open("ref_file_not_exist", encoding="utf-8") as f:
            for c, f in json.load(f).items():
                ref_file_tasks[c] = f

def parse(xls, sheet):
        progress["xls"] = xls
        progress["sheet"] = sheet
        sheet = xlrd.open_workbook("xls/{}".format(xls)).sheet_by_name(sheet)
        return parse_sheet(sheet)
        
    print("xls文件解析开始:")
    if not xls_tasks:
        logging.warning("没有更改的xls文件")
    for k, v in xls_tasks.items():
        progress["output"] = k
        for xls, sheet in v:
            logging.info("parse_sheet  {} - {}  to {}".format(xls, sheet, k))
            #json_outputs[k].extend(parse(xls, sheet))添加数据类型
            json_outputs[k].extend(parse(xls, sheet))#extend 使[a,b] 中的元素展开append进去
    global parsing_tag
    parsing_tag = False

for k in set(json_outputs) - olders:
        with open(os.path.join("tmp", ".org", k), "w", encoding="utf-8") as f:
            f.write(dump_sorted(json_outputs[k]))

#patch(json_outputs, types_recorder)

check("Resources/")

for k, v in json_outputs.items():
        with open(os.path.join("tmp", k), "w", encoding="utf-8") as f:#tmp 具体每个sheet中每行的数据
            try:
                assert v
            except:
                logging.error("要保存的数据文件 "+k+" 为空")
                raise RuntimeError
            f.write(dump_sorted(v))

with open("types_recorder", "w", encoding="utf-8") as f:
        f.write(dump_sorted(types_recorder))#types_recorder 每个sheet的属性的数据类型
    with open("ref_file_not_exist", "w", encoding="utf-8") as f:
        f.write(dump_sorted(ref_file_not_exist))

if __name__ == "__main__":
    try:
        main()
    except Exception:
        if parsing_tag:
            fmt = "{:>10} -> {}".format
            for k, v in progress.items():
                print(fmt(k, v))
        raise

</key>