Hi there! Let’s walk through solutions to your two openpyxl questions, using the code you shared as a starting point.

First, here’s the base code you provided for reference:

import openpyxl as op
from openpyxl import Workbook
new_excel = op.load_workbook('SpreadSheet.xlsx', read_only=False, keep_vba= True)
spreadsheet = new_excel.get_sheet_by_name('Input Quote')
spreadsheet['B30'] = 'VAU'
spreadsheet['D30'] = 1000
spreadsheet['F30'] = 5000

1. Saving the edited workbook as a separate .xlsx file #

It’s super straightforward—just use the save() method on your workbook object, and specify a new filename to avoid overwriting your original file. Add this line to the end of your code:

# Save to a new file (won't touch the original SpreadSheet.xlsx)
new_excel.save('Edited_SpreadSheet.xlsx')

A couple quick notes:

• If the filename you specify already exists, openpyxl will overwrite it without warning, so using a unique name is a safe bet to preserve your original data.
• Since you’re using keep_vba=True, the saved file will retain any VBA macros from the original. If you don’t need the macros, you can remove that parameter—it doesn’t affect the save operation.

2. Triggering formula recalculation for files with existing formulas #

Openpyxl doesn’t automatically calculate formula results on its own—it only reads the formula text or the cached results stored in the original Excel file. Here are two reliable ways to handle formula recalculation:

Option 1: Let Excel recalculate when the file is opened #

You can configure the workbook to tell Excel to automatically recalculate all formulas the next time someone opens the file. Add this line before saving:

# Set the workbook to auto-calculate formulas on open
new_excel.calculation.calcMode = 'auto'
new_excel.save('Edited_SpreadSheet.xlsx')

This is the most robust method because it leverages Excel’s full calculation engine, which handles even complex formulas, macros, or array formulas that openpyxl can’t process.

Option 2: Calculate formulas directly in Python (limited use cases) #

If you need to get formula results while running your Python script, you can use openpyxl.utils.formula.calculate, but keep in mind this only works for simple formulas (it doesn’t support all Excel functions or complex logic). Example:

from openpyxl.utils.formula import calculate

# Example: If G30 has the formula =D30+F30
calculated_result = calculate(spreadsheet['G30'].value, spreadsheet)
spreadsheet['G30'] = calculated_result  # Writes the result over the original formula

Again, for most real-world scenarios, Option 1 is the better choice since it relies on Excel’s native calculation capabilities.

内容的提问来源于stack exchange，提问作者user3662369