Bing Chat – use a class as a template prompt

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()