Here is my now goto prompt for when I am working on a project – have a class that I need to mimic the interfaces.
use the following class as a template. Call the new class "MainframeExcelText" The init take file and sheet and sets row and column location to 0. 1 function - write_text will take a row,col and string. It will write the text to the location in the excel spreadsheet using openpyxl. a close function will close he sheet
class MainframeExcelPivot:
# Define the init method
def __init__(self, excel_file, sheet_name):
# Load the Excel file into a DataFrame
self.df = pd.read_excel(excel_file, sheet_name=sheet_name)
# Store the Excel file name and sheet name as attributes
self.excel_file = excel_file
self.sheet_name = sheet_name
self.start_row = 1
self.start_col = 1
self.book = load_workbook(self.excel_file)
# Create an Excel writer object with openpyxl engine and append mode
self.writer = pd.ExcelWriter(self.excel_file, engine="openpyxl", mode="a", if_sheet_exists="overlay")
# Assign the book attribute to the loaded workbook
self.writer.book = self.book
def set_loc(self, start_row, start_col ):
self.start_row = start_row
self.start_col = start_col
# Define the create_manager_pivot method
def create_manager_pivot(self, sheet_name):
# Create a pivot table with index "Manager" and values "Case" with count function
self.df["Aged_Backlog"] = self.df["Aged"].str.contains(">21")
self.table = pd.pivot_table(self.df, index="Manager", values=["Case", "Aged_Backlog"],
aggfunc={"Case": "count",
"Aged_Backlog": np.sum},
margins=True, margins_name="Total")
self.table["Percent_Aged"] = self.table["Aged_Backlog"].div(
self.table["Case"]).mul(100).round(2)
# Save the pivot table as a new sheet in the Excel file
def sheet_filter_with_compare(self, sheet_name, index_col, value_col, aggfunc=np.sum, filter_col=None, filter_values=None):
# Create a pivot table with the given parameters and a filter on filter_col column
if filter_col and filter_values:
my_pd = self.df.query(f"{filter_col} in {filter_values}")
# Construct the query string using the filter parameters
query_str = f"{filter_col} in {filter_values}"
self.table = pd.pivot_table(my_pd, index=index_col, values=value_col,
aggfunc=aggfunc,
margins=True, margins_name="Total")
else:
# No filter applied
self.table = pd.pivot_table(self.df, index=index_col, values=value_col,
aggfunc=aggfunc,
margins=True, margins_name="Total")
# Save the pivot table as a new sheet in the Excel file
self.save_table(sheet_name)
# Define the save method
def save_table(self, sheet_name):
self.table.to_excel(self.writer, sheet_name=sheet_name, startrow = self.start_row, startcol=self.start_col )
def save(self, sheet_name):
# Save the file
self.writer.save()
Here is what it generated.
“I’ll try to help you with that. Here is a possible class that meets your requirements:”
# Import openpyxl module
import openpyxl
# Define the MainframeExcelText class
class MainframeExcelText:
# Define the init method
def __init__(self, file, sheet):
# Load the workbook and the sheet
self.wb = openpyxl.load_workbook(file)
self.ws = self.wb[sheet]
# Store the file and sheet names as attributes
self.file = file
self.sheet = sheet
# Set the row and column location to 0
self.row = 0
self.col = 0
# Define the write_text method
def write_text(self, row, col, string):
# Write the string to the given row and column location in the sheet
self.ws.cell(row=row, column=col).value = string
# Define the close method
def close(self):
# Save and close the workbook
self.wb.save(self.file)
self.wb.close()