def writeSpreadsheetReport(self,reporttable,reporttype):
book = Workbook(encoding="utf-8")
sheet1 = book.add_sheet("Household Disposable Income")
style1 = easyxf('font: name Arial;''font: bold True;')
#Write Table Title
reptitle = "%s" % reporttype
sheet1.write(0, 0, reptitle, style1)
sheet1.col(0).width = 2500
#write Headers
sheet1.write(2, 0, 'hhid', style1)
if reporttype=='Standard of Living':
sheet1.write(2, 1, 'DIncome - Below STOL', style1)
sheet1.write(2, 2, 'DIncome - Above STOL', style1)
sheet1.col(1).width = 6000
sheet1.col(2).width = 6000
else:
sheet1.col(1).width = 4500
sheet1.write(2, 1, 'Disposable Income', style1)
#write Data
myrow = 3
for row in reporttable:
hhid = row[0]
householdDI = row[1]
sheet1.write(myrow, 0, int(hhid))
if reporttype=='Standard of Living':
positionmarker = row[2]
if positionmarker == 0:
sheet1.write(myrow, 2, float(householdDI))
sheet1.write(myrow, 1, 0)
else:
sheet1.write(myrow, 1, float(householdDI))
sheet1.write(myrow, 2, 0)
else:
sheet1.write(myrow, 1, float(householdDI))
myrow = myrow + 1
folder = "outputs/spreadsheets/income_sources/"
filename = folder + "openihm_DisposableIncome-" + str(time()) + ".xls"
book.save(filename)
completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
filepath= os.path.relpath(filename,start='.')
os.system(filepath)
def writeSpreadsheetReport(self,reporttable,reporttype):
book = Workbook(encoding="utf-8")
sheet1 = book.add_sheet("Households By Income Source")
style1 = easyxf('font: name Arial;''font: bold True;')
#Write Table Title
reptitle = "%s" % reporttype
sheet1.write(0, 0, reptitle, style1)
sheet1.col(0).width = 1500
#write Headers
i = 1
if len(reporttable)!=0:
row = reporttable[0]
keylist = row.keys()
keylist.sort()
for key in keylist:
if key !='incometotal':
if key =='hhid':
sheet1.write(2, 0, key, style1)
#sheet1.col(0).width = 1500
else:
sheet1.write(2, i, key,style1)
sheet1.col(i).width = 5500
i = i + 1
#write Data
myrow = 3
mycol = 1
for row in reporttable:
for key in keylist:
if key !='incometotal':
#for key in row.keys():
if row[key]:
value = row[key]
else:
value = 0
if key =='hhid':
sheet1.write(myrow, 0, float(value))
else:
sheet1.write(myrow, mycol, float(value))
mycol = mycol + 1
mycol =1
myrow = myrow + 1
folder = "outputs/spreadsheets/income_sources/"
filename = folder + "openihm_incomesources-" + str(time()) + ".xls"
book.save(filename)
completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
filepath= os.path.relpath(filename,start='.')
os.system(filepath)
def writeSpreadsheetReport(self,reporttable,simreporttable):
reporttype = 'Disposable Income Simulation'
book = Workbook(encoding="utf-8")
sheet1 = book.add_sheet("Household Disposable Income")
style1 = easyxf('font: name Arial;''font: bold True;')
#Write Table Title
reptitle = "%s" % reporttype
sheet1.write(0, 0, reptitle, style1)
sheet1.col(0).width = 2500
#write Headers
sheet1.write(2, 0, 'hhid', style1)
sheet1.write(2, 1, 'Normal DIncome', style1)
sheet1.write(2, 2, 'Simulation DIncome', style1)
sheet1.col(1).width = 6000
sheet1.col(2).width = 6000
#write Data
myrow = 3
counter = len(reporttable)
for i in range(0,counter):
hhid = reporttable[i][0]
householdDI = reporttable[i][1]
simhouseholdDI = simreporttable[i][1]
sheet1.write(myrow, 0, int(hhid))
sheet1.write(myrow, 1, float(householdDI))
sheet1.write(myrow, 2, float(simhouseholdDI))
myrow = myrow + 1
folder = "outputs/spreadsheets/income_sources/"
filename = folder + "openihm_SimulatedDisposableIncome-" + str(time()) + ".xls"
book.save(filename)
completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
filepath= os.path.relpath(filename,start='.')
os.system(filepath)
def writeSpreadsheetReport(self,selectedHouseholds,householdMembership,householdAssets,householdCashIncome,householdFoodIncome,householdBudgetSummary):
book = Workbook(encoding="utf-8")
style1 = easyxf('font: name Arial;''font: bold True;' 'border: left thick, top thick, right thick, bottom thick')
style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thin, top thin, right thin, bottom thin')
style3 = easyxf('font: name Arial;''border: left thin, top thin, right thin, bottom thin')
style4 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
sheetNumber = 1
listIndex = 0
for hid in selectedHouseholds:
sheetName = 'sheet' + '%s' % sheetNumber
sheetNumber = sheetNumber + 1
sheetTitle = 'Household Number ' + '%s' % hid
sheetName = book.add_sheet(sheetTitle)
sectionrow = self.writePageTitleSection(sheetName,style1)
sectionrow = self.writeHouseholdMembership(householdMembership[listIndex],sheetName,sectionrow,style1,style2,style3)
sectionrow = self.writeAssetDetails(householdAssets[listIndex],sheetName,sectionrow,style1,style2,style3)
sectionrow = self.writeHouseholdIncome(householdCashIncome[listIndex],householdFoodIncome[listIndex],sheetName,sectionrow,style1,style2,style3)
self.writeFinalSummary(householdBudgetSummary[listIndex],sheetName,sectionrow,style1,style2,style3)
listIndex = listIndex + 1
self.saveReport(book)
def writeDataSheets(self):
book = Workbook(encoding="utf-8")
#set style for headers
style1 = easyxf('font: name Arial;''font: bold True;')
style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
style3 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
#create sheet for entering project households
sheettitle = "%s" % self.pid
sheet1 = book.add_sheet(sheettitle)
sheet1.write(0, 0, "Project Households", style1)
sheet1.write(1, 0, "HouseholdNumber", style2)
sheet1.write(1, 1, "HouseholdName", style2)
sheet1.write(1, 2, "DateVisited", style2)
#set column width for sheet1
for i in range(0,3):
sheet1.col(i).width = 6000
self.populateIncomeSourcesSheet(book,style1,style2)
self.addProjectAssets(book,style1,style2)
#Basic Details for Household Members
sheet4 = book.add_sheet("HouseholdID")
sheet4.write(1, 0, "HouseholdMembers", style1)
sheet4.write(2, 0, "Sex", style2)
sheet4.write(2, 1, "Age", style2)
sheet4.write(2, 2, "YearofBirth", style2)
sheet4.write(2, 3, "HouseholdHead", style2)
sheet4.write(2, 4, "PeriodAway", style2)
#get personal and household characteristics, configured for current project
pchars = self.getPersonalCharacteristics()
hchars = self.getHouseholdCharacteristics()
#section for extended personal characteristics
col = 0
sheet4.write(8, 0, "PersonalCharacteristics", style1)
sheet4.write(9, col, 'personid', style2)
sheet4.write(10, col, 'String', style3)
col = col + 1
for char in pchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(9, col, value, style2)
sheet4.write(10, col, vartype, style3)
col = col + 1
#section for household characteristics
sheet4.write(17, 0, "HouseholdCharacteristics", style1)
col = 0
for char in hchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(18, col, value, style2)
sheet4.write(19, col, vartype, style3)
col = col + 1
headerrow = 25
itemrow = 26
#Assets
sheet4.write(headerrow, 0, "Assets", style1)
headerrow = headerrow + 1
headerrow = self.populateProjectAssetssection(book,style1,style2,headerrow)
#Expenditure
headerrow = headerrow + 5
sheet4.write(headerrow, 0, "Expenditure", style1)
headerrow = headerrow + 1
sheet4.write(headerrow, 0, "Type", style2)
sheet4.write(headerrow, 1, "Unit", style2)
sheet4.write(headerrow, 2, "KCalPerUnit", style2)
sheet4.write(headerrow, 3, "UnitCost", style2)
sheet4.write(headerrow, 4, "Units", style2)
#Crop, Livestock, and Wildfood Income
headerrow = headerrow + 11
headerrow = self.populateFoodsCropsSections(book,style1,style2,headerrow)
#Employment
headerrow = self.populateEmployemntDetails(headerrow,book,style1,style2)
#Social Transfers
headerrow = self.populateSocialTranfers(book,style1,style2,headerrow)
#Transfers from Organisations
headerrow = self.populateOfficialTranfers(book,style1,style2,headerrow)
#set column width for sheet2
for i in range(0,7):
sheet4.col(i).width = 6000
folder = "inputs/"
filename = folder + "dataEntrySheet-ProjectID-" + str(self.pid) + ".xls"
book.save(filename)
completionmessage = '''Template Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Data Entry Template', completionmessage)
#
os.system(os.path.curdir + "\\inputs\\dataEntrySheet-ProjectID-" + str(self.pid) + ".xls")