回答編集履歴
3
test
CHANGED
|
@@ -5,6 +5,7 @@
|
|
|
5
5
|
from openpyxl.styles import PatternFill, Font
|
|
6
6
|
from openpyxl.worksheet.cell_range import CellRange
|
|
7
7
|
|
|
8
|
+
# Excel formatting rule
|
|
8
9
|
wb = openpyxl.load_workbook('data.xlsx')
|
|
9
10
|
ws = wb['データ']
|
|
10
11
|
max_row, max_col = ws.max_row, ws.max_column
|
|
@@ -14,8 +15,7 @@
|
|
|
14
15
|
dxf = DifferentialStyle(font=font, fill=fill)
|
|
15
16
|
for r in range(start_row, ws.max_row+1):
|
|
16
17
|
rg = CellRange(min_col=start_col, min_row=r, max_col=max_col, max_row=r)
|
|
17
|
-
vrg = ':'.join(f'${i}' for i in rg.coord.split(':'))
|
|
18
|
-
rule = Rule(type='cellIs', operator='greaterThanOrEqual', formula=[f'=AVERAGE({
|
|
18
|
+
rule = Rule(type='cellIs', operator='greaterThanOrEqual', formula=[f'=AVERAGE(${rg})'], dxf=dxf)
|
|
19
19
|
ws.conditional_formatting.add(str(rg), rule)
|
|
20
20
|
|
|
21
21
|
wb.save('output.xlsx')
|
2
test
CHANGED
|
@@ -7,12 +7,13 @@
|
|
|
7
7
|
|
|
8
8
|
wb = openpyxl.load_workbook('data.xlsx')
|
|
9
9
|
ws = wb['データ']
|
|
10
|
-
max_row, max_col
|
|
10
|
+
max_row, max_col = ws.max_row, ws.max_column
|
|
11
|
+
start_row, start_col = 2, 2
|
|
11
12
|
font = Font(color='000000')
|
|
12
13
|
fill = PatternFill(bgColor='FF0000', patternType='solid')
|
|
13
14
|
dxf = DifferentialStyle(font=font, fill=fill)
|
|
14
15
|
for r in range(start_row, ws.max_row+1):
|
|
15
|
-
rg = CellRange(min_col=
|
|
16
|
+
rg = CellRange(min_col=start_col, min_row=r, max_col=max_col, max_row=r)
|
|
16
17
|
vrg = ':'.join(f'${i}' for i in rg.coord.split(':'))
|
|
17
18
|
rule = Rule(type='cellIs', operator='greaterThanOrEqual', formula=[f'=AVERAGE({vrg})'], dxf=dxf)
|
|
18
19
|
ws.conditional_formatting.add(str(rg), rule)
|
1
test
CHANGED
|
@@ -4,27 +4,18 @@
|
|
|
4
4
|
from openpyxl.styles.differential import DifferentialStyle
|
|
5
5
|
from openpyxl.styles import PatternFill, Font
|
|
6
6
|
from openpyxl.worksheet.cell_range import CellRange
|
|
7
|
-
import pandas as pd
|
|
8
7
|
|
|
9
|
-
# Excel spreadsheet
|
|
10
|
-
excel_file = 'data.xlsx'
|
|
11
|
-
|
|
12
|
-
# rowwise mean values
|
|
13
|
-
df = pd.read_excel(excel_file)
|
|
14
|
-
mean = df.iloc[:, 1:].mean(axis=1)
|
|
15
|
-
|
|
16
|
-
# Excel formatting rule
|
|
17
|
-
wb = openpyxl.load_workbook(
|
|
8
|
+
wb = openpyxl.load_workbook('data.xlsx')
|
|
18
9
|
ws = wb['データ']
|
|
19
|
-
max_row, max_col = ws.max_row, ws.max_column
|
|
10
|
+
max_row, max_col, start_row = ws.max_row, ws.max_column, 2
|
|
20
11
|
font = Font(color='000000')
|
|
21
12
|
fill = PatternFill(bgColor='FF0000', patternType='solid')
|
|
22
13
|
dxf = DifferentialStyle(font=font, fill=fill)
|
|
23
|
-
for r in range(
|
|
14
|
+
for r in range(start_row, ws.max_row+1):
|
|
24
15
|
rg = CellRange(min_col=2, min_row=r, max_col=max_col, max_row=r)
|
|
16
|
+
vrg = ':'.join(f'${i}' for i in rg.coord.split(':'))
|
|
25
|
-
rule = Rule(type='cellIs', operator='greaterThanOrEqual', formula=[
|
|
17
|
+
rule = Rule(type='cellIs', operator='greaterThanOrEqual', formula=[f'=AVERAGE({vrg})'], dxf=dxf)
|
|
26
18
|
ws.conditional_formatting.add(str(rg), rule)
|
|
27
19
|
|
|
28
|
-
# save to new file
|
|
29
20
|
wb.save('output.xlsx')
|
|
30
21
|
```
|