Esempio n. 1
0
 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)
Esempio n. 4
0
 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)
Esempio n. 5
0
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)
Esempio n. 6
0
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
Esempio n. 8
0
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
Esempio n. 9
0
    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)
Esempio n. 10
0
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()
Esempio n. 11
0
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)
Esempio n. 12
0
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)
Esempio n. 13
0
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()
Esempio n. 14
0
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()
Esempio n. 15
0
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()
Esempio n. 16
0
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()
Esempio n. 17
0
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()
Esempio n. 18
0
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()
Esempio n. 19
0
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('&nbsp;'), 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)
Esempio n. 20
0
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()
Esempio n. 21
0
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('&nbsp;'),
                                   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)
Esempio n. 22
0
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()
Esempio n. 23
0
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()
Esempio n. 24
0
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()