def test_createWorkbook(self):
'''
Test the method structjour.layoutsheet.LayoutSheet.createWorkbook
'''
df = pd.DataFrame(np.random.randint(0, 100, size=(100, 7)),
columns=list('ABCDEFG'))
# df
margin = 25
spacing = 3
inputlen = len(df)
ls = LayoutSheet(margin, inputlen, spacing=spacing)
wb, ws, df = ls.createWorkbook(df)
for row, (i, dfrow) in zip(ws, df.iterrows()):
# We inserted the column headers in this row (ws starts with 1, not 0)
if i + 1 == ls.topMargin:
for ms, x in zip(row, df.columns):
self.assertEqual(x, ms.value)
# everything else is verbatim
else:
for ms, x in zip(row, dfrow):
self.assertEqual(x, ms.value)
wb.save("out/SCHNOrK.xlsx")
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 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_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)
def setUpClass(cls):
ddiirr = os.path.dirname(__file__)
os.chdir(os.path.realpath(ddiirr + '/../'))
outdir = 'test/out'
cls.outdir = os.path.realpath(outdir)
cls.db = 'data/testdb.sqlite'
cls.db = os.path.realpath(cls.db)
if os.path.exists(cls.db):
clearTables(cls.db)
cls.rtg = RTG(db=cls.db)
# cls.dates = ['20200203 09:30', '20200204 07:30', '20200205 09:35', '20200206 11:40', '20200207 10:39']
cls.theDate = '20200207 10:39'
cls.infile = cls.rtg.saveSomeTestFiles([cls.theDate], cls.outdir)[0]
settings = QSettings('zero_substance', 'structjour')
# for i, name in enumerate(cls.infiles):
name = os.path.join(cls.outdir, cls.infile)
x, inputType = getStatementType(name)
if inputType == 'DAS':
ds = DasStatement(name, settings, cls.theDate)
ds.getTrades(testFileLoc=cls.outdir, testdb=cls.db)
elif inputType == "IB_CSV":
ibs = IbStatement(db=cls.db)
ibs.openIBStatement(name)
else:
raise ValueError(f'Unsupported File type: {inputType}')
statement = StatementDB(db=cls.db)
df = statement.getStatement(cls.theDate)
dtrade = DefineTrades(inputType)
dframe, cls.ldf = dtrade.processDBTrades(df)
cls.jf = JournalFiles(indir=cls.outdir,
outdir=outdir,
theDate=cls.theDate,
infile=name)
cls.sc = SumControl()
lf = LayoutForms(cls.sc, cls.jf, dframe)
lf.runTtoSummaries(cls.ldf)
# Setup mistake note fields to test
cls.note = 'Ground control to Major Tom'
for i, key in enumerate(lf.ts):
tto = lf.ts[key]
notex = cls.note + str(i + 1)
tto['MstkNote'] = notex
# Setup a couple images to add
imdir = 'images/'
img1 = os.path.join(imdir, 'fractal-art-fractals.jpg')
img2 = os.path.join(imdir, 'psych.jpg')
assert os.path.exists(img1)
assert os.path.exists(img2)
for key in lf.ts:
tto = lf.ts[key]['chart1'] = img1
tto = lf.ts[key]['chart2'] = img2
t = ExportToExcel(lf.ts, cls.jf, df)
imageNames = t.getImageNamesFromTS()
cls.imageLocation, dframe = t.layoutExcelData(t.df, cls.ldf,
imageNames)
assert len(cls.ldf) == len(cls.imageLocation)
# Create an openpyxl wb from the dataframe
ls = LayoutSheet(t.topMargin, len(t.df))
cls.wb, ws, nt = ls.createWorkbook(dframe)
# Place both forms 2 cells to the right of the main table
mstkAnchor = (len(dframe.columns) + 2, 1)
cls.mistake = MistakeSummary(numTrades=len(cls.ldf), anchor=mstkAnchor)
cls.t = t
cls.a = mstkAnchor