def test_isEmptyCell(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet = book['Sheet1']
self.assertTrue(
cellOperations.isEmptyCell(sheet.cell(column=13,
row=1))) # empty cell
self.assertTrue(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=2))) # cell containing spaces
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=3))) # cell containing zero
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=4))) # cell containing non-zero number
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=5))) # cell containing text
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=6))) # cell containing formula
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=7))) # cell containing float
self.assertFalse(
cellOperations.isEmptyCell(sheet.cell(
column=13, row=8))) # cell containing date
def test_parseRow(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet = book['Sheet1']
headers = sheetOperations.parseHeaders(sheet, lastHeaderRow=2)
row1 = sheet[3]
result1 = rowOperations.parseRow(headers, row1)
self.assertDictEqual(
result1, {
'row number': 'row one',
'parent one': {
'child one': 1
},
'parent two': {
'child one': 3,
'child two': 6,
'child three': 9
},
'parent three': {
'child one': 11,
'child two': 14
},
'no parent': 17,
'no children': 20
})
row2 = sheet[4]
result2 = rowOperations.parseRow(headers, row2)
self.assertDictEqual(
result2, {
'row number': 'row two',
'parent one': {
'child one': 2
},
'parent two': {
'child one': 4,
'child two': 7,
'child three': None
},
'parent three': {
'child one': 12,
'child two': 15
},
'no parent': 18,
'no children': 21
})
def test_getCellValue(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet = book['Sheet1']
self.assertIsNone(
cellOperations.getCellValue(sheet.cell(row=1, column=1)))
self.assertEqual(
cellOperations.getCellValue(sheet.cell(row=1, column=2)),
"parent one")
self.assertEqual(
cellOperations.getCellValue(sheet.cell(row=1, column=3)),
"parent two")
self.assertEqual(
cellOperations.getCellValue(sheet.cell(row=1, column=4)),
"parent two")
def test_isChildMergedCell(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet = book['Sheet1']
self.assertFalse(
cellOperations.isChildMergedCell(sheet.cell(column=1,
row=1))) # empty cell
self.assertFalse(
cellOperations.isChildMergedCell(sheet.cell(
column=2, row=1))) # standard cell with content
self.assertFalse(
cellOperations.isChildMergedCell(sheet.cell(
column=3, row=1))) # parent merged cell
self.assertTrue(
cellOperations.isChildMergedCell(sheet.cell(
column=4, row=1))) # child merged cell
def test_workbook_loads(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
self.assertIsInstance(book, openpyxl.workbook.workbook.Workbook)
def test_parseSheet(self):
expectedResult = {
'row one': {
'row number': 'row one',
'parent one': {
'child one': 1
},
'parent two': {
'child one': 3,
'child two': 6,
'child three': 9
},
'parent three': {
'child one': 11,
'child two': 14
},
'no parent': 17,
'no children': 20
},
'row two': {
'row number': 'row two',
'parent one': {
'child one': 2
},
'parent two': {
'child one': 4,
'child two': 7,
'child three': None
},
'parent three': {
'child one': 12,
'child two': 15
},
'no parent': 18,
'no children': 21
},
'row three': {
'row number': 'row three',
'parent one': {
'child one': None
},
'parent two': {
'child one': 5,
'child two': 7,
'child three': 10
},
'parent three': {
'child one': 13,
'child two': 16
},
'no parent': 19,
'no children': 22
}
}
self.maxDiff = None
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet1 = book['Sheet1']
result = sheetOperations.parseSheet(sheet1, lastHeaderRow=2)
self.assertDictEqual(expectedResult,
result,
msg="\n\nresult is:\n{}\n\n".format(result))
sheet2 = book['Sheet2']
result2 = sheetOperations.parseSheet(sheet2,
firstHeaderRow=4,
lastHeaderRow=5)
self.assertDictEqual(expectedResult,
result2,
msg="\n\nresult is:\n{}\n\n".format(result2))
def test_parseHeaders(self):
book = workbookOperations.openWorkbook(TEST_SHEET)
sheet1 = book['Sheet1']
headers = sheetOperations.parseHeaders(sheet1, lastHeaderRow=2)
self.assertEqual(len(headers), 9, msg=headers)
self.assertCountEqual(
headers,
[['row number'], ['child one', 'parent one'],
['child one', 'parent two'], ['child two', 'parent two'],
['child three', 'parent two'], ['child one', 'parent three'],
['child two', 'parent three'], ['no parent'], ['no children']])
headers1a = sheetOperations.parseHeaders(sheet1,
lastHeaderRow=2,
maxColumnGap=0)
self.assertEqual(len(headers1a), 9, msg=headers)
self.assertCountEqual(
headers1a,
[['row number'], ['child one', 'parent one'],
['child one', 'parent two'], ['child two', 'parent two'],
['child three', 'parent two'], ['child one', 'parent three'],
['child two', 'parent three'], ['no parent'], ['no children']])
sheet2 = book['Sheet2']
headers2 = sheetOperations.parseHeaders(sheet2,
firstHeaderRow=4,
lastHeaderRow=5)
self.assertEqual(len(headers2), 9, msg=headers2)
self.assertCountEqual(
headers2,
[['row number'], ['child one', 'parent one'],
['child one', 'parent two'], ['child two', 'parent two'],
['child three', 'parent two'], ['child one', 'parent three'],
['child two', 'parent three'], ['no parent'], ['no children']])
headers2a = sheetOperations.parseHeaders(sheet2,
firstHeaderRow=4,
lastHeaderRow=5,
maxColumnGap=0)
self.assertEqual(len(headers2a), 9, msg=headers2)
self.assertCountEqual(
headers2a,
[['row number'], ['child one', 'parent one'],
['child one', 'parent two'], ['child two', 'parent two'],
['child three', 'parent two'], ['child one', 'parent three'],
['child two', 'parent three'], ['no parent'], ['no children']])
headers3 = sheetOperations.parseHeaders(sheet2,
firstHeaderRow=4,
lastHeaderRow=5,
camelCaseHeaders=True)
self.assertEqual(len(headers3), 9, msg=headers3)
self.assertCountEqual(
headers3,
[['rowNumber'], ['childOne', 'parentOne'],
['childOne', 'parentTwo'], ['childTwo', 'parentTwo'],
['childThree', 'parentTwo'], ['childOne', 'parentThree'],
['childTwo', 'parentThree'], ['noParent'], ['noChildren']])
sheet3 = book['Sheet3']
headers4 = sheetOperations.parseHeaders(sheet3,
firstHeaderRow=4,
lastHeaderRow=5)
self.assertEqual(len(headers4), 9, msg=headers4)
self.assertCountEqual(
headers4,
[['row number', 'parent one'], ['child one', 'parent one'],
['child one', 'parent two'], ['child two', 'parent two'],
['child three', 'parent two'], ['child one', 'parent three'],
['child two', 'parent three'], ['no parent'], ['no children']])