def _check_to_write(self, row, col, key, write_format, metrics, Sheet):
		if key in metrics:
			try:
				if re.search("=", write_format):
					if metrics[key] != "":
						cell1 = xl_rowcol_to_cell(row, col-2)
						cell2 = xl_rowcol_to_cell(row, col-1)
						Sheet.write_formula(row, col, "=%s-%s"%(cell1, cell2), self.formats[write_format[1:]])
				elif re.search("num_format", write_format):
					if not isinstance(metrics[key], int) and not isinstance(metrics[key], float):
						Sheet.write_number(row, col, int(metrics[key].replace(',','')), self.formats[write_format])
					else:
						Sheet.write_number(row, col, metrics[key], self.formats[write_format])
				elif re.search("perc_format", write_format):
					# begin and end amp_cov temporary fix
					if key == 'begin_amp_cov' or key == 'end_amp_cov':
						if float(metrics[key]) > 1:
							metrics[key] = metrics[key] / 2
					elif float(metrics[key]) > 2:
						metrics[key] = metrics[key] / 100
					Sheet.write_number(row, col, float(metrics[key]), self.formats[write_format])
				elif re.search("dec3_format", write_format):
					Sheet.write_number(row, col, float(metrics[key]), self.formats[write_format])
				# special case to write the formula for the +-10 bp col
				else:
					# if write_format is blank, then self.formats will also be blank
					Sheet.write(row, col, metrics[key], self.formats[write_format])
			except ValueError:
				Sheet.write(row, col, metrics[key], self.formats[write_format])
		return 1

def check_to_write(row, col, key, write_format, metrics):
	if key in metrics:
		try:
			if re.search("=", write_format):
				if metrics[key] != "":
					cell1 = xl_rowcol_to_cell(row, col-2)
					cell2 = xl_rowcol_to_cell(row, col-1)
					QCsheet.write_formula(row, col, "=(%s-%s)/%s"%(cell1, cell2, cell1), formats[write_format[1:]])
			elif re.search("num_format", write_format):
				if not isinstance(metrics[key], int) and not isinstance(metrics[key], float):
					QCsheet.write_number(row, col, int(metrics[key].replace(',','')), formats[write_format])
				else:
					QCsheet.write_number(row, col, metrics[key], formats[write_format])
			elif re.search("perc_format", write_format):
				if float(metrics[key]) > 1:
					metrics[key] = metrics[key] / 100
				QCsheet.write_number(row, col, float(metrics[key]), formats[write_format])
			elif re.search("dec3_format", write_format):
				QCsheet.write_number(row, col, float(metrics[key]), formats[write_format])
			# special case to write the formula for the +-10 bp col
			else:
				# if write_format is blank, then formats will also be blank
				QCsheet.write(row, col, metrics[key], formats[write_format])
		except ValueError:
			QCsheet.write(row, col, metrics[key], formats[write_format])
	return 1

    def __add_similarities_compilation(self, stats_worksheet, row_pos):
        format = self.__style_manager.get_format(RowTypes.session, StyleType.crashed, '')
        stats_worksheet.write(xl_rowcol_to_cell(row_pos, 0), 'Matches', format)
        stats_worksheet.write(xl_rowcol_to_cell(row_pos, 1), 'Similar', format)
        stats_worksheet.write(xl_rowcol_to_cell(row_pos, 2), 'Total', format)
        stats_worksheet.write(xl_rowcol_to_cell(row_pos, 3), 'Message', format)
        stats_worksheet.write(xl_rowcol_to_cell(row_pos, 4), 'Link', format)
        row_pos += 1

        count = 0
        for similarity in self.__log_folder_parser.log_similiatities.similarities:
            match_count = 1
            similarity_count = 0
            for match in similarity.matches:
                if match.ratio == 100:
                    match_count += 1
                else:
                    similarity_count += 1

            format = self.__style_manager.get_format(RowTypes.line,
                                                     StyleType.alt1 if count % 2 == 0 else StyleType.alt2,
                                                     '')
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 0), match_count, format)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 1), similarity_count, format)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 2), match_count + similarity_count, format)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 3), match.message, format)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 4), '', format)
            count += 1
            row_pos += 1

        row_pos += 3
        return row_pos

