python實作excel公式格式化-有解無憂

小小明：

一名從初中開始編程，已具備10年以上基礎編碼經驗的碼農，

之前跟一些小伙伴有個討論：

大概就是很多跟資料打交道的朋友都面對過很復雜的excel公式，有時嵌套層數特別多，肉眼觀看很容易蒙圈，
有了這樣的需求，我就有了解決問題的想法，說干就干，于是一個比較牛逼的excel公式格式化的工具就出現了，

效果體驗

先看看效果吧：

=IF(C11>100%*C4,IF(C11<=200%*C4,C11*50%-C4*15%,C11*60%-C4*35%),IF(C11<=C4*50%,C11*30%,C11*40%-C4*5%))

的格式化結果是：

=IF(
  C11>100%*C4,
  IF(
    C11<=200%*C4,
    C11*50%-C4*15%,
    C11*60%-C4*35%
  ),
  IF(
    C11<=C4*50%,
    C11*30%,
    C11*40%-C4*5%
  )
)

(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100-MIN(SMA(MAX(CLOSE-DELAY(
CLOSE,1),0),12,1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,12))/(MAX(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,
1)/SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,12)-MIN(SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)/SMA(ABS(
CLOSE-DELAY(CLOSE,1)),12,1)*100,12))

的格式化結果為：

(
  SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
  /
  SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)
  *
  100-MIN(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
)
/
(
  MAX(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
  -
  MIN(
    SMA(MAX(CLOSE-DELAY(CLOSE,1),0),12,1)
    /
    SMA(ABS(CLOSE-DELAY(CLOSE,1)),12,1)*100,
    12
  )
)

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-yWj19ysf-1608598379417)(https://gitee.com/wangzhouming/blogimgs/raw/master/img/image-20201214165643898.png)]

=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
4),1,"")&56),0)))

的格式化結果為：

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")
        &
        56
      ),
      0
    )
  )
)

（文末有體驗網址）

不過接下來，將公布這套格式化程式的完整代碼和開發思想，有技術能力的小伙伴可以考慮改進該代碼，

完整代碼

__author__ = 'xiaoxiaoming'

from collections import deque
import re


class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

    def is_single_node(self):
        for e in self.data:
            if not isinstance(e, str):
                return False
        return True

    def get_single_text(self):
        return "".join(self.data)


def split_text_blocks(excel_func_text):
    """
    將excel公式字串，按照一定的規則切割成陣列
    :param excel_func_text: 被切割的excel公式字串
    :return: 切割后的結果
    """
    excel_func_text = excel_func_text.replace('\n', '').replace('\r', '')
    excel_func_text = re.sub(" +", " ", excel_func_text)
    lines = []
    i, j = 0, 0
    while j < len(excel_func_text):
        c = excel_func_text[j]
        if (c == '(' and excel_func_text[j + 1] != ')') or c == ',':
            lines.append(excel_func_text[i:j + 1])
            i = j = j + 1
        elif c == ')' and excel_func_text[j - 1] != '(':
            if i < j:
                lines.append(excel_func_text[i:j])
                i = j  # 起始檔案塊置于)處
            # 以下代碼查找,如果中間不包含(或)，則將)和,之間的文本塊加入到劃分結果
            k = excel_func_text.find(",", j + 1)
            l = excel_func_text.find("(", j + 1, k)
            m = excel_func_text.find(")", j + 1, k)
            if k != -1 and l == -1 and m == -1:
                lines.append(excel_func_text[i:k + 1])
                i = j = k + 1
            elif j + 1 < len(excel_func_text) and excel_func_text[j + 1] != ')':
                lines.append(")")
                lines.append(excel_func_text[j + 1])
                i = j = j + 2
            else:
                lines.append(")")
                i = j = j + 1
        elif c == '"':
            j = excel_func_text.find('"', j + 1) + 1
        else:
            j += 1
    return lines


blank_char_count = 2


