def show_color(sheet):
    colNum = 6
    width = 5000
    height = 500
    colors = ['aqua','black','blue','blue_gray','bright_green','brown','coral','cyan_ega','dark_blue','dark_blue_ega','dark_green','dark_green_ega','dark_purple','dark_red',
            'dark_red_ega','dark_teal','dark_yellow','gold','gray_ega','gray25','gray40','gray50','gray80','green','ice_blue','indigo','ivory','lavender',
            'light_blue','light_green','light_orange','light_turquoise','light_yellow','lime','magenta_ega','ocean_blue','olive_ega','olive_green','orange','pale_blue','periwinkle','pink',
            'plum','purple_ega','red','rose','sea_green','silver_ega','sky_blue','tan','teal','teal_ega','turquoise','violet','white','yellow']

    for colorIndex in range(len(colors)):
            rowIndex = colorIndex / colNum
            colIndex = colorIndex - rowIndex*colNum
            sheet.col(colIndex).width = width
            sheet.row(rowIndex).set_style(easyxf('font:height %s;'%height)) 
            color = colors[colorIndex]
            whiteStyle = easyxf('pattern:pattern solid, fore_colour %s;'
                                    'align: vertical center, horizontal center;'
                                    'font: bold true, colour white;' % color)
            blackStyle = easyxf('pattern:pattern solid, fore_colour %s;'
                                    'align: vertical center, horizontal center;'
                                    'font: bold true, colour black;' % color)


            if color == 'black':
                    sheet.write(rowIndex, colIndex, color, style = whiteStyle)
            else:
                    sheet.write(rowIndex, colIndex, color, style = blackStyle)

