示例#1
0
文件: survey.py 项目: flavour/cert
 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)
示例#2
0
 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)
示例#3
0
文件: survey.py 项目: flavour/cert
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()
示例#4
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()