1

I have a problem connecting to Excel API in windows 10. I use Office365 and with it Excel2016. My goal is: to download CSV file from a client FTPS Server, merge it with the existing files,perfom some action on it(with pandas) and then load the whole data into excel and do reporting with it... Up to the point of loading it into Excel everything is fine.I managed to do all steps automatically with Python (sorry if my code looks a little cluttered - I am new to Python)

import subprocess
import os
import ftplib
import fnmatch
import sys
from ftplib import FTP_TLS
from win32com.client import Dispatch
import pandas as pd

filematch = '*.csv'
target_dir = 'cannot tell you the path :-) '

def loginftps(servername,user,passwort):
    ftps = FTP_TLS(servername)
    ftps.login(user=user,passwd=passwort)
    ftps.prot_p()
    ftps.cwd('/changes to some directory')

    for filename in ftps.nlst(filematch):
        target_file_name = os.path.join(target_dir,os.path.basename(filename))
        with open(target_file_name,'wb') as fhandle:
                ftps.retrbinary('RETR %s' %filename, fhandle.write)

def openExcelApplication():
    xl = Dispatch("Excel.Application")
    xl.Visible = True # otherwise excel is hidden

def mergeallFilestoOneFile():
    subprocess.call(['prepareData_executable.bat'])

def deletezerorows():
    rohdaten = pd.read_csv("merged.csv",engine="python",index_col=False,encoding='Latin-1',delimiter=";", quoting = 3)
    rohdaten = rohdaten.convert_objects(convert_numeric=True)
    rohdaten = rohdaten[rohdaten.UN_PY > 0]
    del rohdaten['deletes something']
    del rohdaten['deletes something']
    rohdaten.to_csv('merged_angepasst.csv',index=False,sep=";")

def rohdatenExcelAuswertung():
    csvdaten = pd.csv_read("merged.csv")

servername = input("please enter FTPS serveradress:")
user = input("Loginname:")
passwort = input("Password:")

loginftps(servername,user,passwort)
mergeallFilestoOneFile()
deletezerorows()

And here I am stuck somehow,.. I did extensive google research but somehow nobody has ever tried to perform Excel tasks from within Python??

I found this stackoverflow discussion: Opening/running Excel file from python but I somehow cannot figure out where my Excel-Application is stored to run code mentioned in this thread.

What I have is an Excel-Workbook which has a data connection to my CSV file. I want Python to open MS-Excel, refresh data connection and refresh a PivoTable & then save and close the file.

Has anybody here ever tried to to something similar and can provide some code to get me started? Thanks

BayerischerSchweitzer
  • 51
  • 1
  • 5
  • Have you looked at [xlwings](https://www.xlwings.org)? Also [this SO post](https://stackoverflow.com/questions/441758/driving-excel-from-python-in-windows) might help. – DocZerø May 25 '17 at 12:25
  • Hi Kristof thanks for your reply,..I have looked at xlwings, pandas ect. but I do not want to manipulate data inside Excel,.. I want to perform Excel actions like: refresh a data connection and refresh a specific pivot table. Can xlwings do this? – BayerischerSchweitzer May 25 '17 at 12:31

1 Answers1

1

A small snippet of code that should work for opening an excel file, updating linked data, saving it, and finally closing it:

from win32com.client import Dispatch

xl = Dispatch("Excel.Application")
xl.Workbooks.Open(Filename='C:\\Users\\Xukrao\\Desktop\\workbook.xlsx', UpdateLinks=3)
xl.ActiveWorkbook.Close(SaveChanges=True)
xl.Quit()
Xukrao
  • 8,003
  • 5
  • 26
  • 52