def set_selection_in_ipython(*args):
"""Gets the value of the selected cell and copies it to
the globals dict in the IPython kernel.
"""
from pyxll import xl_app, XLCell
try:
if not getattr(sys, "_ipython_app",
None) or not sys._ipython_kernel_running:
raise Exception("IPython kernel not running")
# Get the current selected range
xl = xl_app(com_package="win32com")
selection = xl.Selection
if not selection:
raise Exception("Nothing selected")
# Check to see if it looks like a pandas DataFrame
try_dataframe = False
has_index = False
if selection.Rows.Count > 1 and selection.Columns.Count > 1:
try:
import pandas as pd
except ImportError:
pd = None
pass
if pd is not None:
# If the top left corner is empty assume the first column is an index.
try_dataframe = True
top_left = selection.Cells[1].Value
if top_left is None:
has_index = True
# Get an XLCell object from the range to make it easier to get the value
cell = XLCell.from_range(selection)
# Get the value using PyXLL's dataframe converter, or as a plain value.
value = None
if try_dataframe:
try:
type_kwargs = {"index": 1 if has_index else 0}
value = cell.options(type="dataframe",
type_kwargs=type_kwargs).value
except:
_log.warning("Error converting selection to DataFrame",
exc_info=True)
if value is None:
value = cell.value
# set the value in the shell's locals
sys._ipython_app.shell.user_ns["_"] = value
print("\n\n>>> Selected value set as _")
except:
app = _get_qt_app()
QMessageBox.warning(None, "Error", "Error setting selection in Excel")
_log.error("Error setting selection in Excel", exc_info=True)
def xl_set(self, line):
"""Set a value to the current selection in Excel."""
argv = self._split_args(line)
args = self.xl_set.parser.parse_args(argv)
value = eval(args.value, self.shell.user_ns, self.shell.user_ns)
xl = xl_app(com_package="win32com")
# Get the specified range, or use the current selection
if args.cell:
selection = xl.Range(args.cell.strip("\"' "))
else:
selection = xl.Selection
if not selection:
raise Exception("Nothing selected")
# Get an XLCell object from the range to make it easier to set the value
cell = XLCell.from_range(selection)
if not args.no_auto_resize:
cell = cell.options(auto_resize=True)
if args.formatter:
formatter = eval(args.formatter, self.shell.user_ns,
self.shell.user_ns)
cell = cell.options(formatter=formatter)
if args.type:
cell = cell.options(type=args.type)
else:
try:
import pandas as pd
except ImportError:
pd = None
if pd is not None and isinstance(value, pd.DataFrame):
type_kwargs = {}
if value.index.name:
type_kwargs["index"] = True
cell = cell.options(type="dataframe", type_kwargs=type_kwargs)
# Finally set the value in Excel
cell.value = value
def xl_get(self, line):
"""Get the current selection in Excel into Python."""
argv = self._split_args(line)
args = self.xl_get.parser.parse_args(argv)
xl = xl_app(com_package="win32com")
# Get the specified range, or use the current selection
if args.cell:
selection = xl.Range(args.cell.strip("\"' "))
else:
selection = xl.Selection
if not selection:
raise Exception("Nothing selected")
# Expand the range if possible
if not args.no_auto_resize:
top_left = selection.Cells[1]
bottom_left = top_left.GetOffset(selection.Rows.Count-1, 0)
bottom_right = top_left.GetOffset(selection.Rows.Count-1, selection.Columns.Count-1)
# Check to see if there is a value below this range
below = bottom_left.GetOffset(1, 0)
if below.Value is not None:
new_bottom_left = bottom_left.End(self.xlDown)
# If the initial value is None then navigating down from it will only
# go to the next non-empty cell, not right to be bottom of the range.
# This happens if the top left cell is empty (above an index) and only
# that single cell is selected.
if selection.Count == 1 and bottom_left.Value is None:
new_below = new_bottom_left.GetOffset(1, 0)
if new_below.Value is not None:
new_bottom_left = new_bottom_left.End(self.xlDown)
new_bottom_right = new_bottom_left.GetOffset(0, selection.Columns.Count-1)
# Check to see if we can expand to the right as well
right = new_bottom_right.GetOffset(0, 1)
if right.Value is not None:
new_bottom_right = new_bottom_right.End(self.xlToRight)
if new_bottom_right.Row < bottom_right.Row:
new_bottom_right = new_bottom_left.GetOffset(0, selection.Columns.Count-1)
selection = xl.Range(top_left, new_bottom_right)
# Get an XLCell object from the range to make it easier to get the value
cell = XLCell.from_range(selection)
# If a type was passed use that
if args.type:
return cell.options(type=args.type).value
# Otherwise check to see if it looks like a pandas DataFrame
try_dataframe = False
has_index = False
if selection.Rows.Count > 1 and selection.Columns.Count > 1:
try:
import pandas as pd
except ImportError:
pd = None
if pd is not None:
# If the top left corner is empty assume the first column is an index.
try_dataframe = True
top_left = selection.Cells[1].Value
if top_left is None:
has_index = True
# Get the value using PyXLL's dataframe converter, or as a plain value.
value = None
if try_dataframe:
try:
type_kwargs = {"index": 1 if has_index else 0}
value = cell.options(type="dataframe", type_kwargs=type_kwargs).value
except:
_log.warning("Error converting selection to DataFrame", exc_info=True)
if value is None:
value = cell.value
return value