Beispiel #1
0
	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
Beispiel #2
0
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
Beispiel #3
0
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 __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 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
Beispiel #6
0
def crear_vlookup(filas, aparato):
    formula = '='
    for f in range(filas):
        for c in range(0, 12):
            esquina_1 = xl_rowcol_to_cell(10 + 11 * f, 1 + 4 * c)
            esquina_2 = xl_rowcol_to_cell(13 + 11 * f, 2 + 4 * c)
            formula += 'IFERROR(VLOOKUP("' + aparato + '",Detalles!' + esquina_1 + ':' + esquina_2 + ',2,FALSE),0)' + '+'
    return formula[:-1]
def generate_excel_sheet_footer(cell_format_percent, last_raw_number, worksheet):
    # noinspection SpellCheckingInspection
    worksheet.write(last_raw_number + 1, 3, '=COUNTIF(D:D, "finished")/' + str(last_raw_number - 1),
                    cell_format_percent)
    worksheet.write(last_raw_number + 1, 4, '=AVERAGE(E3:' + xl_rowcol_to_cell(last_raw_number, 4) + ')',
                    cell_format_percent)
    worksheet.write(last_raw_number + 1, 5, '=SUM(F3:' + xl_rowcol_to_cell(last_raw_number, 5) + ')')
    worksheet.write(last_raw_number + 1, 6, '=SUM(G3:' + xl_rowcol_to_cell(last_raw_number, 6) + ')')
Beispiel #8
0
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 __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
Beispiel #10
0
def absoluteValueDifference(worksheet, orthoWallsList):
    col = 1
    worksheet.write(5, 0, 'Absolute Value Difference in Inches')
    for x in range(len(orthoWallsList)):
        cellFour = xl_rowcol_to_cell(4, col)
        cellThree = xl_rowcol_to_cell(3, col)
        formulaString = "=ABS(" + str(cellThree) + "-" + str(cellFour) + ") * 12"
        worksheet.write(5, col, formulaString)
        col += 1
Beispiel #11
0
def generate_xlsx_file(file_name, df, column_list, sheet_name="SUMMARY"):

    # put df into Excel  sheet
    xls_writer = pd.ExcelWriter(file_name)
    df.to_excel(xls_writer, sheet_name, index=True)
    worksheet = xls_writer.sheets[sheet_name]
    workbook = xls_writer.book

    # format header - must use conditional format here since we don't want to format the entire column (which worksheet.set_column does)
    # so we pass some trivial conditional test that is always true for the header (no_blanks)

    header_rng = "{}:{}".format(xlsutil.xl_rowcol_to_cell(0, 0),
                                xlsutil.xl_rowcol_to_cell(0, len(column_list)))
    header_fmt_dict = {
        'bold': True,
        'font_color': '#FFFFFF'  ## white
        ,
        'bg_color': '#376283'  ## dark blue
    }
    header_format_wb = workbook.add_format(header_fmt_dict)
    worksheet.conditional_format(header_rng, {
        'type': 'no_blanks',
        'format': header_format_wb
    })

    # format columns
    for i in range(len(column_list)):

        #print("processing column '{}' ...".format(column_list[i][0]))

        col_width = column_list[i][1]
        col_num_fmt = column_list[i][2]
        col_align = column_list[i][3]
        xls_cell = xlsutil.xl_rowcol_to_cell(
            0, i)  # returns header Excel position e.g. A1
        xls_col = '{col}:{col}'.format(
            col=xls_cell[:-1])  # convert to column notation e.g. A:A

        # format columns
        col_align = 'left' if col_align is None else col_align
        col_fmt_dict = {
            'font_name': 'Roboto',
            'font_size': 9,
            'align': col_align
        }

        # add numeric fmt to dict if passed
        if col_num_fmt is not None:
            col_fmt_dict['num_format'] = col_num_fmt
        xls_col_format = workbook.add_format(col_fmt_dict)

        # set format in wb
        worksheet.set_column(xls_col, col_width, xls_col_format)

    xls_writer.save()

    print("\ndata saved in xlsx : {}\n".format(file_name))
Beispiel #12
0
 def cell_select(self, response):
     self.selectedCell.set(xl_rowcol_to_cell(response[1], response[2]))
     self.selectedCellSumMean.set("")
     self.updateHighlightedCells(True)
     for bnd in self.updateBinds:
         if xl_rowcol_to_cell(response[1],
                              response[2]) in self.updateBinds[bnd]:
             self.highlightedCells.append(xl_cell_to_rowcol(bnd))
     self.updateHighlightedCells()
Beispiel #13
0
 def updateCellFromFormulaResult(self, response):
     if (xl_rowcol_to_cell(response[0], response[1]) in self.updateBinds):
         for updQE in self.updateBinds[xl_rowcol_to_cell(
                 response[0], response[1])]:
             self.updateCellFromFormulaResult(xl_cell_to_rowcol(updQE))
     self.sheet.set_cell_data(
         response[0], response[1],
         self.interpret(self.getFormulaForResponse(response)[1:], response))
     self.sheet.refresh(False, False)
Beispiel #14
0
def calc_difference(sheet_list):
    for sheet in sheet_list:
        for row in range(2, 8):
            for col in range(1, 17):
                polygon_item_cell = xl_rowcol_to_cell(row, col)
                intorno_item_cell = xl_rowcol_to_cell(row, col + 17)
                dest_cell = xl_rowcol_to_cell(row, col + 17 + 17)
                formula = '=ABS(-' + polygon_item_cell + '+' + intorno_item_cell + ')'  # Formula: intorno - poligono
                sheet.write_formula(dest_cell, formula)
Beispiel #15
0
 def delk(self, event):
     for cell in self.sheet.get_selected_cells():
         for bnd in self.updateBinds:
             if xl_rowcol_to_cell(cell[0],
                                  cell[1]) in self.updateBinds[bnd]:
                 self.updateBinds[bnd].remove(
                     xl_rowcol_to_cell(cell[0], cell[1]))
         self.formulas[cell[0]][cell[1]] = "=0"
         self.sheet.set_cell_data(cell[0], cell[1], "")
     self.sheet.refresh(False, False)
