def protection(cell_locked=1, formula_hidden=0):
'''
@param cell_locked: 锁定
@param formula_hidden: formula_hidden
'''
pron = xlwt.Protection()
pron.cell_locked = cell_locked
pron.formula_hidden = formula_hidden
return pron
def series_export_spreadsheet(matrix, matrix_answers, logo):
"""
Now take the matrix data type and generate a spreadsheet from it
"""
try:
import xlwt
except ImportError:
response.error = T(
"xlwt not installed, so cannot export as a Spreadsheet")
output = s3_rest_controller("survey",
"survey_series",
rheader=s3db.survey_series_rheader)
return output
import math
from io import BytesIO
# -------------------------------------------------------------------------
def wrap_text(sheet, cell, style):
row = cell.row
col = cell.col
try:
text = s3_str(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:
log = current.log
log.error(msg)
log.debug("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]:
log.debug(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 merge_styles(list_template, style_list):
"""
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(style_list) == 0:
final_style = xlwt.XFStyle()
elif len(style_list) == 1:
final_style = list_template[style_list[0]]
else:
zero_style = xlwt.XFStyle()
final_style = xlwt.XFStyle()
for i in range(0, len(style_list)):
final_style = merge_object_diff(final_style,
list_template[style_list[i]],
zero_style)
return final_style
# -------------------------------------------------------------------------
def merge_object_diff(base_obj, new_obj, zero_obj):
"""
Function to copy all the elements in new_obj that are different from
the zero_obj and place them in the base_obj
"""
element_list = new_obj.__dict__
for (element, value) in element_list.items():
try:
base_obj.__dict__[element] = merge_object_diff(
base_obj.__dict__[element], value,
zero_obj.__dict__[element])
except:
if zero_obj.__dict__[element] != value:
base_obj.__dict__[element] = value
return base_obj
COL_WIDTH_MULTIPLIER = 240
book = xlwt.Workbook(encoding="utf-8")
output = BytesIO()
protection = xlwt.Protection()
protection.cell_locked = 1
no_protection = xlwt.Protection()
no_protection.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
border_t1 = xlwt.Borders()
border_t1.top = xlwt.Borders.THIN
border_t2 = xlwt.Borders()
border_t2.top = xlwt.Borders.MEDIUM
border_l1 = xlwt.Borders()
border_l1.left = xlwt.Borders.THIN
border_l2 = xlwt.Borders()
border_l2.left = xlwt.Borders.MEDIUM
border_r1 = xlwt.Borders()
border_r1.right = xlwt.Borders.THIN
border_r2 = xlwt.Borders()
border_r2.right = xlwt.Borders.MEDIUM
border_b1 = xlwt.Borders()
border_b1.bottom = xlwt.Borders.THIN
border_b2 = xlwt.Borders()
border_b2.bottom = xlwt.Borders.MEDIUM
align_base = xlwt.Alignment()
align_base.horz = xlwt.Alignment.HORZ_LEFT
align_base.vert = xlwt.Alignment.VERT_TOP
align_wrap = xlwt.Alignment()
align_wrap.horz = xlwt.Alignment.HORZ_LEFT
align_wrap.vert = xlwt.Alignment.VERT_TOP
align_wrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
shaded_fill = xlwt.Pattern()
shaded_fill.pattern = xlwt.Pattern.SOLID_PATTERN
shaded_fill.pattern_fore_colour = 0x16 # 25% Grey
shaded_fill.pattern_back_colour = 0x08 # Black
heading_fill = xlwt.Pattern()
heading_fill.pattern = xlwt.Pattern.SOLID_PATTERN
heading_fill.pattern_fore_colour = 0x1F # ice_blue
heading_fill.pattern_back_colour = 0x08 # Black
style_title = xlwt.XFStyle()
style_title.font.height = 0x0140 # 320 twips, 16 points
style_title.font.bold = True
style_title.alignment = align_base
style_header = xlwt.XFStyle()
style_header.font.height = 0x00F0 # 240 twips, 12 points
style_header.font.bold = True
style_header.alignment = align_base
style_sub_header = xlwt.XFStyle()
style_sub_header.font.bold = True
style_sub_header.alignment = align_wrap
style_section_heading = xlwt.XFStyle()
style_section_heading.font.bold = True
style_section_heading.alignment = align_wrap
style_section_heading.pattern = heading_fill
style_hint = xlwt.XFStyle()
style_hint.protection = protection
style_hint.font.height = 160 # 160 twips, 8 points
style_hint.font.italic = True
style_hint.alignment = align_wrap
style_text = xlwt.XFStyle()
style_text.protection = protection
style_text.alignment = align_wrap
style_instructions = xlwt.XFStyle()
style_instructions.font.height = 0x00B4 # 180 twips, 9 points
style_instructions.font.italic = True
style_instructions.protection = protection
style_instructions.alignment = align_wrap
style_box = xlwt.XFStyle()
style_box.borders = borders
style_box.protection = no_protection
style_input = xlwt.XFStyle()
style_input.borders = borders
style_input.protection = no_protection
style_input.pattern = shaded_fill
box_l1 = xlwt.XFStyle()
box_l1.borders = border_l1
box_l2 = xlwt.XFStyle()
box_l2.borders = border_l2
box_t1 = xlwt.XFStyle()
box_t1.borders = border_t1
box_t2 = xlwt.XFStyle()
box_t2.borders = border_t2
box_r1 = xlwt.XFStyle()
box_r1.borders = border_r1
box_r2 = xlwt.XFStyle()
box_r2.borders = border_r2
box_b1 = xlwt.XFStyle()
box_b1.borders = border_b1
box_b2 = xlwt.XFStyle()
box_b2.borders = border_b2
style_list = {}
style_list["styleTitle"] = style_title
style_list["styleHeader"] = style_header
style_list["styleSubHeader"] = style_sub_header
style_list["styleSectionHeading"] = style_section_heading
style_list["styleHint"] = style_hint
style_list["styleText"] = style_text
style_list["styleInstructions"] = style_instructions
style_list["styleInput"] = style_input
style_list["boxL1"] = box_l1
style_list["boxL2"] = box_l2
style_list["boxT1"] = box_t1
style_list["boxT2"] = box_t2
style_list["boxR1"] = box_r1
style_list["boxR2"] = box_r2
style_list["boxB1"] = box_b1
style_list["boxB2"] = box_b2
sheet1 = book.add_sheet(T("Assessment"))
sheet2 = book.add_sheet(T("Metadata"))
max_col = 0
for cell in matrix.matrix.values():
if cell.col + cell.mergeH > 255:
current.log.warning("Cell (%s,%s) - (%s,%s) ignored" % \
(cell.col, cell.row, cell.col + cell.mergeH, cell.row + cell.mergeV))
continue
if cell.col + cell.mergeH > max_col:
max_col = cell.col + cell.mergeH
if cell.joined():
continue
style = merge_styles(style_list, cell.styleList)
if (style.alignment.wrap == style.alignment.WRAP_AT_RIGHT):
# get all the styles from the joined cells
# and merge these styles in.
joined_styles = matrix.joinedElementStyles(cell)
joined_style = merge_styles(style_list, joined_styles)
try:
wrap_text(sheet1, cell, joined_style)
except:
pass
else:
if cell.merged():
# get all the styles from the joined cells
# and merge these styles in.
joined_styles = matrix.joinedElementStyles(cell)
joined_style = merge_styles(style_list, joined_styles)
try:
sheet1.write_merge(
cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
s3_str(cell.text),
joined_style,
)
except Exception as msg:
log = current.log
log.error(msg)
log.debug("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]:
log.debug(matrix.matrix[posn])
else:
sheet1.write(
cell.row,
cell.col,
s3_str(cell.text),
style,
)
CELL_WIDTH = 480 # approximately 2 characters
if max_col > 255:
max_col = 255
for col in range(max_col + 1):
sheet1.col(col).width = CELL_WIDTH
sheet2.write(0, 0, "Question Code")
sheet2.write(0, 1, "Response Count")
sheet2.write(0, 2, "Values")
sheet2.write(0, 3, "Cell Address")
for cell in matrix_answers.matrix.values():
style = merge_styles(style_list, cell.styleList)
sheet2.write(
cell.row,
cell.col,
s3_str(cell.text),
style,
)
if logo != None:
sheet1.insert_bitmap(logo, 0, 0)
sheet1.protect = True
sheet2.protect = True
for i in range(26):
sheet2.col(i).width = 0
sheet2.write(
0,
26,
s3_str(T("Please do not remove this sheet")),
style_header,
)
sheet2.col(26).width = 12000
book.save(output)
return output
def 创建Excel保护(单元格锁定=1, 公式隐藏=0):
protection = xlwt.Protection()
protection.cell_locked = 单元格锁定
protection.formula_hidden = 公式隐藏
return protection
def series_export_spreadsheet(matrix, matrixAnswers, logo):
######################################################################
#
# Now take the matrix data type and generate a spreadsheet from it
#
######################################################################
import math
try:
import xlwt
except ImportError:
output = s3_rest_controller(module,
resourcename,
rheader=response.s3.survey_series_rheader)
return output
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 > 255:
print >> sys.stderr, "Cell (%s,%s) - (%s,%s) ignored" % (cell.col, cell.row, cell.col + cell.mergeH, cell.row + cell.mergeV)
continue
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
)
cellWidth = 480 # approximately 2 characters
if maxCol > 255:
maxCol = 255
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)
return output
def make_edit_style():
style = make_default_style()
protection = xlwt.Protection()
protection.cell_locked = False
style.protection = protection
return style
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()