def visitor_excel(request):
    book = xlwt.Workbook(encoding='utf8')
    sheet = book.add_sheet('untitled')

    default_style = xlwt.Style.default_style
    datetime_style = xlwt.easyxf(num_format_str='mm/dd/yyyy hh:mm')
    date_style = xlwt.easyxf(num_format_str='mm/dd/yyyy')

    values_list = Visitor.objects.all().values_list()

    for row, rowdata in enumerate(values_list):
        for col, val in enumerate(rowdata):
            if isinstance(val, datetime):
                style = datetime_style
                val=val.replace(tzinfo=None)
            elif isinstance(val, date):
                style = date_style
            else:
                style = default_style

            sheet.write(row, col, val, style=style)

    response = HttpResponse(mimetype='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=visitors.xls'
    book.save(response)
    return response

def export_xlwt(model, values_list, fields):
    import xlwt
    from datetime import datetime, date
    modelname = model._meta.verbose_name_plural.lower()
    book = xlwt.Workbook(encoding='utf8')
    sheet = book.add_sheet(modelname)

    default_style = xlwt.Style.default_style
    datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
    date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')

    for j, f in enumerate(fields):
        sheet.write(0, j, fields[j])

    for row, rowdata in enumerate(values_list):
        for col, val in enumerate(rowdata):
            if isinstance(val, datetime):
                style = datetime_style
            elif isinstance(val, date):
                style = date_style
            else:
                style = default_style

            sheet.write(row + 1, col, val, style=style)

    response = HttpResponse(mimetype='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' % modelname
    book.save(response)
    return response

def add_scores_to_sheet(sheet, imported_rec):
    sheet.write_merge(0, 0, 0, 1, 'Oil Category Scores')
    sheet.write(1, 0, 'Category', xlwt.easyxf('font: underline on;'))
    sheet.write(1, 1, 'Score', xlwt.easyxf('font: underline on;'))
    col = sheet.col(0)
    col.width = 220 * 27

    tests = (score_demographics,
             score_api,
             score_pour_point,
             score_flash_point,
             score_sara_fractions,
             score_emulsion_constants,
             score_interfacial_tensions,
             score_densities,
             score_viscosities,
             score_cuts,
             score_toxicities)

    idx = 0
    for idx, t in enumerate(tests):
        sheet.write(2 + idx, 0, t.__name__)
        sheet.write(2 + idx, 1, t(imported_rec))

    v_offset = 2 + idx + 2
    sheet.write(v_offset, 0, 'Overall Score')
    sheet.write(v_offset, 1,
                xlwt.Formula('AVERAGE($B$3:$B${0})'.format(v_offset)))

    def add_to_excel(self, state):
        """ adds content to the excel file based on the specific statistic type """
        import xlwt
        answers = state['answers']
        ws = state['ws']
        current_row = state['current_row']
        translator = self.getSite().getPortalTranslations()
        total, unanswered, per_row_and_choice = self.calculate(self.question, answers)

        #define Excel styles
        header_style = xlwt.easyxf('font: bold on; align: vert centre')
        normal_style = xlwt.easyxf('align: vert centre')

        #write cell elements similarly to the zpt-->html output
        ws.write(current_row, 1, self.question.title, header_style)
        current_row += 1
        current_col = 2
        for choice in self.question.choices:
            ws.write(current_row, current_col, choice, header_style)
            current_col += 1
        ws.write(current_row, current_col, translator('Not answered'), header_style)
        current_row += 1

        for row in self.question.rows:
            r = self.question.rows.index(row)
            ws.write(current_row, 1, row, header_style)
            current_col = 2
            for statistics in per_row_and_choice[r]:
                ws.write(current_row, current_col, '%u (%.2f%%)'
                    % (statistics[0], round(statistics[1], 2)), normal_style)
                current_col += 1
            ws.write(current_row, current_col, '%u (%.2f%%)'
                % (unanswered[r][0], round(unanswered[r][1], 2)), normal_style)
            current_row += 1
        state['current_row'] = current_row

def write_file(result_list, deal_date, company_name, filename):
    '''
    given a list, put it into excel file.
    deal_date specifies a string which will be rendered as bold
    company_name and filename are self-explanatory
    '''
    w = Workbook()
    sheet = w.add_sheet(company_name)
    row = 2
    boldfont = easyxf(strg_to_parse='font: bold on')
    normalfont = easyxf(strg_to_parse='')
    sheet.write(0, 0, company_name)
    sheet.write(1, 0, 'Date')
    sheet.write(1, 1, 'Open')
    sheet.write(1, 2, 'Close')

    for line in result_list:
        elements = line.decode('utf8').split(',')
        date_string = elements[0]
        open_value = elements[1]
        close_value = elements[4]
        if datetime.strptime(date_string, '%Y-%m-%d') == deal_date:
            style = boldfont
        else:
            style = normalfont
        sheet.write(row, 0, date_string, style)
        sheet.write(row, 1, open_value, style)
        sheet.write(row, 2, close_value, style)
        row += 1

        print(date_string, open_value, close_value)
    w.save(filename)

    def generate_custom_excel_output(self, fields, comments):

        normal_style = xlwt.easyxf('align: wrap on, horiz left, vert top;')
        header_style = xlwt.easyxf('font: bold on; align: wrap on, horiz left;')

        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('Sheet 1')
        row = 0
        for col in range(len(fields)):
            ws.row(row).set_cell_text(col, fields[col][0], header_style)

        ws.col(0).width = 3000
        ws.col(1).width = 20000
        ws.col(2).width = 7500
        ws.col(3).width = 5000
        for item in comments:
            row += 1
            for col in range(len(fields)):
                ws.row(row).set_cell_text(col, fields[col][1](item),
                                          normal_style)
            row += 1
            ws.row(row).set_cell_text(0, 'EEA Comments' , header_style)
        output = StringIO()
        wb.save(output)

        return output.getvalue()

 def predict(self,wave):
     style = xl.easyxf( get_style(height=210,color='green',bold=True,horizontal='center') )
     if not wave.has_subwaves:
         self.sheet.row(self.row_no).write(3,'No subwaves, cannot predict waves for wave',style)
         return 
     row_title = self.sheet.row(self.row_no)
     row_title.write(3, 'Predict p3 and p5 for {}'.format(wave.name), style)
     self.nextline()
     p31,p51,p52,p53,p54,p54s,fib31,fib51,fib52,fib53,fib54,fib54s = get_prediction(wave)
     style_n = xl.easyxf( get_style(bold=False,horizontal='center'))# + 'borders: bottom thin;')
     style_hl = xl.easyxf( get_style(bold=True,horizontal='center'))# + 'borders: bottom thin;')
     def write(title1,title2,price1,price2,price3,fib,style=style_n):
         row = self.sheet.row(self.row_no)
         row.write(1,title1,style); row.write(2,title2,style); row.write(3,price1,style); row.write(4,price2,style)
         row.write(5,price3,style); row.write(6,fib,style)
         self.nextline()
     style_20 = xl.easyxf( get_style(bold=True,horizontal='center') + 'borders: bottom thin;')
     #write('p3','2.0*i',     p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),(p31-wave.ii.end)/wave.i.size,style=style_20)
     #write('p5','2.382*i',   p51,p51-wave.start,compute_percent(wave.start,p51),(p51-wave.i.start)/wave.i.size)
     #write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),int((p52-wave.i.start)/wave.i.size) )
     #write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),int((p53-wave.i.start)/wave.i.size),style=style_hl)
     #write('p5','p4+1.0*i',  p54,p54-wave.start,compute_percent(wave.start,p54),int((p54-wave.i.start)/wave.i.size))
     #write('p5','p4+0.618*iii',  p54s,p54s-wave.start,compute_percent(wave.start,p54s),int((p54s-wave.i.start)/wave.i.size) )
     write('p3','2.0*i',     p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),fib31,style=style_20)
     write('p5','2.382*i',   p51,p51-wave.start,compute_percent(wave.start,p51),fib51)
     write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),fib52)
     write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),fib53,style=style_hl)
     write('p5','p4+1.0*i',  p54,p54-wave.start,compute_percent(wave.start,p54),fib54)
     write('p5','p4+0.618*iii',  p54s,p54s-wave.start,compute_percent(wave.start,p54s),fib54s)

    def from_data(self, fields, rows, file_address):
        if file_address:
            bk = xlrd.open_workbook(file_address, formatting_info=True)
            workbook = xlutils.copy.copy(bk)
            worksheet = workbook.get_sheet(0)
            for i, fieldname in enumerate(fields):
                self.setOutCell(worksheet, 0, i, fieldname)
            for row, row_vals in enumerate(rows):
                for col, col_value in enumerate(row_vals):
                    if isinstance(col_value, basestring):
                        col_value = re.sub("\r", " ", col_value)
                    self.setOutCell(worksheet, col, row + 1, col_value)
        else:
            workbook = xlwt.Workbook()
            worksheet = workbook.add_sheet('Sheet 1')
            base_style = xlwt.easyxf('align: wrap yes')
            date_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD')
            datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS')

            for row_index, row in enumerate(rows):
                for cell_index, cell_value in enumerate(row):
                    cell_style = base_style
                    if isinstance(cell_value, basestring):
                        cell_value = re.sub("\r", " ", cell_value)
                    elif isinstance(cell_value, datetime.datetime):
                        cell_style = datetime_style
                    elif isinstance(cell_value, datetime.date):
                        cell_style = date_style
                    worksheet.write(row_index + 1, cell_index, cell_value, cell_style)
        fp_currency = StringIO.StringIO()
        workbook.save(fp_currency)
        fp_currency.seek(0)
        data = fp_currency.read()
        fp_currency.close()
        return data