Beispiel #16
0
def percentageDifference(worksheet, orthoWallsList, percent_fmt):
    worksheet.write(6, 0, 'Percentage Difference')
    worksheet.set_row(6, None, percent_fmt)
    col = 1
    for x in range(len(orthoWallsList)):       
        cellFive = xl_rowcol_to_cell(5, col)
        cellThree = xl_rowcol_to_cell(3, col)
        formulaString = "=" + str(cellFive) + "/" + str(cellThree) + "/ 12" 
        worksheet.write(6, col, formulaString)
        col += 1
Beispiel #17
0
def weightedPercentage(worksheet, orthoWallsList, percent_fmt):
    worksheet.write('A8', 'Weighted Percentage')
    col = 1
    worksheet.set_row(7, None, percent_fmt)
    for x in range(len(orthoWallsList)):
        cellEight = xl_rowcol_to_cell(8, col) 
        cellSix =  xl_rowcol_to_cell(6, col)
        formulaString = "=" + str(cellEight) + "*" + str(cellSix)
        worksheet.write_formula(7, col, formulaString)
        col += 1
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 make_formula(formula_str, row, col, first_data_row=None):
    # noinspection SpellCheckingInspection
    """
        A cell will be written as a formula if the HTML tag has the attribute "data-excel" set.

        Note that this function is called when the spreadsheet is being created. The cell it applies to knows where it
        is and what the first data row is.

        Allowed formula strings:

            "SUM ROW A-C": sum the current row from A-C

            "SUM ROW A,C": sum cells A and C in the current row

            "SUM COL": sums current col from first_row to row - 1

            "FORMULA RAW IF(F13 > 0, (F13-E13)/F13, '')": uses formula as is

            "FORMULA RELATIVE IF(colm001rowp000 > 0, (colm001rowp0-colm002rowp000)/colm001rowp001, '')": creates the
                formula relative to the current location. colm002 means two cols to the left of the current cell.
                rowp000 means the current row plus 0 (e.g. the current row)

        :param formula_str: the value of the "data-excel" tag containing params for generating the formula
        :param row: cell row
        :param col: cell column
        :param first_data_row: for column formulas
        :return: a string
        """
    parts = formula_str.split(' ')
    func = parts[0]
    args = parts[-1]

    formula = ''
    if func == 'SUM':
        func_modifier = parts[1]
        if func_modifier == 'ROW':
            if '-' in args:
                cols = args.split('-')
                formula = '=SUM({}{}:{}{})'.format(cols[0], row + 1, cols[1], row + 1)
            elif ',' in args:
                cols = map(methodcaller('strip'), args.split(','))

                # Put the row number after each col letter and then add them together
                cols = '+'.join(map(lambda x: x + str(row + 1), cols))
                formula = '=SUM({})'.format(cols)
        elif func_modifier == 'COL':
            formula = '=SUM({}:{})'.format(xl_rowcol_to_cell(first_data_row, col), xl_rowcol_to_cell(row - 1, col))
    elif func == 'FORMULA':
        func_modifier = parts[1]
        formula_str = ' '.join(parts[2:])
        if func_modifier == 'RAW':
            formula = '=' + formula_str
        elif func_modifier == 'RELATIVE':
            formula = '=' + locate_cells(formula_str, row, col)
    return formula
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 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
Beispiel #23
0
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_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
Beispiel #25
0
 def cellNames(self):
     rows = []
     if self.width() == 1:
         for r in range(self.rows.start, self.rows.stop):
             rows.append(xl_rowcol_to_cell(r, self.cols.start))
     else:
         for r in range(self.rows.start, self.rows.stop):
             cols = []
             rows.append(cols)
             for c in range(self.cols.start, self.cols.stop):
                 cols.append(xl_rowcol_to_cell(r, c))
     return rows
Beispiel #26
0
 def write_formula(row,
                   col_name,
                   formula,
                   formula_col1,
                   formula_col2,
                   fmt=fmt_default):
     # type: (int, str, str, str, str, xl.format.Format) -> None
     ws.write_formula(
         row, col_to_idx[col_name],
         formula.format(
             xlutil.xl_rowcol_to_cell(row, col_to_idx[formula_col1]),
             xlutil.xl_rowcol_to_cell(row, col_to_idx[formula_col2])), fmt)
Beispiel #27
0
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()
Beispiel #28
0
    def create_workbook_template(self,
                                 filename,
                                 licence_category='Fauna Other Purpose'):
        """
        Creates a blank template with purposes and column headings only
        """
        meta = OrderedDict()
        if os.path.isfile(filename):
            logger.warn('File already exists {}'.format(filename))
            return None

        wb = xlsxwriter.Workbook(filename)
        ws = wb.add_worksheet(APP_SHEET_NAME)
        self.set_formats(wb)

        row_num = 0
        col_num = 0
        cell_dict = {}
        cell_start = xl_rowcol_to_cell(row_num,
                                       col_num,
                                       row_abs=True,
                                       col_abs=True)
        sys_cols = ExcelApplication.objects.all().last().cols_output.keys()
        for col_name in sys_cols:
            ws.write(row_num, col_num, col_name, self.bold_unlocked)
            col_num += 1
        cell_end = xl_rowcol_to_cell(row_num,
                                     col_num - 1,
                                     row_abs=True,
                                     col_abs=True)
        cell_dict.update({SYSTEM: [cell_start, cell_end]})

        activity_name_list = self.get_purposes(licence_category)
        for activity_name in activity_name_list:
            #cols = self.cols_output(None, 'Importing Fauna (Non-Commercial)')
            activity_type_cols = self.cols_output(None, activity_name).keys()
            ws.write(row_num, col_num, '', self.bold)
            col_num += 1
            cell_start = xl_rowcol_to_cell(row_num,
                                           col_num,
                                           row_abs=True,
                                           col_abs=True)
            for col_name in activity_type_cols:
                ws.write(row_num, col_num, col_name, self.bold_unlocked)
                col_num += 1
            cell_end = xl_rowcol_to_cell(row_num,
                                         col_num - 1,
                                         row_abs=True,
                                         col_abs=True)
            cell_dict.update({activity_name: [cell_start, cell_end]})

        self.write_sheet_meta(wb, cell_dict, activity_name_list)
        wb.close()
