python excel公式フォーマットを実現するコードの例


前にいくつかの子供と議論がありました。
image-20201214091536248
データに関わる多くの友達が複雑なエクセルの公式に直面しています。時にはネストの層数が多く、肉眼で見てもよく見えます。
このような需要があったら、問題を解決する考えがあります。やればできると言って、牛より強い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%
 )
)
image-20201214165452189

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

=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
  )
 )
)
image-20201214165926821
(文末体験URLがあります)
しかし、次はこのフォーマットプログラムの完全なコードと開発思想を発表します。
完全コード

__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('
', '').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("
".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 "
".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('
', '').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のみを示す)は以下の通りである。
image-20201214180114188
この多叉樹を巡回印刷します。

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("
".join(result))
結果:

=IF(
 ROW()>COLUMN(),
 "",
 IF(
  ROW()=COLUMN(),
  $B15,
  ROUNDDOWN(
   $B15*INDIRECT(
    SUBSTITUTE(
     ADDRESS(
      1,
      3+COLUMN()-ROW(),
       4
     ),
     1,
     ""
    )
    &
    56
   ),
   0
  )
 )
)
一番内側のノードをマージ
明らかに一番内側のノードをまとめて読むのが良いです。
image-20201214181546248
まずデータ構造に追加して、テキストノードかどうかを判断する方法:

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("
".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("
".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("
".join(result))
結果:

=IF(
 ROW()>COLUMN(),
 "",
 IF(
  ROW()=COLUMN(),
  $B15,
  ROUNDDOWN(
   $B15*INDIRECT(SUBSTITUTE(ADDRESS(1,3+COLUMN()-ROW(), 4),1,"")&56),
   0
  )
 )
)
三回結合した後のメモリの状況:
image-20201214182511540
体験サイト
http://xiaoxiaoming.xyz:8088/excel
永久有効は保証されません。
ここでは、pythonのexcel公式フォーマットを実現するためのコード例についての記事を紹介します。python excel公式フォーマットの内容については、以前の文章を検索したり、下記の関連記事を引き続き閲覧したりしてください。これからもよろしくお願いします。