英文:
Open, save and close an excel file from the command prompt without interation
问题
我有一个需要通过 Python 脚本自动修改的 Excel 文件,同时也需要允许手动修改。这是一个包含多个工作表的 Excel 文件,每个工作表都包含一个表格,并且在某些列中包含公式。
我的脚本使用 openpyxl 打开 Excel 文件,以避免破坏表格内部的所有结构,并自动修改某些特定工作表中的某些行。我必须使用 openpyxl,因为我需要使 Excel 文件可以以相同的方式手动修改,这意味着用户可以打开它,例如在表格中添加一行,这将扩展表格并在 Excel 中自动填充公式,这是 Excel 表格的正常行为。
问题是,在 Python 中保存后,我需要在 Tableau Prep 中读取它,这是一个用于数据准备的程序。这个程序无法读取我使用 openpyxl 保存后的公式,只有当我手动打开修改后的 Excel、不进行任何手动修改并关闭它后,它才能读取。我立刻看到文件的大小发生了变化,这意味着 openpyxl 以略有不同的方式保存,所以 Excel 会自行重新组织。
问题是,我希望这个脚本可以完全自动执行,而不需要最后手动打开文件。因此,我正在尝试寻找一种通过命令提示符打开 Excel 文件、保存并关闭它的方法。到目前为止,我唯一找到的方法是尝试使用一个简单的 VBA 宏:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
但是我无法使其从命令行工作,它只是打开 Excel,不会自动执行任何操作。
我尝试过以下命令:
start excel.exe excelfile.xlsm /mSaveAndCloseWithoutModifications
我感到困惑,请帮助我。
英文:
I have an excel file that needs to be automatically modified (with a python script) and also be available for manual modifications. It is an excel file with several sheets, each containing a single table and containing formulas in some columns.
My script opens the excel file with openpyxl, to avoid breaking all the structure that exists inside the tables and automatically modify some rows in some specific sheets. I have to use openpyxl beacuse I need that the excel file can be manually modified the same way, meaning that users can open it, add for instance a new line into a table, which will extend the table and fill the formulas automatically inside excel, normal behavior of tables in excel.
The problem is that, after saving it in python, I need to read it in Tableau Prep, a program for data preparation. This program cannot read my formulas after I save the excel file with openpyxl, It only reads if I manually open the modified excel, save it without any manual modification and close it. I immediatly see that the size of the file changes, meaning that openpyxl save things in a slightly different way than how excel operates, so Excel re-organize things himself.
The thing is that I need this script to do everything automatically, without the need of manually open the file at the end.
So I am trying to search for a way to open an excel file via command prompt, save it and close it. So far, the only thing I found is to try to use a simple VBA macro:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
but I do not succeed to make it work from command line, it simply opens the excel and does nothing automatically.
I try:
start excel.exe excelfile.xlsm /mSaveAndCloseWithoutModifications
I am lost here, please help me
答案1
得分: 1
我会使用vbs来实现这个:
Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Open("excelfile.xlsm")
app.Visible = True ' 用于调试
wb.Worksheets("Sheet1").Range("A1").Value = 1 ' 用于调试输入一个唯一的数字
wb.Save
WScript.Echo "按OK退出Excel" ' 用于调试
app.Quit
如果你以前没有使用过vbs,请创建一个纯文本文件,将其命名为"something.vbs",然后将上述代码放入其中。在命令提示符中,它可以通过something.vbs
或WScript something.vbs
来启动。
英文:
I would use vbs for this:
Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Open("excelfile.xlsm")
app.Visible = True ' For debugging
wb.Worksheets("Sheet1").Range("A1").Value = 1 ' For debugging enter a unique number
wb.Save
WScript.Echo "Press OK to quit Excel" ' For debugging
app.Quit
If you have not used vbs before, just create a plain text file, name it as "something.vbs", and put the above code in it. From cmd it can be started either as something.vbs
or as WScript something.vbs
答案2
得分: 0
感谢您的评论。我没有尝试您的vbs方法,但感谢 @z32a7ul。
我通过以下方式解决了这个问题:
我的xlsm文件中有一个带有以下代码的模块:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
End Sub
然后,在Python中,在使用openpyxl修改并保存文件后,我执行以下操作:
import win32com.client as win32
excel = win32.Dispatch('Excel.Application')
workbook = excel.Workbooks.Open(filepath)
excel.Run('SaveAndCloseWithoutModifications')
excel.Quit()
英文:
Thanks for your comments. I didn't try you vbs approach, but thanks @z32a7ul.
I managed to solve the problem by doing the following:
My xlsm file has a module with the code:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
End Sub
Then, in python, after having modified and saved the file with openpyxl, I do:
import win32com.client as win32
excel = win32.Dispatch('Excel.Application')
workbook = excel.Workbooks.Open(filepath)
excel.Run('SaveAndCloseWithoutModifications')
excel.Quit()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论