Beispiel #29
0
def saveDataInExcel_2D_ACF(workbook, radii, g_r, random_g_r, expMeanGr,
                           randomMeanGr, grIndex):
    # experimental and random raw data
    worksheet = workbook.add_worksheet('2D ACF raw data')
    for i in range(0, len(g_r) + 1):
        for j in range(0, len(randomMeanGr) + 2):
            if (i == 0 and j == 0):
                worksheet.write(i, j, 'radius (nm)')
            if (i == 0 and j == 1):
                worksheet.write(i, j, 'experimental data')
            if (i == 0 and j > 1):
                worksheet.write(i, j, 'random_' + str(j - 1))
            if (i > 0 and j == 0):
                worksheet.write(i, j, radii[i - 1])
            if (i > 0 and j == 1):
                worksheet.write(i, j, g_r[i - 1])
            if (i > 0 and j > 1):
                worksheet.write(i, j, random_g_r[j - 2][i - 1])

    # experimental and random summary
    worksheet = workbook.add_worksheet('2D ACF summary')
    for i in range(0, len(randomMeanGr) + 1):
        if (i == 0):
            worksheet.write(i, 0, 'random mean')
        if (i > 0):
            worksheet.write(i, 0, randomMeanGr[i - 1])

    # median value calculation for experimental and random data
    randomMedianGr = np.median(
        np.resize(np.asarray(random_g_r),
                  (len(random_g_r), len(random_g_r[0]))))
    expMedian = np.median(g_r)

    cellStart = xl_rowcol_to_cell(
        1, 0
    )  # converts the zero indexed row and column cell reference to a A1 style string
    cellEnd = xl_rowcol_to_cell((len(randomMeanGr)), 0)

    worksheet.write(0, 2, 'mean random mean')
    worksheet.write(1, 2, '=AVERAGE({!s} : {!s})'.format(cellStart, cellEnd))

    worksheet.write(5, 2, 'random median')
    worksheet.write(6, 2, randomMedianGr)

    worksheet.write(0, 4, 'experimental mean')
    worksheet.write(1, 4, expMeanGr)
    worksheet.write(3, 4, grIndex)
    worksheet.write(3, 3, '1st index bigger')

    worksheet.write(5, 4, 'experimental median')
    worksheet.write(6, 4, expMedian)
 def writeCellDivisionDenomFixed(self,
                                 worksheet,
                                 cell_range,
                                 numer_list,
                                 denom_cell,
                                 base_sheet_name,
                                 axis=0):
     """Write Column Division, Fixed Denominator
     
     Args:
         cell_range: "A1:A10"
         numer_list: ["B1", "C2", ..., "H10"]
         denom_cell: "C7"
         axis: 
             0 means write to next row first (default)
             1 means write to next column first
     
     Usage:
         self.writeCellDivisionDenomFixed(
             worksheet, 
             "A1:A3", 
             ["B1", "B2", "B3"],
             "C8", 
             "data sheet", 
             axis=0
         )
     """
     # get number from cell range
     (start_row,
      start_col), (end_row,
                   end_col) = self.getNumberFromCellsRange(cell_range)
     # loop through the numer list
     i = 0
     # write to next row first
     if axis == 0:
         for col in range(start_col, end_col + 1):
             for row in range(start_row, end_row + 1):
                 cell = utility.xl_rowcol_to_cell(row, col)
                 formula = "='{0}'!{1}/'{0}'!{2}".format(
                     base_sheet_name, numer_list[i], denom_cell)
                 self.writeFormulaToCell(worksheet, cell, formula)
                 i += 1
     # write to next column first
     elif axis == 1:
         for row in range(start_row, end_row + 1):
             for col in range(start_col, end_col + 1):
                 cell = utility.xl_rowcol_to_cell(row, col)
                 formula = "='{0}'!{1}/'{0}'!{2}".format(
                     base_sheet_name, numer_list[i], denom_cell)
                 self.writeFormulaToCell(worksheet, cell, formula)
                 i += 1
Beispiel #31
0
def create_report(data, file_name, coord):
    report = xls.Workbook(file_name)
    data_sheet = report.add_worksheet("Data")
    time_format = report.add_format()
    time_format.set_num_format("hh:mm:ss")
    data_sheet.write(0, 7, "Latitude(exemplary)")
    data_sheet.write(0, 8, "Longitude(exemplary)")
    data_sheet.write(1, 7, coord[0])
    data_sheet.write(1, 8, coord[1])
    data_sheet.write(0, 0, "Latitude")
    data_sheet.write(0, 1, "Longitude")
    data_sheet.write(0, 2, "Time (present)")
    data_sheet.write(0, 3, "Time (measurement)")
    data_sheet.write(0, 4, "Inaccuracy, m")
    row, col = 1, 0
    for lat, long, time in data:
        data_sheet.write(row, col, lat)
        data_sheet.write(row, col + 1, long)
        data_sheet.write(row, col + 2, time, time_format)
        data_sheet.write(row, col + 3, "=%s-%s" % (xl_rowcol_to_cell(row, col + 2), xl_rowcol_to_cell(0, col + 2, row_abs = True, col_abs = True)), time_format)
        data_sheet.write(row, col + 4, "=IF(OR(%s=0,%s=0),0,"
                    "6371*(ACOS(SIN(RADIANS(%s))*SIN(RADIANS(%s))+"
                    "COS(RADIANS(%s))*COS(RADIANS(%s))*COS(RADIANS(%s)-RADIANS(%s)))) * 1000)" % (
                        xl_rowcol_to_cell(row, col),
                        xl_rowcol_to_cell(row, col + 1),
                        xl_rowcol_to_cell(row, col),
                        xl_rowcol_to_cell(1,7, row_abs = True, col_abs = True),
                        xl_rowcol_to_cell(row, col),
                        xl_rowcol_to_cell(1,7, row_abs = True, col_abs = True),
                        xl_rowcol_to_cell(row, col + 1),
                        xl_rowcol_to_cell(1,8, row_abs = True, col_abs = True))
                    )
        row += 1
    chart_sheet = report.add_chartsheet("Chart")
    chart = report.add_chart({"type": "scatter", "subtype": "straight"})
    chart.add_series({
        "values": ["Data", 0, 4, row - 1, 4],
        "categories": ["Data", 0, 3, row - 1, 3],
        })
    chart.set_x_axis({
        "name": "time",
         "major_gridlines": {'visible': True},
         "minor_gridlines": {'visible': True},
        })
    chart.set_y_axis({
        "name": "inaccuracy",
        "major_gridlines": {'visible': True},
        "minor_gridlines": {'visible': True},
        })
    chart_sheet.set_chart(chart)
    report.close()