def generate_xls(Con):
    wb = Workbook()
    ws = wb.add_sheet(sheetname='Order Results')
    headerStyle = easyxf('font: bold True')
    wrapStyle = easyxf('alignment: wrap True')
    ws.write(0, 0, 'Date', headerStyle)
    ws.write(0, 1, 'Particulars', headerStyle)
    ws.write(0, 2, 'Amount', headerStyle)
    ws.write(0, 3, 'Revenue', headerStyle)
    max_width_col0 = 0
    max_width_col1 = 0
    i = 1
    for order in Con.Orders:
        ws.write(i, 0, order.orderDate)
        ws.write(i, 1, order.Particulars, wrapStyle)
        ws.write(i, 2, float(order.Total_Amount))
        ws.write(i, 3, float(order.Revenue_Amount))
        for s in order.Particulars.split('\n'):
            if max_width_col1 < len(s):
                max_width_col1 = len(s)

        if max_width_col0 < len(str(order.orderDate)):
            max_width_col0 = len(str(order.orderDate))
        i += 1

    ws.col(1).width = 256 * max_width_col1 + 2
    ws.col(0).width = 256 * max_width_col0 + 20
    ws.write(i, 1, 'Total Amount', headerStyle)
    ws.write(i, 2, Con.Total_Amount, headerStyle)
    ws.write(i, 3, Con.Revenue_Amount, headerStyle)
    filename = Con.Username + '_' + Con.Label + '_'
    filename += Con.FromDate.strftime('%Y%m%d') + '_'
    filename += Con.ToDate.strftime('%Y%m%d') + '.xls'
    wb.save(config.get_main_dir() + '/Output/' + filename)

