问Python熊猫将结果数据写入xlsm，而不丢失宏。

EN

import openpyxl
import os
import string
import pandas as pd
import numpy as np

path = #folder directory
target_file = #excel filename
sheetname = #working sheet that you wish to work on with

filename = os.path.join(path, target_file)

wb = openpyxl.load_workbook(filename, keep_vba=True)
sheet = wb.get_sheet_by_name(sheetname)

# To Erase All Values within Selected Columns
d = dict()
for x, y in zip(range(1, 27), string.ascii_lowercase):
    d[x] = y.upper()

max_row = sheet.max_row
max_col = sheet.max_column

for row in range(max_row):
    row += 1
    if row == 1: continue
    for col in range(max_col):
        col += 1
        if col == 1: continue
        sheet['{}{}'.format(d[col], row)] = None

# To Write Values to the Blank Worksheet
path_dataframe = # folder directory to the csv file
target_compiled = # csv filename
filename_compiled = os.path.join(path_compiled, target_compiled)

compiled = pd.read_csv(filename_compiled, low_memory=False, encoding = "ISO-8859-1")

for row in range(len(compiled.index)):
    row += 1
    if row == 1: continue # I do not want to change the value in row 1 in excel file because they are headers
    for col in range(max_col): 
        col += 1
        if col == 1: continue # I do not want to change the values in column 1 in excel file since they contain formula
        value = compiled.iloc[row-2][col-2]
        if type(value) is str: value = value
        elif type(value) is np.float64: value = float(value)
        elif type(value) is np.int64: value = int(value)
        sheet['{}{}'.format(d[col], row)] = value

wb.save(filename)

import pandas as pd 
import openpyxl # one excel reader/writer compatible with pandas

book = openpyxl.load_workbook('input.xlsm', keep_vba = True) # Load existing .xlsm file

with pd.ExcelWriter('output.xlsm', engine='openpyxl') as writer: # open a writer instance with the filename of the 
    
    writer.book = book # Hand over input workbook
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets) # Hand over worksheets
    writer.vba_archive = book.vba_archive # Hand over VBA information 


    df_write.to_excel(writer, sheet_name = 'Sheet1', columns = ['A'],
                  header = False, index = False,
                  startrow = 1, startcol = 0)
    # Writes a column A of the Dataframe into the excel sheet 'Sheet1', which can 
    # already be present in input.xlsm, to row 1, col 0

    
    writer.save()

import pandas as pd
import win32com.client as win32

# ...same pandas code...    
macrofile = "C:\\Path\\To\\Macro\\Workbook.xlsm"
strfile = "C:\\Path\\To\\CSV\\Output.csv"
df.to_csv(strfile)

try:
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    wb = xl.Workbooks.Open(macrofile)

    # DELETE PREVIOUS DATA
    wb.Sheets("Source").Range("B:Q").EntireColumn.Delete()

    # ADD QUERYTABLE (SPECIFYING DESTINATION CELL START)
    qt = wb.Sheets("Source").QueryTables.Add(Connection="TEXT;" + strfile, 
                                             Destination=wb.Sheets(1).Cells(2, 2))
    qt.TextFileParseType = 1
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = False
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh(BackgroundQuery=False)

    # REMOVE QUERYTABLE
    for qt in wb.Sheets("Source").QueryTables:
        qt.Delete()

    # CLOSES WORKBOOK AND SAVES CHANGES
    wb.Close(True)

except Exception as e:
    print(e)

finally:    
    qt = None
    wb = None
    xl = None

Public Sub ImportCSV(strfile As String)
    Dim qt As QueryTable

    ThisWorkbook.Sheets("Source").Range("B:Q").EntireColumn.Delete

    ' ADD QUERYTABLE
    With ThisWorkbook.Sheets("Source").QueryTables.Add(Connection:="TEXT;" & strfile, _
        Destination:=ThisWorkbook.Sheets(1).Cells(2, 2))
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False

            .Refresh BackgroundQuery:=False
    End With

    ' REMOVE QUERYTABLE
    For Each qt In ThisWorkbook.Sheets(1).QueryTables
        qt.Delete
    Next qt

    Set qt = Nothing
End Sub

import pandas as pd
import win32com.client as win32

# ...same pandas code...    
macrofile = "C:\\Path\\To\\Macro\\Workbook.xlsm"
strfile = "C:\\Path\\To\\CSV\\Output.csv"
df.to_csv(strfile)

try:
    xl = win32.gencache.EnsureDispatch('Excel.Application')

    wb = xl.Workbooks.Open(macrofile)
    xl.Application.Run('ImportCSV', strfile)

    wb.Close(True)
    xl.Quit

except Exception as e:
    print(e)

finally:    
    wb = None
    xl = None