def write_to_excel(df_in, filetosave, columns_in):
    writer = pd.ExcelWriter(filetosave,
                            engine='xlsxwriter',
                            options={
                                'strings_to_urls': False,
                                'strings_to_numbers': True
                            })
    df_in.to_excel(writer, sheet_name='Sheet1', index=False)
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    last_col_idx = len(df_in.columns)
    overallstat_idx = df_in.columns.get_loc('Overall_check')

    # Add a format. Light red fill with dark red text.
    format1 = workbook.add_format({
        'bg_color': '#FFC7CE',
        'font_color': '#9C0006'
    })

    # Add a format. Green fill with dark green text.
    format2 = workbook.add_format({
        'bg_color': '#C6EFCE',
        'font_color': '#006100'
    })

    for row_now in range(1, len(df_in) + 1):
        cell_compute = xl_rowcol_to_cell(row_now, overallstat_idx)
        cell_start = xl_rowcol_to_cell(row_now, last_col_idx - len(columns_in))
        cell_end = xl_rowcol_to_cell(row_now, last_col_idx - 2)
        formula_str = '=IF(COUNTIF(' + cell_start + ':' + cell_end + ', "FALSE") > 0, "FAIL", "OK")'
        worksheet.write_formula(cell_compute, formula_str)
        worksheet.data_validation(cell_start + ':' + cell_end, \
                                  {'validate': 'list', \
                                  'source':['FALSE', 'OK'], \
                                  'error_message':'The value to be entered must be either "FALSE" or "OK"'})

    cell_compute_start = xl_rowcol_to_cell(1, last_col_idx - 1)
    cell_compute_end = xl_rowcol_to_cell(len(df_in), last_col_idx - 1)

    worksheet.conditional_format(cell_compute_start + ':' + cell_compute_end, \
                                 {'type': 'cell', \
                                 'criteria': 'equal to', \
                                 'value':'"FAIL"', \
                                 'format': format1})
    worksheet.conditional_format(cell_compute_start + ':' + cell_compute_end, \
                                 {'type': 'cell', \
                                 'criteria': 'equal to', \
                                 'value':'"OK"', \
                                 'format': format2})

    writer.save()
Beispiel #33
0
def write_sheet(wb,
                sheet_name,
                col_descs,
                queryset,
                get_row,
                sub_columns=None):
    ws = wb.add_worksheet(sheet_name)

    bold = wb.add_format({"bold": True})
    bold.set_text_wrap()
    formats = {"bold": bold, "percent": wb.add_format({"num_format": "0.00%"})}

    advanced = any([1 for cd in col_descs if "format" in cd])
    max_height = (max([c["title"].count("\n")
                       for c in col_descs]) + 1)  # Nb of lines in titles
    ws.set_row(0, 15 * max_height, bold)  # default height is 15
    ws.freeze_panes(1, 0)

    for i, col_desc in enumerate(col_descs):
        xl_col = xl_rowcol_to_cell(0, i)
        size = col_desc["width"] if "width" in col_desc else len(
            col_desc["title"])
        col_format = formats.get(
            col_desc["format"]) if "format" in col_desc else None
        ws.set_column(xl_col + ":" + xl_col, size, col_format)

    row_num = 1
    col_titles = [cd["title"] for cd in col_descs]
    ws.write_row("A" + str(row_num), col_titles, bold)

    if sub_columns:
        row_num += 1
        ws.write_row("A" + str(row_num), sub_columns)

    for item in queryset:
        row_num += 1
        if row_num % 1000 == 0 and settings.DEBUG:
            print(f"Sheet {sheet_name} row {row_num}")

        if not advanced:
            ws.write_row("A" + str(row_num), get_row(item, row_num=row_num))
        else:
            for col_num, column in enumerate(get_row(item, row_num=row_num)):
                cell = xl_rowcol_to_cell(row_num - 1, col_num)
                # The link format is using a text for the link display. Without specific format, a http link
                # will still be automatically colored by Excel but will show the full URL instead of a short text.
                if (str(column).startswith("http")
                        and col_descs[col_num].get("format", "") == "link"):
                    ws.write_url(cell, str(column), string="Lien")
                else:
                    ws.write(cell, column)
Beispiel #34
0
def fugas(datos, horas, precio, inicio, final, cliente, mes, nombre, workbook):

    worksheet = workbook.add_worksheet('Fugas')
    worksheet.set_column(1, 1, 2)
    worksheet.set_column(4, 4, 2)

    bold_1 = workbook.add_format({'bold': True, 'font_size': 15})
    bold_2 = workbook.add_format({'bold': True, 'align': 'center'})
    center = workbook.add_format({'num_format': '0.0', 'align': 'center'})
    center_2 = workbook.add_format({'align': 'center'})

    worksheet.write('A1', 'Resumen de fugas de ' + nombre, bold_1)
    worksheet.write('A4', 'Circuito', bold_2)
    worksheet.write('C4', 'Fuga (W)', bold_2)
    worksheet.write('D4', 'A', bold_2)
    worksheet.write('F4', 'Notas', bold_2)

    for i in range(0, 5):
        worksheet.write_formula(
            xl_rowcol_to_cell(5 + 2 * i, 3),
            '=IFERROR(' + xl_rowcol_to_cell(5 + 2 * i, 2) + '/127,0)', center)
        worksheet.write_formula(
            xl_rowcol_to_cell(5 + 2 * i, 0), '=IFERROR(MID(Desciframiento!' +
            xl_rowcol_to_cell(14 + i, 2) + ',FIND(" ",Desciframiento!' +
            xl_rowcol_to_cell(14 + i, 2) + ')+1,256)," ")', center_2)
        worksheet.write(
            xl_rowcol_to_cell(5 + 2 * i, 2), '=IFERROR(MID(Desciframiento!' +
            xl_rowcol_to_cell(14 + i, 3) + ',FIND(" ",Desciframiento!' +
            xl_rowcol_to_cell(14 + i, 3) + ')+1,256)," ")', center_2)
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
Beispiel #36
0
def _format_validation_range(validation_rows, validation_column,
                             data_dict_worksheet_name):
    start = xl_rowcol_to_cell(
        1,  # 1 is to skip first row in DD sheet that is for header
        validation_column,
        row_abs=True,
        col_abs=True,
    )
    stop = xl_rowcol_to_cell(validation_rows,
                             validation_column,
                             row_abs=True,
                             col_abs=True)

    return f"'{data_dict_worksheet_name}'!{start}:{stop}"
