打开、保存并关闭一个Excel文件,无需交互,可通过命令提示符完成。

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

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 打开、保存并关闭一个Excel文件,无需交互,可通过命令提示符完成。

答案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.vbsWScript 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()

huangapple
  • 本文由 发表于 2023年6月26日 17:33:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76555391.html
匿名

发表评论

匿名网友

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

确定