def check_attr_diff():
    attributes = ["datanode_java_heapsize","dfs_datanode_failed_volumes_tolerated","dfs_namenode_handler_count","hbase_hregion_max_filesize","hbase_master_java_heapsize","hbase_regionserver_java_heapsize","io_sort_mb","jobtracker_java_heapsize","mapred_child_java_opts_max_heap","mapred_job_tracker_handler_count","mapred_submit_replication","mapred_tasktracker_map_tasks_maximum","mapred_tasktracker_reduce_tasks_maximum","maxSessionTimeout","namenode_java_heapsize","override_mapred_child_java_opts_max_heap","secondary_namenode_java_heapsize","task_tracker_java_heapsize","override_mapred_compress_map_output","override_mapred_map_tasks_speculative_execution","override_mapred_output_compress","override_mapred_reduce_tasks_speculative_execution"]
    
    START_ROW = 1
    ATTR_COL = 2
    VALUE_COL = 3
    w_sheets = [0]*10
    rb = open_workbook("CombinedClusterConfigs.xls",formatting_info=True)
    styles = Styles(rb)
    r_sheets = rb.sheets() # read only copy of the sheets
    print r_sheets
    wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
    style1 = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
    style2 = xlwt.easyxf('pattern: pattern solid, fore_colour orange;')
    style3 = xlwt.easyxf('pattern: pattern solid, fore_colour blue;')
    for i in range(0,rb.nsheets):
        last_attrib = 0
        last_value = 0
        w_sheets[i] = wb.get_sheet(i) # the sheets to write to within the writable copy
        for row_index in range(START_ROW, r_sheets[i].nrows):
            current_attrib = r_sheets[i].cell(row_index, ATTR_COL).value
            current_value = r_sheets[i].cell(row_index, VALUE_COL).value
            #print w_sheets[i]
            if current_attrib == last_attrib:
                if current_value <> last_value:
                    w_sheets[i].write(row_index, VALUE_COL, r_sheets[i].cell(row_index, VALUE_COL).value, style1)
                    continue
            for attribute in attributes:
                if r_sheets[i].cell(row_index, ATTR_COL).value == attribute:
                    last_attrib = r_sheets[i].cell(row_index, ATTR_COL).value
                    last_value = r_sheets[i].cell(row_index, VALUE_COL).value
                    break
    wb.save("CombinedClusterConfigs.xls")