Beispiel #37
0
def fugas(datos,precio,inicio,final,cliente,mes,nombre,workbook):
    
    worksheet = workbook.add_worksheet('Fugas')
    worksheet.set_column(1,1,2)
    worksheet.set_column(4,4,2)
        
    bold_1 = workbook.add_format({'bold': True,'font_size':15})
    bold_2 = workbook.add_format({'bold': True,'align':'center'})
    center = workbook.add_format({'num_format': '0.0','align':'center'})
    center_2 = workbook.add_format({'align':'center'})
    formato_nota = workbook.add_format({'align': 'left', 'num_format': '#'})
    
    
    worksheet.write('A1','Resumen de fugas de ' + nombre,bold_1)
    worksheet.write('A4','Circuito',bold_2)
    worksheet.write('C4','Fuga (W)',bold_2)
    worksheet.write('D4','A',bold_2)
    worksheet.write('F4','Notas',bold_2)
    
    for i, elemento in enumerate(porcentajes_fugas):
        elemento_ = xl_cell_to_rowcol(elemento)
        fuga_titulo = xl_rowcol_to_cell(elemento_[0],elemento_[1]-1)
        circ_titulo = xl_rowcol_to_cell(elemento_[0],elemento_[1]-2)
        nota_titulo = xl_rowcol_to_cell(elemento_[0],elemento_[1]+3+4)
        worksheet.write_formula(xl_rowcol_to_cell(5+2*i,3),'=IFERROR('+xl_rowcol_to_cell(5+2*i,2)+'/127,0)',center)  # Columna del amperaje
        worksheet.write_formula(xl_rowcol_to_cell(5+2*i,0),'=IFERROR(MID(Desciframiento!'+circ_titulo  
                                                            +',FIND(" ",Desciframiento!'+circ_titulo+')+1,256)," ")',center_2)  # Columna del circuito
        worksheet.write(xl_rowcol_to_cell(5+2*i,2),'=IFERROR(MID(Desciframiento!'+fuga_titulo
                                                            +',FIND(" ",Desciframiento!'+fuga_titulo+')+1,256)," ")',center_2)  # Columna de los watts
        
        worksheet.write(xl_rowcol_to_cell(5+2*i,5),'=Desciframiento!'+nota_titulo, formato_nota)
    def set_simple_cond_format(self, df: pd.DataFrame, pos: tuple,
                               col_name: str, boundary, order: str):
        """
        Утановка условного форматирования на ячейки таблицы. Односторонее
        форматирование

        Parameters:
        -----------
        df:pd.DataFrame
            исходный датафрейм
        pos:tuple
            Координаты левого верхнего угла таблицы на странице excel
        col_name:
            имя столбца для установки формата
        upper
            верхняя граница УФ
        lower
            нижняя граница УФ
        order:str
            order = "straight":
                        < lower - green
                        >= upper - red

            order = "reverse":
                        > lower - green
                        <= upper - red
        """
        rows, cols = pos
        col_num = df.columns.get_loc(col_name)

        fmt_start = xl_rowcol_to_cell(rows + 1, cols + col_num)
        fmt_end = xl_rowcol_to_cell(rows + df.shape[0], cols + col_num)

        if order == "straight":
            self.worksheet.conditional_format(
                f"{fmt_start}:{fmt_end}", {
                    "type": "cell",
                    "criteria": "<=",
                    "value": boundary,
                    "format": self.format_blind_red
                })

        elif order == "reverse":
            self.worksheet.conditional_format(
                f"{fmt_start}:{fmt_end}", {
                    "type": "cell",
                    "criteria": ">=",
                    "value": boundary,
                    "format": self.format_blind_red
                })
Beispiel #39
0
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)
Beispiel #40
0
    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_history_data(self, history_values_lst):
        row_pos = self.row_pos
        start_row_pos = row_pos
        stop_row_pos = row_pos
        length = len(history_values_lst)
        is_summary_section = False
        for index in range(length + 1):
            if index < length:
                history_values = history_values_lst[index]
            else:
                is_summary_section = True
                history_values = {
                    'internal_ref': _('Total'),
                    'barcode': '',
                    'name': '',
                    'quantity': '',
                    'standard_price': '',
                    'value': 'SUM({}:{})',
                }
            for col_pos, column in enumerate(self.columns):
                cell_type = self.info_labels.get(column).get('type', '')
                cell_format = self.info_labels.get(column).get(
                    'format', self.format_default)
                cell_value = history_values.get(column, '')

                if is_summary_section:
                    cell_format = self.format_table_header

                if cell_type == 'formula':
                    cell_value = cell_value or '{}*{}'
                    start_cell = xl_rowcol_to_cell(row_pos, col_pos - 2)
                    stop_cell = xl_rowcol_to_cell(row_pos, col_pos - 1)

                    if is_summary_section:
                        cell_format = self.format_table_number_bold
                        start_cell = xl_rowcol_to_cell(start_row_pos, col_pos)
                        stop_cell = xl_rowcol_to_cell(stop_row_pos, col_pos)

                    cell_formula_value = cell_value.format(
                        start_cell, stop_cell)
                    self.sheet.write_formula(
                        row_pos, col_pos, cell_formula_value, cell_format)
                else:
                    self.sheet.write(
                        row_pos, col_pos, cell_value, cell_format)

            stop_row_pos = row_pos
            row_pos += 1
        self.row_pos = row_pos