def combine_node(root, text_max_length=60, max_combine_layer=3):
    """
    合并最內層的只有純文本子節點的節點為單個文本節點
    :param root: 被合并的節點
    :param text_max_length: 合并后的文本長度不超過該引數，則應用該合并替換原節點
    :param max_combine_layer: 最大合并層數
    :return:
    """
    for _ in range(max_combine_layer):
        no_change = True
        stack = deque([root])
        while stack:
            node = stack.pop()
            tmp = {}
            for i, e in enumerate(node.data):
                if isinstance(e, Node):
                    if e.is_single_node():
                        single_text = e.get_single_text()
                        if len(single_text) < text_max_length:
                            tmp[i] = single_text
                    else:
                        stack.append(e)
            for i, e in tmp.items():
                node.data[i] = e
            if len(tmp) != 0:
                no_change = False
        if no_change:
            break


def node_next_line(node):
    for i, e in enumerate(node.data):
        if isinstance(e, str):
            if i == 0 or i == len(node.data) - 1:
                tab = node.tab_size - 1
            else:
                tab = node.tab_size
            yield f"{' ' * blank_char_count * tab}{e}"
        else:
            yield from node_next_line(e)
            

def excel_func_format(excel_func_text, blank_count=2, combine_single_node=True, text_max_length=60,
                      max_combine_layer=3):
    """
    將excel公式格式化成比較容易閱讀的格式
    :param excel_func_text: 被格式化的excel公式字串
    :param blank_count: 最終顯示的格式化字串的1個tab用幾個空格表示
    :param combine_single_node: 是否合并純文本節點，該引數設定為True后面的引數才生效
    :param text_max_length: 合并后的文本長度不超過該引數，則應用該合并替換原節點
    :param max_combine_layer: 最大合并層數
    :return: 格式化后的字串
    """
    global blank_char_count
    blank_char_count = blank_count
    blocks = split_text_blocks(excel_func_text)
    # print("\n".join(blocks))
    # print('-----------拆分結果-----------')
    tab_size = 0
    node = root = Node()
    for block in blocks:
        if block.endswith("("):
            tab_size += 1
            child_node = Node(node, tab_size)
            node.data.append(child_node)
            node = child_node
            node.data.append(block)
        elif block.startswith(")"):
            tab_size -= 1
            node.data.append(block)
            node = node.parent
        else:
            node.data.append(block)
    if combine_single_node:
        combine_node(root, text_max_length, max_combine_layer)
    result = [line for line in node_next_line(root)]
    return "\n".join(result)

處理流程淺析

下面都以如下公式作為示例：

=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
4),1,"")&56),0)))

文本分塊切分

def split_text_blocks(excel_func_text):
    """
    將excel公式字串，按照一定的規則切割成陣列
    :param excel_func_text: 被切割的excel公式字串
    :return: 切割后的結果
    """
    excel_func_text = excel_func_text.replace('\n', '').replace('\r', '')
    excel_func_text = re.sub(" +", " ", excel_func_text)
    lines = []
    i, j = 0, 0
    while j < len(excel_func_text):
        c = excel_func_text[j]
        if (c == '(' and excel_func_text[j + 1] != ')') or c == ',':
            lines.append(excel_func_text[i:j + 1])
            i = j = j + 1
        elif c == ')' and excel_func_text[j - 1] != '(':
            if i < j:
                lines.append(excel_func_text[i:j])
                i = j  # 起始檔案塊置于)處
            # 以下代碼查找,如果中間不包含(或)，則將)和,之間的文本塊加入到劃分結果
            k = excel_func_text.find(",", j + 1)
            l = excel_func_text.find("(", j + 1, k)
            m = excel_func_text.find(")", j + 1, k)
            if k != -1 and l == -1 and m == -1:
                lines.append(excel_func_text[i:k + 1])
                i = j = k + 1
            elif j + 1 < len(excel_func_text) and excel_func_text[j + 1] != ')':
                lines.append(")")
                lines.append(excel_func_text[j + 1])
                i = j = j + 2
            else:
                lines.append(")")
                i = j = j + 1
        elif c == '"':
            j = excel_func_text.find('"', j + 1) + 1
        else:
            j += 1
    return lines

s = """=IF(ROW()>COLUMN(),"",IF(ROW()=COLUMN(),$B15,ROUNDDOWN($B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(),
        4),1,"")&56),0))) """

blocks = split_text_blocks(s)
for block in blocks:
    print(block)

的運行結果為：

=IF(
ROW()>COLUMN(),
"",
IF(
ROW()=COLUMN(),
$B15,
ROUNDDOWN(
$B15*INDIRECT(
SUBSTITUTE(
ADDRESS(
1,
3+COLUMN()-ROW(),
 4
),
1,
""
)
&
56
),
0
)
)
)

