def test_mergeStuff(self):
'''
Test the method tradestyle.TradeFormat.mergeStuff. Specificaly test that a merged cell
group exists where it is should be after saving and opening an xlsx file.
'''
wb = Workbook()
ws = wb.active
begin = (1, 1)
end = (5, 5)
anchor = (3, 3)
t = TradeFormat(wb)
b, e = t.mergeStuff(ws, begin=begin, end=end, anchor=anchor)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
x = ws2.merged_cells.ranges
# print(b, (x[0].min_col, x[0].min_row))
# print(e, (x[0].max_col, x[0].max_row))
self.assertEqual(b, (x[0].min_col, x[0].min_row))
self.assertEqual(e, (x[0].max_col, x[0].max_row))
os.remove(dispath)
def test_TradeFormatAddNamedStyle(self):
'''
Test the object formation and the creation of the styles in __init__ and addNamedStyle for
the Workbook. Specifically test that the named style is created and stored in the workbook
(still there when you close it and open it.)
'''
wb = Workbook()
ws = wb.active
tf = TradeFormat(wb)
# Use a list to verify the order
styleList = list(tf.styles.keys())
if not os.path.exists("out/"):
os.mkdir("out/")
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
# Write one cell for each named style in TradeFormat
for i, key in enumerate(styleList):
# print(key, c((1, i+1)))
ws[c((1, i + 1))] = key
ws[c((1, i + 1))].style = tf.styles[key]
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# Open it back up and check the the named styles are where we think they are
for i, key in enumerate(styleList):
# print(key,ws2[c((1, i+1))].style )
self.assertEqual(key, ws2[c((1, i + 1))].style)
def test_placeImagesAndSumStyles(self):
'''
At a loss for how to test image placement---the image is in a zip, the links are not in
the worksheet. Openpyxl does not get the image info from load_workbook. While these asserts
are not satisfactory, they will probably fail if something has gone wrong with the insert
locations for images and the SumStyle forms.
'''
tf = TradeFormat(self.wb)
ws = self.wb.active
self.mistake.mstkSumStyle(ws, tf, self.a)
self.mistake.dailySumStyle(ws, tf, self.a)
self.t.placeImagesAndSumStyles(self.imageLocation, ws, tf)
# self.t.saveXL(self.wb, self.jf)
for loc in self.imageLocation:
sumcell = tcell((1, loc[0][0][1]))
aboveSumCell = tcell((1, loc[0][0][1] - 1))
self.assertEqual(ws[sumcell].style, 'titleStyle', sumcell)
self.assertEqual(ws[aboveSumCell].style, 'Normal', aboveSumCell)
for iloc, fn in zip(loc[0], loc[1]):
imgcell = tcell(iloc)
sumcell = tcell((1, iloc[1]))
self.assertIsNotNone(ws[imgcell].value)
def test_styleTopwithnothin(self):
'''
Test the method layoutsheet.LayoutSheet.styleTop. Test that it still works without
table data. We still know too much about the method,. Note that we are using a protected
member of Worksheet ws._tables
'''
quoteRange = [(1, 1), (13, 5)]
noteRange = [(1, 6), (13, 24)]
quoteStyle = 'normStyle'
noteStyle = 'explain'
margin = 25
inputlen = 50 # len(df)
wb = Workbook()
ws = wb.active
# Make sure the out dir exists
if not os.path.exists("out/"):
os.mkdir("out/")
# Make sure the file we are about to create does not exist
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
ls = LayoutSheet(margin, inputlen)
ls.styleTop(ws, 13, TradeFormat(wb))
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
listOfMerged = list()
listOfMerged.append(
tcell((quoteRange[0])) + ':' + tcell((quoteRange[1])))
listOfMerged.append(
tcell((noteRange[0])) + ':' + tcell((noteRange[1])))
for xx in ws2.merged_cells.ranges:
# print (str(xx) in listOfMerged)
self.assertIn(str(xx), listOfMerged)
self.assertEqual(ws[tcell(quoteRange[0])].style, quoteStyle)
self.assertEqual(ws[tcell(noteRange[0])].style, noteStyle)
self.assertEqual(len(ws._tables), 1)
begin = tcell((1, ls.topMargin))
end = tcell((13, ls.topMargin + ls.inputlen))
tabRange = f'{begin}:{end}'
key = list(ws._tables.keys())
if key:
self.assertEqual(tabRange, ws._tables[key[0]].ref)
os.remove(dispath)
def test_formatTrade(self):
'''
Test the method TradeFormat.formatTrade. Specifically test that each of the elements in
srf.tfcolumns has a corresponding elementcorrectly styled in the re-opened workbook and
that each merged element in srf.tfcolumns is found in the the worsheet.
'''
wb = Workbook()
ws = wb.active
t = TradeFormat(wb)
srf = SumReqFields()
ws = wb.active
t.formatTrade(ws, srf)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# test that each listed item in srf.tfcolumns has a corresponding style set in the
# appropriate cell of the re-opened workbook
for x in srf.tfcolumns:
address = srf.tfcolumns[x][0]
st = srf.tfcolumns[x][1]
if isinstance(address, list):
self.assertEqual(ws2[c(address[0])].style, st)
else:
self.assertEqual(ws2[c(address)].style, st)
# test that each list element has a corresponding merged cell group in the worbook
listofmerge = [
c(srf.tfcolumns[x][0][0], srf.tfcolumns[x][0][1])
for x in srf.tfcolumns if isinstance(srf.tfcolumns[x][0], list)
]
wsmerged = ws.merged_cells.ranges
self.assertEqual(len(listofmerge), len(wsmerged))
for msmerge in wsmerged:
self.assertTrue(str(msmerge) in listofmerge)
def exportExcel(self):
'''
Export to excel the trade tables, trade summaries, and daily forms
'''
# Create the space in dframe to add the summary information for each trade.
# Then create the Workbook.
settings = QSettings('zero_substance', 'structjour')
val = settings.value('inputType')
# Get a list of Trades from self.df
tu = DefineTrades(val)
ldf = tu.getTradeList(self.df)
# Lay out a dataframe with space for charts
imageNames = self.getImageNamesFromTS()
imageLocation, dframe = self.layoutExcelData(self.df, ldf, imageNames)
assert len(ldf) == len(imageLocation)
# Create an openpyxl wb from the dataframe
ls = LayoutSheet(self.topMargin, len(self.df))
wb, ws, nt = ls.createWorkbook(dframe)
tf = TradeFormat(wb)
ls.styleTop(ws, len(nt.columns), tf)
mstkAnchor = (len(dframe.columns) + 2, 1)
mistake = MistakeSummary(numTrades=len(ldf), anchor=mstkAnchor)
mistake.mstkSumStyle(ws, tf, mstkAnchor)
mistake.dailySumStyle(ws, tf, mstkAnchor)
# tradeSummaries = ls.runSummaries(imageLocation, ldf, self.jf, ws, tf)
self.placeImagesAndSumStyles(imageLocation, ws, tf)
self.populateXLDailyFormulas(imageLocation, ws)
self.populateXLMistakeForm(mistake, ws, imageLocation)
self.populateXLDailySummaryForm(mistake, ws, mstkAnchor)
self.populateXLDailyNote(ws)
self.saveXL(wb, self.jf)
logging.info("Processing complete. Saved {}".format(
self.jf.outpathfile))
def test_dailySumStyle(self):
'''
Test the method MistakeSummary.mstkSumStyle.
'''
wb = Workbook()
ws = wb.active
tf = TradeFormat(wb)
numTrades = 5
ms = MistakeSummary(numTrades)
ms.dailySumStyle(ws, tf)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
anchor = (1, ms.anchor[1] + ms.numTrades + 5)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# test that each listed item in ms.mistakeFields has a corresponding style set in the
# appropriate cell in the re-opened workbook.
for x in ms.dailySummaryFields:
cell = ''
entry = ms.dailySummaryFields[x]
if isinstance(entry[0], list):
cell = tcell(entry[0][0], anchor=anchor)
else:
cell = tcell(entry[0], anchor=anchor)
self.assertEqual(entry[1], ws2[cell].style)
# Get the entries with the static values-- the headers
if len(entry) == 3:
self.assertEqual(ws2[cell].value, ms.dailySummaryFields[x][2])
os.remove(dispath)
def test_mstkSumStyles(self):
'''
Test the method MistakeSummary.mstkSumStyle.
'''
wb = Workbook()
ws = wb.active
tf = TradeFormat(wb)
ms = MistakeSummary(5)
ms.mstkSumStyle(ws, tf)
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
# test that each listed item in ms.mistakeFields has a corresponding style set in the
# appropriate cell in the re-opened workbook.
for x in ms.mistakeFields:
cell = ''
entry = ms.mistakeFields[x]
if isinstance(entry[0], list):
cell = tcell(entry[0][0])
else:
cell = tcell(entry[0])
# print(cell, entry[1], ws[cell].style)
self.assertEqual(entry[1], ws[cell].style)
if len(entry) == 3:
self.assertEqual(ws2[cell].value, ms.mistakeFields[x][2])
os.remove(dispath)
def test_styleTop(self):
'''
Test the method layoutsheet.LayoutSheet.styleTop. This will probably produce warnings from
openpyxl as there is empty data when it makes the headers. No worries.
Note that we are using a protected member of Worksheet ws._tables, so if it this fails, look
at that. openpyxl does not provide a public attribute for tables.
Note that knowing the quoteRange and noteRange is bad design. Eventually these two bits of
design data should be abstracted to somewhere accessible by the user. (and testing too)
'''
quoteRange = [(1, 1), (13, 5)]
noteRange = [(1, 6), (13, 24)]
quoteStyle = 'normStyle'
noteStyle = 'explain'
margin = 25
inputlen = 50 # len(df)
wb = Workbook()
ws = wb.active
# Make sure the out dir exists
if not os.path.exists("out/"):
os.mkdir("out/")
# Make sure the file we are about to create does not exist
dispath = "out/SCHNOrK.xlsx"
if os.path.exists(dispath):
os.remove(dispath)
# Create table header and data in the ws
headers = [
'Its', 'the', 'end', 'of', 'the', 'world', 'as', 'we', 'know',
'it.', 'Bout', 'Fn', 'Time!'
]
for i in range(1, 14):
ws[tcell((i, 25))] = headers[i - 1]
ls = LayoutSheet(margin, inputlen)
for x in range(ls.topMargin + 1, ls.inputlen + ls.topMargin + 1):
for xx in range(1, 14):
ws[tcell((xx, x))] = randint(-1000, 10000)
ls.styleTop(ws, 13, TradeFormat(wb))
wb.save(dispath)
wb2 = load_workbook(dispath)
ws2 = wb2.active
listOfMerged = list()
listOfMerged.append(
tcell((quoteRange[0])) + ':' + tcell((quoteRange[1])))
listOfMerged.append(
tcell((noteRange[0])) + ':' + tcell((noteRange[1])))
for xx in ws2.merged_cells.ranges:
# print (str(xx) in listOfMerged)
self.assertTrue(str(xx) in listOfMerged)
self.assertEqual(ws[tcell(quoteRange[0])].style, quoteStyle)
self.assertEqual(ws[tcell(noteRange[0])].style, noteStyle)
self.assertEqual(len(ws._tables), 1)
begin = tcell((1, ls.topMargin))
end = tcell((13, ls.topMargin + ls.inputlen))
tabRange = f'{begin}:{end}'
self.assertEqual(tabRange, ws._tables[0].ref)
os.remove(dispath)