Saving an excel file with a custom name each time I run the module

Question

I’m trying to create a program that reads and analyzes an excel file(.xlsx), creates a summary table, then saves that table into a new .xlsx file.

I’d like to have an input prompt ask me - “Filename?” I’m analyzing several sets of data and I’d like the summary table to be saved with a new filename each time.

I’ve tried setting a variable equal to my input, then saving the output as that variable. However, it won’t save as a .xlsx, only a .txt. Any help is greatly appreciated!

Answer 1

This code above is for reading and printing in the console. I copied this code from one of my projects. If you need more help and guidance, ask here.

import xlwings as xw
import openpyxl


# Specifying a sheet
ws = xw.Book(excel_file_name + '.xlsx').sheets['Sheet1']

# Define variable to load the dataframe
dataframe = openpyxl.load_workbook(excel_file_name + '.xlsx')

sheet = dataframe.worksheets[0]

v1 = ws.range("A1:A"+str(int(row_count)+1)).value
v2 = ws.range("B1:B"+str(int(row_count)+1)).value
v3 = ws.range("C1:C"+str(int(row_count)+1)).value
v4 = ws.range("D1:D"+str(int(row_count)+1)).value

for key in v1:
    for value in v2:
        res[key] = value
        v2.remove(value)
        break

for i, key in enumerate(res):
    res[key] = (res[key], v3[i], v4[i])

for key, value in res.items():
    print(key + ' ' + value[0] + ' ' + value[1] + ' ' + value[2] + '\n')

Answer 2

You've tagged your question with pandas tag, so I am guessing you are working with dataframes, which you want to save.

pandas DataFrame has method as_excel(), which does exactly that. However, you would have to install openpyxl first.

Here is a snippet, that puts the original file name into a variable, creates new file name for analysis results, reads the original file, performs a simple aggregation and saves it into a new file. Hope that helps!

import numpy as np
import pandas as pd

# get current file name
file_name = "file.xlsx"

# prepare file name for analysis
name, extention = file_name.split(".")
new_name = name + "_analysis"
new_file_name = ".".join([new_name, extention])

print("Analysis file name: {}\n".format(new_file_name))

data = pd.read_excel(file_name)
print("Data before analysis: {}\n".format(data.head))

# perform simple analysis and save to a new file
data \
    .groupby("column", as_index=False) \
    .mean() \
    .to_excel(new_file_name, index=False)

Output:

Analysis file name: file_analysis.xlsx

Data before analysis: <bound method NDFrame.head of   column  value
0      a      2
1      a     -2
2      a      3
3      b     89
4      b     62
5      b     22>