예제 #1
0
    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
예제 #2
0
    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
예제 #3
0
    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
예제 #4
0
 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
예제 #5
0
파일: table.py 프로젝트: zyxws012/PTVS
 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
예제 #6
0
파일: table.py 프로젝트: xNUTs/PTVS
 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)
예제 #7
0
파일: table.py 프로젝트: zyxws012/PTVS
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)
예제 #8
0
 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
예제 #9
0
파일: table.py 프로젝트: xNUTs/PTVS
    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
예제 #10
0
    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)
예제 #11
0
파일: table.py 프로젝트: xNUTs/PTVS
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)
예제 #12
0
파일: tools.py 프로젝트: xNUTs/PTVS
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()