回答編集履歴
1
修正
test
CHANGED
@@ -1,59 +1,160 @@
|
|
1
|
+
|
2
|
+
コードにしてみました。
|
1
|
-
|
3
|
+
同一ブック内の別シート参照まで再帰的に参照セルを探します。
|
4
|
+
別ブックへの参照はそれ以上検索しません。(openpyxlでは正しくブックパスが取得できない模様)
|
2
|
-
なお、計算式の解析は
|
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_c
|
33
|
+
def get_ref_cells(ws_name, formula):
|
9
34
|
tok = Tokenizer(formula)
|
10
|
-
c
|
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
|
-
|
39
|
+
key = get_key(t.value)
|
23
40
|
|
24
|
-
#
|
41
|
+
# シート名がなければセット
|
25
|
-
|
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,
|
58
|
+
if isinstance(cells,Iterable): # セル範囲
|
59
|
+
tmp = []
|
28
|
-
|
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_
|
77
|
+
ws_name = cell.parent.title
|
34
|
-
key = (ws_
|
78
|
+
key = ('','',ws_name, cell.coordinate) # 各セルを識別するキー
|
35
79
|
|
36
|
-
# 計算式で未取得
|
80
|
+
# 計算式で未取得なら式内の参照セルを取得
|
37
|
-
if cell.data_type != 'f' or key in l
|
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
|
-
|
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=
|
105
|
+
wb = load_workbook('test.xlsx', read_only=False)
|
106
|
+
|
48
|
-
cells = wb['Sheet1']['
|
107
|
+
cells = wb['Sheet1']['B1:B20'] # 取得範囲セル
|
49
|
-
l
|
108
|
+
results = dict()
|
50
|
-
get_
|
109
|
+
get_refs(wb, cells, results)
|
110
|
+
|
111
|
+
for c in results.items():
|
51
|
-
print(
|
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
|
+
|