Beispiel #42
0
def createSVerweisTable():
    worksheet_sverwtable = workbook.add_worksheet(SVERWTABLE_NAME)
    notentable = (["6", "ungenügend", "", 6], ["5-", "mangelhaft", "minus",
                                               5], ["5", "mangelhaft", "", 5],
                  ["5+", "mangelhaft", "plus",
                   5], ["4-", "ausreichend", "minus",
                        4], ["4", "ausreichend", "",
                             4], ["4+", "ausreichend", "plus",
                                  4], ["3-", "befriedigend", "minus",
                                       3], ["3", "befriedigend", "", 3],
                  ["3+", "befriedigend", "plus",
                   3], ["2-", "gut", "minus",
                        2], ["2", "gut", "",
                             2], ["2+", "gut", "plus",
                                  2], ["1-", "sehr gut", "minus",
                                       1], ["1", "sehr gut", "", 1])

    if (punktesystem.get()):
        notentable += (["1+", "sehr gut", "plus", 1], )

    row = 0
    col = 0

    for note, note_text, tendenz_text, note_num in notentable:
        worksheet_sverwtable.write_string(row, col, note, format_rot_b)
        worksheet_sverwtable.write_string(row, col + 1, note_text,
                                          format_rot_b)
        worksheet_sverwtable.write_string(row, col + 2, tendenz_text,
                                          format_rot_b)
        worksheet_sverwtable.write_number(row, col + 3, note_num, format_rot_b)
        row += 1

    row = 17
    col = 0
    counter = 0
    while (counter < anzahl_sus):
        formula = "='2. Auswertung eintragen'!" + xl_rowcol_to_cell(
            8 + counter, 2 + anzahl_aufg_gesamt + 3)
        worksheet_sverwtable.write_formula(row + counter, col, formula,
                                           format_rot_b)
        formula = "=IF(" + xl_rowcol_to_cell(
            row + counter, col) + '="","",VLOOKUP(' + xl_rowcol_to_cell(
                row + counter, col) + "," + xl_rowcol_to_cell(
                    0, 0, True, True) + ":" + xl_rowcol_to_cell(
                        counter_table - 1, 3, True, True) + ",4,))"
        worksheet_sverwtable.write_formula(row + counter, col + 1, formula,
                                           format_rot_b)
        counter += 1
    worksheet_sverwtable.hide()
 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
Beispiel #44
0
def xlsx_report(event_list, filename, year, month, attendees_catalog=None):
    print('Generating report for {}: {}'.format(
        str(year) + '/' + str(month), filename))
    if attendees_catalog is not None:
        attendees_dict = load_attendees(attendees_catalog)
    else:
        attendees_dict = {}

    events, attendees = parse_events(event_list)

    # HEADER ROW
    rows = [['Date'] + [
        attendees_dict[a] if a in attendees_dict.keys() else a
        for a in attendees
    ] + ['Total']]

    # GENERATE ROWS
    for day in range(calendar.monthrange(year, month)[1]):
        row_date = datetime(year=year, month=month,
                            day=day + 1).strftime("%Y/%m/%d")
        row = [row_date]
        if row_date in events.keys():
            row += [
                events[row_date][a] if a in events[row_date].keys() else 0
                for a in attendees
            ]
        else:
            row += [0] * len(attendees)
        rows.append(row)

    # WRITE FILE
    book = xlsxwriter.Workbook(filename)
    sheet = book.add_worksheet('Rapport')

    row_id = 0
    for row in rows:
        first_cell = xl_rowcol_to_cell(row_id, 1)
        if row_id > 0:
            if len(attendees) > 1:
                last_cell = xl_rowcol_to_cell(row_id, len(attendees))
                row += ['=SUM({}:{})'.format(first_cell, last_cell)]
            else:
                row += ['=SUM({})'.format(first_cell)]
        sheet.write_row(row_id, 0, rows[row_id])
        row_id += 1

    book.close()
    print('Report generation complete.')
Beispiel #45
0
def line(workbook, labels, series1, *others, subtype='', headings=["Series","values"], sheetname="Sheet"):
    worksheet = workbook.add_worksheet(sheetname)
    length = len(labels)
    sheet_name = worksheet.get_name()
    
    chart1 = workbook.add_chart({'type': 'line', 'subtype': subtype})
    worksheet.write_row('A1', headings, bold)
    worksheet.write_column("A2", labels)
    
    worksheet.write_column("B2", series1)
    chart1.add_series({
        'name':       [sheet_name, 0, 1], 
        'categories': [sheet_name, 1, 0,length,0], # '=%s!$A$2:$A$%s' % (sheet_name, length),
        'values':     [sheet_name, 1, 1,length,1], #'=%s!$B$2:$B$%s' % (sheet_name, length),
        'data_labels': {'value': True}
    })
    
    for idx, val in enumerate(others):
        cell = xl_rowcol_to_cell(1,idx+2)
        worksheet.write_column(cell, val)
        chart1.add_series({
            'name':       [sheet_name, 0, idx+2], 
            'categories': [sheet_name, 1, 0,length,0], # '=%s!$A$2:$A$%s' % (sheet_name, length),
            'values':     [sheet_name, 1, idx+2,length,idx+2], #'=%s!$B$2:$B$%s' % (sheet_name, length),
            'data_labels': {'value': True}
        })
        
    worksheet.insert_chart('D2', chart1)
Beispiel #46
0
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'})
Beispiel #47
0
    def apply_format_table(self):

        # Apply format to dataframe cells
        for index, row in self.df.iterrows():

            rowIndex = self.df.index.get_loc(index)
            iRow, worksheetRow = self.convert_to_row_index(index)

            for iCol in range(self.nCols):
                iCol, worksheetCol = self.convert_to_col_index(iCol)
                x = row.iloc[iCol]
                cell = xl_rowcol_to_cell(worksheetRow, worksheetCol)
                formatDic = self.formatTable[rowIndex][iCol]
                cellFormat = self.workbook.add_format(formatDic)
                self.worksheet.write(cell, x, cellFormat)

        # Apply format to header cells
        for i in range(self.nColLevels):
            formatDic = self.formatTableHeader[i]
            cellFormat = self.workbook.add_format(formatDic)
            self.worksheet.set_row(i, self.headerRowsHeight[i], cellFormat)

        # Apply format to index cells
        for j in range(self.nIndexLevels):
            formatDic = self.formatTableIndex[i]
            cellFormat = self.workbook.add_format(formatDic)
            self.worksheet.set_column(xl_range(1, j, 1, j),
                                      self.indexColWidth[j], cellFormat)
