def test_string_representation(self):
cell = Cell.at('A2')
self.assertEqual('A2', str(cell))
cell = Cell.at('B')
self.assertEqual('B', str(cell))
cell = Cell.at('3')
self.assertEqual('3', str(cell))
def test_get_relative_cell(self):
cell = Cell.at('A2')
relative = cell.get_relative_cell(cols=3, rows=2)
self.assertEqual(str(relative), 'D4')
cell = Cell.at('B')
relative = cell.get_relative_cell(cols=3, rows=2)
self.assertEqual(str(relative), 'E')
cell = Cell.at('3')
relative = cell.get_relative_cell(cols=3, rows=2)
self.assertEqual(str(relative), '5')
def grid_range(tab_id, range_name=None):
""" https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridRange """
if isinstance(range_name, Cell):
range_name = str(range_name)
data = {
'sheetId': tab_id,
}
if not range_name:
pass
elif range_name.isdigit():
data['startRowIndex'] = int(range_name) - 1
data['endRowIndex'] = int(range_name)
elif ':' not in range_name:
cell = Cell.at(range_name)
if cell.col:
data['startColumnIndex'] = cell.col - 1
data['endColumnIndex'] = cell.col
if cell.row:
data['startRowIndex'] = cell.row - 1
data['endRowIndex'] = cell.row
else:
start, end = range_name.split(':')
start_cell = Cell.at(start)
if start_cell.col:
data['startColumnIndex'] = start_cell.col - 1
if start_cell.row:
data['startRowIndex'] = start_cell.row - 1
end_cell = Cell.at(end)
if end_cell.col:
data['endColumnIndex'] = end_cell.col
if end_cell.row:
data['endRowIndex'] = end_cell.row
return data
def demo_data(sheet):
tab_name = "Gsheets Demo: Data"
# Delete/recreate the tab
sheet.delete_tabs([tab_name])
sheet.add_tabs([tab_name], rows=6, cols=6)
# Get tab object
tab = sheet.get_tab(tab_name)
# Get cell pointer
pointer = Cell.at('A1')
with tab.action_set():
# Actions that can be safely performed together
# Write a single row of data
tab.write_row(pointer, ['do', 're', 'mi'])
# Move down two rows
pointer.move(rows=2)
# Write data as list of row arrays
tab.write_rows(pointer,
[['1', '2', '3'], ['4', '5', '6'], ['7', '8', '9']])
# Move over 4 cols
pointer.move(cols=4)
# Write a single column of data
tab.write_column(pointer, ['fa', 'so', 'la'])
def demo_formula(sheet):
tab_name = "Gsheets Demo: Formula"
# Delete/recreate the tab
sheet.delete_tabs([tab_name])
sheet.add_tabs([tab_name], rows=4, cols=5)
# Get tab object
tab = sheet.get_tab(tab_name)
# Get cell pointers
data_cell = Cell.at('A2')
header_cell = Cell.at('D1')
with tab.action_set():
# Actions that can be safely performed together
# Write basic headers
tab.write_row(header_cell, ["Sum", "Average"])
# Write some data
tab.write_rows(data_cell, [
[2, 3, 5],
[7, 11, 13],
[17, 19, 23],
])
tab.write_formula(get_range(data_cell.get_relative_cell(cols=3)),
"=SUM(%s)" % get_range(data_cell, cols=3))
tab.write_formula(get_range(data_cell.get_relative_cell(cols=4)),
"=AVERAGE(%s)" % get_range(data_cell, cols=3))
data_cell.move(rows=1)
tab.write_formula(get_range(data_cell.get_relative_cell(cols=3)),
"=SUM(%s)" % get_range(data_cell, cols=3))
tab.write_formula(get_range(data_cell.get_relative_cell(cols=4)),
"=AVERAGE(%s)" % get_range(data_cell, cols=3))
data_cell.move(rows=1)
tab.write_formula(get_range(data_cell.get_relative_cell(cols=3)),
"=SUM(%s)" % get_range(data_cell, cols=3))
tab.write_formula(get_range(data_cell.get_relative_cell(cols=4)),
"=AVERAGE(%s)" % get_range(data_cell, cols=3))
def demo_formatting(sheet):
tab_name = "Gsheets Demo: Formatting"
# Delete/recreate the tab
sheet.delete_tabs([tab_name])
sheet.add_tabs([tab_name], rows=4, cols=3, color="#0000AA")
# Get tab object
tab = sheet.get_tab(tab_name)
# Get cell pointers
header_cell = Cell.at('A1')
subheader_cell = header_cell.get_relative_cell(rows=1)
data_cell = header_cell.get_relative_cell(rows=2)
with tab.action_set():
# Actions that can be safely performed together
# Ranges, e.g. "A1:B10" or "A" (column) or "3" (row)
header_range = get_range(header_cell, cols=-1, rows=1)
subheader_range = get_range(subheader_cell, cols=-1, rows=1)
# Format top header row
tab.formatting(header_range,
align_horiz="CENTER",
bg_color="#AAAAAA",
font_size=12,
bold=True)
tab.border(header_range,
location='bottom',
style='DASHED',
width=1,
color='#000000')
tab.formatting(subheader_range,
align_horiz="CENTER",
bg_color="#CCCCCC",
font_size=10,
bold=True)
tab.border(subheader_range,
location='bottom',
style='SOLID',
width=2,
color='#000000')
# Write some data
tab.write_row(header_cell, ['Header Row', 'Value #1'])
tab.write_row(subheader_cell, ['Subheader Row', 'Value #2'])
tab.write_row(data_cell, ['Data Row', 'Value #3'])
# Auto-resize
tab.auto_resize("A:C")
def demo_movement(sheet):
tab_name = "Gsheets Demo: Movement"
# Delete/recreate the tab
sheet.delete_tabs([tab_name])
sheet.add_tabs([tab_name], rows=7, cols=7)
# Get tab object
tab = sheet.get_tab(tab_name)
# Get cell pointers
pointerA = Cell.at('A1')
pointerB = pointerA.get_relative_cell(cols=6)
pointerC = pointerA.get_relative_cell(rows=6)
pointerD = pointerA.get_relative_cell(cols=6, rows=6)
with tab.action_set():
# Actions that can be safely performed together
tab.formatting(pointerA, bg_color="#DDDDDD")
tab.formatting(pointerB, bg_color="#DDDDDD")
tab.formatting(pointerC, bg_color="#DDDDDD")
tab.formatting(pointerD, bg_color="#DDDDDD")
pointerA.move(rows=1, cols=1)
pointerB.move(rows=1, cols=-1)
pointerC.move(rows=-1, cols=1)
pointerD.move(rows=-1, cols=-1)
tab.formatting(pointerA, bg_color="#AAAAAA")
tab.formatting(pointerB, bg_color="#AAAAAA")
tab.formatting(pointerC, bg_color="#AAAAAA")
tab.formatting(pointerD, bg_color="#AAAAAA")
pointerA.move(rows=1, cols=1)
pointerB.move(rows=1, cols=-1)
pointerC.move(rows=-1, cols=1)
pointerD.move(rows=-1, cols=-1)
tab.formatting(pointerA, bg_color="#666666")
tab.formatting(pointerB, bg_color="#666666")
tab.formatting(pointerC, bg_color="#666666")
tab.formatting(pointerD, bg_color="#666666")
pointerA.move(rows=1, cols=1)
tab.formatting(pointerA, bg_color="#000000")
def test_move(self):
cell = Cell.at('A2')
self.assertEqual(1, cell.col)
self.assertEqual(2, cell.row)
cell.move(cols=3)
self.assertEqual(4, cell.col)
self.assertEqual(2, cell.row)
cell.move(cols=-2)
self.assertEqual(2, cell.col)
self.assertEqual(2, cell.row)
cell.move(rows=3)
self.assertEqual(2, cell.col)
self.assertEqual(5, cell.row)
cell.move(rows=-2)
self.assertEqual(2, cell.col)
self.assertEqual(3, cell.row)
def get_range(cell, cols=1, rows=1):
if isinstance(cell, str):
cell = Cell.at(cell)
col = cell.col
row = cell.row
if col and not isinstance(col, int):
col = letters_to_col(col)
# Start of range
start = col_to_letters(col)
if row:
start += str(row)
# End of range
if cols < 0:
end_col = ''
else:
end_col = col_to_letters(col + cols - 1)
if rows < 0:
end_row = ''
else:
end_row = str(row + rows - 1)
end = end_col + end_row
# Range
verdict = start
if end:
verdict += ':' + end
return verdict
def test_cell(self):
cell = Cell(col=1, row=2)
self.assertEqual(1, cell.col)
self.assertEqual(2, cell.row)
self.assertEqual('A', cell.col_letter)
def test_cell_at_row_only(self):
cell = Cell.at('2')
self.assertEqual(None, cell.col)
self.assertEqual(2, cell.row)
self.assertEqual('', cell.col_letter)
def test_cell_at(self):
cell = Cell.at('A2')
self.assertEqual(1, cell.col)
self.assertEqual(2, cell.row)
self.assertEqual('A', cell.col_letter)
def test_get_range(self):
self.assertEqual('A1:A1', get_range('A1', cols=1))
self.assertEqual('A1:A1', get_range(Cell.at('A1'), cols=1))
self.assertEqual('A1:B1', get_range(Cell.at('A1'), cols=2))
self.assertEqual('A1:A2', get_range(Cell.at('A1'), rows=2))
self.assertEqual('A1:B2', get_range(Cell.at('A1'), cols=2, rows=2))
self.assertEqual('B2:AC3', get_range(Cell.at('B2'), cols=28, rows=2))
self.assertEqual('A1:A1', get_range(Cell.at('A1'), cols=1))
self.assertEqual('A1:B1', get_range(Cell.at('A1'), cols=2))
self.assertEqual('A1:A2', get_range(Cell.at('A1'), rows=2))
self.assertEqual('A1:B2', get_range(Cell.at('A1'), cols=2, rows=2))
self.assertEqual('B2:AC3', get_range(Cell.at('B2'), cols=28, rows=2))
self.assertEqual('B2:B', get_range(Cell.at('B2'), cols=1, rows=-1))
self.assertEqual('B2', get_range(Cell.at('B2'), cols=-1, rows=-1))
self.assertEqual('C:D', get_range(Cell.at('C'), cols=2, rows=-1))
self.assertEqual('3:4', get_range(Cell.at('3'), cols=-1, rows=2))