def styleUpdate(target):
    rb = open_workbook(target)
    wb = copy(rb)

    #style
    headline = xlwt.easyxf('pattern: pattern solid, fore_color yellow, back_color orange; font: height 260, bold true, color black; align: horizontal center, vertical center, wrap true; border: top thick, bottom double')
    plain = xlwt.easyxf('font: height 200, color black; align: horizontal center, vertical center; border: top thin, bottom thin, right thin, left thin')
    date_format = xlwt.XFStyle()
    date_format.num_format_str = 'yyyy-mm-dd'

    sheetNumber = len(rb.sheet_names())
    #loop through the sheet
    for num in range(sheetNumber):
        sheet = wb.get_sheet(num)

        #looping through columns
        for colNum in range(1,20):
            sheet.col(0).width = 256*4
            sheet.write(0,colNum,rb.sheet_by_index(num).cell(0,colNum).value,style=headline)
            sheet.col(colNum).width = 256*15
            for rowNum in range(1,len(sheet.rows)):
                sheet.write(rowNum,colNum,rb.sheet_by_index(num).cell(rowNum,colNum).value,style=plain)

        #write the content with style

        sheet.row(0).height_mismatch = True
        sheet.row(0).height = 256*3

    wb.save(target)

 def print_header_data(self, ws, _p, data, row_position, xlwt, _xs, initial_balance_text):   
     cell_format = _xs['borders_all'] + _xs['wrap'] + _xs['top']
     cell_style = xlwt.easyxf(cell_format)
     cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
     c_specs = [
         ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-', None, cell_style_center),
         ('af', 1, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All'), None, cell_style_center),
     ]
     df = _('From') + ': '
     if _p.filter_form(data) == 'filter_date':
         df += _p.start_date if _p.start_date else u'' 
     else:
         df += _p.start_period.name if _p.start_period else u''
     df += ' ' + _('\nTo') + ': '
     if _p.filter_form(data) == 'filter_date':
         df += _p.stop_date if _p.stop_date else u''
     else:
         df += _p.stop_period.name if _p.stop_period else u''
     c_specs += [
         ('df', 1, 0, 'text', df, None, cell_style_center),
         ('tm', 1, 0, 'text', _p.display_partner_account(data), None, cell_style_center),
         ('pf', 1, 0, 'text', _p.display_target_move(data), None, cell_style_center),
         ('ib', 1, 0, 'text', initial_balance_text[_p.initial_balance_mode], None, cell_style_center),
         ('coa', 1, 0, 'text', _p.chart_account.name, None, cell_style_center),
     ]       
     row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
     row_position = self.xls_write_row(ws, row_position, row_data, row_style=cell_style)
     return row_position

    def generate_excel_output(self, fields, comments):

        header_style = xlwt.easyxf('font: bold on; align: horiz left;')
        normal_style = xlwt.easyxf('align: horiz left, vert top;')

        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('Sheet 1')

        ws.row(0).set_cell_text(0, 'Consultation deadline', header_style)
        ws.row(0).set_cell_text(1, (self.end_date + 1).strftime('%Y/%m/%d %H:%M'),
                                normal_style)
        ws.row(1).set_cell_text(0, 'Date of export', header_style)
        ws.row(1).set_cell_text(1, DateTime().strftime('%Y/%m/%d %H:%M'),
                                normal_style)

        row = 2
        for col in range(len(fields)):
            ws.row(row).set_cell_text(col, fields[col][0], header_style)

        for item in comments:
            row += 1
            for col in range(len(fields)):
                ws.row(row).set_cell_text(col, fields[col][1](item),
                                          normal_style)
        output = StringIO()
        wb.save(output)

        return output.getvalue()

 def __init__(self, name, table,
              rml=False, parser=False, header=True, store=False):
     super(AccountBalanceReportingXls,
           self).__init__(name, table, rml, parser, header, store)
     # Cell Styles
     _xs = self.xls_styles
     # header
     rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
     self.rh_cell_style = xlwt.easyxf(rh_cell_format)
     self.rh_cell_style_center = xlwt.easyxf(
         rh_cell_format + _xs['center'])
     self.rh_cell_style_right = xlwt.easyxf(
         rh_cell_format + _xs['right'])
     # lines
     aml_cell_format = _xs['borders_all']
     self.aml_cell_style = xlwt.easyxf(aml_cell_format)
     self.aml_cell_style_center = xlwt.easyxf(
         aml_cell_format + _xs['center'])
     self.aml_cell_style_date = xlwt.easyxf(
         aml_cell_format + _xs['left'],
         num_format_str=report_xls.date_format)
     self.aml_cell_style_decimal = xlwt.easyxf(
         aml_cell_format + _xs['right'],
         num_format_str=report_xls.decimal_format)
     # totals
     rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
     self.rt_cell_style = xlwt.easyxf(rt_cell_format)
     self.rt_cell_style_right = xlwt.easyxf(
         rt_cell_format + _xs['right'])
     self.rt_cell_style_decimal = xlwt.easyxf(
         rt_cell_format + _xs['right'],
         num_format_str=report_xls.decimal_format)

def report_group_summary_monthly_excel(request,year,month):
    book = xlwt.Workbook(encoding='utf8')
    sheet = book.add_sheet('untitled')

    default_style = xlwt.Style.default_style
    datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
    date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')
    row = 1

    student_list = Student.objects.all()
    for student in student_list:
        row=row+1
        col=0
        sheet.write(row, col, "%s %s"  % (student.people.name_th,student.people.lastname_th) , style=default_style)
        activity_group_list =  ActivityGroup.objects.filter()
        for activity_group in activity_group_list:
            activity_list = Activity.objects.filter(group=activity_group,date__month=month,date__year=year)
            for activity in activity_list:
                col=col+1
                try :
                    sheet.write(row, col, ("%s" % ActivityReport.objects.get(activity=activity,attendance=student,version=activity.version).transaction_status),style=default_style)

                except ActivityReport.DoesNotExist:
                    sheet.write(row, col, "-",style=default_style)

    response = HttpResponse(mimetype='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=report_group_summary_monthly_excel-%s-%s.xls' % (year,month)
    book.save(response)
    return response

 def generateReceipt(itemNames, itemPrices, subTotal, salesTax):
     """This new method relies on being called from makeSale(). It takes two
        lists, and two integer values to generate a receipt for the customer.
        It prints out to an excel file with formatted numbers. This is the
        best we have to make a receipt for the cashier. It launches the file
        after it has been created so the customer can print the receipt."""
     
     global ticketID
     global ALIGN_RIGHT
     global ALIGN_CENTER
     receipt = xlwt.Workbook()
     sale = receipt.add_sheet('Customer Receipt')
     sale.write_merge(0, 1, 0, 5, "My Tool", BRAND_CELL)
     sale.write_merge(2, 2, 0, 3, "Today's date:", xlwt.easyxf('align: horiz right'))
     sale.write_merge(2, 2, 4, 5, str(time.strftime("%m-%d-%Y")), xlwt.easyxf('align: horiz right'))
     sale.write_merge(3, 3, 0, 3, "Your unique sale ID:", xlwt.easyxf('align: horiz right'))
     sale.write_merge(3, 3, 4, 5, ticketID, xlwt.easyxf('align: horiz right'))
     sale.write_merge(4, 4, 0, 3, "Item", BRAND_CELL)
     sale.write_merge(4, 4, 4, 5, "Price", BRAND_CELL)
     for x in range(0, len(itemNames)):
         sale.write_merge(5+x, 5+x, 0, 3, itemNames[x])
         sale.write_merge(5+x, 5+x, 4, 5, str(itemPrices[x]/100), ALIGN_RIGHT)
     sale.write_merge(5+len(itemNames), 5+len(itemNames), 0, 3, "Sales Tax:", ALIGN_RIGHT)
     sale.write_merge(5+len(itemNames), 5+len(itemNames), 4, 5, str(salesTax/100), ALIGN_RIGHT)
     sale.write_merge(6+len(itemNames), 6+len(itemNames), 0, 3, "Sub total:", ALIGN_RIGHT)
     sale.write_merge(6+len(itemNames), 6+len(itemNames), 4, 5, str(subTotal/100), ALIGN_RIGHT)
     sale.write_merge(7+len(itemNames), 7+len(itemNames), 0, 3, "Total:", ALIGN_RIGHT)
     sale.write_merge(7+len(itemNames), 7+len(itemNames), 4, 5, str((salesTax+subTotal)/100), ALIGN_RIGHT)
     sale.write_merge(8+len(itemNames), 8+len(itemNames), 0, 5, "Thanks for shopping at My Tool!", ALIGN_RIGHT)
     sale.write_merge(9+len(itemNames), 9+len(itemNames), 0, 5, "Have a great day!", ALIGN_CENTER)
     receipt.save('Customer_receipt_' + str(ticketID)+'.xls')
     os.system("start Customer_receipt_" + str(ticketID)+ '.xls')

def _generate_excel(response, columns, headers, grads):
    import xlwt
    book = xlwt.Workbook(encoding='utf-8')
    sheet = book.add_sheet('Search Results')
    hdrstyle = xlwt.easyxf('font: bold on; pattern: pattern solid, fore_colour grey25; align: horiz centre')
    evenstyle = xlwt.easyxf('pattern: back_colour gray40')
    oddstyle = xlwt.easyxf('pattern: pattern sparse_dots, fore_colour grey25')
    
    # header row
    sheet.write(0, 0, u'Graduate Student Search Results', xlwt.easyxf('font: bold on, height 320'))
    sheet.row(0).height = 400
    for i,hdr in enumerate(headers):
        sheet.write(1, i, hdr, hdrstyle)
    
    # data rows
    for i,grad in enumerate(grads):
        style = [oddstyle, evenstyle][i%2]
        for j,column in enumerate(columns):
            sheet.write(i+2, j, getattribute(grad, column, html=False), style)
    
    # set column widths
    for i,c in enumerate(columns):
        wid = COLUMN_WIDTHS[c]
        sheet.col(i).width = wid
    
    count = len(grads)
    sheet.write(count+4, 0, 'Number of students: %i' % (count))
    sheet.write(count+5, 0, 'Report generated: %s' % (datetime.datetime.now()))
    
    book.save(response)

def make_protected(workbook_male, workbook_female, worksheet_male, worksheet_female, country):

    row_country_m, col_country_m = find_row_col_index(country, worksheet_male)
    row_country_f, col_country_f = find_row_col_index(country, worksheet_female)

    wb_male = copy(workbook_male)
    wb_female = copy(workbook_female)

    for i in range(17, 258):
        for j in range(3, 50):
            if i != row_country_m:
                wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
            else:
                wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
    wb_male.get_sheet(0).set_protect(True)
    wb_male.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.XLS')

    for i in range(17, 258):
        for j in range(3, 50):
            if i != row_country_f:
                wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
            else:
                wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
    wb_female.get_sheet(0).set_protect(True)
    wb_female.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_FEMALE.XLS')

    def add_to_excel(self, state):
        """ adds content to the excel file based on the specific statistic type """
        import xlwt
        answers = state['answers']
        ws = state['ws']
        current_row = state['current_row']
        translator = self.getSite().getPortalTranslations()
        total, answered, unanswered = self.calculate(self.question, answers)

        #define Excel styles
        header_style = xlwt.easyxf('font: bold on; align: vert centre')
        normal_style = xlwt.easyxf('align: vert centre')

        #write cell elements similarly to the zpt-->html output
        ws.write(current_row, 1, self.question.title, header_style)
        current_row += 1
        ws.write(current_row, 2, translator('Count'), header_style)
        ws.write(current_row, 3, translator('Percent'), header_style)
        current_row += 1
        ws.write(current_row, 1, translator('Answered'), header_style)
        ws.write(current_row, 2, answered[0], normal_style)
        ws.write(current_row, 3, '%.2f%%'
            % (round(answered[1], 2), ), normal_style)
        current_row += 1
        ws.write(current_row, 1, translator('Not answered'), header_style)
        ws.write(current_row, 2, unanswered[0], normal_style)
        ws.write(current_row, 3, '%.2f%%'
            % (round(unanswered[1], 2), ), normal_style)
        current_row += 1
        ws.write(current_row, 1, translator('Total'), header_style)
        ws.write(current_row, 2, total, normal_style)
        ws.write(current_row, 3, '100%%', normal_style)
        current_row += 1

        state['current_row'] = current_row