def view(self, obj, name=None, to=None):
"""Writes a Python iterable to an available location in the workbook, with an optional header (name).
The optional `to` argument specifies a location hint.
If None, the values are written to an empty column on the active sheet.
If `to` is a Range, the values are written to it (like Range.set, but with the header prepended)
If `to` is a Table, the values are written to a new column in the table."""
# Python version of splatting to cells.
if to is None:
ws = self.active_sheet
# $$$ is this where with_hidden should come from?
c = Range(ws.xlWorksheet.Columns(ws._findOpenColumn()),
with_hidden=False)
elif isinstance(to, table.Table):
c = to.append_empty_columns(num_new_cols=1)
elif isinstance(to, Range):
c = to
else:
raise ValueError("'to' argument must be a Range, Table, or None")
# write a header, this will will cooperate with autofilters.
if (name == None):
name = "values"
if isinstance(obj, basestring):
obj = [obj]
obj = list(obj)
vals = [name] + obj
c.set(vals)
data_only = c._adjust_unfiltered_size(rows=-1)._offset_unfiltered(
rows=1)
return data_only
def append_empty_columns(self, num_new_cols):
"""Appends the specified number of columns to the right of this table. The columns are empty,
except for the possibility of Excel-generated default column headers. The inserted range,
including headers, is returned"""
# We assume below that at least one column is added
# $$$ Decide how to represent empty Ranges()
if num_new_cols == 0: return None
adjacent = self._adjacent_column_range(num_new_cols)
self._reserve_column_space(adjacent)
# The insert has helpfully updated xlRanges from underneath us. That is, adjacent has shifted by num_new_cols
adjacent = self._adjacent_column_range(num_new_cols)
# AutoFilter tables are hard to extend, but easy to promote to a 'real' table
if self._from_auto_filter: self._convert_to_listobject_table()
# For ListObject tables, putting a value in a column header triggers table-ification magic
# Removing the value generates a default column name. Neat.
# This accomplishes nothing if this is an AutoFilter table
# $$$ update this when slicing is added
adj_header_range = Range(adjacent._full_xlRange.Rows(1), with_hidden=True)
adj_header_range.set( [u" "] * num_new_cols )
adj_header_range.set( [u""] * num_new_cols )
# adjacent is now a subset of the inserted empty space
# However, this instance's rData and rHeader attributes are now out of date
# We have been possibly using hidden cells above, but want to return a safer range to users
# $$$ investigate if updating rData / rHeader is vital
return adjacent.excluding_hidden
def view(self, obj, name=None, to=None):
"""Writes a Python iterable to an available location in the workbook, with an optional header (name).
The optional `to` argument specifies a location hint.
If None, the values are written to an empty column on the active sheet.
If `to` is a Range, the values are written to it (like Range.set, but with the header prepended)
If `to` is a Table, the values are written to a new column in the table."""
# Python version of splatting to cells.
if to is None:
ws = self.active_sheet
# $$$ is this where with_hidden should come from?
c = Range(ws.xlWorksheet.Columns(ws._findOpenColumn()), with_hidden=False)
elif isinstance(to, table.Table):
c = to.append_empty_columns(num_new_cols=1)
elif isinstance(to, Range):
c = to
else:
raise ValueError("'to' argument must be a Range, Table, or None")
# write a header, this will will cooperate with autofilters.
if (name == None):
name = "values"
if isinstance(obj, basestring):
obj = [ obj ]
obj = list(obj)
vals = [ name ] + obj
c.set(vals)
data_only = c._adjust_unfiltered_size(rows=-1)._offset_unfiltered(rows=1)
return data_only
def _adjacent_column_range(self, num_cols):
"""Returns a num_cols-wide range right-adjacent to this table. The range shares the same height, incl.
the header row if applicable. This does not modify the worksheet. The returned range includes hidden cells."""
# $$$ update this when slicing is added
# We remove filtering here, because we should insert after any hidden cols
full_table = self.table_range.including_hidden
last_existing_col = Range(full_table._full_xlRange.Columns(full_table.num_columns), with_hidden=True)
# first_new_col_xlRange = last_existing_col_xlRange._offset_unfiltered(0, 1)
first_new_col = last_existing_col._offset_unfiltered(cols=1)
# Add additional columns beyond the first
new_cols = first_new_col._adjust_unfiltered_size(cols=num_cols - 1)
return new_cols
def _adjacent_column_range(self, num_cols):
"""Returns a num_cols-wide range right-adjacent to this table. The range shares the same height, incl.
the header row if applicable. This does not modify the worksheet. The returned range includes hidden cells."""
# $$$ update this when slicing is added
# We remove filtering here, because we should insert after any hidden cols
full_table = self.table_range.including_hidden
last_existing_col = Range(full_table._full_xlRange.Columns(full_table.num_columns), with_hidden=True)
# first_new_col_xlRange = last_existing_col_xlRange._offset_unfiltered(0, 1)
first_new_col = last_existing_col._offset_unfiltered(cols=1)
# Add additional columns beyond the first
new_cols = first_new_col._adjust_unfiltered_size(cols=num_cols - 1)
return new_cols
def table_range(self):
"""The full Range of this table; encompasses headers (if any) as well as data"""
assert not self.rData is None
app = self.rData._full_xlRange.Application
if self.rHeader is None: return self.rData
return Range(app.Union(self.rData._full_xlRange,
self.rHeader._full_xlRange),
with_hidden=False)
def tableFromAutoFilter(xlSheet):
"""Each excel sheet can have 1 auto-filter. Return it if present. Else return None."""
a = xlSheet.AutoFilter
if a == None:
return None # no autofilter on this sheet
# We have to manually split out the header and range.
r = a.Range
# In certain peculiar cases, Worksheet.AutoFilter is set, but
# actually refers to a ListObject table. See excel_issues.py
if r.ListObject != None: return None
(r1,c1,r2,c2) = _getBounds(r)
rHeader = Range(xlSheet.Range(xlSheet.Cells(r1, c1), xlSheet.Cells(r1, c2)), with_hidden=False)
rData = Range(xlSheet.Range(xlSheet.Cells(r1+1, c1), xlSheet.Cells(r2, c2)), with_hidden=False)
return Table("AutoFilter " + xlSheet.Name, rHeader, rData, from_auto_filter=True)
def _get_named_range(self, name):
name = name.lower()
for n in self.xlWorkbook.Names:
if n.Name.lower() == name:
r = n.RefersToRange
# excel allows Names that are bound directly to Values and not ranges on the spreadsheet
if r == None:
raise NotImplementedError("Name " + name + " is not backed by a range")
# $$$ what should with_hidden be here
return Range(r, with_hidden=False)
return None
def append_empty_columns(self, num_new_cols):
"""Appends the specified number of columns to the right of this table. The columns are empty,
except for the possibility of Excel-generated default column headers. The inserted range,
including headers, is returned"""
# We assume below that at least one column is added
# $$$ Decide how to represent empty Ranges()
if num_new_cols == 0: return None
adjacent = self._adjacent_column_range(num_new_cols)
self._reserve_column_space(adjacent)
# The insert has helpfully updated xlRanges from underneath us. That is, adjacent has shifted by num_new_cols
adjacent = self._adjacent_column_range(num_new_cols)
# AutoFilter tables are hard to extend, but easy to promote to a 'real' table
if self._from_auto_filter: self._convert_to_listobject_table()
# For ListObject tables, putting a value in a column header triggers table-ification magic
# Removing the value generates a default column name. Neat.
# This accomplishes nothing if this is an AutoFilter table
# $$$ update this when slicing is added
adj_header_range = Range(adjacent._full_xlRange.Rows(1),
with_hidden=True)
adj_header_range.set([u" "] * num_new_cols)
adj_header_range.set([u""] * num_new_cols)
# adjacent is now a subset of the inserted empty space
# However, this instance's rData and rHeader attributes are now out of date
# We have been possibly using hidden cells above, but want to return a safer range to users
# $$$ investigate if updating rData / rHeader is vital
return adjacent.excluding_hidden
def range(self, object):
"""Returns a Range for the requested named Excel range or Excel address.
The returned range is not normalized, e.g. range("A:A") returns a Range containing ~1mil rows,
rather than clipping to the 'used range' / table areas. See also `get`"""
# Named ranges are workbook wide, but we don't have a workbook lookup function. So explicitly
# check for them now.
r = self._get_named_range(object)
if r != None:
return r
# $$$ Is there a better way (avoid needing the sheet), especially for sheet qualified ranges?
xlSheet = self.xlWorkbook.ActiveSheet
# _xlRange_parse throws an ExcelRangeError if it fails
xlRange = _xlRange_parse(xlSheet, object)
# Un-normalized range is returned; if the user specifies A2:D10, they probably meant it
# $$$ what should with_hidden be here
return Range(xlRange, with_hidden=False)
def tableFromListObject(xlListObject):
"""Given an ListObject, return a Table abstraction"""
# See more about ListObjects: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobject_members.aspx
rHeader = Range(xlListObject.HeaderRowRange, with_hidden=False)
rData = Range(xlListObject.DataBodyRange, with_hidden=False)
return Table(xlListObject.Name, rHeader, rData, from_auto_filter=False)
def selected_range():
"""Gets the currently selected range. The returned range filters
hidden cells by default"""
wb = Workbook.default_workbook()
xlApp = wb.xlWorkbook.Application
return Range(xlApp.Selection, with_hidden=False).normalize()