def write_per_player_stats(worksheet, player_name, player_maps, Time, stat_func):
	global row_cnt
	row = row_cnt
	row_cnt += 1

	worksheet.write(row, 0, player_name)
	col = 0
	player_map = map(lambda whole_map : whole_map[player_name], player_maps)

	range_str = "%s:%s" % (xl_rowcol_to_cell(row, len(stat_func) * len(player_map) + 1), xl_rowcol_to_cell(row, len(player_map) * (len(stat_func) + len(Time))))
	
	func = "SUM"
	for i in xrange(len(player_map)):
		col += 1
		worksheet.write_formula(row, col, 
			"{=%s(%s * (MOD(COLUMN(%s),%d)=%d))}" % (func, range_str, range_str, len(player_map), i))
	
	func = "AVERAGE"
	for i in xrange(len(player_map)):
		col += 1
		worksheet.write_formula(row, col, "= %s / %d" % (xl_rowcol_to_cell(row, col - len(stat_func)), len(Time)))
        
	for time in Time:
		for item in player_map:
			col += 1
			if time in item:
				worksheet.write(row, col, item[time])
			else:
				worksheet.write(row, col, None)

def cash_flow_formulas(table_height, cash_flow_col, top_table_row):
    formulas = []
    for i in range(table_height):
        signal_cell = xl_rowcol_to_cell(top_table_row, cash_flow_col - 1)
        value_cell = xl_rowcol_to_cell(top_table_row, cash_flow_col - 2)
        formulas.append('=IF(' + signal_cell + '="Buy",' + value_cell + '* -1,' + value_cell +')') # Looks like =IF($E$14="Buy", $F$14*-1, $F$14)
        top_table_row += 1
    return formulas

 def __cell_coord(self, col_name, rowtype, row=None):
     if row is None:
         row = self.__row_count
     col = IndexedHeaders[col_name]
     cell = xl_rowcol_to_cell(row, col - 1)
     if rowtype == RowTypes.session and col_name == Headers.message:
         cell1 = xl_rowcol_to_cell(row, col)
         cell = cell + ':' + cell1
     return cell

def worksheetRegionFormat(worksheet, neededFormat,
                          firstRow = 0,firstColumn = 0,
                          lastRow = 10, lastColumn = 10):
    cellLocation1 =  xl_rowcol_to_cell(firstRow, firstColumn)
    cellLocation2 =  xl_rowcol_to_cell(lastRow, lastColumn)
    format_range = '%s:%s' % (cellLocation1,cellLocation2)
    worksheet.conditional_format(format_range, {'type': 'no_errors',
                                                'format': neededFormat})
    return format_range

def formula(worksheet):

    global TOTAL_DAYS

    row_init = 0
    col_init = 0
    row_end = TOTAL_DAYS + 1 

    for row in range(5,row_end,1):
        #current row value
        col_B = xl_rowcol_to_cell(row, 1)
        col_C = xl_rowcol_to_cell(row, 2)
        col_D = xl_rowcol_to_cell(row, 3)
        col_E = xl_rowcol_to_cell(row, 4)
        col_G = xl_rowcol_to_cell(row, 6)
        col_H = xl_rowcol_to_cell(row, 7)
        col_I = xl_rowcol_to_cell(row, 8)

        # 5MA
        first_row = xl_rowcol_to_cell(row-4, 4) #col_E
        worksheet.write_formula(row, col_init+6, "AVERAGE(%s:%s)"%(first_row, col_E))

        if row >= 30:
            # 30MA
            first_row = xl_rowcol_to_cell(row-29, 4) #col_E
            worksheet.write_formula(row, col_init+7, "AVERAGE(%s:%s)"%(first_row, col_E))

            # 5-30MA
            worksheet.write_formula(row, col_init+8, "%s-%s"%(col_G, col_H))

def pl_per_bs_percent_formulas(table_height, cash_flow_col, top_table_row):
    formulas = []
    for i in range(table_height):
        if i % 2 == 0:
            formulas.append('=NA()')
        else:
            prev_value_cell = xl_rowcol_to_cell(top_table_row - 1, cash_flow_col - 2)
            value_cell = xl_rowcol_to_cell(top_table_row, cash_flow_col - 2)
            formulas.append('=(' + prev_value_cell + '+' + value_cell + ')/ABS(' + prev_value_cell + ')') # Looks like =ABS(G13-G14)
        top_table_row += 1
    return formulas

def pl_per_bs_formulas(table_height, cash_flow_col, top_table_row):
    formulas = []
    for i in range(table_height):
        if i % 2 == 0:
            formulas.append('=NA()')
        else:
            prev_value_cell = xl_rowcol_to_cell(top_table_row - 1, cash_flow_col - 1)
            value_cell = xl_rowcol_to_cell(top_table_row, cash_flow_col - 1)
            formulas.append('=(' + prev_value_cell + '+' + value_cell + ')') # Looks like =G13+G14 where both cells could be +'ve or -'ve
        top_table_row += 1
    return formulas

