How to use Range.Insert in the xlwings API way

The Insert method of the Range object in the Excel object model is a powerful tool for dynamically modifying worksheets by shifting cells to accommodate new data or formatting. In xlwings, this functionality is exposed through the api property, allowing Python scripts to leverage Excel’s native capabilities seamlessly. This method is particularly useful for inserting cells, rows, or columns without manually moving existing content, thereby streamlining data manipulation tasks in automated workflows.

Functionality
The Insert method inserts a range of cells into a worksheet, shifting other cells in the specified direction to make space. It can be applied to insert entire rows, columns, or a specific cell range. This is essential for tasks like adding new data entries, expanding tables, or reformatting layouts without disrupting existing structures.

Syntax
In xlwings, the method is accessed via the api property of a Range object. The syntax is:

range_obj.api.Insert(Shift, CopyOrigin)

• Shift (optional): Specifies the direction to shift cells. It accepts values from the XlInsertShiftDirection enumeration:
• xlShiftToRight (or -4161): Shifts cells to the right.
• xlShiftDown (or -4121): Shifts cells downward (default if omitted).
• CopyOrigin (optional): Specifies the origin for copying formatting. It uses values from the XlInsertFormatOrigin enumeration:
• xlFormatFromLeftOrAbove (or 0): Copies formatting from left or above (default).
• xlFormatFromRightOrBelow (or 1): Copies formatting from right or below.

If parameters are omitted, Excel defaults to shifting down and copying formatting from above or left.

Code Examples
Below are practical examples demonstrating the Insert method with xlwings.

1. Insert a single cell and shift down:
   This inserts a cell at B2, moving existing cells in column B downward.

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
ws.range('B2').api.Insert(Shift=-4121) # xlShiftDown
wb.save()

2. Insert an entire row:
   Inserts a new row at row 5, shifting rows below it down. Formatting is copied from the row above.

ws.range('5:5').api.Insert(Shift=-4121, CopyOrigin=0)

3. Insert a column shifting right:
   Adds a new column at column C, moving existing columns to the right, with formatting from the left column.

ws.range('C:C').api.Insert(Shift=-4161, CopyOrigin=0) # xlShiftToRight

4. Insert a range of cells:
   Inserts a 2×2 block starting at D4, shifting cells down, and uses formatting from above or left.

ws.range('D4:E5').api.Insert(Shift=-4121)