I'm trying to develop an application that uses Python and PyWin32 to create Excel files, and I'd like to allow the user to save the file using the default format and extension for the version of Excel they are currently using. The "Open XML Workbook" format, which has the ".xlsx" extension, maybe the default format, depending on the version of Excel they are using. In other instances, the ".xls" extension and standard Excel format could be used. Additionally, the user can set Excel to utilize a different default format.
Although I am aware of where to locate the default format (Application.DefaultSaveFormat), I am unable to figure out where to find the format's default extension. My file names frequently begin with periods even before the extension; for example, if the fundamental file name is "filename.BOM," the actual file name should be "filename.BOM.xls" or "filename.BOM.xlsx," depending on the format that is being used.
Everything would be great if I didn't use a double period in the file name. Therefore, Workbook.SaveAs("filename") would generate a file with the name "filename.xlsx" if the default format is "Open XML Workbook". However, when you use Workbook.SaveAs("filename.BOM"), a file with that same name is created. When Excel detects a period in the file name, it does not apply for the default extension.
My only solution is to save a temporary file, extract the extension from it, and then remove the temporary file, but that sounds like a rather kludgy workaround. Does anyone have a more effective answer?
from tempfile import mktemp
from os import path
from os import remove as delfile
def __init__( self):
self.app = DispatchEx( "Excel.Application" )
def saveas_default_ext_format( self, workbook, filename):
# filename - file name with path but without extension
tmpname = mktemp()
alerts = self.app.DisplayAlerts
self.app.DisplayAlerts = False
self.app.DisplayAlerts = alerts
tmpname = self.app.ActiveWorkbook.FullName
x, ext = path.splitext( tmpname)
fullname = filename + ext