How to use xlwings - 10 common examples

def check_cell(address, value):
    # Active Sheet
    Range(address).value = value
    cell = Range(address).value

    assert_equal(cell, value)

    # SheetName
    Range('Sheet2', address).value = value
    cell = Range('Sheet2', address).value
    assert_equal(cell, value)

    # SheetIndex
    Range(3, address).value = value
    cell = Range(3, address).value
    assert_equal(cell, value)

def test_range_address():
    """ Style: Range('A1:C3') """
    address = 'C1:E3'

    # Active Sheet
    Range(address[:2]).value = data  # assign to starting cell only
    cells = Range(address).value
    assert_equal(cells, data)

    # Sheetname
    Range('Sheet2', address).value = data
    cells = Range('Sheet2', address).value
    assert_equal(cells, data)

    # Sheetindex
    Range(3, address).value = data
    cells = Range(3, address).value
    assert_equal(cells, data)

def test_dataframe():
    df_expected = DataFrame({'a': [1, 2, 3.3, np.nan], 'b': ['test1', 'test2', 'test3', None]})
    Range('Sheet5', 'A1').value = df_expected
    cells = Range('Sheet5', 'B1:C5').value
    df_result = DataFrame(cells[1:], columns=cells[0])
    assert_frame_equal(df_expected, df_result)

def test_horizontal():
    Range('Sheet4', 'A20').value = data
    cells = Range('Sheet4', 'A20').horizontal.value
    assert_equal(cells, [data[0]])

Defaults to the Workbook that was instantiated last or set via ``Workbook.set_current()``.

        Examples
        --------
        >>> Sheet.all()
        [, ]
        >>> [i.name.lower() for i in Sheet.all()]
        ['sheet1', 'sheet2']
        >>> [i.autofit() for i in Sheet.all()]

        .. versionadded:: 0.2.3
        """
        xl_workbook = Workbook.get_xl_workbook(wkb)
        sheet_list = []
        for i in range(1, xlplatform.count_worksheets(xl_workbook) + 1):
            sheet_list.append(Sheet(i, wkb=wkb))

        return sheet_list

address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, Range):
            row2 = max(arg1.row + arg1.shape[0] - 1, arg2.row + arg2.shape[0] - 1)
            col2 = max(arg1.column + arg1.shape[1] - 1, arg2.column + arg2.shape[1] - 1)
            address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, string_types):
            address2 = arg2
        elif arg2 is None:
            if isinstance(arg1, string_types) and len(arg1.split(':')) == 2:
                address2 = arg1.split(':')[1]
            else:
                return Range(self, "{0}".format(address1))
        else:
            raise ValueError("Invalid parameters")

        return Range(self, "{0}:{1}".format(address1, address2))

def range(self, arg1, arg2=None):
        if isinstance(arg1, tuple):
            if len(arg1) == 2:
                if 0 in arg1:
                    raise IndexError("Attempted to access 0-based Range. xlwings/Excel Ranges are 1-based.")
                row1 = arg1[0]
                col1 = arg1[1]
                address1 = self.xl.rows[row1].columns[col1].get_address()
            elif len(arg1) == 4:
                return Range(self, arg1)
            else:
                raise ValueError("Invalid parameters")
        elif isinstance(arg1, Range):
            row1 = min(arg1.row, arg2.row)
            col1 = min(arg1.column, arg2.column)
            address1 = self.xl.rows[row1].columns[col1].get_address()
        elif isinstance(arg1, string_types):
            address1 = arg1.split(':')[0]
        else:
            raise ValueError("Invalid parameters")

        if isinstance(arg2, tuple):
            if 0 in arg2:
                raise IndexError("Attempted to access 0-based Range. xlwings/Excel Ranges are 1-based.")
            row2 = arg2[0]
            col2 = arg2[1]

if 0 in arg2:
                raise IndexError("Attempted to access 0-based Range. xlwings/Excel Ranges are 1-based.")
            row2 = arg2[0]
            col2 = arg2[1]
            address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, Range):
            row2 = max(arg1.row + arg1.shape[0] - 1, arg2.row + arg2.shape[0] - 1)
            col2 = max(arg1.column + arg1.shape[1] - 1, arg2.column + arg2.shape[1] - 1)
            address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, string_types):
            address2 = arg2
        elif arg2 is None:
            if isinstance(arg1, string_types) and len(arg1.split(':')) == 2:
                address2 = arg1.split(':')[1]
            else:
                return Range(self, "{0}".format(address1))
        else:
            raise ValueError("Invalid parameters")

        return Range(self, "{0}:{1}".format(address1, address2))

elif isinstance(arg1, Range):
            row1 = min(arg1.row, arg2.row)
            col1 = min(arg1.column, arg2.column)
            address1 = self.xl.rows[row1].columns[col1].get_address()
        elif isinstance(arg1, string_types):
            address1 = arg1.split(':')[0]
        else:
            raise ValueError("Invalid parameters")

        if isinstance(arg2, tuple):
            if 0 in arg2:
                raise IndexError("Attempted to access 0-based Range. xlwings/Excel Ranges are 1-based.")
            row2 = arg2[0]
            col2 = arg2[1]
            address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, Range):
            row2 = max(arg1.row + arg1.shape[0] - 1, arg2.row + arg2.shape[0] - 1)
            col2 = max(arg1.column + arg1.shape[1] - 1, arg2.column + arg2.shape[1] - 1)
            address2 = self.xl.rows[row2].columns[col2].get_address()
        elif isinstance(arg2, string_types):
            address2 = arg2
        elif arg2 is None:
            if isinstance(arg1, string_types) and len(arg1.split(':')) == 2:
                address2 = arg1.split(':')[1]
            else:
                return Range(self, "{0}".format(address1))
        else:
            raise ValueError("Invalid parameters")

        return Range(self, "{0}:{1}".format(address1, address2))

Keyword Arguments
        -----------------
        wkb : Workbook object, default Workbook.current()
            Defaults to the Workbook that was instantiated last or set via ``Workbook.set_current()``.

        Examples
        --------
        >>> Sheet.all()
        [, ]
        >>> [i.name.lower() for i in Sheet.all()]
        ['sheet1', 'sheet2']
        >>> [i.autofit() for i in Sheet.all()]

        .. versionadded:: 0.2.3
        """
        xl_workbook = Workbook.get_xl_workbook(wkb)
        sheet_list = []
        for i in range(1, xlplatform.count_worksheets(xl_workbook) + 1):
            sheet_list.append(Sheet(i, wkb=wkb))

        return sheet_list