回答編集履歴

1

修正

2022/10/03 05:42

投稿

can110
can110

スコア38234

test CHANGED
@@ -1,59 +1,160 @@
1
+
2
+ コードにしてみました。
1
- 粗削りですがコードにしてみました。同一ブック内シートをまたぐ参照まで対応ています。
3
+ 同一ブック内の別シート参照まで再帰的に参照セルを探します。
4
+ 別ブックへの参照はそれ以上検索しません。(openpyxlでは正しくブックパスが取得できない模様)
2
- なお、計算式の解析は[Parsing Formulas](https://openpyxl.readthedocs.io/en/latest/formula.html#parsing-formulas)を参考にしました。
5
+ なお、計算式の解析はParsing Formulasを参考にしました。
3
6
  ```Python
7
+ from typing import Iterable
8
+ import re
4
9
  from openpyxl import load_workbook
5
10
  from openpyxl.formula import Tokenizer
6
11
 
12
+
13
+ # セル範囲を表現する文字列からセルを識別するキー
14
+ # [親ディレクトリ名, ブック名, シート名, セル名]を得る
15
+ def get_key(exp):
16
+ # 'C:hoge\[test2.xlsx]Sheet1'!$A$1
17
+ m = re.match(r"'(.+?)\[(.+?)\](.+?)'\!(.+)", exp)
18
+ if m:
19
+ return list(m.groups())
20
+ # [test2.xlsx]Sheet1!$A$1
21
+ m = re.match(r"\[(.+?)\](.+?)\!(.+)", exp)
22
+ if m:
23
+ return [''] + list(m.groups())
24
+ # Sheet1!A1 Sheet1!#REF!
25
+ m = re.match(r"(.+?)\!(.+)", exp)
26
+ if m:
27
+ return ['',''] + list(m.groups())
28
+ # A1
29
+ return ['','','',exp]
30
+
31
+
7
- # 計算式に含まれる子供(セル)を取得
32
+ # 計算式に含まれる参照セル(を示すキーリストを取得
8
- def get_children(ws_title, formula):
33
+ def get_ref_cells(ws_name, formula):
9
34
  tok = Tokenizer(formula)
10
- children = []
35
+ ref_cells = []
11
36
  for t in tok.items:
12
37
  if t.type == 'OPERAND' and t.subtype == 'RANGE':
13
- # 「A1」や「Sheet2!A1」など
14
- ts = t.value.split('!')
15
- if len(ts) > 1: # 「Sheet2!A1」
16
- title, coordinate = ts
17
- else: # 「A1」
18
- title, coordinate = ws_title, t.value
19
- coordinate = coordinate.replace('$', '') # 絶対参照の正規化
20
- children.append((title, coordinate))
21
38
 
22
- return children
39
+ key = get_key(t.value)
23
40
 
24
- # 指定さた単一ルorセル範囲の計算式を解析
41
+ # シート名がなけット
25
- def get_link(wb, cells, links):
42
+ if not key[2]:
43
+ key[2] = ws_name
26
44
 
45
+ # 絶対参照の正規化
46
+ key[3] = key[3].replace('$', '')
47
+
48
+ ref_cells.append(tuple(key))
49
+
50
+ return ref_cells
51
+
52
+
53
+ # セル(範囲)を一次元リストに展開
54
+ # 単一セル(A1) A1
55
+ # 行列範囲(A1:B2) ((A1,B1),(A2,B2))
56
+ # 行範囲 (1:1) (A1,A2,A3)
57
+ def flatten(cells):
27
- if isinstance(cells, tuple): # セル範囲
58
+ if isinstance(cells,Iterable): # セル範囲
59
+ tmp = []
28
- cells = [c[0] for c in cells]
60
+ for row in cells:
61
+ if isinstance(row,Iterable):
62
+ tmp += row
63
+ else:
64
+ tmp += [row]
65
+ cells = tmp
29
- else: # 単一セル
66
+ else: # 単一セル A1
30
67
  cells = [cells]
31
68
 
69
+ return cells
70
+
71
+
72
+ # 指定された単一セルorセル範囲の計算式から参照セルを取得
73
+ def get_refs(wb, cells, results):
74
+
75
+ cells = flatten(cells)
32
76
  for cell in cells:
33
- ws_title = cell.parent.title
77
+ ws_name = cell.parent.title
34
- key = (ws_title, cell.coordinate) # 各セルは(シート名, セル名)で管理する
78
+ key = ('','',ws_name, cell.coordinate) # 各セルを識別するキー
35
79
 
36
- # 計算式で未取得
80
+ # 計算式で未取得なら式内の参照セルを取得
37
- if cell.data_type != 'f' or key in links:
81
+ if cell.data_type != 'f' or key in results:
38
82
  continue
83
+ formula = cell.value
84
+ ref_cells = get_ref_cells(ws_name, formula)
39
85
 
40
- children = get_children(ws_title, cell.value)
86
+ results[key] = {'formula':formula, 'refs':ref_cells}
41
- links[key] = children
42
- # 子供(単一セルorセル範囲)毎に再帰解析
43
- for child in children:
44
- title, coordnate = child
45
- get_link(wb, wb[title][coordnate], links)
46
87
 
88
+ # 再帰取得
89
+ for ref_cell in ref_cells:
90
+ # このブック内で参照エラーではないセル
91
+ dir_name, wb_name, ws_name, coord = ref_cell
92
+ if not dir_name and not wb_name and not coord.startswith('#'):
93
+ try:
94
+ cur_refs = wb[ws_name][coord]
95
+ get_refs(wb, cur_refs, results)
96
+ except AttributeError as e:
97
+ print(e)
98
+ pass
99
+
100
+
101
+
102
+ # 「A:A」のような列全体も取得するにはread_only=Falseである必要あり
103
+ # Iterate through columns in Read-only workbook in openpyxl
104
+ # https://stackoverflow.com/questions/47582274/iterate-through-columns-in-read-only-workbook-in-openpyxl
47
- wb = load_workbook('test.xlsx', read_only=True)
105
+ wb = load_workbook('test.xlsx', read_only=False)
106
+
48
- cells = wb['Sheet1']['A1:B4'] # 解析範囲セル
107
+ cells = wb['Sheet1']['B1:B20'] # 取得範囲セル
49
- links =dict()
108
+ results = dict()
50
- get_link(wb, cells, links)
109
+ get_refs(wb, cells, results)
110
+
111
+ for c in results.items():
51
- print(links)
112
+ print(c)
52
- """
53
- {
54
- ('Sheet1', 'A1'): [('Sheet1', 'B1'), ('Sheet1', 'C1')],
55
- ('Sheet1', 'C1'): [('Sheet1', 'D1')],
56
- ('Sheet1', 'A2'): [('Sheet2', 'A1')]
57
- }
58
- """
59
113
  ```
114
+ .xlsx例
115
+ | | A | B | C | D | E | F | G | H |
116
+ |---:|:-------------------|:----------------------------------|:-------|:-----|:-----|:-----|:-----|:----|
117
+ | 0 | 実値 | 123 | | | | | | |
118
+ | 1 | 単純な計算式 | =C2 | 123 | | | | | |
119
+ | 2 | 別シートを参照 | =Sheet2!A1 | | | | | | |
120
+ | 3 | 多段で参照 | =C4+D4+E4 | =D4+E4 | =E4 | 1 | | | |
121
+ | 4 | 参照先が行範囲 | =SUM(Sheet2!A1:E1) | | | | | | |
122
+ | 5 | 参照先が列範囲 | =SUM(Sheet2!A1:A5) | | | | | | |
123
+ | 6 | 参照先が行全体 | =SUM(Sheet2!1:1) | | | | | | |
124
+ | 7 | 参照先が列全体 | =SUM(Sheet2!A:A) | | | | | | |
125
+ | 8 | 参照先が行×列範囲 | =SUM(D9:E11) | | =F9 | =G9 | 12.0 | 34.0 | |
126
+ | 9 | 参照先が飛びセル | =SUM(C10,E10) | 12 | | 34 | | | |
127
+ | 10 | 参照元が結合セル | =D11+E11 | | =F11 | =G11 | 56.0 | 78.0 | |
128
+ | 11 | 参照先が結合セル | =D12 | | 123 | | | | |
129
+ | 12 | 両方が結合セル | =D13 | | 456 | | | | |
130
+ | 13 | 削除された別シート | =#REF! | | | | | | |
131
+ | 14 | 参照先が範囲外 | =SUM(Sheet2!#REF!) | | | | | | |
132
+ | 15 | 参照先が別ブック | ='C:hoge\[test2.xlsx]Sheet1'!$A$1 | | | | | | |
133
+ | 16 | NAME? | =SUMM(A1) | | | | | | |
134
+
135
+ 結果例
136
+ ```PlainText
137
+ (('', '', 'Sheet1', 'B2'), {'formula': '=C2', 'refs': [('', '', 'Sheet1', 'C2')]})
138
+ (('', '', 'Sheet1', 'B3'), {'formula': '=Sheet2!A1', 'refs': [('', '', 'Sheet2', 'A1')]})
139
+ (('', '', 'Sheet1', 'B4'), {'formula': '=C4+D4+E4', 'refs': [('', '', 'Sheet1', 'C4'), ('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
140
+ (('', '', 'Sheet1', 'C4'), {'formula': '=D4+E4', 'refs': [('', '', 'Sheet1', 'D4'), ('', '', 'Sheet1', 'E4')]})
141
+ (('', '', 'Sheet1', 'D4'), {'formula': '=E4', 'refs': [('', '', 'Sheet1', 'E4')]})
142
+ (('', '', 'Sheet1', 'B5'), {'formula': '=SUM(Sheet2!A1:E1)', 'refs': [('', '', 'Sheet2', 'A1:E1')]})
143
+ (('', '', 'Sheet1', 'B6'), {'formula': '=SUM(Sheet2!A1:A5)', 'refs': [('', '', 'Sheet2', 'A1:A5')]})
144
+ (('', '', 'Sheet1', 'B7'), {'formula': '=SUM(Sheet2!1:1)', 'refs': [('', '', 'Sheet2', '1:1')]})
145
+ (('', '', 'Sheet1', 'B8'), {'formula': '=SUM(Sheet2!A:A)', 'refs': [('', '', 'Sheet2', 'A:A')]})
146
+ (('', '', 'Sheet1', 'B9'), {'formula': '=SUM(D9:E11)', 'refs': [('', '', 'Sheet1', 'D9:E11')]})
147
+ (('', '', 'Sheet1', 'D9'), {'formula': '=F9', 'refs': [('', '', 'Sheet1', 'F9')]})
148
+ (('', '', 'Sheet1', 'E9'), {'formula': '=G9', 'refs': [('', '', 'Sheet1', 'G9')]})
149
+ (('', '', 'Sheet1', 'D11'), {'formula': '=F11', 'refs': [('', '', 'Sheet1', 'F11')]})
150
+ (('', '', 'Sheet1', 'E11'), {'formula': '=G11', 'refs': [('', '', 'Sheet1', 'G11')]})
151
+ (('', '', 'Sheet1', 'B10'), {'formula': '=SUM(C10,E10)', 'refs': [('', '', 'Sheet1', 'C10'), ('', '', 'Sheet1', 'E10')]})
152
+ (('', '', 'Sheet1', 'B11'), {'formula': '=D11+E11', 'refs': [('', '', 'Sheet1', 'D11'), ('', '', 'Sheet1', 'E11')]})
153
+ (('', '', 'Sheet1', 'B12'), {'formula': '=D12', 'refs': [('', '', 'Sheet1', 'D12')]})
154
+ (('', '', 'Sheet1', 'B13'), {'formula': '=D13', 'refs': [('', '', 'Sheet1', 'D13')]})
155
+ (('', '', 'Sheet1', 'B14'), {'formula': '=#REF!', 'refs': []})
156
+ (('', '', 'Sheet1', 'B15'), {'formula': '=SUM(Sheet2!#REF!)', 'refs': [('', '', 'Sheet2', '#REF!')]})
157
+ (('', '', 'Sheet1', 'B16'), {'formula': '=[1]Sheet1!$A$1', 'refs': [('', '1', 'Sheet1', 'A1')]})
158
+ (('', '', 'Sheet1', 'B17'), {'formula': '=SUMM(A1)', 'refs': [('', '', 'Sheet1', 'A1')]})
159
+ ```
160
+