python實(shí)現(xiàn)excel公式格式化的示例代碼

更新時(shí)間：2020年12月23日 11:16:41 作者：代碼實(shí)體

這篇文章主要介紹了python實(shí)現(xiàn)excel公式格式化的示例代碼，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

之前跟一些小伙伴有個(gè)討論：

大概就是很多跟數(shù)據(jù)打交道的朋友都面對(duì)過(guò)很復(fù)雜的excel公式，有時(shí)嵌套層數(shù)特別多，肉眼觀看很容易蒙圈。
有了這樣的需求，我就有了解決問(wèn)題的想法，說(shuō)干就干，于是一個(gè)比較牛逼的excel公式格式化的工具就出現(xiàn)了。

效果體驗(yàn)

先看看效果吧：

=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%))

的格式化結(jié)果是：

=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))

的格式化結(jié)果為：

(
 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
 )
)

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

的格式化結(jié)果為：

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

（文末有體驗(yàn)網(wǎng)址）

不過(guò)接下來(lái)，將公布這套格式化程序的完整代碼和開(kāi)發(fā)思想，有技術(shù)能力的小伙伴可以考慮改進(jìn)該代碼。

完整代碼

__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公式字符串，按照一定的規(guī)則切割成數(shù)組
  :param excel_func_text: 被切割的excel公式字符串
  :return: 切割后的結(jié)果
  """
  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 # 起始文件塊置于)處
      # 以下代碼查找,如果中間不包含(或)，則將)和,之間的文本塊加入到劃分結(jié)果
      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):
  """
  合并最內(nèi)層的只有純文本子節(jié)點(diǎn)的節(jié)點(diǎn)為單個(gè)文本節(jié)點(diǎn)
  :param root: 被合并的節(jié)點(diǎn)
  :param text_max_length: 合并后的文本長(zhǎng)度不超過(guò)該參數(shù)，則應(yīng)用該合并替換原節(jié)點(diǎn)
  :param max_combine_layer: 最大合并層數(shù)
  :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個(gè)tab用幾個(gè)空格表示
  :param combine_single_node: 是否合并純文本節(jié)點(diǎn)，該參數(shù)設(shè)置為T(mén)rue后面的參數(shù)才生效
  :param text_max_length: 合并后的文本長(zhǎng)度不超過(guò)該參數(shù)，則應(yīng)用該合并替換原節(jié)點(diǎn)
  :param max_combine_layer: 最大合并層數(shù)
  :return: 格式化后的字符串
  """
  global blank_char_count
  blank_char_count = blank_count
  blocks = split_text_blocks(excel_func_text)
  # print("\n".join(blocks))
  # print('-----------拆分結(jié)果-----------')
  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公式字符串，按照一定的規(guī)則切割成數(shù)組
  :param excel_func_text: 被切割的excel公式字符串
  :return: 切割后的結(jié)果
  """
  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 # 起始文件塊置于)處
      # 以下代碼查找,如果中間不包含(或)，則將)和,之間的文本塊加入到劃分結(jié)果
      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)

的運(yùn)行結(jié)果為：

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

這端代碼首先替換掉所有的換行符，將多個(gè)空格替換為單個(gè)空格，然后將左右括號(hào)和逗號(hào)作為切分點(diǎn)進(jìn)行切分。

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

構(gòu)建多叉樹(shù)層次結(jié)構(gòu)

設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)：

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

parent存儲(chǔ)父節(jié)點(diǎn)的指針，tab_size存儲(chǔ)當(dāng)前節(jié)點(diǎn)的層級(jí)，data存儲(chǔ)當(dāng)前節(jié)點(diǎn)的所有數(shù)據(jù)。

構(gòu)建代碼：

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)

構(gòu)建完畢后，這段數(shù)據(jù)在內(nèi)存中的結(jié)構(gòu)（僅展示data）如下:

遍歷打印這顆多叉樹(shù)

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))

結(jié)果：

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

合并最內(nèi)層的節(jié)點(diǎn)

顯然將最內(nèi)層的node5節(jié)點(diǎn)合并一下閱讀性更好：

首先給數(shù)據(jù)結(jié)構(gòu)增加判斷是否為純文本節(jié)點(diǎn)的方法：

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)

下面是合并純文本節(jié)點(diǎn)的實(shí)現(xiàn)，max_combine_layer決定了合并的最大次數(shù)，如果合并后長(zhǎng)度超過(guò)text_max_length參數(shù)，則不應(yīng)用這次合并：

from collections import deque

def combine_node(root, text_max_length=60, max_combine_layer=3):
  """
  合并最內(nèi)層的只有純文本子節(jié)點(diǎn)的節(jié)點(diǎn)為單個(gè)文本節(jié)點(diǎn)
  :param root: 被合并的節(jié)點(diǎn)
  :param text_max_length: 合并后的文本長(zhǎng)度不超過(guò)該參數(shù)，則應(yīng)用該合并替換原節(jié)點(diǎn)
  :param max_combine_layer: 最大合并層數(shù)
  :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))

結(jié)果：

=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))

結(jié)果：

=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))

結(jié)果：

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

合并三次后的內(nèi)存情況：