def test_auto_filter(self):
        ws = Worksheet(self.wb)
        ws.auto_filter.ref = ws.iter_rows('a1:f1')
        assert ws.auto_filter.ref == 'A1:F1'

        ws.auto_filter.ref = ''
        assert ws.auto_filter.ref is None

        ws.auto_filter.ref = 'c1:g9'
        assert ws.auto_filter.ref == 'C1:G9'
 def test_cell_range_name(self):
     ws = Worksheet(self.wb)
     self.wb.create_named_range('test_range_single', ws, 'B12')
     c_range_name = ws.get_named_range('test_range_single')
     c_range_coord = tuple(tuple(ws.iter_rows('B12'))[0])
     c_cell = ws.cell('B12')
     assert c_range_coord == (c_cell, )
     assert c_range_name == (c_cell, )
 def test_iter_rows(self, row, column, coordinate):
     from itertools import islice
     ws = Worksheet(self.wb)
     ws.cell('A1').value = 'first'
     ws.cell('C9').value = 'last'
     assert ws.calculate_dimension() == 'A1:C9'
     rows = ws.iter_rows()
     first_row = tuple(next(islice(rows, row - 1, row)))
     assert first_row[column].coordinate == coordinate
Exemple #4
0
def colorNext3Rows(ws: Worksheet, startRow):
    color = openpyxl.styles.Color(rgb='f0f0f0')
    for index, row in enumerate(
            ws.iter_rows(min_row=startRow, max_row=startRow + 2)):
        for cell in row:
            cell.fill = openpyxl.styles.PatternFill(patternType='solid',
                                                    fgColor=color)
 def test_cell_range_name(self):
     ws = Worksheet(self.wb)
     self.wb.create_named_range('test_range_single', ws, 'B12')
     c_range_name = ws.get_named_range('test_range_single')
     c_range_coord = tuple(tuple(ws.iter_rows('B12'))[0])
     c_cell = ws.cell('B12')
     assert c_range_coord == (c_cell,)
     assert c_range_name == (c_cell,)
Exemple #6
0
def applyBorders(ws: Worksheet):
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    for index, row in enumerate(ws.iter_rows()):
        for cell in row:
            cell.border = thin_border
 def test_iter_rows(self, row, column, coordinate):
     from itertools import islice
     ws = Worksheet(self.wb)
     ws.cell('A1').value = 'first'
     ws.cell('C9').value = 'last'
     assert ws.calculate_dimension() == 'A1:C9'
     rows = ws.iter_rows()
     first_row = tuple(next(islice(rows, row-1, row)))
     assert first_row[column].coordinate == coordinate
    def test_auto_filter(self):
        ws = Worksheet(self.wb)
        ws.auto_filter.ref = ws.iter_rows('a1:f1')
        assert ws.auto_filter.ref == 'A1:F1'

        ws.auto_filter.ref = ''
        assert ws.auto_filter.ref is None

        ws.auto_filter.ref = 'c1:g9'
        assert ws.auto_filter.ref == 'C1:G9'
    def test_iter_rows_offset(self):
        ws = Worksheet(self.wb)
        rows = ws.iter_rows('A1:C4', 1, 3)
        expected = [
            ('D2', 'E2', 'F2'),
            ('D3', 'E3', 'F3'),
            ('D4', 'E4', 'F4'),
            ('D5', 'E5', 'F5'),
        ]

        for row, coord in zip(rows, expected):
            assert tuple(c.coordinate for c in row) == coord
    def test_iter_rows(self, ):
        ws = Worksheet(self.wb)
        expected = [
            ('A1', 'B1', 'C1'),
            ('A2', 'B2', 'C2'),
            ('A3', 'B3', 'C3'),
            ('A4', 'B4', 'C4'),
        ]

        rows = ws.iter_rows('A1:C4')
        for row, coord in zip(rows, expected):
            assert tuple(c.coordinate for c in row) == coord
    def test_iter_rows_offset(self):
        ws = Worksheet(self.wb)
        rows = ws.iter_rows('A1:C4', 1, 3)
        expected = [
            ('D2', 'E2', 'F2' ),
            ('D3', 'E3', 'F3' ),
            ('D4', 'E4', 'F4' ),
            ('D5', 'E5', 'F5' ),
        ]

        for row, coord in zip(rows, expected):
            assert tuple(c.coordinate for c in row) == coord
    def test_iter_rows(self, ):
        ws = Worksheet(self.wb)
        expected = [
            ('A1', 'B1', 'C1' ),
            ('A2', 'B2', 'C2' ),
            ('A3', 'B3', 'C3' ),
            ('A4', 'B4', 'C4' ),
        ]

        rows = ws.iter_rows('A1:C4')
        for row, coord in zip(rows, expected):
            assert tuple(c.coordinate for c in row) == coord
    def test_append_2d_list(self):

        ws = Worksheet(self.wb)

        ws.append(['This is A1', 'This is B1'])
        ws.append(['This is A2', 'This is B2'])

        vals = ws.iter_rows('A1:B2')
        expected = (
            ('This is A1', 'This is B1'),
            ('This is A2', 'This is B2'),
        )
        for e, v in zip(expected, flatten(vals)):
            assert e == tuple(v)
    def test_append_2d_list(self):

        ws = Worksheet(self.wb)

        ws.append(['This is A1', 'This is B1'])
        ws.append(['This is A2', 'This is B2'])

        vals = ws.iter_rows('A1:B2')
        expected = (
            ('This is A1', 'This is B1'),
            ('This is A2', 'This is B2'),
        )
        for e, v in zip(expected, flatten(vals)):
            assert e == tuple(v)
Exemple #15
0
def parse_sheet(sheet: Worksheet) -> (list, list):
    logging.info('parsing sheet {}'.format(sheet.title))
    i = 0
    headings = []
    rows = []
    for row in sheet.iter_rows():
        row_data = []
        for cell in row:
            print(cell)
            if i == 0:
                headings.append(cell.value)
            else:
                row_data.append(cell.value)
        rows.append(row_data)
    return headings, rows
Exemple #16
0
def applyStrategyBorders(ws: Worksheet):
    top_thick_border = Border(left=Side(style='thin'),
                              right=Side(style='thin'),
                              top=Side(style='thick'),
                              bottom=Side(style='thin'))
    bottom_thick_border = Border(left=Side(style='thin'),
                                 right=Side(style='thin'),
                                 top=Side(style='thin'),
                                 bottom=Side(style='thick'))

    for index, row in enumerate(ws.iter_rows()):
        if ws['A' + str(index + 1)].value is not None and (
                index + 1) >= firstRowOutput:
            for cell in row:
                cell.border = top_thick_border
            rowBottom = ws[index + 2 + 1]
            for cell in rowBottom:
                cell.border = bottom_thick_border