def worksheetCondRegionFormat(worksheet, neededFormat, criteria_str, value,
                              firstRow = 0,firstColumn = 0,
                              lastRow = 10, lastColumn = 10):
    cellLocation1 =  xl_rowcol_to_cell(firstRow, firstColumn)
    cellLocation2 =  xl_rowcol_to_cell(lastRow, lastColumn)
    format_range = '%s:%s' % (cellLocation1,cellLocation2)
    worksheet.conditional_format(format_range, {'type': 'cell',
                                                'criteria': criteria_str,
                                                'value': value, 'format': neededFormat})
    
    return format_range

def pl_over_time_percent_formulas(table_height, pl_over_time_col, top_table_row):
    formulas = []
    ini_invest_cell = xl_rowcol_to_cell(top_table_row, pl_over_time_col - 6, row_abs=True, col_abs=True)
    for i in range(table_height):
        if i % 2 == 0:
            formulas.append('=NA()')
        else:
            cul_pl_cell = xl_rowcol_to_cell(top_table_row, pl_over_time_col - 1)
            formulas.append('=(' + cul_pl_cell + '/' + ini_invest_cell + ')') # Looks like =(G13+G14)
        top_table_row += 1
    return formulas

def populate_index_worksheet(worksheet, pms, site_url):

    # Widen the columns to make the text clearer.
    worksheet.set_column('A:A', 9)
    worksheet.set_column('B:B', 50)
    worksheet.set_column('C:C', 12)
    worksheet.set_column('D:D', 15)
    worksheet.set_column('E:E', 13)
    worksheet.set_column('F:F', 13)
    worksheet.set_column('G:G', 13)

    for i, pm in enumerate(pms, start=1):
        url = site_url + "/pc/notify/link?recordId=" + str(pm["SPEC_ID"])

        table_name = "Table" + pm["TRIIDTX"]

        worksheet.write(i, 0, pm["TRIIDTX"])
        worksheet.write(i, 1, pm["TRINAMETX"])
        worksheet.write(i, 2, pm["TRIPMTYPECLASSCL"])
        worksheet.write(i, 3, "Yes" if pm["TRITASKGROUPINGRULELI"] == "Create Task For Each Asset/Location" else "No")
        worksheet.write(i, 4, '=COUNTIF({}[Valid?], "OK")'.format(table_name))
        worksheet.write(i, 5, '=COUNTIF({}[Valid?], "ERROR")'.format(table_name))
        formula = "={}+{}".format(xl_rowcol_to_cell(i, 4), xl_rowcol_to_cell(i, 5))
        worksheet.write(i, 6, formula)
        worksheet.write_url(i, 7,
                            url="internal:{}!A1".format(pm["TRIIDTX"]),
                            string="Details",
                            tip="View details about " + pm["TRIIDTX"])
        worksheet.write_url(i, 8,
                            url=url,
                            string="View",
                            tip="View record " + pm["TRIIDTX"] + " in TRIRIGA")

        cellA = xl_rowcol_to_cell(i, 4, row_abs=True, col_abs=True)
        cellB = xl_rowcol_to_cell(i, 6, row_abs=True, col_abs=True)
        worksheet.conditional_format(i, 4, i, 6, {'type': 'formula',
                                                  'criteria': "={}={}".format(cellA, cellB),
                                                  'format': xlFormats['good']})

    worksheet.add_table(0, 0, i, 8, { 'style': 'Table Style Light 11',
                                      'columns': [
                                          {'header': 'PM ID'},
                                          {'header': 'PM Schedule'},
                                          {'header': 'Recurrence'},
                                          {'header': 'Task Grouping?'},
                                          {'header': 'Valid'},
                                          {'header': 'Invalid'},
                                          {'header': 'Total'},
                                          {'header': 'Details'},
                                          {'header': 'View'},
                                     ]})

    worksheet.activate()

def pl_over_time_formulas(table_height, cash_flow_col, top_table_row):
    formulas = ['=NA()', '=' + xl_rowcol_to_cell(top_table_row + 1, cash_flow_col - 2) + '+ 0']
    top_table_row += 2
    for i in range(table_height - 2):
        if i % 2 == 0:
            formulas.append('=NA()')
        else:
            prev_cul_pl_cell = xl_rowcol_to_cell(top_table_row - 2, cash_flow_col)
            cul_pl_cell = xl_rowcol_to_cell(top_table_row, cash_flow_col - 2)
            formulas.append('=(' + prev_cul_pl_cell + '+' + cul_pl_cell + ')') # Looks like =(G13+G14)
        top_table_row += 1
    return formulas

