def setUp(self):
self.filename = tempfile.mktemp()
contents = dict()
for i in range(1000):
contents["key%d" % i] = "value%d" % i
languages.write_dict(self.filename, contents)
languages.read_dict(self.filename)
def setUp(self):
self.filename = tempfile.mktemp()
contents = dict()
for i in range(1000):
contents["key%d" % i] = "value%d" % i
languages.write_dict(self.filename, contents)
languages.read_dict(self.filename)
def test_update_all_languages(self):
languages.update_all_languages(self.apppath)
en_file = os.path.join(self.apppath, 'languages', 'en.py')
pt_file = os.path.join(self.apppath, 'languages', 'pt.py')
en_dict = languages.read_dict(en_file)
pt_dict = languages.read_dict(pt_file)
for key in ['hello', 'world', '%s %%{shop}', 'ahoy']:
self.assertTrue(key in en_dict)
self.assertTrue(key in pt_dict)
def test_update_all_languages(self):
languages.update_all_languages(self.apppath)
en_file = os.path.join(self.apppath, 'languages', 'en.py')
pt_file = os.path.join(self.apppath, 'languages', 'pt.py')
en_dict = languages.read_dict(en_file)
pt_dict = languages.read_dict(pt_file)
for key in ['hello', 'world', '%s %%{shop}', 'ahoy']:
self.assertTrue(key in en_dict)
self.assertTrue(key in pt_dict)
def edit_language():
""" Edit language file """
filename = '/'.join(request.args)
from gluon.languages import read_dict, write_dict
strings = read_dict(apath(filename, r=request))
keys = sorted(strings.keys())
rows = []
rows.append(H2(T('Original/Translation')))
for i,key in enumerate(keys):
if len(key) <= 40:
elem = INPUT(_type='text', _name=str(i),value=strings[key],_size=70)
else:
elem = TEXTAREA(_name=str(i), value=strings[key], _cols=70, _rows=5)
# Making the short circuit compatible with <= python2.4
k = (strings[key] != key) and key or B(key)
rows.append(P(k, BR(), elem, TAG.BUTTON(T('delete'),
_onclick='return delkey("%s")' % i), _id=str(i)))
rows.append(INPUT(_type='submit', _value=T('update')))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for i,key in enumerate(keys):
if form.vars[str(i)]==chr(127): continue
strs[key] = form.vars[str(i)]
write_dict(apath(filename, r=request), strs)
session.flash = T('file saved on %(time)s', dict(time=time.ctime()))
redirect(URL(r=request,args=request.args))
return dict(app=request.args[0], filename=filename, form=form)
def edit_language():
""" Edit language file """
#app = get_app()
lang = ("zh-tw",T("Mandarin"))
args = ['devicedb','languages',lang[0]+'.py']
filename = '/'.join(args)
response.title = args[-1]
strings = read_dict(apath(filename, r=request))
if '__corrupted__' in strings:
form = SPAN(strings['__corrupted__'], _class='error')
return dict(filename=filename, form=form)
keys = sorted(strings.keys(), lambda x, y: cmp(
unicode(x, 'utf-8').lower(), unicode(y, 'utf-8').lower()))
rows = []
rows.append(H2(T('Original/Translation')))
for key in keys:
name = md5_hash(key)
s = strings[key]
(prefix, sep, key) = key.partition('\x01')
if sep:
prefix = SPAN(prefix + ': ', _class='tm_ftag')
k = key
else:
(k, prefix) = (prefix, '')
_class = 'untranslated' if k == s else 'translated'
if len(s) <= 40:
elem = INPUT(_type='text', _name=name, value=s,
_size=70, _class=_class)
else:
elem = TEXTAREA(_name=name, value=s, _cols=70,
_rows=5, _class=_class)
# Making the short circuit compatible with <= python2.4
k = (s != k) and k or B(k)
new_row = DIV( LABEL(prefix, k, _style="font-weight:normal;"),
CAT(elem, '\n', TAG.BUTTON(
T('delete'),
_onclick='return delkey("%s")' % name,
_class='btn' )), _id=name, _class='span6 well well-small')
rows.append(DIV(new_row,_class="row-fluid"))
rows.append(DIV(INPUT(_type='submit', _value=T('update'), _class="btn btn-primary"), _class='controls'))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for key in keys:
name = md5_hash(key)
if form.vars[name] == chr(127):
continue
strs[key] = form.vars[name]
write_dict(apath(filename, r=request), strs)
session.flash = T('saved on UTC') + request.utcnow.strftime(" %Y-%m-%d %H:%M")
redirect(URL(r=request, args=request.args))
return dict(app=args[0], filename=filename, form=form, lang=lang)
def read_write(args):
(filename, iterations) = args
for i in range(0, iterations):
content = languages.read_dict(filename)
if not len(content):
return False
languages.write_dict(filename, content)
return True
def read_write(args):
(filename, iterations) = args
for i in range(0, iterations):
content = languages.read_dict(filename)
if not len(content):
return False
languages.write_dict(filename, content)
return True
def dc_question_l10n_onaccept(form):
"""
On-accept routine for dc_question_l10n:
- Update the Translations file with translated Options
"""
try:
question_l10n_id = form.vars.id
except AttributeError:
return
db = current.db
# Read the Question
qtable = db.dc_question
ltable = db.dc_question_l10n
query = (qtable.id == ltable.question_id) & \
(ltable.id == question_l10n_id)
question = db(query).select(qtable.field_type,
qtable.options,
ltable.options_l10n,
ltable.language,
limitby=(0, 1)).first()
if question["dc_question.field_type"] != 6:
# Nothing we need to do
return
options = question["dc_question.options"]
options_l10n = question["dc_question_l10n.options_l10n"]
len_options = len(options)
if len_options != len(options_l10n):
current.session.error(
T("Number of Translated Options don't match original!"))
return
# Read existing translations (if any)
w2pfilename = os.path.join(
current.request.folder, "languages",
"%s.py" % question["dc_question_l10n.language"])
if os.path.exists(w2pfilename):
translations = read_dict(w2pfilename)
else:
translations = {}
# Add ours
for i in range(len_options):
original = s3_str(options[i])
translated = s3_str(options_l10n[i])
if original != translated:
translations[original] = translated
# Write out new file
write_dict(w2pfilename, translations)
def series_export_formatted():
s3mgr.load("survey_series")
s3mgr.load("survey_complete")
# Check that the series_id has been passed in
if len(request.args) != 1:
output = s3_rest_controller(module, resourcename, rheader=response.s3.survey_series_rheader)
return output
series_id = request.args[0]
vars = current.request.post_vars
seriesName = response.s3.survey_getSeriesName(series_id)
series = response.s3.survey_getSeries(series_id)
if not series.logo:
logo = None
else:
if "Export_Spreadsheet" in vars:
ext = "bmp"
else:
ext = "png"
logo = os.path.join(request.folder, "uploads", "survey", "logo", "%s.%s" % (series.logo, ext))
if not os.path.exists(logo) or not os.path.isfile(logo):
logo = None
# Get the translation dictionary
langDict = dict()
if "translationLanguage" in request.post_vars:
lang = request.post_vars.translationLanguage
if lang == "Default":
langDict = dict()
else:
try:
lang_fileName = "applications/%s/uploads/survey/translations/%s.py" % (request.application, lang)
langDict = read_dict(lang_fileName)
except:
langDict = dict()
if "Export_Spreadsheet" in vars:
(matrix, matrixAnswers) = series_prepare_matrix(series_id, series, logo, langDict, justified=True)
output = series_export_spreadsheet(matrix, matrixAnswers, logo)
filename = "%s.xls" % seriesName
contentType = ".xls"
elif "Export_Word" in vars:
template = response.s3.survey_getTemplateFromSeries(series_id)
template_id = template.id
title = "%s (%s)" % (series.name, template.name)
title = survey_T(title, langDict)
widgetList = response.s3.survey_getAllWidgetsForTemplate(template_id)
output = series_export_word(widgetList, langDict, title, logo)
filename = "%s.rtf" % seriesName
contentType = ".rtf"
else:
output = s3_rest_controller(module, resourcename, rheader=response.s3.survey_series_rheader)
return output
output.seek(0)
response.headers["Content-Type"] = contenttype(contentType)
response.headers["Content-disposition"] = 'attachment; filename="%s"' % filename
return output.read()
def edit_language():
""" Edit language file """
filename = '/'.join(request.args)
from gluon.languages import read_dict, write_dict
strings = read_dict(apath(filename, r=request))
keys = sorted(strings.keys(), lambda x, y: cmp(x.lower(), y.lower()))
rows = []
rows.append(H2(T('Original/Translation')))
for key in keys:
name = md5_hash(key)
if key == strings[key]:
_class = 'untranslated'
else:
_class = 'translated'
if len(key) <= 40:
elem = INPUT(_type='text',
_name=name,
value=strings[key],
_size=70,
_class=_class)
else:
elem = TEXTAREA(_name=name,
value=strings[key],
_cols=70,
_rows=5,
_class=_class)
# Making the short circuit compatible with <= python2.4
k = (strings[key] != key) and key or B(key)
rows.append(
P(k,
BR(),
elem,
TAG.BUTTON(T('delete'), _onclick='return delkey("%s")' % name),
_id=name))
rows.append(INPUT(_type='submit', _value=T('update')))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for key in keys:
name = md5_hash(key)
if form.vars[name] == chr(127): continue
strs[key] = form.vars[name]
write_dict(apath(filename, r=request), strs)
session.flash = T('file saved on %(time)s', dict(time=time.ctime()))
redirect(URL(r=request, args=request.args))
return dict(app=request.args[0], filename=filename, form=form)
def edit_language():
""" Edit language file """
app = get_app()
filename = "/".join(request.args)
from gluon.languages import read_dict, write_dict
strings = read_dict(apath(filename, r=request))
keys = sorted(strings.keys(), lambda x, y: cmp(x.lower(), y.lower()))
rows = []
rows.append(H2(T("Original/Translation")))
for key in keys:
name = md5_hash(key)
if key == strings[key]:
_class = "untranslated"
else:
_class = "translated"
if len(key) <= 40:
elem = INPUT(_type="text", _name=name, value=strings[key], _size=70, _class=_class)
else:
elem = TEXTAREA(_name=name, value=strings[key], _cols=70, _rows=5, _class=_class)
# Making the short circuit compatible with <= python2.4
k = (strings[key] != key) and key or B(key)
rows.append(P(k, BR(), elem, TAG.BUTTON(T("delete"), _onclick='return delkey("%s")' % name), _id=name))
rows.append(INPUT(_type="submit", _value=T("update")))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for key in keys:
name = md5_hash(key)
if form.vars[name] == chr(127):
continue
strs[key] = form.vars[name]
write_dict(apath(filename, r=request), strs)
session.flash = T("file saved on %(time)s", dict(time=time.ctime()))
redirect(URL(r=request, args=request.args))
return dict(app=request.args[0], filename=filename, form=form)
def templateTranslateDownload():
# Load Model
module = "survey"
resourcename = "translate"
tablename = "%s_%s" % (module, resourcename)
s3mgr.load("survey_template")
s3mgr.load("survey_translate")
s3mgr.load("survey_complete")
try:
import xlwt
except ImportError:
redirect(URL(c="survey",
f="templateTranslation",
args=[],
vars = {}))
s3 = response.s3
record = s3.survey_getTranslation(request.args[0])
if record == None:
redirect(URL(c="survey",
f="templateTranslation",
args=[],
vars = {}))
code = record.code
language = record.language
lang_fileName = "applications/%s/languages/%s.py" % \
(request.application, code)
try:
strings = read_dict(lang_fileName)
except:
strings = dict()
template_id = record.template_id
template = s3.survey_getTemplate(template_id)
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet(language)
output = StringIO()
qstnList = s3.survey_getAllQuestionsForTemplate(template_id)
original = {}
original[template["name"]] = True
if template["description"] != "":
original[template["description"]] = True
for qstn in qstnList:
original[qstn["name"]] = True
widgetObj = survey_question_type[qstn["type"]](question_id = qstn["qstn_id"])
if isinstance(widgetObj, S3QuestionTypeOptionWidget):
optionList = widgetObj.getList()
for option in optionList:
original[option] = True
sections = s3.survey_getAllSectionsForTemplate(template_id)
for section in sections:
original[section["name"]]=True
section_id = section["section_id"]
layoutRules = s3.survey_getQstnLayoutRules(template_id, section_id)
layoutStr = str(layoutRules)
posn = layoutStr.find("heading")
while posn != -1:
start = posn + 11
end = layoutStr.find("}", start)
original[layoutStr[start:end]] = True
posn = layoutStr.find("heading", end)
row = 0
sheet.write(row,
0,
unicode("Original")
)
sheet.write(row,
1,
unicode("Translation")
)
originalList = original.keys()
originalList.sort()
for text in originalList:
row += 1
original = unicode(text)
sheet.write(row,
0,
original
)
if (original in strings):
sheet.write(row,
1,
strings[original]
)
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
filename = "%s.xls" % code
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def templateTranslateDownload():
"""
Download a Translation Template
@ToDo: Rewrite as S3Method handler
"""
error_url = URL(c="survey", f="templateTranslation", args=[], vars={})
try:
translation_id = request.args[0]
except:
redirect(error_url)
try:
import xlwt
except ImportError:
redirect(error_url)
table = s3db.survey_translate
record = db(table.id == translation_id).select(table.code,
table.language,
table.template_id,
limitby=(0, 1)).first()
if record is None:
redirect(error_url)
code = record.code
language = record.language
lang_fileName = "applications/%s/languages/%s.py" % \
(appname, code)
try:
from gluon.languages import read_dict
strings = read_dict(lang_fileName)
except:
strings = dict()
template_id = record.template_id
# Load Model
table = s3db.survey_template
s3db.table("survey_complete")
template = db(table.id == template_id).select(table.name,
table.description,
limitby=(0, 1)).first()
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet(language)
output = StringIO()
qstnList = s3.survey_getAllQuestionsForTemplate(template_id)
original = {}
original[template.name] = True
if template.description != "":
original[template.description] = True
for qstn in qstnList:
original[qstn["name"]] = True
widgetObj = survey_question_type[qstn["type"]](
question_id=qstn["qstn_id"])
if isinstance(widgetObj, S3QuestionTypeOptionWidget):
optionList = widgetObj.getList()
for option in optionList:
original[option] = True
sections = s3.survey_getAllSectionsForTemplate(template_id)
for section in sections:
original[section["name"]] = True
section_id = section["section_id"]
layoutRules = s3.survey_getQstnLayoutRules(template_id, section_id)
layoutStr = str(layoutRules)
posn = layoutStr.find("heading")
while posn != -1:
start = posn + 11
end = layoutStr.find("}", start)
original[layoutStr[start:end]] = True
posn = layoutStr.find("heading", end)
row = 0
sheet.write(row, 0, unicode("Original"))
sheet.write(row, 1, unicode("Translation"))
originalList = original.keys()
originalList.sort()
for text in originalList:
row += 1
original = unicode(text)
sheet.write(row, 0, original)
if (original in strings):
sheet.write(row, 1, strings[original])
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
filename = "%s.xls" % code
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def templateTranslateDownload():
"""
Download a Translation Template
@ToDo: Rewrite as S3Method handler
"""
error_url = URL(c="survey", f="templateTranslation", args=[], vars={})
try:
translation_id = request.args[0]
except:
redirect(error_url)
try:
import xlwt
except ImportError:
redirect(error_url)
table = s3db.survey_translate
record = db(table.id == translation_id).select(table.code,
table.language,
table.template_id,
limitby=(0, 1)).first()
if record is None:
redirect(error_url)
code = record.code
language = record.language
lang_fileName = "applications/%s/languages/%s.py" % \
(appname, code)
try:
from gluon.languages import read_dict
strings = read_dict(lang_fileName)
except:
strings = dict()
template_id = record.template_id
# Load Model
table = s3db.survey_template
s3db.table("survey_complete")
template = db(table.id == template_id).select(table.name,
table.description,
limitby=(0, 1)).first()
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet(language)
output = StringIO()
qstnList = s3.survey_getAllQuestionsForTemplate(template_id)
original = {}
original[template.name] = True
if template.description != "":
original[template.description] = True
for qstn in qstnList:
original[qstn["name"]] = True
widgetObj = survey_question_type[qstn["type"]](question_id = qstn["qstn_id"])
if isinstance(widgetObj, S3QuestionTypeOptionWidget):
optionList = widgetObj.getList()
for option in optionList:
original[option] = True
sections = s3.survey_getAllSectionsForTemplate(template_id)
for section in sections:
original[section["name"]] = True
section_id = section["section_id"]
layoutRules = s3.survey_getQstnLayoutRules(template_id, section_id)
layoutStr = str(layoutRules)
posn = layoutStr.find("heading")
while posn != -1:
start = posn + 11
end = layoutStr.find("}", start)
original[layoutStr[start:end]] = True
posn = layoutStr.find("heading", end)
row = 0
sheet.write(row,
0,
unicode("Original")
)
sheet.write(row,
1,
unicode("Translation")
)
originalList = original.keys()
originalList.sort()
for text in originalList:
row += 1
original = unicode(text)
sheet.write(row,
0,
original
)
if (original in strings):
sheet.write(row,
1,
strings[original]
)
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
filename = "%s.xls" % code
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def templateTranslateDownload():
# Load Model
module = "survey"
resourcename = "translate"
tablename = "%s_%s" % (module, resourcename)
s3mgr.load("survey_template")
s3mgr.load("survey_translate")
s3mgr.load("survey_complete")
try:
import xlwt
except ImportError:
redirect(URL(c="survey",
f="templateTranslation",
args=[],
vars = {}))
s3 = response.s3
record = s3.survey_getTranslation(request.args[0])
if record == None:
redirect(URL(c="survey",
f="templateTranslation",
args=[],
vars = {}))
code = record.code
language = record.language
lang_fileName = "applications/%s/languages/%s.py" % \
(request.application, code)
try:
strings = read_dict(lang_fileName)
except:
strings = dict()
template_id = record.template_id
template = s3.survey_getTemplate(template_id)
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet(language)
output = StringIO()
qstnList = s3.survey_getAllQuestionsForTemplate(template_id)
original = {}
original[template["name"]] = True
if template["description"] != "":
original[template["description"]] = True
for qstn in qstnList:
original[qstn["name"]] = True
widgetObj = survey_question_type[qstn["type"]](question_id = qstn["qstn_id"])
if isinstance(widgetObj, S3QuestionTypeOptionWidget):
optionList = widgetObj.getList()
for option in optionList:
original[option] = True
sections = s3.survey_getAllSectionsForTemplate(template_id)
for section in sections:
original[section["name"]]=True
section_id = section["section_id"]
layoutRules = s3.survey_getQstnLayoutRules(template_id, section_id)
layoutStr = str(layoutRules)
posn = layoutStr.find("heading")
while posn != -1:
start = posn + 11
end = layoutStr.find("}", start)
original[layoutStr[start:end]] = True
posn = layoutStr.find("heading", end)
row = 0
sheet.write(row,
0,
unicode("Original")
)
sheet.write(row,
1,
unicode("Translation")
)
originalList = original.keys()
originalList.sort()
for text in originalList:
row += 1
original = unicode(text)
sheet.write(row,
0,
original
)
if (original in strings):
sheet.write(row,
1,
strings[original]
)
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
filename = "%s.xls" % code
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def series_export_formatted():
prefix = "survey"
resourcename = "series"
tablename = "%s_%s" % (prefix, resourcename)
s3mgr.load(tablename)
crud_strings = response.s3.crud_strings[tablename]
try:
import xlwt
except ImportError:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
######################################################################
#
# Get the data
# ============
# * The sections within the template
# * The layout rules for each question
######################################################################
# Check that the series_id has been passed in
if len(request.args) != 1:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
if "translationLanguage" in request.post_vars:
lang = request.post_vars.translationLanguage
if lang == "Default":
langDict = dict()
else:
try:
lang_fileName = "applications/%s/uploads/survey/translations/%s.py" % (request.application, lang)
langDict = read_dict(lang_fileName)
except:
langDict = dict()
series_id = request.args[0]
sectionList = response.s3.survey_getAllSectionsForSeries(series_id)
layout = {}
for section in sectionList:
sectionName = section["name"]
rules = response.s3.survey_getQstnLayoutRules(section["template_id"],
section["section_id"]
)
layout[sectionName] = rules
######################################################################
#
# Store the questions into a matrix based on the layout and the space
# required for each question - for example an option question might
# need one row for each possible option, and if this is in a layout
# then the position needs to be recorded carefully...
#
######################################################################
def processRule(series_id, rules, row, col,
matrix, matrixAnswer, action="rows"):
startcol = col
startrow = row
endcol = col
endrow = row
nextrow = row
nextcol = col
for element in rules:
if action == "rows":
row = endrow
col = startcol
elif action == "columns":
row = startrow
if endcol == 0:
col = 0
else:
col = endcol+1
# If the rule is a list then step through each element
if isinstance(element,list):
if action == "rows":
tempAction = "columns"
else:
tempAction = "rows"
(endrow, endcol) = processRule(series_id, element, row, col,
matrix, matrixAnswer, tempAction)
elif isinstance(element,dict):
(endrow, endcol) = processDict(series_id, element, row, col,
matrix, matrixAnswer, action)
else:
(endrow, endcol) = addData(element, row, col,
matrix, matrixAnswer)
if endrow > nextrow:
nextrow = endrow
if endcol > nextcol:
nextcol = endcol
return (nextrow, nextcol)
def processDict(series_id, rules, row, col,
matrix, matrixAnswer, action="rows"):
startcol = col
startrow = row
nextrow = row
nextcol = col
for (key, value) in rules.items():
if (key == "heading"):
cell = MatrixElement(row,col,value, style="styleSubHeader")
cell.merge(horizontal=1)
try:
matrix.addElement(cell)
except Exception as msg:
print msg
return (row,col)
endrow = row + 1
endcol = col + 2
elif (key == "rows") or (key == "columns"):
(endrow, endcol) = processRule(series_id, value, row, col,
matrix, matrixAnswer, action=key)
else:
## Unknown key
continue
if action == "rows":
row = startrow
col = endcol + 1 # Add a blank column
elif action == "columns":
row = endrow
col = startcol
if endrow > nextrow:
nextrow = endrow
if endcol > nextcol:
nextcol = endcol
return (nextrow, nextcol)
def addData(qstn, row, col, matrix, matrixAnswer):
question = response.s3.survey_getQuestionFromCode(qstn, series_id)
if question == {}:
return (row,col)
widgetObj = survey_question_type[question["type"]](question_id = question["qstn_id"])
try:
(endrow, endcol) = widgetObj.writeToMatrix(matrix,
row,
col,
answerMatrix=matrixAnswer,
langDict = langDict
)
except Exception as msg:
print msg
return (row,col)
if question["type"] == "Grid":
matrix.boxRange(row, col, endrow-1, endcol-1)
return (endrow, endcol)
row = 0
col = 0
matrix = DataMatrix()
matrixAnswers = DataMatrix()
template = response.s3.survey_getTemplateFromSeries(series_id)
series = response.s3.survey_getSeries(series_id)
logo = os.path.join(request.folder,
"static",
"img",
"logo",
series.logo
)
if os.path.exists(logo) and os.path.isfile(logo):
cell = MatrixElement(0,col,"", style=["styleText"])
cell.merge(vertical=2)
matrix.addElement(cell)
col = 2
row += 1
else:
logo = None
title = "%s (%s)" % (series.name, template.name)
title = survey_T(title, langDict)
cell = MatrixElement(0, col, title, style="styleHeader")
cell.merge(vertical=1, horizontal=4)
matrix.addElement(cell)
row += 2
for section in sectionList:
col = 0
row += 1
rules = layout[section["name"]]
cell = MatrixElement(row, col, survey_T(section["name"], langDict),
style="styleHeader")
try:
matrix.addElement(cell)
except Exception as msg:
print msg
row += 1
startrow = row
(row, col) = processRule(series_id, rules, row, col, matrix, matrixAnswers)
matrix.boxRange(startrow, 0, row, col-1)
######################################################################
#
# Now take the matrix data type and generate a spreadsheet from it
#
######################################################################
import math
def wrapText(sheet, cell, style):
row = cell.row
col = cell.col
try:
text = unicode(cell.text)
except:
text = cell.text
width = 16
# Wrap text and calculate the row width and height
characters_in_cell = float(width-2)
twips_per_row = 255 #default row height for 10 point font
if cell.merged():
sheet.write_merge(cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
text,
style
)
rows = math.ceil((len(text) / characters_in_cell) / (1 + cell.mergeH))
else:
sheet.write(cell.row,
cell.col,
text,
style
)
rows = math.ceil(len(text) / characters_in_cell)
new_row_height = int(rows * twips_per_row)
new_col_width = width * COL_WIDTH_MULTIPLIER
if sheet.row(row).height < new_row_height:
sheet.row(row).height = new_row_height
if sheet.col(col).width < new_col_width:
sheet.col(col).width = new_col_width
def mergeStyles(listTemplate, styleList):
"""
Take a list of styles and return a single style object with
all the differences from a newly created object added to the
resultant style.
"""
if len(styleList) == 0:
finalStyle = xlwt.XFStyle()
elif len(styleList) == 1:
finalStyle = listTemplate[styleList[0]]
else:
zeroStyle = xlwt.XFStyle()
finalStyle = xlwt.XFStyle()
for i in range(0,len(styleList)):
finalStyle = mergeObjectDiff(finalStyle,
listTemplate[styleList[i]],
zeroStyle)
return finalStyle
def mergeObjectDiff(baseObj, newObj, zeroObj):
"""
function to copy all the elements in newObj that are different from
the zeroObj and place them in the baseObj
"""
elementList = newObj.__dict__
for (element, value) in elementList.items():
try:
baseObj.__dict__[element] = mergeObjectDiff(baseObj.__dict__[element],
value,
zeroObj.__dict__[element])
except:
if zeroObj.__dict__[element] != value:
baseObj.__dict__[element] = value
return baseObj
COL_WIDTH_MULTIPLIER = 240
book = xlwt.Workbook(encoding="utf-8")
output = StringIO()
protection = xlwt.Protection()
protection.cell_locked = 1
noProtection = xlwt.Protection()
noProtection.cell_locked = 0
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borderTL = xlwt.Borders()
borderTL.left = xlwt.Borders.DOUBLE
borderTL.top = xlwt.Borders.DOUBLE
borderT = xlwt.Borders()
borderT.top = xlwt.Borders.DOUBLE
borderL = xlwt.Borders()
borderL.left = xlwt.Borders.DOUBLE
borderTR = xlwt.Borders()
borderTR.right = xlwt.Borders.DOUBLE
borderTR.top = xlwt.Borders.DOUBLE
borderR = xlwt.Borders()
borderR.right = xlwt.Borders.DOUBLE
borderBL = xlwt.Borders()
borderBL.left = xlwt.Borders.DOUBLE
borderBL.bottom = xlwt.Borders.DOUBLE
borderB = xlwt.Borders()
borderB.bottom = xlwt.Borders.DOUBLE
borderBR = xlwt.Borders()
borderBR.right = xlwt.Borders.DOUBLE
borderBR.bottom = xlwt.Borders.DOUBLE
alignBase = xlwt.Alignment()
alignBase.horz = xlwt.Alignment.HORZ_LEFT
alignBase.vert = xlwt.Alignment.VERT_TOP
alignWrap = xlwt.Alignment()
alignWrap.horz = xlwt.Alignment.HORZ_LEFT
alignWrap.vert = xlwt.Alignment.VERT_TOP
alignWrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
shadedFill = xlwt.Pattern()
shadedFill.pattern = xlwt.Pattern.SOLID_PATTERN
shadedFill.pattern_fore_colour = 0x16 # 25% Grey
shadedFill.pattern_back_colour = 0x08 # Black
styleTitle = xlwt.XFStyle()
styleTitle.font.height = 0x0140 # 320 twips, 16 points
styleTitle.font.bold = True
styleTitle.alignment = alignBase
styleHeader = xlwt.XFStyle()
styleHeader.font.height = 0x00F0 # 240 twips, 12 points
styleHeader.font.bold = True
styleHeader.alignment = alignBase
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
styleSubHeader.alignment = alignWrap
styleText = xlwt.XFStyle()
styleText.protection = protection
styleText.alignment = alignWrap
styleInstructions = xlwt.XFStyle()
styleInstructions.font.height = 0x00B4 # 180 twips, 9 points
styleInstructions.font.italic = True
styleInstructions.protection = protection
styleInstructions.alignment = alignWrap
styleBox = xlwt.XFStyle()
styleBox.borders = borders
styleBox.protection = noProtection
styleInput = xlwt.XFStyle()
styleInput.borders = borders
styleInput.protection = noProtection
styleInput.pattern = shadedFill
boxL = xlwt.XFStyle()
boxL.borders = borderL
boxT = xlwt.XFStyle()
boxT.borders = borderT
boxR = xlwt.XFStyle()
boxR.borders = borderR
boxB = xlwt.XFStyle()
boxB.borders = borderB
styleList = {}
styleList["styleTitle"] = styleTitle
styleList["styleHeader"] = styleHeader
styleList["styleSubHeader"] = styleSubHeader
styleList["styleText"] = styleText
styleList["styleInstructions"] = styleInstructions
styleList["styleInput"] = styleInput
styleList["boxL"] = boxL
styleList["boxT"] = boxT
styleList["boxR"] = boxR
styleList["boxB"] = boxB
sheet1 = book.add_sheet(T("Assessment"))
sheetA = book.add_sheet(T("Metadata"))
for cell in matrix.matrix.values():
if cell.joined():
continue
style = mergeStyles(styleList, cell.styleList)
if (style.alignment.wrap == style.alignment.WRAP_AT_RIGHT):
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
try:
wrapText(sheet1, cell, joinedStyle)
except:
pass
else:
if cell.merged():
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
sheet1.write_merge(cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
unicode(cell.text),
joinedStyle
)
else:
sheet1.write(cell.row,
cell.col,
unicode(cell.text),
style
)
sheetA.write(0, 0, "Question Code")
sheetA.write(0, 1, "Response Count")
sheetA.write(0, 2, "Values")
sheetA.write(0, 3, "Cell Address")
for cell in matrixAnswers.matrix.values():
style = mergeStyles(styleList, cell.styleList)
sheetA.write(cell.row,
cell.col,
unicode(cell.text),
style
)
if logo != None:
sheet1.insert_bitmap(logo, 0, 0)
sheet1.protect = True
sheetA.protect = True
for i in range(26):
sheetA.col(i).width = 0
sheetA.write(0,
26,
unicode(T("Please do not remove this sheet")),
styleHeader
)
sheetA.col(26).width = 12000
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
seriesName = response.s3.survey_getSeriesName(series_id)
filename = "%s.xls" % seriesName
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def series_export_formatted():
"""
"""
s3db.table("survey_series")
s3db.table("survey_complete")
# Check that the series_id has been passed in
if len(request.args) != 1:
output = s3_rest_controller("survey",
"series",
rheader=s3db.survey_series_rheader)
return output
series_id = request.args[0]
vars = request.post_vars
seriesName = s3db.survey_getSeriesName(series_id)
series = s3db.survey_getSeries(series_id)
if not series.logo:
logo = None
else:
if "Export_Spreadsheet" in vars:
ext = "bmp"
else:
ext = "png"
logo = os.path.join(request.folder, "uploads", "survey", "logo",
"%s.%s" % (series.logo, ext))
if not os.path.exists(logo) or not os.path.isfile(logo):
logo = None
# Get the translation dictionary
langDict = dict()
if "translationLanguage" in request.post_vars:
lang = request.post_vars.translationLanguage
if lang == "Default":
langDict = dict()
else:
try:
from gluon.languages import read_dict
lang_fileName = "applications/%s/uploads/survey/translations/%s.py" % (
appname, lang)
langDict = read_dict(lang_fileName)
except:
langDict = dict()
if "Export_Spreadsheet" in vars:
(matrix, matrixAnswers) = series_prepare_matrix(series_id,
series,
logo,
langDict,
justified=True)
output = series_export_spreadsheet(
matrix,
matrixAnswers,
logo,
)
filename = "%s.xls" % seriesName
contentType = ".xls"
elif "Export_Word" in vars:
template = s3db.survey_getTemplateFromSeries(series_id)
template_id = template.id
title = "%s (%s)" % (series.name, template.name)
title = survey_T(title, langDict)
widgetList = s3db.survey_getAllWidgetsForTemplate(template_id)
output = series_export_word(widgetList, langDict, title, logo)
filename = "%s.rtf" % seriesName
contentType = ".rtf"
else:
output = s3_rest_controller("survey",
"series",
rheader=s3db.survey_series_rheader)
return output
output.seek(0)
response.headers["Content-Type"] = contenttype(contentType)
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def edit_language():
""" Edit language file, copied from admin/controllers/default.py """
import os
import re
import time
from gluon.languages import (read_dict, write_dict)
from gluon.utils import md5_hash
if len(request.args) == 0:
raise HTTP(400 , "No language provided")
if re.match(request.args[0], r'[^\w\.\-]') is None:
filename = os.path.join(request.folder,"languages",request.args[0] + ".py")
if os.path.isfile(filename):
response.title = "OneZoom language strings for " + request.args[0]
strings = read_dict(filename)
if '__corrupted__' in strings:
form = SPAN(strings['__corrupted__'], _class='error')
return dict(filename=filename, form=form)
keys = sorted(strings.keys(), lambda x, y: cmp(
unicode(x, 'utf-8').lower(), unicode(y, 'utf-8').lower()))
rows = []
rows.append(H2(T('Original/Translation')))
for key in keys:
name = md5_hash(key)
s = strings[key]
(prefix, sep, key) = key.partition('\x01')
if sep:
prefix = SPAN(prefix + ': ', _class='tm_ftag')
k = key
else:
(k, prefix) = (prefix, '')
_class = 'untranslated' if k == s else 'translated'
if len(s) <= 40 and '\n' not in s:
elem = INPUT(_type='text', _name=name, value=s,
_size=70, _class=_class)
else:
elem = TEXTAREA(_name=name, value="\n"+s, _cols=80,
_rows=5, _class=_class)
# Making the short circuit compatible with <= python2.4
k = (s != k) and k.replace('\n','¶') or B(k.replace('\n','¶'))
new_row = DIV(LABEL(prefix, k, _style="font-weight:normal; display:block;"),
CAT(elem, '\n', TAG.BUTTON(
IMG(_src=URL("static","images","close_red.png"),_width="20",_height="20"), XML(' '), T('Delete'),
_onclick='return delkey("%s")' % name,
_class='btn')), _id=name, _class='span6 well well-small')
rows.append(DIV(new_row, _class="row-fluid"))
rows.append(DIV(INPUT(_type='submit', _value=T('Update'), _class="btn btn-primary"), _class='controls'))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for key in keys:
name = md5_hash(key)
if form.vars[name] == chr(127):
continue
strs[key] = form.vars[name]
write_dict(filename, strs)
session.flash = T('file saved on %(time)s', dict(time=time.ctime()))
redirect(URL(r=request, args=request.args))
return dict(filename=filename, form=form)
def series_export_formatted():
"""
Download a Spreadsheet which can be filled-in offline & uploaded
@ToDo: rewrite as S3Method handler
"""
try:
series_id = request.args[0]
except:
output = s3_rest_controller("survey",
"series",
rheader=s3db.survey_series_rheader)
return output
# Load Model
table = s3db.survey_series
s3db.table("survey_complete")
vars = request.post_vars
series = db(table.id == series_id).select(table.name,
table.logo,
limitby=(0, 1)).first()
if not series.logo:
logo = None
else:
if "Export_Spreadsheet" in vars:
ext = "bmp"
else:
ext = "png"
logo = os.path.join(request.folder, "uploads", "survey", "logo",
"%s.%s" % (series.logo, ext))
if not os.path.exists(logo) or not os.path.isfile(logo):
logo = None
# Get the translation dictionary
lang_dict = {}
lang = request.post_vars.get("translation_language", None)
if lang:
if lang == "Default":
lang_dict = {}
else:
try:
from gluon.languages import read_dict
lang_filename = "applications/%s/uploads/survey/translations/%s.py" % \
(appname, lang)
lang_dict = read_dict(lang_filename)
except:
lang_dict = {}
if "Export_Spreadsheet" in vars:
(matrix, matrix_answers) = series_prepare_matrix(series_id,
series,
logo,
lang_dict,
justified=True)
output = series_export_spreadsheet(
matrix,
matrix_answers,
logo,
)
filename = "%s.xls" % series.name
content_type = ".xls"
elif "Export_Word" in vars:
template = s3db.survey_getTemplateFromSeries(series_id)
template_id = template.id
title = "%s (%s)" % (series.name, template.name)
title = s3db.survey_T(title, lang_dict)
widget_list = s3db.survey_getAllWidgetsForTemplate(template_id)
output = series_export_word(widget_list, lang_dict, title, logo)
filename = "%s.rtf" % series.name
content_type = ".rtf"
else:
output = s3_rest_controller("survey",
"series",
rheader=s3db.survey_series_rheader)
return output
from gluon.contenttype import contenttype
output.seek(0)
response.headers["Content-Type"] = contenttype(content_type)
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def edit_language():
""" Edit language file, copied from admin/controllers/default.py """
import os
import re
import time
from gluon.languages import (read_dict, write_dict, to_native)
from gluon.utils import md5_hash
if len(request.args) == 0:
raise HTTP(400, "No language provided")
if re.match(request.args[0], r'[^\w\.\-]') is None:
filename = os.path.join(request.folder, "languages",
request.args[0] + ".py")
if os.path.isfile(filename):
response.title = "OneZoom language strings for " + request.args[0]
strings = read_dict(filename)
if '__corrupted__' in strings:
form = SPAN(strings['__corrupted__'], _class='error')
return dict(filename=filename, form=form)
keys = sorted(strings.keys(), key=lambda x: to_native(x).lower())
rows = []
rows.append(H2(T('Original/Translation')))
for key in keys:
name = md5_hash(key)
s = strings[key]
(prefix, sep, key) = key.partition('\x01')
if sep:
prefix = SPAN(prefix + ': ', _class='tm_ftag')
k = key
else:
(k, prefix) = (prefix, '')
_class = 'untranslated' if k == s else 'translated'
if len(s) <= 40 and '\n' not in s:
elem = INPUT(_type='text',
_name=name,
value=s,
_size=70,
_class=_class)
else:
elem = TEXTAREA(_name=name,
value="\n" + s,
_cols=80,
_rows=5,
_class=_class)
# Making the short circuit compatible with <= python2.4
k = (s != k) and k.replace('\n', '¶') or B(k.replace(
'\n', '¶'))
new_row = DIV(
LABEL(prefix,
k,
_style="font-weight:normal; display:block;"),
CAT(
elem, '\n',
TAG.BUTTON(IMG(_src=URL("static", "images",
"close_red.png"),
_width="20",
_height="20"),
XML(' '),
T('Delete'),
_onclick='return delkey("%s")' % name,
_class='btn')),
_id=name,
_class='span6 well well-small')
rows.append(DIV(new_row, _class="row-fluid"))
rows.append(
DIV(INPUT(_type='submit',
_value=T('Update'),
_class="btn btn-primary"),
_class='controls'))
form = FORM(*rows)
if form.accepts(request.vars, keepvalues=True):
strs = dict()
for key in keys:
name = md5_hash(key)
with warnings.catch_warnings(record=True) as w:
try:
if form.vars[name] == chr(127):
continue
except UnicodeWarning:
if form.vars[name] == oldchr(127):
continue
strs[key] = form.vars[name]
write_dict(filename, strs)
session.flash = T('file saved on %(time)s',
dict(time=time.ctime()))
redirect(URL(r=request, args=request.args))
return dict(filename=filename, form=form)
def series_export_formatted():
prefix = "survey"
resourcename = "series"
tablename = "%s_%s" % (prefix, resourcename)
s3mgr.load(tablename)
crud_strings = response.s3.crud_strings[tablename]
try:
import xlwt
except ImportError:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
fixedWidth = True
cellWidth = 480 # 2 * 240
######################################################################
#
# Get the data
# ============
# * The sections within the template
# * The layout rules for each question
######################################################################
# Check that the series_id has been passed in
if len(request.args) != 1:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
series_id = request.args[0]
# Get the translation dictionary
langDict = dict()
if "translationLanguage" in request.post_vars:
lang = request.post_vars.translationLanguage
if lang == "Default":
langDict = dict()
else:
try:
lang_fileName = "applications/%s/uploads/survey/translations/%s.py" % (request.application, lang)
langDict = read_dict(lang_fileName)
except:
langDict = dict()
template = response.s3.survey_getTemplateFromSeries(series_id)
template_id = template.id
series = response.s3.survey_getSeries(series_id)
sectionList = response.s3.survey_getAllSectionsForSeries(series_id)
title = "%s (%s)" % (series.name, template.name)
title = survey_T(title, langDict)
layout = []
for section in sectionList:
sectionName = survey_T(section["name"], langDict)
rules = response.s3.survey_getQstnLayoutRules(template_id,
section["section_id"]
)
layoutRules = [sectionName, rules]
layout.append(layoutRules)
logo = os.path.join(request.folder,
"static",
"img",
"logo",
series.logo
)
if not os.path.exists(logo) or not os.path.isfile(logo):
logo = None
widgetList = response.s3.survey_getAllWidgetsForTemplate(template_id)
layoutBlocks = LayoutBlocks()
preliminaryMatrix = getMatrix(title,
logo,
series,
layout,
widgetList,
False,
langDict,
showSectionLabels = False,
layoutBlocks = layoutBlocks
)
if DEBUG:
# print >> sys.stdout, preliminaryMatrix
print >> sys.stdout, "preliminaryMatrix layoutBlocks"
print >> sys.stdout, layoutBlocks
layoutBlocks.align()
if DEBUG:
print >> sys.stdout, "Aligned layoutBlocks"
print >> sys.stdout, layoutBlocks
layoutBlocks = LayoutBlocks()
(matrix1, matrix2) = getMatrix(title,
logo,
series,
layout,
widgetList,
True,
langDict,
showSectionLabels = False,
# layoutBlocks = layoutBlocks
)
# if DEBUG:
# print >> sys.stdout, "formattedMatrix"
# print >> sys.stdout, formattedMatrix
# print >> sys.stdout, "formattedMatrix layoutBlocks"
# print >> sys.stdout, layoutBlocks
#f = open("/home/graeme/web2py/applications/eden/uploads/debug.txt","w+")
#print >> f, matrix1
matrix = matrix1
matrixAnswers = matrix2
######################################################################
#
# Store the questions into a matrix based on the layout and the space
# required for each question - for example an option question might
# need one row for each possible option, and if this is in a layout
# then the position needs to be recorded carefully...
#
######################################################################
# def processRule(rules, row, col,
# matrix, matrixAnswer, action="rows"):
# startcol = col
# startrow = row
# endcol = col
# endrow = row
# nextrow = row
# nextcol = col
# for element in rules:
# if action == "rows":
# row = endrow
# col = startcol
# elif action == "columns":
# row = startrow
# if endcol == 0:
# col = 0
# else:
# col = endcol+1
# # If the rule is a list then step through each element
# if isinstance(element,list):
# if action == "rows":
# tempAction = "columns"
# else:
# tempAction = "rows"
# (endrow, endcol) = processRule(element, row, col,
# matrix, matrixAnswer, tempAction)
# elif isinstance(element,dict):
# (endrow, endcol) = processDict(element, row, col,
# matrix, matrixAnswer, action)
# else:
# (endrow, endcol) = addData(element, row, col,
# matrix, matrixAnswer)
# if endrow > nextrow:
# nextrow = endrow
# if endcol > nextcol:
# nextcol = endcol
# return (nextrow, nextcol)
#
# def processDict(rules, row, col,
# matrix, matrixAnswer, action="rows"):
# startcol = col
# startrow = row
# nextrow = row
# nextcol = col
# for (key, value) in rules.items():
# if (key == "heading"):
# cell = MatrixElement(row,col,value, style="styleSubHeader")
# cell.merge(horizontal=1)
# try:
# matrix.addElement(cell)
# except Exception as msg:
# print msg
# return (row,col)
# endrow = row + 1
# endcol = col + 2
# elif (key == "rows") or (key == "columns"):
# (endrow, endcol) = processRule(value, row, col,
# matrix, matrixAnswer, action=key)
# else:
# ## Unknown key
# continue
# if action == "rows":
# row = startrow
# col = endcol + 1 # Add a blank column
# elif action == "columns":
# row = endrow
# col = startcol
# if endrow > nextrow:
# nextrow = endrow
# if endcol > nextcol:
# nextcol = endcol
# return (nextrow, nextcol)
#
# def addData(qstn, row, col, matrix, matrixAnswer):
# question = response.s3.survey_getQuestionFromCode(qstn, series_id)
# if question == {}:
# return (row,col)
# widgetObj = survey_question_type[question["type"]](question_id = question["qstn_id"])
# try:
# (endrow, endcol) = widgetObj.writeToMatrix(matrix,
# row,
# col,
# answerMatrix=matrixAnswer,
# langDict = langDict
# )
# except Exception as msg:
# print >> sys.stderr, msg
# return (row,col)
# if question["type"] == "Grid":
# matrix.boxRange(row, col, endrow-1, endcol-1)
# return (endrow, endcol)
# matrix = DataMatrix()
# matrixAnswers = DataMatrix()
# template = response.s3.survey_getTemplateFromSeries(series_id)
# series = response.s3.survey_getSeries(series_id)
# logo = os.path.join(request.folder,
# "static",
# "img",
# "logo",
# series.logo
# )
# if os.path.exists(logo) and os.path.isfile(logo):
# cell = MatrixElement(0,col,"", style=["styleText"])
# cell.merge(vertical=2)
# matrix.addElement(cell)
# col = 2
# row += 1
# else:
# logo = None
# title = "%s (%s)" % (series.name, template.name)
# title = survey_T(title, langDict)
# cell = MatrixElement(0, col, title, style="styleHeader")
# cell.merge(vertical=1, horizontal=len(title))
# matrix.addElement(cell)
# row += 2
# for section in sectionList:
# col = 0
# row += 1
# rules = layout[section["name"]]
# cell = MatrixElement(row, col, survey_T(section["name"], langDict),
# style="styleHeader")
# try:
# matrix.addElement(cell)
# except Exception as msg:
# print >> sys.stderr, msg
# row += 1
# startrow = row
# (row, col) = processRule(rules, row, col, matrix, matrixAnswers)
# matrix.boxRange(startrow, 0, row, col-1)
######################################################################
#
# Now take the matrix data type and generate a spreadsheet from it
#
######################################################################
import math
def wrapText(sheet, cell, style):
row = cell.row
col = cell.col
try:
text = unicode(cell.text)
except:
text = cell.text
width = 16
# Wrap text and calculate the row width and height
characters_in_cell = float(width-2)
twips_per_row = 255 #default row height for 10 point font
if cell.merged():
try:
sheet.write_merge(cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
text,
style
)
except Exception as msg:
print >> sys.stderr, msg
print >> sys.stderr, "row: %s + vert: %s, col: %s + horiz %s" % (cell.row, cell.mergeV, cell.col, cell.mergeH)
posn = "%s,%s"%(cell.row, cell.col)
if matrix.matrix[posn]:
print >> sys.stderr, matrix.matrix[posn]
rows = math.ceil((len(text) / characters_in_cell) / (1 + cell.mergeH))
else:
sheet.write(cell.row,
cell.col,
text,
style
)
rows = math.ceil(len(text) / characters_in_cell)
new_row_height = int(rows * twips_per_row)
new_col_width = width * COL_WIDTH_MULTIPLIER
if sheet.row(row).height < new_row_height:
sheet.row(row).height = new_row_height
if sheet.col(col).width < new_col_width:
sheet.col(col).width = new_col_width
def mergeStyles(listTemplate, styleList):
"""
Take a list of styles and return a single style object with
all the differences from a newly created object added to the
resultant style.
"""
if len(styleList) == 0:
finalStyle = xlwt.XFStyle()
elif len(styleList) == 1:
finalStyle = listTemplate[styleList[0]]
else:
zeroStyle = xlwt.XFStyle()
finalStyle = xlwt.XFStyle()
for i in range(0,len(styleList)):
finalStyle = mergeObjectDiff(finalStyle,
listTemplate[styleList[i]],
zeroStyle)
return finalStyle
def mergeObjectDiff(baseObj, newObj, zeroObj):
"""
function to copy all the elements in newObj that are different from
the zeroObj and place them in the baseObj
"""
elementList = newObj.__dict__
for (element, value) in elementList.items():
try:
baseObj.__dict__[element] = mergeObjectDiff(baseObj.__dict__[element],
value,
zeroObj.__dict__[element])
except:
if zeroObj.__dict__[element] != value:
baseObj.__dict__[element] = value
return baseObj
COL_WIDTH_MULTIPLIER = 240
book = xlwt.Workbook(encoding="utf-8")
output = StringIO()
protection = xlwt.Protection()
protection.cell_locked = 1
noProtection = xlwt.Protection()
noProtection.cell_locked = 0
borders = xlwt.Borders()
borders.left = xlwt.Borders.DOTTED
borders.right = xlwt.Borders.DOTTED
borders.top = xlwt.Borders.DOTTED
borders.bottom = xlwt.Borders.DOTTED
borderT1 = xlwt.Borders()
borderT1.top = xlwt.Borders.THIN
borderT2 = xlwt.Borders()
borderT2.top = xlwt.Borders.MEDIUM
borderL1 = xlwt.Borders()
borderL1.left = xlwt.Borders.THIN
borderL2 = xlwt.Borders()
borderL2.left = xlwt.Borders.MEDIUM
borderR1 = xlwt.Borders()
borderR1.right = xlwt.Borders.THIN
borderR2 = xlwt.Borders()
borderR2.right = xlwt.Borders.MEDIUM
borderB1 = xlwt.Borders()
borderB1.bottom = xlwt.Borders.THIN
borderB2 = xlwt.Borders()
borderB2.bottom = xlwt.Borders.MEDIUM
alignBase = xlwt.Alignment()
alignBase.horz = xlwt.Alignment.HORZ_LEFT
alignBase.vert = xlwt.Alignment.VERT_TOP
alignWrap = xlwt.Alignment()
alignWrap.horz = xlwt.Alignment.HORZ_LEFT
alignWrap.vert = xlwt.Alignment.VERT_TOP
alignWrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
shadedFill = xlwt.Pattern()
shadedFill.pattern = xlwt.Pattern.SOLID_PATTERN
shadedFill.pattern_fore_colour = 0x16 # 25% Grey
shadedFill.pattern_back_colour = 0x08 # Black
headingFill = xlwt.Pattern()
headingFill.pattern = xlwt.Pattern.SOLID_PATTERN
headingFill.pattern_fore_colour = 0x1F # ice_blue
headingFill.pattern_back_colour = 0x08 # Black
styleTitle = xlwt.XFStyle()
styleTitle.font.height = 0x0140 # 320 twips, 16 points
styleTitle.font.bold = True
styleTitle.alignment = alignBase
styleHeader = xlwt.XFStyle()
styleHeader.font.height = 0x00F0 # 240 twips, 12 points
styleHeader.font.bold = True
styleHeader.alignment = alignBase
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
styleSubHeader.alignment = alignWrap
styleSectionHeading = xlwt.XFStyle()
styleSectionHeading.font.bold = True
styleSectionHeading.alignment = alignWrap
styleSectionHeading.pattern = headingFill
styleHint = xlwt.XFStyle()
styleHint.protection = protection
styleHint.font.height = 160 # 160 twips, 8 points
styleHint.font.italic = True
styleHint.alignment = alignWrap
styleText = xlwt.XFStyle()
styleText.protection = protection
styleText.alignment = alignWrap
styleInstructions = xlwt.XFStyle()
styleInstructions.font.height = 0x00B4 # 180 twips, 9 points
styleInstructions.font.italic = True
styleInstructions.protection = protection
styleInstructions.alignment = alignWrap
styleBox = xlwt.XFStyle()
styleBox.borders = borders
styleBox.protection = noProtection
styleInput = xlwt.XFStyle()
styleInput.borders = borders
styleInput.protection = noProtection
styleInput.pattern = shadedFill
boxL1 = xlwt.XFStyle()
boxL1.borders = borderL1
boxL2 = xlwt.XFStyle()
boxL2.borders = borderL2
boxT1 = xlwt.XFStyle()
boxT1.borders = borderT1
boxT2 = xlwt.XFStyle()
boxT2.borders = borderT2
boxR1 = xlwt.XFStyle()
boxR1.borders = borderR1
boxR2 = xlwt.XFStyle()
boxR2.borders = borderR2
boxB1 = xlwt.XFStyle()
boxB1.borders = borderB1
boxB2 = xlwt.XFStyle()
boxB2.borders = borderB2
styleList = {}
styleList["styleTitle"] = styleTitle
styleList["styleHeader"] = styleHeader
styleList["styleSubHeader"] = styleSubHeader
styleList["styleSectionHeading"] = styleSectionHeading
styleList["styleHint"] = styleHint
styleList["styleText"] = styleText
styleList["styleInstructions"] = styleInstructions
styleList["styleInput"] = styleInput
styleList["boxL1"] = boxL1
styleList["boxL2"] = boxL2
styleList["boxT1"] = boxT1
styleList["boxT2"] = boxT2
styleList["boxR1"] = boxR1
styleList["boxR2"] = boxR2
styleList["boxB1"] = boxB1
styleList["boxB2"] = boxB2
sheet1 = book.add_sheet(T("Assessment"))
sheetA = book.add_sheet(T("Metadata"))
maxCol = 0
for cell in matrix.matrix.values():
if cell.col + cell.mergeH > maxCol:
maxCol = cell.col + cell.mergeH
if cell.joined():
continue
style = mergeStyles(styleList, cell.styleList)
if (style.alignment.wrap == style.alignment.WRAP_AT_RIGHT):
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
try:
wrapText(sheet1, cell, joinedStyle)
except:
pass
else:
if cell.merged():
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
try:
sheet1.write_merge(cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
unicode(cell.text),
joinedStyle
)
except Exception as msg:
print >> sys.stderr, msg
print >> sys.stderr, "row: %s + vert: %s, col: %s + horiz %s" % (cell.row, cell.mergeV, cell.col, cell.mergeH)
posn = "%s,%s"%(cell.row, cell.col)
if matrix.matrix[posn]:
print >> sys.stderr, matrix.matrix[posn]
else:
sheet1.write(cell.row,
cell.col,
unicode(cell.text),
style
)
if fixedWidth:
for col in range(maxCol+1):
sheet1.col(col).width = cellWidth
sheetA.write(0, 0, "Question Code")
sheetA.write(0, 1, "Response Count")
sheetA.write(0, 2, "Values")
sheetA.write(0, 3, "Cell Address")
for cell in matrixAnswers.matrix.values():
style = mergeStyles(styleList, cell.styleList)
sheetA.write(cell.row,
cell.col,
unicode(cell.text),
style
)
if logo != None:
sheet1.insert_bitmap(logo, 0, 0)
sheet1.protect = True
sheetA.protect = True
# for i in range(26):
# sheetA.col(i).width = 0
sheetA.write(0,
26,
unicode(T("Please do not remove this sheet")),
styleHeader
)
sheetA.col(26).width = 12000
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
seriesName = response.s3.survey_getSeriesName(series_id)
filename = "%s.xls" % seriesName
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def series_export_formatted():
"""
Download a Spreadsheet which can be filled-in offline & uploaded
@ToDo: rewrite as S3Method handler
"""
try:
series_id = request.args[0]
except:
output = s3_rest_controller(module, "series",
rheader = s3db.survey_series_rheader)
return output
# Load Model
table = s3db.survey_series
s3db.table("survey_complete")
vars = request.post_vars
series = db(table.id == series_id).select(table.name,
table.logo,
limitby = (0, 1)
).first()
if not series.logo:
logo = None
else:
if "Export_Spreadsheet" in vars:
ext = "bmp"
else:
ext = "png"
logo = os.path.join(request.folder,
"uploads",
"survey",
"logo",
"%s.%s" % (series.logo, ext)
)
if not os.path.exists(logo) or not os.path.isfile(logo):
logo = None
# Get the translation dictionary
lang_dict = dict()
lang = request.post_vars.get("translation_language", None)
if lang:
if lang == "Default":
lang_dict = dict()
else:
try:
from gluon.languages import read_dict
lang_filename = "applications/%s/uploads/survey/translations/%s.py" % \
(appname, lang)
lang_dict = read_dict(lang_filename)
except:
lang_dict = dict()
if "Export_Spreadsheet" in vars:
(matrix, matrix_answers) = series_prepare_matrix(series_id,
series,
logo,
lang_dict,
justified = True
)
output = series_export_spreadsheet(matrix,
matrix_answers,
logo,
)
filename = "%s.xls" % series.name
content_type = ".xls"
elif "Export_Word" in vars:
template = s3db.survey_getTemplateFromSeries(series_id)
template_id = template.id
title = "%s (%s)" % (series.name, template.name)
title = s3db.survey_T(title, lang_dict)
widget_list = s3db.survey_getAllWidgetsForTemplate(template_id)
output = series_export_word(widget_list, lang_dict, title, logo)
filename = "%s.rtf" % series.name
content_type = ".rtf"
else:
output = s3_rest_controller(module, "series",
rheader = s3db.survey_series_rheader)
return output
output.seek(0)
response.headers["Content-Type"] = contenttype(content_type)
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def series_export_formatted():
prefix = "survey"
resourcename = "series"
tablename = "%s_%s" % (prefix, resourcename)
s3mgr.load(tablename)
crud_strings = response.s3.crud_strings[tablename]
try:
import xlwt
except ImportError:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
######################################################################
#
# Get the data
# ============
# * The sections within the template
# * The layout rules for each question
######################################################################
# Check that the series_id has been passed in
if len(request.args) != 1:
output = s3_rest_controller(prefix,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
if "translationLanguage" in request.post_vars:
lang = request.post_vars.translationLanguage
if lang == "Default":
langDict = dict()
else:
try:
lang_fileName = "applications/%s/uploads/survey/translations/%s.py" % (
request.application, lang)
langDict = read_dict(lang_fileName)
except:
langDict = dict()
series_id = request.args[0]
sectionList = response.s3.survey_getAllSectionsForSeries(series_id)
layout = {}
for section in sectionList:
sectionName = section["name"]
rules = response.s3.survey_getQstnLayoutRules(section["template_id"],
section["section_id"])
layout[sectionName] = rules
######################################################################
#
# Store the questions into a matrix based on the layout and the space
# required for each question - for example an option question might
# need one row for each possible option, and if this is in a layout
# then the position needs to be recorded carefully...
#
######################################################################
def processRule(series_id,
rules,
row,
col,
matrix,
matrixAnswer,
action="rows"):
startcol = col
startrow = row
endcol = col
endrow = row
nextrow = row
nextcol = col
for element in rules:
if action == "rows":
row = endrow
col = startcol
elif action == "columns":
row = startrow
if endcol == 0:
col = 0
else:
col = endcol + 1
# If the rule is a list then step through each element
if isinstance(element, list):
if action == "rows":
tempAction = "columns"
else:
tempAction = "rows"
(endrow, endcol) = processRule(series_id, element, row, col,
matrix, matrixAnswer,
tempAction)
elif isinstance(element, dict):
(endrow, endcol) = processDict(series_id, element, row, col,
matrix, matrixAnswer, action)
else:
(endrow, endcol) = addData(element, row, col, matrix,
matrixAnswer)
if endrow > nextrow:
nextrow = endrow
if endcol > nextcol:
nextcol = endcol
return (nextrow, nextcol)
def processDict(series_id,
rules,
row,
col,
matrix,
matrixAnswer,
action="rows"):
startcol = col
startrow = row
nextrow = row
nextcol = col
for (key, value) in rules.items():
if (key == "heading"):
cell = MatrixElement(row, col, value, style="styleSubHeader")
cell.merge(horizontal=1)
try:
matrix.addElement(cell)
except Exception as msg:
print msg
return (row, col)
endrow = row + 1
endcol = col + 2
elif (key == "rows") or (key == "columns"):
(endrow, endcol) = processRule(series_id,
value,
row,
col,
matrix,
matrixAnswer,
action=key)
else:
## Unknown key
continue
if action == "rows":
row = startrow
col = endcol + 1 # Add a blank column
elif action == "columns":
row = endrow
col = startcol
if endrow > nextrow:
nextrow = endrow
if endcol > nextcol:
nextcol = endcol
return (nextrow, nextcol)
def addData(qstn, row, col, matrix, matrixAnswer):
question = response.s3.survey_getQuestionFromCode(qstn, series_id)
if question == {}:
return (row, col)
widgetObj = survey_question_type[question["type"]](
question_id=question["qstn_id"])
try:
(endrow,
endcol) = widgetObj.writeToMatrix(matrix,
row,
col,
answerMatrix=matrixAnswer,
langDict=langDict)
except Exception as msg:
print msg
return (row, col)
if question["type"] == "Grid":
matrix.boxRange(row, col, endrow - 1, endcol - 1)
return (endrow, endcol)
row = 0
col = 0
matrix = DataMatrix()
matrixAnswers = DataMatrix()
template = response.s3.survey_getTemplateFromSeries(series_id)
series = response.s3.survey_getSeries(series_id)
logo = os.path.join(request.folder, "static", "img", "logo", series.logo)
if os.path.exists(logo) and os.path.isfile(logo):
cell = MatrixElement(0, col, "", style=["styleText"])
cell.merge(vertical=2)
matrix.addElement(cell)
col = 2
row += 1
else:
logo = None
title = "%s (%s)" % (series.name, template.name)
title = survey_T(title, langDict)
cell = MatrixElement(0, col, title, style="styleHeader")
cell.merge(vertical=1, horizontal=4)
matrix.addElement(cell)
row += 2
for section in sectionList:
col = 0
row += 1
rules = layout[section["name"]]
cell = MatrixElement(row,
col,
survey_T(section["name"], langDict),
style="styleHeader")
try:
matrix.addElement(cell)
except Exception as msg:
print msg
row += 1
startrow = row
(row, col) = processRule(series_id, rules, row, col, matrix,
matrixAnswers)
matrix.boxRange(startrow, 0, row, col - 1)
######################################################################
#
# Now take the matrix data type and generate a spreadsheet from it
#
######################################################################
import math
def wrapText(sheet, cell, style):
row = cell.row
col = cell.col
try:
text = unicode(cell.text)
except:
text = cell.text
width = 16
# Wrap text and calculate the row width and height
characters_in_cell = float(width - 2)
twips_per_row = 255 #default row height for 10 point font
if cell.merged():
sheet.write_merge(cell.row, cell.row + cell.mergeV, cell.col,
cell.col + cell.mergeH, text, style)
rows = math.ceil(
(len(text) / characters_in_cell) / (1 + cell.mergeH))
else:
sheet.write(cell.row, cell.col, text, style)
rows = math.ceil(len(text) / characters_in_cell)
new_row_height = int(rows * twips_per_row)
new_col_width = width * COL_WIDTH_MULTIPLIER
if sheet.row(row).height < new_row_height:
sheet.row(row).height = new_row_height
if sheet.col(col).width < new_col_width:
sheet.col(col).width = new_col_width
def mergeStyles(listTemplate, styleList):
"""
Take a list of styles and return a single style object with
all the differences from a newly created object added to the
resultant style.
"""
if len(styleList) == 0:
finalStyle = xlwt.XFStyle()
elif len(styleList) == 1:
finalStyle = listTemplate[styleList[0]]
else:
zeroStyle = xlwt.XFStyle()
finalStyle = xlwt.XFStyle()
for i in range(0, len(styleList)):
finalStyle = mergeObjectDiff(finalStyle,
listTemplate[styleList[i]],
zeroStyle)
return finalStyle
def mergeObjectDiff(baseObj, newObj, zeroObj):
"""
function to copy all the elements in newObj that are different from
the zeroObj and place them in the baseObj
"""
elementList = newObj.__dict__
for (element, value) in elementList.items():
try:
baseObj.__dict__[element] = mergeObjectDiff(
baseObj.__dict__[element], value,
zeroObj.__dict__[element])
except:
if zeroObj.__dict__[element] != value:
baseObj.__dict__[element] = value
return baseObj
COL_WIDTH_MULTIPLIER = 240
book = xlwt.Workbook(encoding="utf-8")
output = StringIO()
protection = xlwt.Protection()
protection.cell_locked = 1
noProtection = xlwt.Protection()
noProtection.cell_locked = 0
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borderTL = xlwt.Borders()
borderTL.left = xlwt.Borders.DOUBLE
borderTL.top = xlwt.Borders.DOUBLE
borderT = xlwt.Borders()
borderT.top = xlwt.Borders.DOUBLE
borderL = xlwt.Borders()
borderL.left = xlwt.Borders.DOUBLE
borderTR = xlwt.Borders()
borderTR.right = xlwt.Borders.DOUBLE
borderTR.top = xlwt.Borders.DOUBLE
borderR = xlwt.Borders()
borderR.right = xlwt.Borders.DOUBLE
borderBL = xlwt.Borders()
borderBL.left = xlwt.Borders.DOUBLE
borderBL.bottom = xlwt.Borders.DOUBLE
borderB = xlwt.Borders()
borderB.bottom = xlwt.Borders.DOUBLE
borderBR = xlwt.Borders()
borderBR.right = xlwt.Borders.DOUBLE
borderBR.bottom = xlwt.Borders.DOUBLE
alignBase = xlwt.Alignment()
alignBase.horz = xlwt.Alignment.HORZ_LEFT
alignBase.vert = xlwt.Alignment.VERT_TOP
alignWrap = xlwt.Alignment()
alignWrap.horz = xlwt.Alignment.HORZ_LEFT
alignWrap.vert = xlwt.Alignment.VERT_TOP
alignWrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
shadedFill = xlwt.Pattern()
shadedFill.pattern = xlwt.Pattern.SOLID_PATTERN
shadedFill.pattern_fore_colour = 0x16 # 25% Grey
shadedFill.pattern_back_colour = 0x08 # Black
styleTitle = xlwt.XFStyle()
styleTitle.font.height = 0x0140 # 320 twips, 16 points
styleTitle.font.bold = True
styleTitle.alignment = alignBase
styleHeader = xlwt.XFStyle()
styleHeader.font.height = 0x00F0 # 240 twips, 12 points
styleHeader.font.bold = True
styleHeader.alignment = alignBase
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
styleSubHeader.alignment = alignWrap
styleText = xlwt.XFStyle()
styleText.protection = protection
styleText.alignment = alignWrap
styleInstructions = xlwt.XFStyle()
styleInstructions.font.height = 0x00B4 # 180 twips, 9 points
styleInstructions.font.italic = True
styleInstructions.protection = protection
styleInstructions.alignment = alignWrap
styleBox = xlwt.XFStyle()
styleBox.borders = borders
styleBox.protection = noProtection
styleInput = xlwt.XFStyle()
styleInput.borders = borders
styleInput.protection = noProtection
styleInput.pattern = shadedFill
boxL = xlwt.XFStyle()
boxL.borders = borderL
boxT = xlwt.XFStyle()
boxT.borders = borderT
boxR = xlwt.XFStyle()
boxR.borders = borderR
boxB = xlwt.XFStyle()
boxB.borders = borderB
styleList = {}
styleList["styleTitle"] = styleTitle
styleList["styleHeader"] = styleHeader
styleList["styleSubHeader"] = styleSubHeader
styleList["styleText"] = styleText
styleList["styleInstructions"] = styleInstructions
styleList["styleInput"] = styleInput
styleList["boxL"] = boxL
styleList["boxT"] = boxT
styleList["boxR"] = boxR
styleList["boxB"] = boxB
sheet1 = book.add_sheet(T("Assessment"))
sheetA = book.add_sheet(T("Metadata"))
for cell in matrix.matrix.values():
if cell.joined():
continue
style = mergeStyles(styleList, cell.styleList)
if (style.alignment.wrap == style.alignment.WRAP_AT_RIGHT):
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
try:
wrapText(sheet1, cell, joinedStyle)
except:
pass
else:
if cell.merged():
# get all the styles from the joined cells
# and merge these styles in.
joinedStyles = matrix.joinedElementStyles(cell)
joinedStyle = mergeStyles(styleList, joinedStyles)
sheet1.write_merge(cell.row, cell.row + cell.mergeV,
cell.col, cell.col + cell.mergeH,
unicode(cell.text), joinedStyle)
else:
sheet1.write(cell.row, cell.col, unicode(cell.text), style)
sheetA.write(0, 0, "Question Code")
sheetA.write(0, 1, "Response Count")
sheetA.write(0, 2, "Values")
sheetA.write(0, 3, "Cell Address")
for cell in matrixAnswers.matrix.values():
style = mergeStyles(styleList, cell.styleList)
sheetA.write(cell.row, cell.col, unicode(cell.text), style)
if logo != None:
sheet1.insert_bitmap(logo, 0, 0)
sheet1.protect = True
sheetA.protect = True
for i in range(26):
sheetA.col(i).width = 0
sheetA.write(0, 26, unicode(T("Please do not remove this sheet")),
styleHeader)
sheetA.col(26).width = 12000
book.save(output)
output.seek(0)
response.headers["Content-Type"] = contenttype(".xls")
seriesName = response.s3.survey_getSeriesName(series_id)
filename = "%s.xls" % seriesName
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
