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