英文:
How can I edit my python code to prevent .xlsx file form saying "We found a problem with some content in file.xlsx" when opened?
问题
当我打开我的 .xlsx 文件时，会出现 "We found a problem with some content in file.xlsx..." 的提示。一旦修复后，它会显示 "Removed Records: Formula from /xl/worksheets/sheet1.xml part."。
它正在移除我通过以下 Python 代码添加的 vlookup 公式：
wb = load_workbook(f'{working_folder}\\{final_excel_file_name}')
sheet1_name = '-'.join(re.findall(r'[0-9]+', str(wb.worksheets[0])))
sheet2_name = '-'.join(re.findall(r'[0-9]+', str(wb.worksheets[1])))
sheet1 = wb[sheet1_name]
sheet2 = wb[sheet2_name]
sheet1['Q1'] = ''
for cell in sheet1['Q']:
cell.value = "=VLOOKUP(I{0},'" + sheet2_name + "'!I:I,1,FALSE)"
wb.save(f'{working_folder}\\test.xlsx')
有没有一种方法可以格式化我的 cell.value，以防止 Excel 在打开时想要删除它？
此外，如果您对如何改进我的代码有任何建议，将不胜感激。
我希望在 "Q" 列的每个单元格中都有 "=VLOOKUP(I2,Sheet2!I:I,1,FALSE)"，"I2" 会随着下移而动态更改（I3，I4 等）。
英文:
When I open my .xlsx file, I am greeted with "We found a problem with some content in file.xlsx...". Once it fixes, it says "Removed Records: Formula from /xl/worksheets/sheet1.xml part."
It is removing my vlookups I am adding via Python with the below code:
wb = load_workbook(f'{working_folder}\\{final_excel_file_name}')
sheet1_name = '-'.join(re.findall(r'[0-9]+', str(wb.worksheets[0])))
sheet2_name = '-'.join(re.findall(r'[0-9]+', str(wb.worksheets[1])))
sheet1 = wb[sheet1_name]
sheet2 = wb[sheet2_name]
sheet1['Q1'] = ''
for cell in sheet1['Q']:
cell.value = "=VLOOKUP(I{0},'" + sheet2_name + "'!I:I,1,FALSE)"
wb.save(f'{working_folder}\\test.xlsx')
Is there a way to format my cell.value to prevent excel from wanting to delete it when opening?
Additionally, if you have any suggestions on how to improve my code, it would be apricated.
I'm expecting to have =VLOOKUP(I2,Sheet2!I:I,1,FALSE) in every cell in column "Q". I2 Dynamically changing as it goes down (I3, I4, etc).
答案1
得分: 1
我不确定sheet1_name和sheet2_name的join部分的目的是什么。
从提供的示例公式
=VLOOKUP(I2,Sheet2!I:I,1,FALSE)
看来，一个工作表的名称是Sheet2，你要将公式添加到Sheet1。然而，这些代码行似乎只会返回连字符形式的数字？
如果你能更好地解释这部分内容，或者进一步评论一下，那就好了。
无论如何，为了回答这个问题，我将假设工作表的名称是：
Sheet1，在这里编写公式
Sheet2，表格存在的地方
公式的这一部分，你可以使用单元格来获取行数，并使用f string更容易理解如何组合你的公式；
...
sheet2_name = 'Sheet2'
### 遍历Q列中的每个单元格，直到最大使用行
for cell in sheet1['Q']:
### 使用f字符串创建公式
cell.value = f"=VLOOKUP(I{cell.row},'{sheet2_name}'!A:A,1,FALSE)"
请注意，围绕工作表名称'{sheet2_name}'的单引号只在工作表名称确实包含空格时才是必需的。
英文:
I'm not sure what the sheet1_name and sheet2_name join section is supposed to do.<br>
From the example formula provided
=VLOOKUP(I2,Sheet2!I:I,1,FALSE)
it appears that one sheet name is Sheet2 and you are adding the formula to Sheet1. However those lines of code would just return hyphenated numbers???<br>
If you can better explain this section, can perhaps comment on it further.
Anyway for the purposes of answering the question I'll assume the sheet names are;<br>
Sheet1 where the formulas will be written<br>
Sheet2 where the table exists<br>
That part of the formula you can use the cell for a row count and using an f string is easier to understand how your formula should be put together;
...
sheet2_name = 'Sheet2'
### Loop each cell in column Q to max used row
for cell in sheet1['Q']:
### Use an f string to create the formula
cell.value = f"=VLOOKUP(I{cell.row},'{sheet2_name}'!A:A,1,FALSE)"
Note the single quotes around the sheet name '{sheet2_name}' would only be necessary if the sheet name did actually have spaces.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论