英文:
Write data into excel files using python and save text in exact cells
问题
我想要问一下,我如何将字符串写入Excel文件以在输出Excel中看到计算结果。
示例:
我想在列L中的单元格L6中放入这个字符串:=K6-L2+L5,并将其迭代到如下图片所示的整个表格中:
这些单元格中的逻辑如下:
L6 = K6 - L2 + L5
M6 = L6 - M2 + M5
N6 = M6 - N2 + N5
O6 = N6 - O2 + O5
并且应该在所有具有类型E的列中都存在。
我尝试过这样做,但我的代码不会对文件进行任何更改。
import openpyxl
wb = openpyxl.load_workbook(filename='newfile.xlsx')
ws = wb.worksheets[0]
sb = [i for i in range(6,len(out),5)] #E cells
total = [i for i in range(2,len(out),5)]#A cells
dvl = [i for i in range(5,len(out),5)]#D cells
columns_names = ['K','L','M','N','O','P','R','S','T','U','V','W','X','Y','Z','AA','AB']
colNr = len(columns_names)
for i in sb:
ws[f'K{i}'] = f'=G{i}';
for i in range(0,len(sb)):
for cellnr in range(0,colNr):
try:
ws[f'{columns_names[cellnr+1]}{sb[i]}'] = f'{columns_names[cellnr]}{sb[i]} - {columns_names[cellnr+1]}{total[i]} + {columns_names[cellnr+1]}{dvl[i]}';
except:
pass
wb.save(filename='test.xlsx')
以下是数据:
类型 2023/07 2023/08 2023/09 2023/10 2023/11 2023/12 2024/01 2024/02 2024/03 2024/04 2024/05 2024/06 2024/07 2024/08 2024/09 2024/10 2024/11 2024/12
A 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 127 124 121 118 115 112 109 106 103 100 97 94 91 88 85 82 79 76
A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633
A 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 1637 1632 1627 1622 1617 1612 1607 1602 1597 1592 1587 1582 1577 1572 1567 1562 1557 1552
A 1 49 49 61 37 37 37 25 37 25 25 25 49 49 37 49 37 13
B 0 48 48 60 36 36 36 24 36 24 24 24 48 48 36 48 36 12
C 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
D 10000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 1023 974 925 864 827 790 753 728 691 666 641 616 567 518 481 432 395 382
英文:
I want to ask how I can write into an Excel file string to see the calculation in output Excel.
example:
I would like to have in column L in cell L6 this string: =K6-L2+L5 and iterate it through the whole table as it is in the picture below:
The logic in those cells is like that:
L6 = K6 - L2 + L5
M6 = L6 - M2 + M5
N6 = M6 - N2 + N5
O6 = N6 - O2 + O5
and it should be in every column that has a type E.
I've tried like that but my code does not create any changes in the file.
import openpyxl
wb = openpyxl.load_workbook(filename='newfile.xlsx')
ws = wb.worksheets[0]
sb = [i for i in range(6,len(out),5)] #E cells
total = [i for i in range(2,len(out),5)]#A cells
dvl = [i for i in range(5,len(out),5)]#D cells
columns_names = ['K','L','M','N','O','P','R','S','T','U','V','W','X','Y','Z','AA','AB']
colNr = len(columns_names)
for i in sb:
ws[f'K{i}'] = f'=G{i}'
for i in range(0,len(sb)):
for cellnr in range(0,colNr):
try:
ws[f'{columns_names[cellnr+1]}{sb[i]}'] = f'={columns_names[cellnr]}{sb[i]} - {columns_names[cellnr+1]}{total[i]} + {columns_names[cellnr+1]}{dvl[i]}'
except:
pass
wb.save(filename='test.xlsx')
Here are the data:
Type 2023/07 2023/08 2023/09 2023/10 2023/11 2023/12 2024/01 2024/02 2024/03 2024/04 2024/05 2024/06 2024/07 2024/08 2024/09 2024/10 2024/11 2024/12
A 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 127 124 121 118 115 112 109 106 103 100 97 94 91 88 85 82 79 76
A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633 3633
A 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5
B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 1637 1632 1627 1622 1617 1612 1607 1602 1597 1592 1587 1582 1577 1572 1567 1562 1557 1552
A 1 49 49 61 37 37 37 25 37 25 25 25 49 49 37 49 37 13
B 0 48 48 60 36 36 36 24 36 24 24 24 48 48 36 48 36 12
C 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
D 10000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E 1023 974 925 864 827 790 753 728 691 666 641 616 567 518 481 432 395 382
答案1
得分: 1
你可以使用Openpyxl翻译器来复制/更新整行中的公式。<br>
在单元格L6中插入初始公式
=K6-L2+L5
然后在循环中使用该公式来更新其余的单元格;
示例代码
import openpyxl
from openpyxl.formula.translate import Translator
from openpyxl.utils.cell import get_column_letter as gcl
from openpyxl.utils.cell import column_index_from_string as cifs
wb = openpyxl.load_workbook(filename='foo.xlsx')
ws = wb['Sheet1']
orig_formula = "=K6-L2+L5" # L是列10
start_col = 'L'
row = 6
### 初始单元格公式('L6')
orig_cell = f'{start_col}{row}'
### 复制范围从列L开始
rng_start = cifs(start_col)
### 使用Openpyxl翻译器更新第6、11、16、21行中的所有单元格的公式
### 循环插入每一列中的每一行
for col in range(rng_start, ws.max_column + 1):
for cur_row in range(6, ws.max_row+1, 5):
dst_cell = f"{gcl(col)}{cur_row}"
ws[dst_cell] = Translator(orig_formula, origin=orig_cell).translate_formula(dst_cell)
wb.save('foo_out.xlsx')
英文:
You can use the Openpyxl translator to copy/update the formula across the row.<br>
Insert the initial formula
=K6-L2+L5
into cell L6 then use that in a loop to update the rest;
Sample Code
import openpyxl
from openpyxl.formula.translate import Translator
from openpyxl.utils.cell import get_column_letter as gcl
from openpyxl.utils.cell import column_index_from_string as cifs
wb = openpyxl.load_workbook(filename='foo.xlsx')
ws = wb['Sheet1']
orig_formula = "=K6-L2+L5" # L is col 10
start_col = 'L'
row = 6
### Initial cell formula ('L6')
orig_cell = f'{start_col}{row}'
### Copy range starts at column L
rng_start = cifs(start_col)
### Use the Openpyxl Translator to update all the cells in row 6, 11, 16, 21 with the formula
### Loop inserts into each row per column
for col in range(rng_start, ws.max_column + 1):
for cur_row in range(6, ws.max_row+1, 5):
dst_cell = f"{gcl(col)}{cur_row}"
ws[dst_cell] = Translator(orig_formula, origin=orig_cell).translate_formula(dst_cell)
wb.save('foo_out.xlsx')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论