python - excel CSV文件可以注入宏吗?
问题描述
我正在尝试使用自动适应 excel 中列宽的宏注入我的 csv 文件,但没有任何反应。我正在使用上一篇文章中的代码(使用 Python 将宏注入电子表格)。但这里是代码
import os
import sys
# Import System libraries
import glob
import random
import re
#sys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED
# USE COMTYPES OR WIN32COM
#import comtypes
#from comtypes.client import CreateObject
# USE COMTYPES OR WIN32COM
import win32com
from win32com.client import Dispatch
desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
x = r'C:\This\is\the\path'
scripts_dir = x
conv_scripts_dir = x
strcode = \
'''
sub test()
Column.Autofit
end sub
'''
#com_instance = CreateObject("Excel.Application", dynamic = True) # USING COMTYPES
com_instance = Dispatch("Excel.Application") # USING WIN32COM
com_instance.Visible = True#False
com_instance.DisplayAlerts = True#False
for script_file in glob.glob(os.path.join(scripts_dir, '*.csv')):
print("Processing: %s" % scr ipt_file)
# do the operation in background without actually opening Excel
(file_path, file_name) = os.path.split(script_file)
objworkbook = com_instance.Workbooks.Open(script_file)
xlmodule = objworkbook.VBProject.VBComponents.Add(1)
xlmodule.CodeModule.AddFromString(strcode.strip())
objworkbook.SaveAs(os.path.join(conv_scripts_dir, file_name))
com_instance.Quit()
这段代码实际上是在 excel 中打开一个文件,执行一个宏,然后关闭 excel 窗口。为什么宏在 python 命令行中不起作用,但在 excel 中起作用?
解决方案
确切地。CSV 中没有任何格式。这就像尝试向文本文件添加格式。你不能那样做。您可以将 CSV 文件转换为 XLSM 文件(Excel 宏)或 XLSB(二进制)。
Sub CSVtoXLSB2()
Dim wb As Workbook
Dim CSVPath As String
Dim sProcessFile As String
CSVPath = "C:\your_path_here\"
sProcessFile = Dir(CSVPath & "*.csv")
Do Until sProcessFile = "" ' Loop until no file found.
Set wb = Application.Workbooks.Open(CSVPath & sProcessFile)
wb.SaveAs CSVPath & Split(wb.Name, ".")(0) & ".xlsb", FileFormat _
:=50, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
wb.Close
sProcessFile = Dir() ' Get next entry.
Loop
Set wb = Nothing
End Sub
现在,如果您想将模块从一个工作簿复制到另一个工作簿,请按照下列步骤操作:
推荐阅读
- c++ - runtime polymorphic invocation of pure virtual function via std::reference_wrapper behaving inconsistently
- python - How to filter DataTime field by hour in Django?
- php - 如何使用 php 将 keynote 文件转换为 PDF 文件?
- angular - 角度范围问题
- java - NotSerializableException with java.awt.geom.Area
- html - How do I add and remove HTML elements with only HTML5 and CSS?
- r - Keep duplicates on multiple columns in R
- php - How to verfiy postcode using php?
- asp.net-mvc - 使用令牌错误 Access-Control-Allow-Origin' 进行登录身份验证
- python-3.x - python中的相似词分组