Beispiel #48
0
    def add_nested_ratings(self, ratings, row, col):
        rating_rows = []

        for info in ratings:
            weight, rating = info[0:2]

            rating_rows.append(row)
            self.rating_ws.write(row, col, weight)
            self.rating_ws.write(row, col + 1, rating)

            if len(info) > 2:
                # add sub-ratings
                rows, last_row = self.add_nested_ratings(info[2], row + 1, col + 1)

                # now set this rating's score to the product of the children
                col_name = xl_col_to_name(col + 1)
                for i in xrange(self.n_columns):
                    rating_col_name = xl_col_to_name(self.rating_col(i))
                    # weight * score
                    formula = '+'.join('%s%s*%s%s' % (col_name, r + 1, rating_col_name, r + 1) for r in rows)
                    self.rating_ws.write_formula(row, self.rating_col(i), formula)

                row = last_row
            else:
                # actual rating
                score_row = self.score_row[rating]

                for i in xrange(self.n_columns):
                    cell = xl_rowcol_to_cell(score_row, self.score_col(i), row_abs=True, col_abs=True)
                    self.rating_ws.write(row, self.rating_col(i), '=Raw!%s' % cell)
            row += 1

        return rating_rows, row
Beispiel #49
0
    def write_sumif_formula(self,
                            worksheet,
                            first_formula_range,
                            condition_cell,
                            second_formula_range,
                            formula_cell,
                            cells_format=None):
        formula_first_first_cell_row, formula_first_first_cell_column, formula_first_second_cell_row, \
        formula_first_second_cell_column = first_formula_range.get_range_cells_coordinates()

        formula_second_first_cell_row, formula_second_first_cell_column, formula_second_second_cell_row, \
        formula_second_second_cell_column = second_formula_range.get_range_cells_coordinates()

        condition_cell_row, condition_cell_column = condition_cell.get_cell_coordinates(
        )
        formula_cell_row, formula_cell_column = formula_cell.get_cell_coordinates(
        )

        first_range = xl_range(formula_first_first_cell_row,
                               formula_first_first_cell_column,
                               formula_first_second_cell_row,
                               formula_first_second_cell_column)
        second_range = xl_range(formula_second_first_cell_row,
                                formula_second_first_cell_column,
                                formula_second_second_cell_row,
                                formula_second_second_cell_column)
        formula_condition_cell = xl_rowcol_to_cell(condition_cell_row,
                                                   condition_cell_column)
        formula = self._sumif_formula_template.format(first_range,
                                                      formula_condition_cell,
                                                      second_range)
        worksheet.write_formula(formula_cell_row, formula_cell_column, formula,
                                cells_format)
Beispiel #50
0
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)
    def __write_similiarities_block_detailled(self, stats_worksheet, similarity, row_pos):
        begin_pos = row_pos
        count = 0
        for match in similarity.matches:
            format = self.__style_manager.get_format(RowTypes.line,
                                                     StyleType.alt1 if match.ratio == 100 else StyleType.alt2,
                                                     Headers.date)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 0), match.log_line.date, format)
            format = self.__style_manager.get_format(RowTypes.line,
                                                     StyleType.alt1 if match.ratio == 100 else StyleType.alt2,
                                                     Headers.time)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 1), match.log_line.time, format)
            format = self.__style_manager.get_format(RowTypes.line,
                                                     StyleType.percent_alt1 if match.ratio == 100 else StyleType.percent_alt2, '')
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 2), match.ratio / 100, format)
            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 3), match.message, format)
            format = self.__style_manager.get_format(RowTypes.line,
                                                     StyleType.url_alt1 if match.ratio == 100 else StyleType.url_alt2,
                                                     '')
            if self.__url_count <= 16384 and (similarity.limit is None or count < similarity.limit):
                stats_worksheet.write_formula(xl_rowcol_to_cell(row_pos, 4),
                                              '=HYPERLINK("#\'Log Compilation\'!{0}","Click to go to original extracted line")'.format(
                                                  match.log_line.excel_cell), format)
                self.__url_added_to_excel += 1
            else:
                stats_worksheet.write(xl_rowcol_to_cell(row_pos, 4),
                                      'Log Compilation Tab, Cell : {0}'.format(match.log_line.excel_cell), format)

            stats_worksheet.write(xl_rowcol_to_cell(row_pos, 5),
                                  '=HYPERLINK("#\'Log Similarities\'!D{0}","Go to section")'.format(
                                      begin_pos), format)
            self.__url_count += 1
            count += 1
            row_pos += 1
        return row_pos
 def __update_similarity_compilation_with_links(self, stats_worksheet):
     row_pos = 4
     count = 0
     for similarity in self.__log_folder_parser.log_similiatities.similarities:
         format = self.__style_manager.get_format(RowTypes.line,
                                                  StyleType.url_alt1 if count % 2 == 0 else StyleType.url_alt2,
                                                  '')
         stats_worksheet.write_formula(xl_rowcol_to_cell(row_pos, 4),
                                       '=HYPERLINK("#\'Log Similarities\'!B{0}","Details")'.format(
                                           similarity.row_pos), format)
         row_pos += 1
         count += 1
Beispiel #53
0
def	add_exists_formulas(sheet,top_row,bottom_row,rubric,number_of_dialects,wb):
	dlist = "C$"+str(top_row)+":C$"+str(bottom_row)
	start_col = 26
	end_col = 60
	if (rubric == "UMM-C"):
		end_col = 93
	if (rubric == "CSW"):
		end_col = 60
	if (rubric == "DCITE"):
		end_col = 50
	drange = "$C"+str(top_row+1)+":$C"+str(bottom_row)
	percent = wb.add_format({'num_format':'0%'})
	range_name =  define_name("dialectList",rubric)

	for row in range(1,number_of_dialects+1):
		for col in range(start_col,end_col):
			cell_start =  xl_rowcol_to_cell(top_row,col-1)
			cell_end =  xl_rowcol_to_cell(bottom_row-1, col-1)
			loc_list = cell_start+":"+cell_end
			formulaStr = "{=SUMIF("+range_name+",C$"+str(row+1)+","+loc_list+")/$Y"+str(row+1)+"}"
			cell_address = xl_rowcol_to_cell(row,col-1)
			sheet.write_formula(cell_address,formulaStr,percent)