這端代碼首先替換掉所有的換行符，將多個空格替換為單個空格，然后將左右括號和逗號作為切分點進行切分，

但存在一些特殊情況，例如ROW()和COLUMN()括號內部沒有任何內容，所有這種括號應該作為普通字符處理，另外被""包含的字串可能包含括號，也應該作為普通字符，

構建多叉樹層次結構

設計資料結構：

class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

parent存盤父節點的指標，tab_size存盤當前節點的層級，data存盤當前節點的所有資料，

構建代碼：

tab_size = 0
node = root = Node()
for block in blocks:
    if block.endswith("("):
        tab_size += 1
        child_node = Node(node, tab_size)
        node.data.append(child_node)
        node = child_node
        node.data.append(block)
    elif block.startswith(")"):
        tab_size -= 1
        node.data.append(block)
        node = node.parent
    else:
        node.data.append(block)

構建完畢后，這段資料在記憶體中的結構（僅展示data）如下:

遍歷列印這顆多叉樹

def node_next_line(node):
    for i, e in enumerate(node.data):
        if isinstance(e, str):
            if i == 0 or i == len(node.data) - 1:
                tab = node.tab_size - 1
            else:
                tab = node.tab_size
            yield f"{' ' * 2 * tab}{e}"
        else:
            yield from node_next_line(e)
            
result = [line for line in node_next_line(root)]
print("\n".join(result))

結果：

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(
          ADDRESS(
            1,
            3+COLUMN()-ROW(),
             4
          ),
          1,
          ""
        )
        &
        56
      ),
      0
    )
  )
)

合并最內層的節點

顯然將最內層的node5節點合并一下閱讀性更好：

首先給資料結構增加判斷是否為純文本節點的方法：

class Node:
    def __init__(self, parent=None, tab_size=0):
        self.parent = parent
        self.tab_size = tab_size
        self.data = []

    def is_single_node(self):
        for e in self.data:
            if not isinstance(e, str):
                return False
        return True

    def get_single_text(self):
        return "".join(self.data)

下面是合并純文本節點的實作，max_combine_layer決定了合并的最大次數，如果合并后長度超過text_max_length引數，則不應用這次合并：

from collections import deque

def combine_node(root, text_max_length=60, max_combine_layer=3):
    """
    合并最內層的只有純文本子節點的節點為單個文本節點
    :param root: 被合并的節點
    :param text_max_length: 合并后的文本長度不超過該引數，則應用該合并替換原節點
    :param max_combine_layer: 最大合并層數
    :return:
    """
    for _ in range(max_combine_layer):
        no_change = True
        stack = deque([root])
        while stack:
            node = stack.pop()
            tmp = {}
            for i, e in enumerate(node.data):
                if isinstance(e, Node):
                    if e.is_single_node():
                        single_text = e.get_single_text()
                        if len(single_text) < text_max_length:
                            tmp[i] = single_text
                    else:
                        stack.append(e)
            for i, e in tmp.items():
                node.data[i] = e
            if len(tmp) != 0:
                no_change = False
        if no_change:
            break

合并一次：

combine_node(root, 100, 1)
result = [line for line in node_next_line(root)]
print("\n".join(result))

結果：

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(
          ADDRESS(1,3+COLUMN()-ROW(), 4),
          1,
          ""
        )
        &
        56
      ),
      0
    )
  )
)

合并二次：

combine_node(root, 100, 2)
result = [line for line in node_next_line(root)]
print("\n".join(result))

結果：

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(
        SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")
        &
        56
      ),
      0
    )
  )
)

合并三次：

combine_node(root, 100, 3)
result = [line for line in node_next_line(root)]
print("\n".join(result))

結果：

=IF(
  ROW()>COLUMN(),
  "",
  IF(
    ROW()=COLUMN(),
    $B15,
    ROUNDDOWN(
      $B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")&56),
      0
    )
  )
)

合并三次后的記憶體情況：

體驗網址

http://xiaoxiaoming.xyz:8088/excel

不保證永久有效，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/239052.html

標籤：python

上一篇：python基本資料型別

下一篇：v4_Python-流程控制 if判斷