def getData_xlrd(fp):
Data = namedtuple('Data',['loc','type','name'])
wb = xlrd.open_workbook(fp)
ws_config = wb.sheet_by_name('config')
ws_form = wb.sheet_by_index(0)
container = [Data._make([c.value for c in ws_config.row(curr_row)]) for curr_row in range(1,ws_config.nrows)]
result = {data.name: ws_form.cell(*cell_to_rowcol2(data.loc)).value for data in container}
return result
def getData_xlrd(fp):
Data = namedtuple('Data', ['loc', 'type', 'name'])
wb = xlrd.open_workbook(fp)
ws_config = wb.sheet_by_name('config')
ws_form = wb.sheet_by_index(0)
container = [
Data._make([c.value for c in ws_config.row(curr_row)])
for curr_row in range(1, ws_config.nrows)
]
result = {
data.name: ws_form.cell(*cell_to_rowcol2(data.loc)).value
for data in container
}
return result
def import_xls(uploadFile):
"""
Import Assessment Spreadsheet
"""
if series_id is None:
response.error = T("Series details missing")
return
openFile = StringIO()
try:
import xlrd
from xlwt.Utils import cell_to_rowcol2
except ImportError:
current.log.error("ERROR: xlrd & xlwt modules are needed for importing spreadsheets")
return None
workbook = xlrd.open_workbook(file_contents=uploadFile)
try:
sheetR = workbook.sheet_by_name("Assessment")
sheetM = workbook.sheet_by_name("Metadata")
except:
session.error = T("You need to use the spreadsheet which you can download from this page")
redirect(URL(c="survey", f="new_assessment", args=[],
vars={"viewing": "survey_series.%s" % series_id}))
header = ""
body = ""
for row in xrange(1, sheetM.nrows):
header += ',"%s"' % sheetM.cell_value(row, 0)
code = sheetM.cell_value(row, 0)
qstn = s3.survey_getQuestionFromCode(code, series_id)
type = qstn["type"]
count = sheetM.cell_value(row, 1)
if count != "":
count = int(count)
optionList = sheetM.cell_value(row, 2).split("|#|")
else:
count = 1
optionList = None
if type == "Location" and optionList != None:
answerList = {}
elif type == "MultiOption":
answerList = []
else:
answerList = ""
for col in range(count):
cell = sheetM.cell_value(row, 3 + col)
(rowR, colR) = cell_to_rowcol2(cell)
try:
cellValue = sheetR.cell_value(rowR, colR)
except IndexError:
cellValue = ""
# BUG: The option list needs to work in different ways
# depending on the question type. The question type should
# be added to the spreadsheet to save extra db calls:
# * Location save all the data as a hierarchy
# * MultiOption save all selections
# * Option save the last selection
if cellValue != "":
if optionList != None:
if type == "Location":
answerList[optionList[col]]=cellValue
elif type == "MultiOption":
answerList.append(optionList[col])
else:
answerList = optionList[col]
else:
if type == "Date":
try:
(dtYear, dtMonth, dtDay, dtHour, dtMinute, dtSecond) = \
xlrd.xldate_as_tuple(cellValue,
workbook.datemode)
dtValue = datetime.date(dtYear, dtMonth, dtDay)
cellValue = dtValue.isoformat()
except:
pass
elif type == "Time":
try:
time = cellValue
hour = int(time * 24)
minute = int((time * 24 - hour) * 60)
cellValue = "%s:%s" % (hour, minute)
except:
pass
answerList += "%s" % cellValue
body += ',"%s"' % answerList
openFile.write(header)
openFile.write("\n")
openFile.write(body)
openFile.seek(0)
return openFile
def import_xls(uploadFile):
"""
Import Assessment Spreadsheet
"""
from io import BytesIO
if series_id is None:
response.error = T("Series details missing")
return
openFile = BytesIO()
try:
import xlrd
from xlwt.Utils import cell_to_rowcol2
except ImportError:
current.log.error(
"ERROR: xlrd & xlwt modules are needed for importing spreadsheets"
)
return None
workbook = xlrd.open_workbook(file_contents=uploadFile)
try:
sheetR = workbook.sheet_by_name("Assessment")
sheetM = workbook.sheet_by_name("Metadata")
except:
session.error = T(
"You need to use the spreadsheet which you can download from this page"
)
redirect(
URL(c="survey",
f="new_assessment",
args=[],
vars={"viewing": "survey_series.%s" % series_id}))
header = ""
body = ""
for row in range(1, sheetM.nrows):
header += ',"%s"' % sheetM.cell_value(row, 0)
code = sheetM.cell_value(row, 0)
qstn = s3.survey_getQuestionFromCode(code, series_id)
type = qstn["type"]
count = sheetM.cell_value(row, 1)
if count != "":
count = int(count)
optionList = sheetM.cell_value(row, 2).split("|#|")
else:
count = 1
optionList = None
if type == "Location" and optionList != None:
answerList = {}
elif type == "MultiOption":
answerList = []
else:
answerList = ""
for col in range(count):
cell = sheetM.cell_value(row, 3 + col)
(rowR, colR) = cell_to_rowcol2(cell)
try:
cellValue = sheetR.cell_value(rowR, colR)
except IndexError:
cellValue = ""
# BUG: The option list needs to work in different ways
# depending on the question type. The question type should
# be added to the spreadsheet to save extra db calls:
# * Location save all the data as a hierarchy
# * MultiOption save all selections
# * Option save the last selection
if cellValue != "":
if optionList != None:
if type == "Location":
answerList[optionList[col]] = cellValue
elif type == "MultiOption":
answerList.append(optionList[col])
else:
answerList = optionList[col]
else:
if type == "Date":
try:
(dtYear, dtMonth, dtDay, dtHour, dtMinute, dtSecond) = \
xlrd.xldate_as_tuple(cellValue,
workbook.datemode)
dtValue = datetime.date(dtYear, dtMonth, dtDay)
cellValue = dtValue.isoformat()
except:
pass
elif type == "Time":
try:
time = cellValue
hour = int(time * 24)
minute = int((time * 24 - hour) * 60)
cellValue = "%s:%s" % (hour, minute)
except:
pass
answerList += "%s" % cellValue
body += ',"%s"' % answerList
openFile.write(header)
openFile.write("\n")
openFile.write(body)
openFile.seek(0)
return openFile
def import_xls(uploadFile):
if series_id == None:
response.error = T("Series details missing")
return
openFile = StringIO()
from datetime import date
try:
import xlrd
from xlwt.Utils import cell_to_rowcol2
except ImportError:
print >> sys.stderr, "ERROR: xlrd & xlwt modules are needed for importing spreadsheets"
return None
workbook = xlrd.open_workbook(file_contents=uploadFile)
sheetR = workbook.sheet_by_name("Assessment")
sheetM = workbook.sheet_by_name("Metadata")
header = ''
body = ''
for row in xrange(1, sheetM.nrows):
header += ',"%s"' % sheetM.cell_value(row, 0)
code = sheetM.cell_value(row, 0)
qstn = s3.survey_getQuestionFromCode(code, series_id)
type = qstn["type"]
count = sheetM.cell_value(row, 1)
if count != "":
count = int(count)
optionList = sheetM.cell_value(row, 2).split("|#|")
else:
count = 1
optionList = None
if type == "Location" and optionList != None:
answerList = {}
elif type == "MultiOption":
answerList = []
else:
answerList = ''
for col in range(count):
cell = sheetM.cell_value(row, 3+col)
(rowR, colR) = cell_to_rowcol2(cell)
try:
cellValue = sheetR.cell_value(rowR, colR)
except IndexError:
cellValue = ""
"""
BUG: The option list needs to work in different ways
depending on the question type. The question type should
be added to the spreadsheet to save extra db calls:
* Location save all the data as a hierarchy
* MultiOption save all selections
* Option save the last selection
"""
if cellValue != "":
if optionList != None:
if type == "Location":
answerList[optionList[col]]=cellValue
elif type == "MultiOption":
answerList.append(optionList[col])
else:
answerList = optionList[col]
else:
if type == "Date":
(dtYear, dtMonth, dtDay, dtHour, dtMinute, dtSecond) = \
xlrd.xldate_as_tuple(cellValue,
workbook.datemode)
dtValue = date(dtYear, dtMonth, dtDay)
cellValue = dtValue.isoformat()
answerList += "%s" % cellValue
body += ',"%s"' % answerList
openFile.write(header)
openFile.write("\n")
openFile.write(body)
openFile.seek(0)
return openFile
def import_xls(uploadFile):
if series_id == None:
response.error = T("Series details missing")
return
openFile = StringIO()
try:
import xlrd
from xlwt.Utils import cell_to_rowcol2
except ImportError:
print >> sys.stderr, "ERROR: xlrd & xlwt modules are needed for importing spreadsheets"
return None
workbook = xlrd.open_workbook(file_contents=uploadFile)
sheetR = workbook.sheet_by_name("Assessment")
sheetM = workbook.sheet_by_name("Metadata")
header = ''
body = ''
for row in xrange(1, sheetM.nrows):
header += ',"%s"' % sheetM.cell_value(row, 0)
code = sheetM.cell_value(row, 0)
qstn = s3.survey_getQuestionFromCode(code, series_id)
type = qstn["type"]
count = sheetM.cell_value(row, 1)
if count != "":
count = int(count)
optionList = sheetM.cell_value(row, 2).split("|#|")
else:
count = 1
optionList = None
if type == "Location" and optionList != None:
answerList = {}
elif type == "MultiOption":
answerList = []
else:
answerList = ''
for col in range(count):
cell = sheetM.cell_value(row, 3 + col)
(rowR, colR) = cell_to_rowcol2(cell)
try:
response = sheetR.cell_value(rowR, colR)
except IndexError:
response = ""
"""
BUG: The option list needs to work in different ways
depending on the question type. The question type should
be added to the spreadsheet to save extra db calls:
* Location save all the data as a hierarchy
* MultiOption save all selections
* Option save the last selection
"""
if response != "":
if optionList != None:
if type == "Location":
answerList[optionList[col]] = response
elif type == "MultiOption":
answerList.append(optionList[col])
else:
answerList = optionList[col]
else:
answerList += "%s" % response
body += ',"%s"' % answerList
openFile.write(header)
openFile.write("\n")
openFile.write(body)
openFile.seek(0)
return openFile