def initialize_summary_formulas(sheet,start_row,end_row,rubric):
		start_row = start_row + 1

		formulaStr = "=MAX(K"+str(start_row)+":K"+str(end_row)+")"
		cell_address = xl_rowcol_to_cell(0,11)
		sheet.write_formula(cell_address,formulaStr)

		formulaStr = "MAX(P"+str(start_row)+":P"+str(end_row)+")"
		cell_address = xl_rowcol_to_cell(0,16)
		sheet.write_formula(cell_address,formulaStr)

		formulaStr = "MAX(U"+str(start_row)+":U"+str(end_row)+")"
		cell_address = xl_rowcol_to_cell(0,21)
		sheet.write_formula(cell_address,formulaStr)

def make_chart(workbook, worksheet, company, DM, chart_type, x_col,\
    y_col,  title, width, height, table_mark, x_axis_name, y_axis_name,\
    table2_height=None, table2_mark=None, x2_col=None, y2_col=None, x2_axis_name=None, y2_axis_name=None):
    chart = workbook.add_chart({'type': chart_type})
    chart.set_title({'name': title})

    # chart.set_legend({'none': True})
    chart.set_size({'width': width, 'height': height})

    for strategy in DM.strategies:
        table_height = DM.strategy_company_data_len(strategy, company)
        chart.add_series({
            'name': strategy,
            'values': '=' + company\
                    + '!'\
                    + xl_rowcol_to_cell(table_mark, y_col, row_abs=True, col_abs=True)\
                    + ':'\
                    + xl_rowcol_to_cell(table_mark + table_height - 1, y_col, row_abs=True, col_abs=True),
            'categories': '=' + company\
                        + '!'\
                        + xl_rowcol_to_cell(table_mark, x_col, row_abs=True, col_abs=True)\
                        + ':'\
                        + xl_rowcol_to_cell(table_mark + table_height - 1, x_col, row_abs=True, col_abs=True),
        })
        table_mark += table_height

    chart.set_y_axis({
        'name': y_axis_name
    })

    chart.set_x_axis({
        'date_axis': True
    })

    if x2_col and y2_col and y2_axis_name:
        chart.add_series({
            'name': strategy,
            'values': '=' + company\
                    + '!'\
                    + xl_rowcol_to_cell(table2_mark, y2_col, row_abs=True, col_abs=True)\
                    + ':'\
                    + xl_rowcol_to_cell(table2_mark + table_height - 1, y2_col, row_abs=True, col_abs=True),
            'categories': '=' + company\
                        + '!'\
                        + xl_rowcol_to_cell(table2_mark, x2_col, row_abs=True, col_abs=True)\
                        + ':'\
                        + xl_rowcol_to_cell(table2_mark + table_height - 1, x2_col, row_abs=True, col_abs=True),
            'y2_axis': True
        })

        chart.set_y2_axis({
            'name': y2_axis_name
        })

        chart.set_x2_axis({
            'date_axis': True
        })


    return chart

    def addDataToSpreadSheet(self,worksheet,workbook):
        name = self.le_outname.text()
        exportDir = self.fileDir +'\\'+ name 
        
        ZipFile = zipfile.ZipFile(self.fileName)
        ZipFile.extractall(exportDir)
        ZipFile.close()
        
        formatStandard = workbook.add_format({'align':'left','border':1})
        
        ws = worksheet
        k = 0

        subdir = glob.glob(exportDir+'\\*\\')[0]
        
        myFile = glob.glob(exportDir+'\\*\\*.txt')
        
        for file in myFile:
            print(file)
            myf = open(file,'r',encoding = 'utf-16') 
            lat_tmp = 0
            lon_tmp = 0
            for i,line in enumerate(myf.readlines()[1:]):
                k = i+10
                data = line.replace('\n','').split(';')
                ws.write_number(k,0,int(data[0]),formatStandard)

                lat = round(float(data[3]),9)
                lon = round(float(data[2]),9)
                ws.write_number(k,1,lat,formatStandard)
                ws.write_number(k,2,lon,formatStandard)
                
                if i == 0:
                    ws.write(k,3,0)
                    ws.write(k,4,0)
                else:
                    ws.write_number(k,3,self.haversine(lon,lat,lon_tmp,lat_tmp),formatStandard)
                    ws.write_formula(k,4,'='+xl_rowcol_to_cell(k-1,4)+'+'+xl_rowcol_to_cell(k,3),formatStandard)
                
                ws.write(k,5,data[7],formatStandard)
                ws.merge_range(k,6,k,15,data[8],formatStandard)
                ws.merge_range(k,16,k,17,'----',formatStandard)
                
                if len(data[5])> 1:
                    ws.write_url(k,18,subdir+'Images\\'+data[5])
                
                lat_tmp,lon_tmp = lat,lon
        
        return k+2

 def __write_similiarities_block_master(self, stats_worksheet, similarity, row_pos, section_row_pos):
     format = self.__style_manager.get_format(RowTypes.session, StyleType.crashed, '')
     stats_worksheet.write(xl_rowcol_to_cell(row_pos, 0), len(similarity.matches), format)
     stats_worksheet.merge_range(xl_rowcol_to_cell(row_pos, 1) + ':' + xl_rowcol_to_cell(row_pos, 3),
                                 similarity.message, format)
     stats_worksheet.set_row(row_pos, 30)
     format = self.__style_manager.get_format(RowTypes.session, StyleType.url_crashed, '')
     stats_worksheet.write_formula(xl_rowcol_to_cell(row_pos, 4),
                                   '=HYPERLINK("#\'Log Compilation\'!{0}","Click to go to original extracted line")'.format(
                                       similarity.log_line.excel_cell), format)
     stats_worksheet.write(xl_rowcol_to_cell(row_pos, 5), '=HYPERLINK("#\'Log Similarities\'!D{0}","Summary")'.format(section_row_pos), format)
     self.__url_count += 1
     self.__url_added_to_excel += 1
     row_pos += 1
     return row_pos

