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 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
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) + ')')
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
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
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))
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()
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)
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)
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)
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
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
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
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
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)
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 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()
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
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()
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)
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
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}"
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 })
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 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
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
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.')
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)
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 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)
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
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)
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
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))
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()
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);
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()
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)