Here I share three excel functions that can be useful when working with Excel Files.
When working with data, we must define an input and output way of dealing with data.
As I used to work with Visual Basic in Excel, I feel more confortably using it.
I usually use a tab as input (tickers or needed parameters for the computation) and once calculations are done, results are saved in a new one.
In this way, I can save the output calculated for an input in the same file.
In order to select the excel file, I will get the one that is open (if there is only one) or allow the selection in the console among the open ones. In case, it is not open, it will be posible to search for it in the file system (normally it will be open, as I should have entered or checked the input).
Do you know a better way of dealing with this?
'''
Functions to interact with excel
'''
#===============================================================================
# LIBRARIES
#===============================================================================
import pandas as pd
import os
from openpyxl import load_workbook
from win32com.client import Dispatch
#===============================================================================
# FUNCTIONS
#===============================================================================
def save_in_new_tab_of_excel(filename, df_data, tab_name):
'''
This function will add a new tab to an existing excel and save the data
passed as argument
'''
#===========================================================================
# # 1. We save the data in a new excel book
# # (It is quicker to save with pandas to_excel function, but I was not
# # able to use it to create a new tab)
#===========================================================================
# 1.1. We create the path+name of the temp file
directory = os.path.dirname(filename)
tempfile = os.path.join(directory, "temp.xlsx")
# 1.2. If the temp file already exist we delete it
if os.path.isfile(tempfile):
os.remove(tempfile)
# 1.3. We create the ExcelWriter object and tab name
writer = pd.ExcelWriter(tempfile)
# 1.4. We save the data in the temp file
df_data.to_excel(writer, tab_name)
writer.save()
#===========================================================================
# # 2. We copy the excel tab to the existing excel book
#===========================================================================
# 2.1. We open an Excel Application object with 32
app = Dispatch("Excel.Application")
# 2.2. We get the sheet we want to copy and the target workbook
wb_temp = app.Workbooks.open(tempfile)
ws_to_copy = wb_temp.sheets(1)
wb_target = app.Workbooks.open(filename)
# 2.3. We copy to the existing excel file (it will copy it in the second place)
ws_to_copy.Copy(None, wb_target.sheets(1))
wb_target.Save()
# 2.4. We remove the temp file
wb_temp.Close()
if os.path.isfile(tempfile):
os.remove(tempfile)
def get_open_excel_files():
"""
This function will get and return a list of excel files open in the moment
of the script execution.
It will allow to interact with open files without having to select them
each time from the files management system
PENDING: To see if it could be useful to get also the sheets names
"""
## 1. We open an Excel Application object with 32
app = Dispatch("Excel.Application")
## 2. We get a list of open excel files
l_wbs = []
for i in range(app.WorkBooks.Count):
path_filename = os.path.join(app.WorkBooks(i+1).Path, app.WorkBooks(i+1).Name)
l_wbs.append(path_filename)
## 3. We return the list
return l_wbs
def select_excel_file():
"""
This function will allow to select the excel file to process:
1. It will use the only open by default if there is only one
2. If there are more than one will allow to choose among the open ones
3. And it also will allow to select one from the files management system
"""
## 1. First we see if there is only one open excel - if there is we use that
l_wbs = get_open_excel_files()
if len(l_wbs) == 1:
filename = l_wbs[0]
## 2. Otherwise we allow to choose among the open ones
elif len(l_wbs) > 1:
# 2.1. We offer to select one of the excel files
s_msg = "Please select the excel file to process:\n"
i=1
d_wbs = {}
for wb in l_wbs:
s_msg += str(i) + ".- " + wb + "\n"
d_wbs[i]=wb
i += 1
s_msg += str(i) + ".- Select from files management system\n"
opt = input(s_msg)
if opt < i:
filename = l_wbs[opt-1]
# 1.3. Oterwise we allow to select it from the files explorer
elif opt==i:
filename = fm.choose_file()
return filename
#===============================================================================
# MAIN
#===============================================================================
if __name__ == '__main__':
filename = 'C:\Users\Ricard\Google Drive\Negocios\\140_WIP_Python\demo.xlsx'
df_data = pd.DataFrame([{'1': 5, '2': 5}, {'1': 13, '2': 13}])
tab_name = 'Test_excel2'
save_in_new_tab_of_excel(filename, df_data, tab_name)
Recent Comments