def format_excel(writer, df_size):
    """ Add Excel specific formatting to the workbook
    df_size is a tuple representing the size of the dataframe - typically called
    by df.shape -> (20,3)
    """
    # Get the workbook and the summary sheet so we can add the formatting
    workbook = writer.book
    worksheet = writer.sheets['summary']
    # Add currency formatting and apply it
    money_fmt = workbook.add_format({'num_format': 42, 'align': 'center'})
    worksheet.set_column('A:A', 20)
    worksheet.set_column('B:C', 15, money_fmt)
    # Add 1 to row so we can include a total
    # subtract 1 from the column to handle because we don't care about index
    table_end = xl_rowcol_to_cell(df_size[0] + 1, df_size[1] - 1)
    # This assumes we start in the left hand corner
    table_range = 'A1:{}'.format(table_end)
    worksheet.add_table(table_range, {'columns': [{'header': 'account',
                                                   'total_string': 'Total'},
                                                  {'header': 'Total Sales',
                                                   'total_function': 'sum'},
                                                  {'header': 'Average Sales',
                                                   'total_function': 'average'}],
                                      'autofilter': False,
                                      'total_row': True,
                                      'style': 'Table Style Medium 20'})

def	add_named_ranges(top_row,bottom_row,rubric,wb,sheetRows,sheet):
	cell_address = xl_rowcol_to_cell(0,2)
	sheet.write(cell_address,"Recommendation")

	rnge = "="+simplify(rubric)+"!$Y$2:$Y$4"
	range_name =  define_name("dialectCounts",rubric)
	add_defined_name(wb,range_name,rnge)

	rnge = "="+simplify(rubric)+"!$C$8:$C$"+str(sheetRows[rubric]+1)
	range_name = define_name("dialectList",rubric)
	add_defined_name(wb,range_name,rnge)

	rnge = "="+simplify(rubric)+"!$C$2:$C$4"
	range_name = define_name("dialectNames",rubric)
	add_defined_name(wb,range_name,rnge)

	rnge = "="+simplify(rubric)+"!$Z$7:$CO$7"
	if (rubric == "CSW"):
		rnge = "="+simplify(rubric)+"!$Z$7:$BG$7"
	if (rubric == "DCITE"):
		rnge = "="+simplify(rubric)+"!$Z$7:$AW$7"
	if (rubric == "UMM-C"):
		rnge = "="+simplify(rubric)+"!$Z$7:$CN$7"
	range_name = define_name("existsColumns",rubric)
	add_defined_name(wb,range_name,rnge)

	rnge = "="+simplify(rubric)+"!$Z$2:$CO$4"
	if (rubric == "CSW"):
		rnge = "="+simplify(rubric)+"!$Z$2:$BG$4"
	if (rubric == "DCITE"):
		rnge = "="+simplify(rubric)+"!$Z$2:$AW$4"
	if (rubric == "UMM-C"):
		rnge = "="+simplify(rubric)+"!$Z$2:$CN$4"
	range_name = define_name("existsSummary",rubric)
	add_defined_name(wb,range_name,rnge)