### Complete Robot Framework Test Suite Example Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt This example showcases a comprehensive Robot Framework test suite using ExcelSage for various Excel operations, including data structure validation, adding new rows, and exporting data. ```robotframework *** Settings *** Library ExcelSage Library Collections *** Variables *** ${EXCEL_PATH} ${CURDIR}/data/employees.xlsx ${OUTPUT_PATH} ${CURDIR}/output/processed.xlsx @{NEW_EMPLOYEE} Jane Smith 28 Marketing 55000 *** Test Cases *** Verify Employee Data Structure [Documentation] Validates the structure of employee data file Open Workbook workbook_name=${EXCEL_PATH} # Verify sheet exists Sheet Should Exist sheet_name=Employees # Verify column structure ${headers}= Get Column Headers sheet_name=Employees Should Contain ${headers} Name Should Contain ${headers} Age Should Contain ${headers} Department # Verify data integrity Column Should Not Contain Duplicates column_name_or_letter=Email sheet_name=Employees Sheet Should Not Contain Empty Rows sheet_name=Employees Close Workbook Add New Employee And Verify [Documentation] Adds a new employee and verifies the addition Open Workbook workbook_name=${EXCEL_PATH} ${initial_count}= Get Row Count sheet_name=Employees Append Row row_data=${NEW_EMPLOYEE} sheet_name=Employees Save Workbook ${new_count}= Get Row Count sheet_name=Employees Should Be Equal As Integers ${new_count} ${initial_count + 1} Column Should Contain column_name_or_letter=Name expected_value=Jane Smith Close Workbook Process And Export Data [Documentation] Processes employee data and exports to CSV Open Workbook workbook_name=${EXCEL_PATH} # Sort by salary ${sorted}= Sort Column column_name_or_letter=Salary asc=False output_format=dataframe # Find duplicates ${duplicates}= Find Duplicates column_names_or_letters=Department output_format=list Log Duplicate departments: ${duplicates} # Export to CSV Export To CSV filename=${EXCEL_PATH} sheet_name=Employees output_filename=${OUTPUT_PATH} overwrite_if_exists=True Close Workbook ``` -------------------------------- ### Install ExcelSage Library Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Install the ExcelSage library using pip. This command installs the package and its dependencies. ```bash pip install robotframework-excelsage ``` -------------------------------- ### Get Multiple Column Values as DataFrame Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from multiple columns and returns them as a pandas DataFrame. A starting cell can be specified. ```python df = excel.get_column_values( column_names_or_letters=["Name", "Department"], output_format="dataframe", starting_cell="A1" ) print(df) ``` -------------------------------- ### Get Column Count Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the number of columns in a sheet. Can ignore empty columns and start from any cell. ```APIDOC ## Get Column Count ### Description Returns the number of columns in a sheet. Can ignore empty columns and start from any cell. ### Method GET (conceptual) ### Endpoint /excel/column_count ### Parameters #### Query Parameters - **sheet_name** (string) - Optional - The name of the sheet to count columns from. Defaults to the active sheet. - **starting_cell** (string) - Optional - The cell from which to start counting columns. Defaults to "A1". - **ignore_empty_columns** (boolean) - Optional - Whether to ignore empty columns in the count. Defaults to False. ### Request Example ```python # Get column count count = excel.get_column_count(sheet_name="Sheet1") # Ignore empty columns clean_count = excel.get_column_count( starting_cell="C1", ignore_empty_columns=True, sheet_name="Sheet1" ) ``` ### Response #### Success Response (200) - **count** (integer) - The number of columns in the sheet. #### Response Example ```json { "count": 5 } ``` ``` -------------------------------- ### Get Column Count (Ignoring Empty Columns) Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the count of non-empty columns, optionally starting from a specific cell. Set `ignore_empty_columns` to True. ```python clean_count = excel.get_column_count( starting_cell="C1", ignore_empty_columns=True, sheet_name="Sheet1" ) print(f"Non-empty columns from C: {clean_count}") ``` -------------------------------- ### Get Column Headers from Offset Position Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves column headers starting from a specified cell in a given sheet. ```python headers = excel.get_column_headers( starting_cell="D5", sheet_name="OffsetData" ) print(f"Offset headers: {headers}") ``` -------------------------------- ### Get Row Count (Ignoring Empty Rows) Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the count of non-empty data rows, optionally starting from a specific cell. Set `ignore_empty_rows` to True. ```python clean_count = excel.get_row_count( sheet_name="Sheet1", starting_cell="B5", ignore_empty_rows=True ) print(f"Non-empty rows: {clean_count}") ``` -------------------------------- ### Compare Two Excel Files Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Compares two Excel files based on specified configurations for sheets, starting cells, and columns. Returns a comparison result. ```python comparison = excel_sage.compare_excels(source_excel="file1.xlsx", target_excel="file2.xlsx", source_excel_config={'sheet_name': 'Sheet1','columns': ['Name', 'Age']}, target_excel_config={'sheet_name': 'Sheet2', 'starting_cell': 'D8', 'columns': ['Name', 'Age']}) ``` -------------------------------- ### Get Column Count Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the number of columns in a sheet. Specify the sheet name for clarity. ```python count = excel.get_column_count(sheet_name="Sheet1") print(f"Columns: {count}") ``` -------------------------------- ### Compare Two Excel Files Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Compares two Excel files and returns a DataFrame showing the differences. Supports basic comparison or comparison with specific configurations for sheets, starting cells, and columns. Use `differences.empty` to check for identical files. ```python from ExcelSage import ExcelSage excel = ExcelSage() # Basic comparison differences = excel.compare_excels( source_excel="data/original.xlsx", target_excel="data/modified.xlsx" ) print(f"Found {len(differences)} different rows") print(differences) # Comparison with specific configuration differences = excel.compare_excels( source_excel="data/source.xlsx", target_excel="data/target.xlsx", source_excel_config={ "sheet_name": "DataSheet", "starting_cell": "B2", "columns": ["Name", "Email", "Status"] }, target_excel_config={ "sheet_name": "ImportedData", "starting_cell": "A1", "columns": ["Name", "Email", "Status"] } ) # Check if files are identical if differences.empty: print("Files are identical") else: print("Differences found:") print(differences.to_string()) ``` -------------------------------- ### Get Multiple Column Values as Dictionary Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from multiple columns and returns them as a dictionary. Column letters can be used for selection. ```python data = excel.get_column_values( column_names_or_letters=["A", "C"], output_format="dict" ) print(data) ``` -------------------------------- ### Get Row Values as Dictionary Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from specified rows and returns them as a dictionary where keys are row indices. ```python row_dict = excel.get_row_values( row_indices=[1, 2, 3], output_format="dict" ) print(row_dict) ``` -------------------------------- ### Fetch Sheet Data Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves data from a sheet in list, dictionary, or pandas DataFrame format. Supports specifying a starting cell and ignoring empty rows/columns. ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/employees.xlsx") # Fetch as list of lists data_list = excel.fetch_sheet_data( sheet_name="Sheet1", output_format="list" ) print(data_list) # Output: [['John', 30, 'Engineering'], ['Jane', 25, 'Marketing']] # Fetch as list of dictionaries data_dict = excel.fetch_sheet_data( sheet_name="Sheet1", output_format="dict" ) print(data_dict) # Output: [{'Name': 'John', 'Age': 30, 'Dept': 'Engineering'}, ...] # Fetch as pandas DataFrame df = excel.fetch_sheet_data( sheet_name="Sheet1", output_format="dataframe", starting_cell="B5", ignore_empty_rows=True, ignore_empty_columns=True ) print(df.head()) excel.close_workbook() ``` -------------------------------- ### Sheet Operations - Get Sheets Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves a list of all sheet names in the active workbook. ```APIDOC ## Get Sheets ### Description Returns a list of all sheet names in the active workbook. Useful for iterating through sheets or validating sheet existence. ### Method GET ### Endpoint /get_sheets ### Parameters No parameters required. ### Request Example ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/multi_sheet.xlsx") # Get all sheet names sheets = excel.get_sheets() print(f"Available sheets: {sheets}") # Output: Available sheets: ['Sheet1', 'Data', 'Summary'] # Iterate through all sheets for sheet in sheets: data = excel.fetch_sheet_data(sheet_name=sheet, output_format="list") print(f"Sheet '{sheet}' has {len(data)} rows") excel.close_workbook() ``` ### Response #### Success Response (200) - **sheets** (list of strings) - A list containing the names of all sheets in the workbook. #### Response Example ```json { "sheets": ["Sheet1", "Data", "Summary"] } ``` ``` -------------------------------- ### Get Column Values Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves all values from specified columns by header name or column letter. Supports multiple columns and various output formats. ```APIDOC ## Get Column Values ### Description Retrieves all values from specified columns by header name or column letter. Supports multiple columns and various output formats. ### Method GET (conceptual) ### Endpoint /excel/column_values ### Parameters #### Query Parameters - **column_names_or_letters** (string or list of strings) - Required - The name(s) or letter(s) of the column(s) to retrieve. - **output_format** (string) - Optional - The desired format for the output. Supported values: "list", "dataframe", "dict". Defaults to "list". - **sheet_name** (string) - Optional - The name of the sheet to read from. Defaults to the active sheet. - **starting_cell** (string) - Optional - The cell from which to start reading columns. Defaults to "A1". ### Request Example ```python # Get single column by header name names = excel.get_column_values( column_names_or_letters="Name", output_format="list", sheet_name="Sheet1" ) # Get multiple columns as DataFrame df = excel.get_column_values( column_names_or_letters=["Name", "Department"], output_format="dataframe", starting_cell="A1" ) ``` ### Response #### Success Response (200) - **data** (list or pandas.DataFrame or dict) - The retrieved column values in the specified format. #### Response Example ```json { "data": ["John", "Jane", "Bob"] } ``` ``` -------------------------------- ### Get Multiple Row Values Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from multiple rows specified by their indices. The output is a list of lists. ```python rows = excel.get_row_values( row_indices=[2, 5, 10], output_format="list" ) for idx, row in enumerate(rows): print(f"Row: {row}") ``` -------------------------------- ### Sheet Management API Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md APIs for managing sheets within an Excel workbook, including getting, adding, deleting, renaming, and copying sheets. ```APIDOC ## Get Sheets ### Description Retrieves a list of all sheet names in the current workbook. ### Method `get_sheets(self)` ``` ```APIDOC ## Add Sheet ### Description Adds a new sheet to the workbook. ### Method `add_sheet(self, sheet_name, sheet_pos, sheet_data)` ### Parameters - **sheet_name** (string) - Required - The name of the new sheet. - **sheet_pos** (integer) - Optional - The position (index) where the sheet should be added. - **sheet_data** (dict) - Optional - Initial data for the new sheet. ``` ```APIDOC ## Delete Sheet ### Description Deletes a specified sheet from the workbook. ### Method `delete_sheet(self, sheet_name)` ### Parameters - **sheet_name** (string) - Required - The name of the sheet to delete. ``` ```APIDOC ## Rename Sheet ### Description Renames an existing sheet in the workbook. ### Method `rename_sheet(self, old_name, new_name)` ### Parameters - **old_name** (string) - Required - The current name of the sheet. - **new_name** (string) - Required - The new name for the sheet. ``` ```APIDOC ## Copy Sheet ### Description Copies a sheet to a new sheet with a different name. ### Method `copy_sheet(self, source_sheet_name, new_sheet_name)` ### Parameters - **source_sheet_name** (string) - Required - The name of the sheet to copy. - **new_sheet_name** (string) - Required - The name for the new copied sheet. ``` ```APIDOC ## Set Active Sheet ### Description Sets a specific sheet as the active sheet in the workbook. ### Method `set_active_sheet(self, sheet_name)` ### Parameters - **sheet_name** (string) - Required - The name of the sheet to set as active. ``` ```APIDOC ## Protect Sheet ### Description Protects a specified sheet with a password. ### Method `protect_sheet(self, password, sheet_name)` ### Parameters - **password** (string) - Required - The password to protect the sheet with. - **sheet_name** (string) - Required - The name of the sheet to protect. ``` ```APIDOC ## Unprotect Sheet ### Description Unprotects a specified sheet. ### Method `unprotect_sheet(self, password, sheet_name)` ### Parameters - **password** (string) - Required - The password for the sheet. - **sheet_name** (string) - Required - The name of the sheet to unprotect. ``` ```APIDOC ## Clear Sheet ### Description Clears all content from the specified sheet. ### Method `clear_sheet(self, sheet_name)` ### Parameters - **sheet_name** (string) - Required - The name of the sheet to clear. ``` -------------------------------- ### Get Single Row Values Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves all values from a single row specified by its index. The output format can be a list or a dictionary. ```python row_data = excel.get_row_values( row_indices=2, output_format="list", sheet_name="Sheet1" ) print(f"Row 2: {row_data}") ``` -------------------------------- ### Write and Get Cell Values in Python Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Enables writing data to a specific cell and retrieving the value from a cell within an opened Excel sheet. Specify the sheet name for the operation. ```python # Write a value to a specific cell excel_sage.write_to_cell(cell_name="A1", cell_value="Test Data", sheet_name="Sheet1") ``` ```python # Fetch a value from a specific cell value = excel_sage.get_cell_value(cell_name="B2", sheet_name="Sheet1") ``` -------------------------------- ### Get Sheet Names with ExcelSage Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns a list of all sheet names in the active workbook. Useful for iterating through sheets or validating their existence. ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/multi_sheet.xlsx") # Get all sheet names sheets = excel.get_sheets() print(f"Available sheets: {sheets}") # Output: Available sheets: ['Sheet1', 'Data', 'Summary'] # Iterate through all sheets for sheet in sheets: data = excel.fetch_sheet_data(sheet_name=sheet, output_format="list") print(f"Sheet '{sheet}' has {len(data)} rows") excel.close_workbook() ``` -------------------------------- ### Get Row Count (Including Header) Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the total number of rows in a sheet, including the header row. Set `include_header` to True. ```python total = excel.get_row_count( sheet_name="Sheet1", include_header=True ) print(f"Total rows: {total}") ``` -------------------------------- ### Get Single Column Values by Header Name Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves all values from a single column identified by its header name. Specify the sheet name if it's not the default. ```python names = excel.get_column_values( column_names_or_letters="Name", output_format="list", sheet_name="Sheet1" ) print(f"Names: {names}") ``` -------------------------------- ### Get Column Headers Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves the header row (column names) from a sheet starting at the specified cell. ```APIDOC ## Get Column Headers ### Description Retrieves the header row (column names) from a sheet starting at the specified cell. ### Method GET (conceptual) ### Endpoint /excel/column_headers ### Parameters #### Query Parameters - **sheet_name** (string) - Optional - The name of the sheet to get headers from. Defaults to the active sheet. - **starting_cell** (string) - Optional - The cell from which to start reading headers. Defaults to "A1". ### Request Example ```python # Get headers from A1 headers = excel.get_column_headers(sheet_name="Sheet1") # Get headers from offset position headers = excel.get_column_headers( starting_cell="D5", sheet_name="OffsetData" ) ``` ### Response #### Success Response (200) - **headers** (list of strings) - A list containing the column header names. #### Response Example ```json { "headers": ["Name", "Age", "Department", "Salary"] } ``` ``` -------------------------------- ### Open and Create Workbooks in Python Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Demonstrates how to open an existing Excel workbook or create a new one with initial sheet data using the ExcelSage library. ```python # Open an existing workbook excel_sage.open_workbook(workbook_name="path/to/excel.xlsx") ``` ```python # Create a new workbook excel_sage.create_workbook(workbook_name="new_excel.xlsx", overwrite_if_exists=True, sheet_data=[['Name', 'Age'], ['Alice', 25]]) ``` -------------------------------- ### Robot Framework - Compare Excels Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Compares two Excel files and logs the comparison result. Requires source and target file paths. ```robotframework ${comparison} Compare Excels source_excel=file1.xlsx target_excel=file2.xlsx Log ${comparison} ``` -------------------------------- ### Get Column Headers from A1 Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves the header row (column names) from the specified sheet, starting from cell A1 by default. ```python headers = excel.get_column_headers(sheet_name="Sheet1") print(f"Headers: {headers}") ``` -------------------------------- ### Robot Framework - Open Workbook Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Opens an existing Excel workbook for manipulation. Specify the path to the workbook. ```robotframework Open Workbook ${EXCEL_PATH} ``` ```robotframework Open Existing Workbook Open Workbook workbook_name=${EXCEL_PATH} ``` -------------------------------- ### Robot Framework - Create Workbook Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Creates a new Excel workbook. Can specify initial sheet data and whether to overwrite existing files. ```robotframework Create Workbook workbook_name=new_excel.xlsx overwrite_if_exists=True sheet_data=[[Name, Age], [Alice, 25]] ``` -------------------------------- ### Create Workbook with ExcelSage Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Creates a new Excel workbook, optionally with initial data. Can overwrite existing files and automatically opens the workbook. ```python from ExcelSage import ExcelSage excel = ExcelSage() # Create empty workbook excel.create_workbook(workbook_name="output/new_file.xlsx") # Create workbook with initial data sheet_data = [ ["Name", "Age", "Department"], ["John Doe", 30, "Engineering"], ["Jane Smith", 25, "Marketing"], ["Bob Johnson", 35, "Sales"] ] excel.create_workbook( workbook_name="output/employees.xlsx", overwrite_if_exists=True, sheet_data=sheet_data, alias="employees" ) # Save and close excel.save_workbook() excel.close_workbook() ``` -------------------------------- ### Robot Framework - Load ExcelSage Library Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Loads the ExcelSage library for use in Robot Framework test suites. This is typically done in the Settings section. ```robotframework *** Settings *** Library ExcelSage ``` -------------------------------- ### Robot Framework - Add New Sheet Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Adds a new sheet to the workbook at a specified position with optional initial data. ```robotframework Add Sheet sheet_name=NewSheet sheet_pos=1 sheet_data=[[ID, Name], [1, John]] ``` -------------------------------- ### Workbook Management - Create Workbook Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Creates a new Excel workbook, optionally with initial data. Can overwrite existing files and opens the new workbook. ```APIDOC ## Create Workbook ### Description Creates a new Excel workbook with optional initial data. Can overwrite existing files if specified and automatically opens the workbook for further manipulation. ### Method POST ### Endpoint /create_workbook ### Parameters #### Query Parameters - **workbook_name** (string) - Required - The name/path for the new Excel file. - **overwrite_if_exists** (boolean) - Optional - If true, overwrites the file if it already exists. - **sheet_data** (list of lists) - Optional - Initial data to populate the default sheet. - **alias** (string) - Optional - An alias to refer to this workbook. ### Request Example ```python from ExcelSage import ExcelSage excel = ExcelSage() # Create empty workbook excel.create_workbook(workbook_name="output/new_file.xlsx") # Create workbook with initial data sheet_data = [ ["Name", "Age", "Department"], ["John Doe", 30, "Engineering"], ["Jane Smith", 25, "Marketing"], ["Bob Johnson", 35, "Sales"] ] excel.create_workbook( workbook_name="output/employees.xlsx", overwrite_if_exists=True, sheet_data=sheet_data, alias="employees" ) # Save and close excel.save_workbook() excel.close_workbook() ``` ### Response #### Success Response (200) - **workbook** (object) - The newly created workbook object. #### Response Example ```json { "workbook": "" } ``` ``` -------------------------------- ### Robot Framework - Fetch Sheet Data Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Fetches data from a specified sheet in the currently open workbook. Supports outputting data as a pandas DataFrame. ```robotframework ${data}= Fetch Sheet Data sheet_name=Sheet1 output_format=dataframe Log ${data} ``` ```robotframework ${data}= Fetch Sheet Data sheet_name=Sheet1 output_format=dataframe starting_cell=A1 Log ${data} ``` -------------------------------- ### Robot Framework - Get Cell Value Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Retrieves the value from a specific cell in a given sheet. ```robotframework ${value}= Get Cell Value A1 sheet_name=Sheet1 Log ${value} ``` -------------------------------- ### Get Row Count Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the number of data rows in a sheet. Can include/exclude header and ignore empty rows. ```APIDOC ## Get Row Count ### Description Returns the number of data rows in a sheet. Can include/exclude header and ignore empty rows. ### Method GET (conceptual) ### Endpoint /excel/row_count ### Parameters #### Query Parameters - **sheet_name** (string) - Optional - The name of the sheet to count rows from. Defaults to the active sheet. - **include_header** (boolean) - Optional - Whether to include the header row in the count. Defaults to False. - **starting_cell** (string) - Optional - The cell from which to start counting rows. Defaults to "A1". - **ignore_empty_rows** (boolean) - Optional - Whether to ignore empty rows in the count. Defaults to False. ### Request Example ```python # Get row count (excluding header by default) count = excel.get_row_count(sheet_name="Sheet1") # Include header in count total = excel.get_row_count( sheet_name="Sheet1", include_header=True ) ``` ### Response #### Success Response (200) - **count** (integer) - The number of data rows in the sheet. #### Response Example ```json { "count": 100 } ``` ``` -------------------------------- ### Open Workbook with ExcelSage Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Opens an existing Excel workbook. Supports aliases for managing multiple workbooks and passes through openpyxl options. ```python from ExcelSage import ExcelSage excel = ExcelSage() # Open a single workbook workbook = excel.open_workbook(workbook_name="data/employees.xlsx") # Open multiple workbooks with aliases excel.open_workbook(workbook_name="data/source.xlsx", alias="source") excel.open_workbook(workbook_name="data/target.xlsx", alias="target") # Open with additional openpyxl options excel.open_workbook( workbook_name="data/macro_file.xlsm", alias="macros", read_only=False, keep_vba=True ) ``` -------------------------------- ### Fetch Sheet Data in Python Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Retrieves data from a specified sheet in an Excel workbook, with options for output format. Requires the workbook to be opened first. ```python # Fetch data from a specific sheet data = excel_sage.fetch_sheet_data(sheet_name="Sheet1", starting_cell="D6", output_format="dataframe") ``` -------------------------------- ### Format Cells in Python Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Allows formatting of specific cells, including font size, color, bold style, and background color. Apply formatting to cells within an opened workbook. ```python # Format cell A1 in Sheet1 excel_sage.format_cell(cell_name="A1", font_size=12, font_color="#FF0000", sheet_name="Sheet1", bold=True, bg_color="#FFFF00") ``` -------------------------------- ### Get Cell Value Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves the value of a specific cell from a given sheet. Returns None if the cell is empty. ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/workbook.xlsx") # Get single cell value name = excel.get_cell_value(cell_name="A2", sheet_name="Employees") print(f"Employee name: {name}") ``` -------------------------------- ### Workbook Management - Open Workbook Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Opens an existing Excel workbook. Supports aliases for managing multiple workbooks and passing openpyxl options. ```APIDOC ## Open Workbook ### Description Opens an existing Excel workbook for manipulation. Supports optional aliases to manage multiple workbooks simultaneously, and passes through openpyxl options like `read_only` and `keep_vba`. ### Method POST ### Endpoint /open_workbook ### Parameters #### Query Parameters - **workbook_name** (string) - Required - The path to the Excel file. - **alias** (string) - Optional - An alias to refer to this workbook. - **read_only** (boolean) - Optional - Whether to open the workbook in read-only mode. - **keep_vba** (boolean) - Optional - Whether to keep VBA macros. ### Request Example ```python from ExcelSage import ExcelSage excel = ExcelSage() # Open a single workbook workbook = excel.open_workbook(workbook_name="data/employees.xlsx") # Open multiple workbooks with aliases excel.open_workbook(workbook_name="data/source.xlsx", alias="source") excel.open_workbook(workbook_name="data/target.xlsx", alias="target") # Open with additional openpyxl options excel.open_workbook( workbook_name="data/macro_file.xlsm", alias="macros", read_only=False, keep_vba=True ) ``` ### Response #### Success Response (200) - **workbook** (object) - The opened workbook object. #### Response Example ```json { "workbook": "" } ``` ``` -------------------------------- ### Robot Framework - Protect Sheet Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Protects a specified sheet in the workbook with a password. ```robotframework Protect Sheet password=my_password sheet_name=Sheet1 ``` -------------------------------- ### Get Multiple Cell Values Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from multiple specified cells. Ensure the workbook is open before calling this function. ```python cells = ["A1", "B1", "C1", "D1"] headers = [excel.get_cell_value(cell_name=cell) for cell in cells] print(f"Headers: {headers}") excel.close_workbook() ``` -------------------------------- ### Get Row Values Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves values from specified row indices. Supports single or multiple rows and list/dict output formats. ```APIDOC ## Get Row Values ### Description Retrieves values from specified row indices. Supports single or multiple rows and list/dict output formats. ### Method GET (conceptual) ### Endpoint /excel/row_values ### Parameters #### Query Parameters - **row_indices** (integer or list of integers) - Required - The index(es) of the row(s) to retrieve. - **output_format** (string) - Optional - The desired format for the output. Supported values: "list", "dict". Defaults to "list". - **sheet_name** (string) - Optional - The name of the sheet to read from. Defaults to the active sheet. ### Request Example ```python # Get single row row_data = excel.get_row_values( row_indices=2, output_format="list", sheet_name="Sheet1" ) # Get rows as dictionary row_dict = excel.get_row_values( row_indices=[1, 2, 3], output_format="dict" ) ``` ### Response #### Success Response (200) - **data** (list or dict) - The retrieved row values in the specified format. #### Response Example ```json { "data": ["John", 30, "Engineering", 50000] } ``` ``` -------------------------------- ### Column Count Should Be Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Asserts that a sheet contains the expected number of columns. ```APIDOC ## POST /api/sheets/column_count ### Description Asserts that a specified sheet has the expected number of columns. ### Method POST ### Endpoint /api/sheets/column_count ### Parameters #### Request Body - **expected_count** (integer) - Required - The expected number of columns. - **sheet_name** (string) - Optional - The name of the sheet. Defaults to the active sheet. ### Request Example { "expected_count": 8, "sheet_name": "Sheet1" } ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example { "status": "Column count matches expected value" } ``` -------------------------------- ### Robot Framework - Merge Excels Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Merges multiple Excel files into a single output file. Supports different merge types like 'sheet_wise'. ```robotframework Merge Excels file_list=[file1.xlsx, file2.xlsx] output_filename=merged_output.xlsx merge_type=sheet_wise ``` -------------------------------- ### Get Single Column Values by Letter Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Retrieves all values from a single column identified by its letter. The output format defaults to 'list'. ```python ages = excel.get_column_values( column_names_or_letters="B", output_format="list" ) ``` -------------------------------- ### Protect and Unprotect Sheets and Workbooks Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Use these keywords to protect individual sheets or the entire workbook with a password. Setting `unprotect_sheets` to `True` will unprotect all sheets. ```robotframework *** Test Cases *** Protect a Sheet Protect Sheet password=my_password sheet_name=Sheet1 Unprotect a Workbook Unprotect Workbook password=my_password unprotect_sheets=True ``` -------------------------------- ### Cell Should Be Empty Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Asserts that a cell is empty. ```APIDOC ## POST /api/cells/empty ### Description Asserts that a specific cell is empty (contains None or an empty string). ### Method POST ### Endpoint /api/cells/empty ### Parameters #### Request Body - **cell_name** (string) - Required - The name or address of the cell (e.g., "F5"). - **sheet_name** (string) - Optional - The name of the sheet. Defaults to the active sheet. - **message** (string) - Optional - A custom message to display on failure. ### Request Example { "cell_name": "F5", "sheet_name": "Sheet1" } ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example { "status": "Cell is empty as expected" } ``` -------------------------------- ### Robot Framework - Format Cell Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Applies various formatting options to a specific cell, including font size, color, bold, italic, and background color. ```robotframework Format Cell A1 font_size=12 font_color=FF0000 sheet_name=Sheet1 bold=True bg_color=FFFF00 ``` ```robotframework Format a Cell Format Cell A1 font_size=14 font_color=#0000FF sheet_name=Sheet1 bold=True italic=True bg_color=#FFFFAA ``` -------------------------------- ### Workbook Management - Switch Workbook Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Switches the active workbook using its alias. Essential for operations involving multiple workbooks. ```APIDOC ## Switch Workbook ### Description Switches between multiple open workbooks using their aliases. Essential for operations that involve reading from one workbook and writing to another. ### Method POST ### Endpoint /switch_workbook ### Parameters #### Query Parameters - **alias** (string) - Required - The alias of the workbook to switch to. ### Request Example ```python from ExcelSage import ExcelSage excel = ExcelSage() # Open multiple workbooks excel.open_workbook(workbook_name="data/source.xlsx", alias="source") excel.open_workbook(workbook_name="data/target.xlsx", alias="target") # Read from source excel.switch_workbook(alias="source") data = excel.fetch_sheet_data(sheet_name="Sheet1", output_format="list") # Write to target excel.switch_workbook(alias="target") for row in data: excel.append_row(row_data=row, sheet_name="Sheet1") excel.save_workbook() excel.close_workbook(alias="source") excel.close_workbook(alias="target") ``` ### Response #### Success Response (200) - **message** (string) - Confirmation that the workbook was switched. #### Response Example ```json { "message": "Switched to workbook 'source'" } ``` ``` -------------------------------- ### Get Row Count (Excluding Header) Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Returns the number of data rows in a sheet, excluding the header row by default. Specify the sheet name. ```python count = excel.get_row_count(sheet_name="Sheet1") print(f"Data rows: {count}") ``` -------------------------------- ### Private Helper Methods Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Internal helper methods used by the ExcelSage library. These are typically not meant for direct use in test cases. ```APIDOC ## Private Helper Methods ### Description These are internal helper methods and are not intended for direct use in test scripts. ### Methods - `__get_active_workbook(self)`: Fetches the active workbook instance. - `__get_active_workbook_name(self)`: Fetches the active workbook name. - `__get_active_sheet_name(self, sheet_name)`: Fetches the name of the active sheet. - `__argument_type_checker(self, arg_list)`: Validates the types of provided arguments. - `__get_column_values_by_name_or_letter(self, sheet, column_name_or_letter)`: Fetches column values by header or column letter. ``` -------------------------------- ### Copy Sheet in Workbook Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Creates a copy of an existing sheet with a new name, preserving all data and formatting. ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/template.xlsx") # Copy sheet with new name new_sheet = excel.copy_sheet( source_sheet_name="Template", new_sheet_name="January_Report" ) print(f"Created: {new_sheet}") # Create multiple copies for different months months = ["February", "March", "April"] for month in months: excel.copy_sheet( source_sheet_name="Template", new_sheet_name=f"{month}_Report" ) excel.save_workbook() excel.close_workbook() ``` -------------------------------- ### Row Count Should Be Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Asserts that a sheet contains the expected number of rows. ```APIDOC ## POST /api/sheets/row_count ### Description Asserts that a specified sheet has the expected number of rows. ### Method POST ### Endpoint /api/sheets/row_count ### Parameters #### Request Body - **expected_count** (integer) - Required - The expected number of rows. - **sheet_name** (string) - Optional - The name of the sheet. Defaults to the active sheet. - **message** (string) - Optional - A custom message to display on failure. ### Request Example { "expected_count": 51, "sheet_name": "Sheet1" } ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example { "status": "Row count matches expected value" } ``` -------------------------------- ### Workbook Management API Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md APIs for managing Excel workbooks, including opening, creating, closing, and switching between workbooks. ```APIDOC ## Open Workbook ### Description Opens an existing Excel workbook. ### Method `open_workbook(self, workbook_name)` ### Parameters - **workbook_name** (string) - Required - The name or path of the workbook to open. ``` ```APIDOC ## Create Workbook ### Description Creates a new Excel workbook. ### Method `create_workbook(self, workbook_name, overwrite_if_exists, sheet_data)` ### Parameters - **workbook_name** (string) - Required - The name for the new workbook. - **overwrite_if_exists** (boolean) - Required - If true, overwrites the workbook if it already exists. - **sheet_data** (dict) - Optional - Initial data for the first sheet. ``` ```APIDOC ## Close Workbook ### Description Closes the currently active workbook. ### Method `close_workbook(self)` ``` ```APIDOC ## Switch Workbook ### Description Switches the active workbook using its alias. ### Method `switch_workbook(self, alias)` ### Parameters - **alias** (string) - Required - The alias of the workbook to switch to. ``` ```APIDOC ## Save Workbook ### Description Saves the currently active workbook. ### Method `save_workbook(self)` ``` -------------------------------- ### Sheet Should Exist Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Asserts that a sheet exists within the workbook. ```APIDOC ## POST /api/sheets/exist ### Description Asserts that a sheet with the given name exists in the currently open workbook. ### Method POST ### Endpoint /api/sheets/exist ### Parameters #### Request Body - **sheet_name** (string) - Required - The name of the sheet to check for. - **message** (string) - Optional - A custom message to display on failure. ### Request Example { "sheet_name": "Summary" } ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example { "status": "Sheet exists" } ``` -------------------------------- ### Robot Framework - Rename Sheet Source: https://github.com/deekshith-poojary98/robotframework-excelsage/blob/main/README.md Renames an existing sheet in the workbook from an old name to a new name. ```robotframework Rename Sheet old_name=OldSheet new_name=NewSheet ``` -------------------------------- ### Switch Between Workbooks with ExcelSage Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Switches the active workbook using its alias. Essential for operations involving multiple workbooks, like reading from one and writing to another. ```python from ExcelSage import ExcelSage excel = ExcelSage() # Open multiple workbooks excel.open_workbook(workbook_name="data/source.xlsx", alias="source") excel.open_workbook(workbook_name="data/target.xlsx", alias="target") # Read from source excel.switch_workbook(alias="source") data = excel.fetch_sheet_data(sheet_name="Sheet1", output_format="list") # Write to target excel.switch_workbook(alias="target") for row in data: excel.append_row(row_data=row, sheet_name="Sheet1") excel.save_workbook() excel.close_workbook(alias="source") excel.close_workbook(alias="target") ``` -------------------------------- ### Format Cell in Excel Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Applies comprehensive formatting to a cell including font, color, alignment, borders, and dimensions. Ensure the workbook is opened before calling this function. ```python from ExcelSage import ExcelSage excel = ExcelSage() excel.open_workbook(workbook_name="data/report.xlsx") # Basic font formatting excel.format_cell( cell_name="A1", font_size=14, font_name="Arial", bold=True, font_color="#0000FF", sheet_name="Sheet1" ) # Full formatting with alignment and background excel.format_cell( cell_name="B1", font_size=12, font_color="#FFFFFF", bg_color="#4472C4", bold=True, alignment={"horizontal": "center", "vertical": "center"}, wrap_text=True, cell_width=20, cell_height=30, sheet_name="Sheet1" ) # Apply borders excel.format_cell( cell_name="C1", border={ "left": True, "right": True, "top": True, "bottom": True, "style": "thin", "color": "#000000" }, sheet_name="Sheet1" ) # Text effects excel.format_cell( cell_name="D1", italic=True, underline=True, strike_through=False, auto_fit_width=True, sheet_name="Sheet1" ) excel.save_workbook() excel.close_workbook() ``` -------------------------------- ### Column Should Contain Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Asserts that a column contains a specific value. ```APIDOC ## POST /api/columns/contain ### Description Asserts that a specified column in a sheet contains the expected value. ### Method POST ### Endpoint /api/columns/contain ### Parameters #### Request Body - **column_name_or_letter** (string) - Required - The name or letter of the column (e.g., "A" or "Department"). - **expected_value** (any) - Required - The value to search for within the column. - **sheet_name** (string) - Optional - The name of the sheet. Defaults to the active sheet. - **message** (string) - Optional - A custom message to display on failure. ### Request Example { "column_name_or_letter": "Department", "expected_value": "Engineering", "sheet_name": "Sheet1" } ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example { "status": "Column contains the expected value" } ``` -------------------------------- ### Assert Sheet Exists Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Use to assert that a sheet with the given name exists within the opened workbook. Custom messages can be provided for specific error reporting. ```robotframework excel.sheet_should_exist(sheet_name="Summary") ``` ```robotframework excel.sheet_should_exist( sheet_name="Q4_Report", message="Q4 Report sheet is required" ) ``` -------------------------------- ### Insert Column Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Inserts a new column at a specific index, shifting existing columns right. Column index must be between 1 and 16,384. ```APIDOC ## Insert Column Inserts a new column at a specific index, shifting existing columns right. Column index must be between 1 and 16,384. ### Method `excel.insert_column(col_data, col_index, sheet_name=None)` ### Parameters #### Arguments - **col_data** (list) - Required - The data for the new column. - **col_index** (int) - Required - The index at which to insert the column (1-based). - **sheet_name** (str) - Optional - The name of the sheet to insert the column into. Defaults to the active sheet. ### Request Example ```python # Insert column at position 2 excel.insert_column( col_data=["Employee ID", "E001", "E002", "E003"], col_index=2, sheet_name="Sheet1" ) # Insert column at the beginning excel.insert_column( col_data=["Row Number", 1, 2, 3, 4, 5], col_index=1 ) excel.save_workbook() excel.close_workbook() ``` ``` -------------------------------- ### Assert Cell Is Empty Source: https://context7.com/deekshith-poojary98/robotframework-excelsage/llms.txt Use to assert that a cell is empty (None or an empty string). Custom messages can be provided for specific failure contexts. ```robotframework excel.cell_should_be_empty( cell_name="F5", sheet_name="Sheet1" ) ``` ```robotframework excel.cell_should_be_empty( cell_name="G10", sheet_name="Sheet1", message="Notes column should be empty for this row" ) ```