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()