def do_formula(i, j, limits_col, height, gap):
    return '=IF({3}="na", "na", AND({3} <= {0}, {4} <= {1}, {5} <= {2}))'.format(
            xlutil.xl_rowcol_to_cell(2, limits_col+2, row_abs=True, col_abs=True),
            xlutil.xl_rowcol_to_cell(3, limits_col+2, row_abs=True, col_abs=True),
            xlutil.xl_rowcol_to_cell(4, limits_col+2, row_abs=True, col_abs=True),
            xlutil.xl_rowcol_to_cell(2+0*(height+gap)+i, 1+j),
            xlutil.xl_rowcol_to_cell(2+1*(height+gap)+i, 1+j),
            xlutil.xl_rowcol_to_cell(2+2*(height+gap)+i, 1+j))
Beispiel #55
0
    def create_workbook_template(self, filename, licence_category='Fauna Other Purpose'):
        """
        Creates a blank template with purposes and column headings only
        """
        meta = OrderedDict()
        if os.path.isfile(filename):
            logger.warn('File already exists {}'.format(filename))
            return None

        wb = xlsxwriter.Workbook(filename)
        ws = wb.add_worksheet(APP_SHEET_NAME)
        self.set_formats(wb)

        row_num = 0
        col_num = 0
        cell_dict = {}
        cell_start = xl_rowcol_to_cell(row_num, col_num, row_abs=True, col_abs=True)
        sys_cols = ExcelApplication.objects.all().last().cols_output.keys()
        for col_name in sys_cols:
            ws.write(row_num, col_num, col_name, self.bold_unlocked)
            col_num += 1
        cell_end = xl_rowcol_to_cell(row_num, col_num-1, row_abs=True, col_abs=True)
        cell_dict.update({SYSTEM: [cell_start, cell_end]})

        activity_name_list = self.get_purposes(licence_category)
        for activity_name in activity_name_list:
            #cols = self.cols_output(None, 'Importing Fauna (Non-Commercial)')
            activity_type_cols = self.cols_output(None, activity_name).keys()
            ws.write(row_num, col_num, '', self.bold); col_num += 1
            cell_start = xl_rowcol_to_cell(row_num, col_num, row_abs=True, col_abs=True)
            for col_name in activity_type_cols:
                ws.write(row_num, col_num, col_name, self.bold_unlocked)
                col_num += 1
            cell_end = xl_rowcol_to_cell(row_num, col_num-1, row_abs=True, col_abs=True)
            cell_dict.update({activity_name: [cell_start, cell_end]})

        self.write_sheet_meta(wb, cell_dict, activity_name_list)
        wb.close()
Beispiel #56
0
def PrintWinningRate(nRowOffset, nColOffset, nTitle, nMaxDateCount):
    stTitleBoldFormat = gstWorkBook.add_format({'bold': True, 'font_color': 'blue'});
    stRedTitleBoldFormat = gstWorkBook.add_format({'bold': True, 'font_color': 'red'});
    stRateFormat = gstWorkBook.add_format({'num_format':'0.000'});

    gstGraphSheet.write(nRowOffset, nColOffset, nTitle, stTitleBoldFormat);
    gstGraphSheet.write(nRowOffset + 1, nColOffset, u"누적 승리", stRedTitleBoldFormat);
    for nDateIndex in range(nMaxDateCount):
        if (nDateIndex == 0):
            continue;

        nDateRowOffset = nDateIndex + (nRowOffset + 2);

        stAccumulatedCell = xl_rowcol_to_cell(nDateRowOffset - 1, nColOffset);
        if (nTitle == u"KOSPI"):
            stAvgStockRate = xl_rowcol_to_cell(nDateRowOffset, nColOffset - 1);
        else:
            stAvgStockRate = xl_rowcol_to_cell(nDateRowOffset, nColOffset - 2);
        stKospiRate = xl_rowcol_to_cell(nDateRowOffset, nColOffset - 3);

        stString = "=IFERROR(" + stAccumulatedCell + " + (" + stAvgStockRate + " - " + stKospiRate + "), \"\")";

        gstGraphSheet.write(nDateRowOffset, nColOffset, stString, stRateFormat);
Beispiel #57
0
    def write(self, data, file_name):
        workbook = xlsxwriter.Workbook(file_name)
        worksheet = workbook.add_worksheet()

        row = 0
        col = 0
        for cost_unit in sorted(data.keys()):
            worksheet.write(row, 0, cost_unit)

            for month in sorted(data[cost_unit].keys()):
                col += 1
                costs = data[cost_unit][month]
                worksheet.write(row, col, sum(costs))

            # generate statistics
            start = xl_rowcol_to_cell(row,1)
            stop = xl_rowcol_to_cell(row,col)
            worksheet.write(0, col+1, '=SUM('+start+":"+stop+")")
	    # new cost unit and hence a new row
            row += 1
            col = 1

        workbook.close()
Beispiel #58
0
def add_summary_formulas(sheet,start_row,end_row,rubric,dialect_name,dialect_row):
		start_row = start_row + 1
		range_name =  simplify("dialectList"+rubric)
		dialect_cellname = "C"+str(dialect_row+1)

		cell_address = xl_rowcol_to_cell(dialect_row,2)
		sheet.write(cell_address,dialect_name)
		
		formulaStr = "{=MAX(IF("+range_name+"="+dialect_cellname+",L"+str(start_row)+":L"+str(end_row)+"))}"
		cell_address = xl_rowcol_to_cell(dialect_row,11)
		sheet.write_formula(cell_address,formulaStr)
	
		formulaStr = "{=MAX(IF("+range_name+"=$"+dialect_cellname+",Q$"+str(start_row)+":Q$"+str(end_row)+"))}"
		cell_address = xl_rowcol_to_cell(dialect_row,16)
		sheet.write_formula(cell_address,formulaStr)
		
		formulaStr = "{=MAX(IF("+range_name+"=$"+dialect_cellname+",V$"+str(start_row)+":V$"+str(end_row)+"))}"
		cell_address = xl_rowcol_to_cell(dialect_row,21)
		sheet.write_formula(cell_address,formulaStr)
	
		formulaStr = "=COUNTIF("+range_name+","+dialect_cellname+")"
		cell_address = xl_rowcol_to_cell(dialect_row,24)
		sheet.write_formula(cell_address,formulaStr)