使用Python将数据写入Excel文件,并将文本保存在精确的单元格中。

huangapple go评论72阅读模式
英文:

Write data into excel files using python and save text in exact cells

问题

我想要问一下,我如何将字符串写入Excel文件以在输出Excel中看到计算结果。

示例:

我想在列L中的单元格L6中放入这个字符串:=K6-L2+L5,并将其迭代到如下图片所示的整个表格中:

使用Python将数据写入Excel文件,并将文本保存在精确的单元格中。

这些单元格中的逻辑如下:

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:

使用Python将数据写入Excel文件,并将文本保存在精确的单元格中。

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=&#39;foo.xlsx&#39;)
ws = wb[&#39;Sheet1&#39;]

orig_formula = &quot;=K6-L2+L5&quot;  # L is col 10
start_col = &#39;L&#39;
row = 6

### Initial cell formula (&#39;L6&#39;)
orig_cell = f&#39;{start_col}{row}&#39;

### 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&quot;{gcl(col)}{cur_row}&quot;
        ws[dst_cell] = Translator(orig_formula, origin=orig_cell).translate_formula(dst_cell)

wb.save(&#39;foo_out.xlsx&#39;)

huangapple
  • 本文由 发表于 2023年7月20日 21:10:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76730270.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定