def write_sheet_meta(self, workbook, cell_dict, activity_name_list): ws_meta = workbook.add_worksheet(META_SHEET_NAME) #ws_meta = workbook.sheet_by_name('Meta') row_num = 0 col_num = 0 workbook.define_name('{0}!{1}'.format(APP_SHEET_NAME, SYSTEM), '={0}!{1}:{2}'.format(APP_SHEET_NAME, cell_dict[SYSTEM][0], cell_dict[SYSTEM][1])) # Hdr ws_meta.write(row_num, col_num, 'Purpose') ws_meta.write(row_num, col_num+1, FIRST_COL) ws_meta.write(row_num, col_num+2, LAST_COL) # System ws_meta.write(row_num+1, col_num, SYSTEM) # Purpose ws_meta.write(row_num+1, col_num+1, xl_cell_to_rowcol(cell_dict[SYSTEM][0])[1]) # First Col ws_meta.write(row_num+1, col_num+2, xl_cell_to_rowcol(cell_dict[SYSTEM][1])[1]) # Last Col # Purposes width = 20 for row_num, activity_name in enumerate(activity_name_list, start=2): workbook.define_name('{0}!{1}'.format(APP_SHEET_NAME, self.replace_special_chars(activity_name)), '={0}!{1}:{2}'.format(APP_SHEET_NAME, cell_dict[activity_name][0], cell_dict[activity_name][1])) ws_meta.write(row_num, col_num, activity_name) # Purpose ws_meta.write(row_num, col_num+1, xl_cell_to_rowcol(cell_dict[activity_name][0])[1]) # First Col ws_meta.write(row_num, col_num+2, xl_cell_to_rowcol(cell_dict[activity_name][1])[1]) # Last Col width = len(activity_name) if len(activity_name) > width else width # lock the Meta Sheet (mostly) ws_meta.set_column('D:G', 13, self.integer ) ws_meta.set_column('A:Z', None, self.locked) ws_meta.protect() ws_meta.set_column(0, 0, width)
def range_to_rows(x, m=None): temp = x.split(":") res = list( range( xl_cell_to_rowcol(temp[0])[0], xl_cell_to_rowcol(temp[1])[0] + 1)) if m is not None: res = list(filter(lambda x: x < m, res)) return res
def _letter_to_col_number(self, col_rng): """ helper function converts the range of column to column number ---------- """ firstCol, lastCol = re.split(':', col_rng.strip()) _, num1 = xl_cell_to_rowcol(firstCol + '1') _, num2 = xl_cell_to_rowcol(lastCol + '1') return num1, num2
def simple_table(worksheet, table_data, STYLES): start_pos = table_data['start'] crow, ccol = srow, scol = xl_cell_to_rowcol(start_pos) assert len(table_data['header_sizes'])== len(table_data['header']), "неправильное наполннеие таблицы" def_hstyle =table_data['header_style'] def_bstyle = get_style(STYLES, table_data['body_style']) hdata = table_data['header'] for n in range(len(hdata)): if type(hdata[n])==str: h_stl,title = def_hstyle, hdata[n] else: h_stl,title = hdata[n][1], hdata[n][0] hstl = get_style(STYLES, h_stl) # print(h_stl, title) draw_text_rect(worksheet, (crow, ccol),(table_data['header_sizes'][n],1), title, hstl) ccol+= table_data['header_sizes'][n] crow, ccol = srow+1, scol #draw_many_text_rect(worksheet, start_pos,dim, data, style, rdim = False) for datarow in table_data['data']: datarow = [int(y) if (type(y)==str and y.isdigit()) else y for y in datarow] draw_many_text_rect(worksheet, (crow, ccol),"1:1", datarow, def_bstyle, rdim = table_data['header_sizes']) crow+=1
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 write(sheet, values, first_cell='A1', date_format=None): """Write a 2-dimensional list to an Excel range. Parameters ---------- sheet : object An xlwt, openpyxl or xlsxwriter sheet object first_cell : str or tuple, optional Top-left corner of the Excel range where you want to write out the DataFrame. Can be a string like 'A1' or a row/col tuple like (1, 1), default is 'A1'. date_format : str, optional Only accepted if sheet is an xlwt sheet. By default, formats dates like so: 'MM/DD/YY' Returns ------- list A 2-dimensional list with the values of the Excel range """ # OpenPyXL if openpyxl and isinstance( sheet, (openpyxl.worksheet.worksheet.Worksheet, openpyxl.worksheet._write_only.WriteOnlyWorksheet)): assert date_format is None if not isinstance(first_cell, tuple): first_cell = openpyxl.utils.coordinate_to_tuple(first_cell) for i, row in enumerate(values): for j, value in enumerate(row): sheet.cell(row=first_cell[0] + i, column=first_cell[1] + j).value = value # XlsxWriter elif xlsxwriter and isinstance(sheet, xlsxwriter.worksheet.Worksheet): assert date_format is None if isinstance(first_cell, tuple): first_cell = first_cell[0] - 1, first_cell[1] - 1 else: first_cell = xl_cell_to_rowcol(first_cell) for r, row_data in enumerate(values): sheet.write_row(first_cell[0] + r, first_cell[1], row_data) # xlwt elif xlwt and isinstance(sheet, xlwt.Worksheet): if date_format is None: date_format = 'MM/DD/YY' date_format = xlwt.easyxf(num_format_str=date_format) if isinstance(first_cell, tuple): first_cell = (first_cell[0] - 1, first_cell[1] - 1) else: first_cell = xlwt.Utils.cell_to_rowcol2(first_cell) for i, row in enumerate(values): for j, cell in enumerate(row): if isinstance(cell, (dt.datetime, dt.date)): sheet.write(i + first_cell[0], j + first_cell[1], cell, date_format) else: sheet.write(i + first_cell[0], j + first_cell[1], cell) else: raise TypeError(f"Couldn't handle {type(sheet)}")
def write_global_formula(self, c, tremblCell): (row, col) = xl_cell_to_rowcol(c) writingCell = xl_rowcol_to_cell(row, col + 1) formulaGlobal = '={}/{}'.format(c, tremblCell) self.worksheet.write_formula(writingCell, formulaGlobal, self.formats.fmt_num_rel) return writingCell
def getNumberFromCellsRange(self, cell_range): """Get Number from Cells Range Args: cell_range: "B2:B10" Returns: tuple(int, int): (start_row, start_col), (end_row, end_col) """ # parce the cell range input start_cell, end_cell = cell_range.split(":") (start_row, start_col) = utility.xl_cell_to_rowcol(start_cell) (end_row, end_col) = utility.xl_cell_to_rowcol(end_cell) return (start_row, start_col), (end_row, end_col)
def draw_kalendar_plan(worksheet, start_pos, DATA, STYLES): def style(stl = 't10:c:_:1'): return get_style_(STYLES, stl) def draw_kalendar_plan_header(): draw_many_text_rect(worksheet,(sy, sx+1),"4:1", MONTH, style(), (4,4,5,4,5,4,4, 4,5, 4,5,4)) draw_many_text_rect(worksheet,(sy+1,sx+1, ),"1:1", range(1,53), style()) worksheet.merge_range(sy, sx, sy+1, sx, "Курс", style('t10:c:r:1')) def draw_kalendar_plan_body(course,week): worksheet.write(sy+2+nrow,sx,course,style()) draw_many_text_rect(worksheet, (sy+2+nrow, sx+1),"1:1", week, style()) #sx, sy = dec_int(start_pos) if type(start_pos) == str else start_pos sy,sx = xl_cell_to_rowcol(start_pos) #style_kalendar, style_kalendar_bold, style(), style_legend = styles #COURSE,WEEK_DATA = DATA MONTH = ('вересень','жовтень','листопад','грудень', 'січень' ,'лютий' ,'березень','квітень', 'травень' , 'червень','липень', 'серпень') LEGEND = """Позначення: Т - теоретичне навчання; С - екзаменаційна сесія;\ П - практика; К - канікули; ДЕ - складання державного екзамену;\ ДП - захист дипломного проекту (роботи), дп - виконання дипломного проекту (роботи)""" draw_kalendar_plan_header() nrow = 0 for course, week in DATA: if len(week)<52: week = week + ' '*(52-len(week)) draw_kalendar_plan_body(course, week) nrow+=1 worksheet.write(sy+2+len(DATA),sx+1,LEGEND, style('t9:l:_'))
def _read_sheet(xl_filename, xl_sheet, anchor=None): """Read data from Excel sheet and yield it as a stream of datapoints.""" # determine a start position wb = xlrd.open_workbook(xl_filename) cur_sheet = wb.sheet_by_name(xl_sheet) # starting position if not anchor: r0,c0 = seek_origin(cur_sheet) else: r0,c0 = xl_cell_to_rowcol(anchor) # strat year is two rows up from r0,c0 start_year = int(cur_sheet.cell(r0-2,c0).value.split()[0]) try: assert start_year == 2009 or start_year == 2010 except: raise ValueError(xl_filename) # ValueError: xls\info_stat_01_2016\01 промышленность\047-049 рыба живая, свежая или охлажденная.xls # starts with 2010 #extraction first column data raw_regions = cur_sheet.col_values(0, start_rowx=r0, end_rowx=None) regions = filter(None, [filter_region_name(r) for r in raw_regions]) #extracting data left-right, up-down for r, region in enumerate(regions,start=r0): for m, c in enumerate(range(c0,cur_sheet.ncols)): year, month = start_year + m//12, m%12 + 1 #cur_sheet.cell(r,c).value may be float or str - must filter value val = filter_cellvalue(cur_sheet.cell(r,c).value) yield (val,region,yearmon(year, month))
def format_columns(self, sheet): """Writes columns from sheet.column_formats[]""" for item in sheet.column_formats: # Add any worksheet-wide formatting, if it exists if sheet.worksheet_format: temp_dict = {key: sheet.worksheet_format[key] for key in \ sheet.worksheet_format} temp_dict.update(item[3]) sheet.set_column(item[0], item[1], item[2], self.add_format(temp_dict), item[4]) else: sheet.set_column(item[0], item[1], item[2], self.add_format(item[3]), item[4]) if item[3] and item[5]: # If there's a format, and you should override other formats: for key in sheet.cells_to_write: row, col = xl_cell_to_rowcol(key) if item[0] <= col <= item[1]: # Priority of formats: cell > row updated_format = {key: item[3][key] for key in item[3]} grrr = {d_key: sheet.cells_to_write[key][1][d_key] \ for d_key in sheet.cells_to_write[key][1]} sheet.cells_to_write[key] = ( sheet.cells_to_write[key][0], updated_format.update(grrr))
def _read_sheet(xl_filename, xl_sheet, anchor=None): """Read data from Excel sheet and yield it as a stream of datapoints.""" # determine a start position wb = xlrd.open_workbook(xl_filename) cur_sheet = wb.sheet_by_name(xl_sheet) # starting position if not anchor: r0, c0 = seek_origin(cur_sheet) else: r0, c0 = xl_cell_to_rowcol(anchor) # strat year is two rows up from r0,c0 start_year = int(cur_sheet.cell(r0 - 2, c0).value.split()[0]) try: assert start_year == 2009 or start_year == 2010 except: raise ValueError(xl_filename) # ValueError: xls\info_stat_01_2016\01 промышленность\047-049 рыба живая, свежая или охлажденная.xls # starts with 2010 #extraction first column data raw_regions = cur_sheet.col_values(0, start_rowx=r0, end_rowx=None) regions = filter(None, [filter_region_name(r) for r in raw_regions]) #extracting data left-right, up-down for r, region in enumerate(regions, start=r0): for m, c in enumerate(range(c0, cur_sheet.ncols)): year, month = start_year + m // 12, m % 12 + 1 #cur_sheet.cell(r,c).value may be float or str - must filter value val = filter_cellvalue(cur_sheet.cell(r, c).value) yield (val, region, yearmon(year, month))
def _read_sheet_q(xl_filename, xl_sheet, anchor=None): """Read data from Excel sheet and yield it as a stream of datapoints.""" # determine a start position wb = xlrd.open_workbook(xl_filename) cur_sheet = wb.sheet_by_name(xl_sheet) # starting position if not anchor: r0,c0 = seek_origin(cur_sheet) else: r0,c0 = xl_cell_to_rowcol(anchor) # strat year is two rows up from r0,c0 start_year = int(cur_sheet.cell(r0-2,c0).value.split()[0]) try: assert start_year == 2009 or start_year == 2010 except: raise ValueError(xl_filename) #extraction first column data raw_regions = cur_sheet.col_values(0, start_rowx=r0, end_rowx=None) regions = filter(None, [filter_region_name(r) for r in raw_regions]) #extracting data left-right, up-down for r, region in enumerate(regions,start=r0): for m, c in enumerate(range(c0,cur_sheet.ncols)): year, quarter = start_year + m//4, m%4 + 1 print("year, quarter ", year, quarter ) #cur_sheet.cell(r,c).value may be float or str - must filter value val = filter_cellvalue(cur_sheet.cell(r,c).value) yield (val,region,yearmon(year, 1+3*(quarter-1))) yield (val,region,yearmon(year, 2+3*(quarter-1))) yield (val,region,yearmon(year, 3+3*(quarter-1)))
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 format_sheet(self, sheet, template_sheet): sheet_data = sheet.get_all_values() rows_num = self.get_sheet_rows_num(sheet) header = sheet_data[0] for cell in header: col_formatting = self.get_col_formatting(template_sheet, cell) first_row = col_formatting[0] col_coord = xl_cell_to_rowcol(first_row) time.sleep(2) last_row = xl_rowcol_to_cell(rows_num - 1, col_coord[1]) gs_formatting.format_cell_range(sheet, first_row + ':' + last_row, col_formatting[1])
def __init__(self, workbook, worksheet, cell_str=None, row=0, column=0, columns_colors=None): self._formats = {} self.workbook = workbook self.worksheet = worksheet self.default_format = {} if cell_str: self.row, self.column = xl_cell_to_rowcol(cell_str) else: self.row = row self.column = column self.start_column = self.column self.start_row = self.row self.column_colors = columns_colors or {}
def draw_predm_table_header(worksheet, start_pos, DATA, STYLES): def draw_header( cur_row, cur_col, header_data): for item in header_data: coord, text, style = item if type(item)!= list else item[0] sx,sy = dec_int(coord) draw_text_rect(worksheet, (cur_row, cur_col), (sx, sy), text, get_style(STYLES,style)) if type(item)== list: draw_header(cur_row+sy,cur_col, item[1:]) cur_col += sx start_row, start_col = xl_cell_to_rowcol(start_pos) worksheet.set_row(start_row+3, 64) draw_header(start_row, start_col, DATA) return start_row+3
def write_deviation_formula_per(self, col, c1, c2, format_adapter=None): (row, col) = xl_cell_to_rowcol(c2) writing_cell = xl_rowcol_to_cell(row, col + 6) # formula_val_diff = '=IF({}=0, 0, ({}-{})/{})'.format(c2, c1, c2, c2) formula_val_diff = '=IF(AND({}=0, {}=0), NA(), ({}-{})/{})'.format( c2, c1, c1, c2, c2) cell_format = self.formats.fmt_num_rel if format_adapter is not None: cell_format = format_adapter(cell_format) # cell_format = format_adapter.append_borders(f, is_left=(0 == h_idx), is_right=(len(h) - 1 == h_idx)) \ self.worksheet.write_formula(writing_cell, formula_val_diff, cell_format)
def xl2coords(rng): """Translate an excel range string to matrix coordinates. Parameters ---------- rng : str An excel range string. Returns ------- tuple length 2 tuple of ints with appropriate coordinates. """ return xl_cell_to_rowcol(rng)
def metrics_handler(df, c): metrics = dict() for i in c.keys(): address = xl_cell_to_rowcol(c[i]) value = df.iloc[address[0], address[1]] _locals = locals() if "%>%" in c[i]: transform_steps = c[i].split("%>%")[1:] for ts in transform_steps: exec( 'value = list(map(lambda x: {}, ["{}"]))[0]'.format( ts, value), globals(), _locals) metrics[i] = _locals['value'] return metrics
def write_data_single_col(worksheet): worksheet.set_column('A:A', 15) worksheet.write_string('A1', 'abs values', cell_formats.fmt_header) worksheet.write_number('A2', -5.0) worksheet.write_number('A3', 0.0) worksheet.write_number('A4', 1.0) worksheet.write_number('A5', 15.0) worksheet.write_string('B1', '%', cell_formats.fmt_header) worksheet.write_number('B2', -.01, cell_formats.fmt_percent) worksheet.write_number('B3', 0.0, cell_formats.fmt_percent) worksheet.write_number('B4', .06, cell_formats.fmt_percent) worksheet.write_number('B5', .45, cell_formats.fmt_percent) (row, last_col) = xl_cell_to_rowcol('B5') return 'A2:A5', 'B2', last_col
def _general_formating_range(self, rng, **kwargs): startCell, endCell = re.split(':', rng.strip()) minRow, minCol = xl_cell_to_rowcol(startCell) maxRow, maxCol = xl_cell_to_rowcol(endCell) rows = self.ws.iter_rows(min_row=minRow + 1, max_row=maxRow + 1, min_col=minCol + 1, max_col=maxCol + 1) for row in rows: for cell in row: if '_set_all_borders' in kwargs: border = Border( left=Side(border_style=kwargs['_border_type'], color='373636'), right=Side(border_style=kwargs['_border_type'], color='373636'), top=Side(border_style=kwargs['_border_type'], color='373636'), bottom=Side(border_style=kwargs['_border_type'], color='373636')) cell.border = border else: pass
def draw_many_text_rect(worksheet, start_pos,dim, data, style, rdim = False): if type(start_pos) == str and ":" not in start_pos: sy,sx = xl_cell_to_rowcol(start_pos) else: sx, sy = dec_int(start_pos) if type(start_pos) == str else start_pos[::-1] w, h = dec_int(dim) if type(dim) == str else dim if not rdim: w = [w] * len(data) else: w = rdim for n,title in enumerate(data): draw_text_rect(worksheet, (sy,sx),(w[n],h), title, style) sx+=w[n]
def insert_text_rows(self, objeto): """insert_text_rows: Inserta filas con texto.""" self.info("Objetos text_rows") row, col = xl_cell_to_rowcol(objeto.get("at")) textos = objeto.get("text", None) format = self.formatos.get(objeto.get("format")) for i, t in enumerate([self.get_string_from_template(t) for t in textos], 0): at = xl_range(row, col + i, row, col + i) if t: self.active_worksheet.write(at, t, format) else: self.active_worksheet.write_blank(at, '', format) self._setup_boundaries_rc(row=row, col=col + i)
def _convert_cell(self, row, col, fromtype, totype, rowabs=False, colabs=False): if fromtype == totype: return row, col if fromtype == self.indextypes.xlscell: row, col = xlsxutils.xl_cell_to_rowcol(row, col, rowabs, colabs) fromtype = self.indextypes.xlsindex row = self._convert_rowindex(row, fromtype, totype) col = self._convert_colindex(col, fromtype, totype) if totype == self.indextypes.xlscell: row, col = xlsxutils.xl_rowcol_to_cell(row, col, rowabs, colabs) return row, col
def preparsing(self, source, config): ignore = config['from_ignore_cols'] copy_from = source.iloc[ range_to_rows(config['from'], len(source)), range_to_cols(config['from'], len(source.columns))] keep_col_indexes = [ i for i in range(len(copy_from.columns)) if i not in [(j if j >= 0 else len(copy_from.columns) + j) for j in ignore] ] copy_from = copy_from.iloc[:, keep_col_indexes] copy_to_cell = xl_cell_to_rowcol(config['to']) for r in range(copy_to_cell[0], copy_to_cell[0] + len(copy_from)): for c in range(copy_to_cell[1], copy_to_cell[1] + len(copy_from.columns)): source.iloc[r, c] = copy_from.iloc[r - copy_to_cell[0], c - copy_to_cell[1]] return source
def format_rows(self, sheet): # row_formats = [(row, height, format, options, override_cell_format), ...] for item in sheet.row_formats: if sheet.worksheet_format: temp_dict = {key: sheet.worksheet_format[key] for key in \ sheet.worksheet_format} temp_dict.update(item[2]) sheet.set_row(item[0], item[1], self.add_format(temp_dict), item[3]) else: sheet.set_row(item[0], item[1], self.add_format(item[2]), item[3]) if item[2] and item[4]: # Look for any cells that need their formatting augmented for key in sheet.cells_to_write: row, col = xl_cell_to_rowcol(key) if row == item[0]: if sheet.cells_to_write[key][1]: sheet.cells_to_write[key][1].update(item[2]) else: sheet.cells_to_write[key] = ( sheet.cells_to_write[key][0], item[2])
def insert_table(self, objeto): """insert_table: Inserta una tabla.""" self.info("Objetos table") source = objeto["source"] ds = self.datasources.get(source["datasource"]) if ds is None: self.error("No se ha definido el datasource {0}".format(source["datasource"])) else: rsnum = source.get("recordset_index", 1) - 1 data = ds.newdata(rsnum) row, col = xl_cell_to_rowcol(objeto.get("at")) self._setup_boundaries_rc(row, col) fmt_header = self.formatos.get(objeto.get("header_format")) header = [] for each in data["colnames"]: header.append({"header": each, "header_format": fmt_header}) self.active_worksheet.add_table(row, col, row + len(data["rows"]), col + len(data["colnames"]) - 1, { 'data': data["rows"], 'style': objeto.get("style", "Table Style Medium 2"), 'total_row': objeto.get("total_row", 1), 'autofilter': objeto.get("autofilter", False), 'columns': header, }) self._setup_boundaries_rc(row + len(data["rows"]), col + len(data["colnames"]) - 1)
def _setup_boundaries_at(self, at): row, col = xl_cell_to_rowcol(at) self._setup_boundaries_rc(row, col)
def convertToRowsColumns(self, ref): thistuple = xl_cell_to_rowcol(ref) return thistuple
# Insert the chart into the worksheet. wsk.insert_chart('C1', chart) wbk.close() str1 = input('Enter a word to be serached: ') f = 0 wb = load_workbook(r'B:\w2.xlsx') sheet = wb['Sheet1'] for i in range(1, sheet.max_row + 1): for j in range(sheet.max_column): if (str1.lower() == sheet[i][j].value): print(sheet[i][j].value, ':', sheet[i][j + 1].value) celli = xl_rowcol_to_cell((i - 1), j) print(celli) cellj = xl_rowcol_to_cell((i - 1), (j + 1)) print(cellj) (row1, col1) = xl_cell_to_rowcol(celli) #print(row1,col1) sheet['A1'].fill = openpyxl.styles.PatternFill( 'solid', openpyxl.styles.colors.GREEN) f = 1 break if (f == 0): print('Word not found') wb.save('w2.xlsx') wb.close() f1.close()
def modelo_paneles(datos,precio,inicio,final,cliente,mes,nombre,workbook): worksheet = workbook.add_worksheet('Modelo_paneles') bold_1 = workbook.add_format({'bold': True,'font_size':16}) bold_2 = workbook.add_format({'bold': True,'align':'center'}) centrado_model = workbook.add_format({'align':'center'}) dinero_centrado = workbook.add_format({'num_format': '$ #,### ','align':'center'}) text_adjust = workbook.add_format() text_adjust.set_text_wrap() background_lime = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_lime.set_pattern(1) # This is optional when using a solid fill. background_lime.set_bg_color('#BFD19F') background_brown = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_brown.set_pattern(1) # This is optional when using a solid fill. background_brown.set_bg_color('#D6C8BF') background_silver1 = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_silver1.set_pattern(1) # This is optional when using a solid fill. background_silver1.set_bg_color('#D2D3D4') background_silver2 = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_silver2.set_pattern(1) # This is optional when using a solid fill. background_silver2.set_bg_color('#D2D3D4') background_silver2.set_text_wrap() background_cyan = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_cyan.set_pattern(1) # This is optional when using a solid fill. background_cyan.set_bg_color('#CCE4ED') background_cyan2 = workbook.add_format({'bold': True,'align':'center','font_size':14}) background_cyan2.set_pattern(1) # This is optional when using a solid fill. background_cyan2.set_bg_color('#CCE4ED') background_cyan2.set_text_wrap() worksheet.set_column(1, 8, 20) # Width of columns set to 20. worksheet.set_column(9,19,15) worksheet.write('B2','Modelo de ahorros comparativos con paneles solares',bold_1) worksheet.write('B3','USO EXCLUSIVO DE FINDERO O SUS CLIENTES') worksheet.write('B4','Creado por Findero el 14 de Septiembre del 2019') worksheet.write('B7','Fechas importantes',background_brown) worksheet.write('B8','Fecha de implementación de medidas',text_adjust) worksheet.write('B9','Fecha inical de bimestre después de implementación',text_adjust) worksheet.write('B10','Días que cubre el recibo',centrado_model) worksheet.write('C7','Día(s)',background_brown) worksheet.write_blank('C8', None,centrado_model) worksheet.write_blank('C9', None,centrado_model) worksheet.write_number('C10',60,centrado_model) worksheet.write('B14','Resultados',bold_2) worksheet.write('B15','Ahorro total con Findero') worksheet.write('B16','Ahorro con paneles solares') worksheet.write('B17','Ganancia/Pérdida con paneles solares') worksheet.write('B18','Ganancia/Pérdida con Findero') worksheet.write('B20','TABLA COMPARATIVA',bold_1) worksheet.write('B21','Bimestre a partir de intervención de ahorro',background_cyan2) worksheet.write('C21','Año',background_cyan) worksheet.write('D21','Consumo',background_cyan) worksheet.write('E21','Tipo de consumo',background_cyan) worksheet.write('F21','Promedio de consumo',background_cyan2) worksheet.write('G21','Tipo de tarifa',background_cyan) worksheet.write('H21','Costo aproximado de la energía',background_cyan2) worksheet.write('B22','Bimestre -6',centrado_model) worksheet.write('B23','Bimestre -5',centrado_model) worksheet.write('B24','Bimestre -4',centrado_model) worksheet.write('B25','Bimestre -3',centrado_model) worksheet.write('B26','Bimestre -2',centrado_model) worksheet.write('B27','Bimestre -1',centrado_model) worksheet.write('B28','Bimestre 0',centrado_model) worksheet.write('B29','Bimestre 1',centrado_model) worksheet.write('B30','Bimestre 2',centrado_model) worksheet.write('B31','Bimestre 3',centrado_model) worksheet.write('B32','Bimestre 4',centrado_model) worksheet.write('B33','Bimestre 5',centrado_model) worksheet.write('B34','Bimestre 6',centrado_model) id_cero = xl_cell_to_rowcol('C22')[0]+1 for j in range(28-22+1): worksheet.write_number('C'+str(id_cero+j),0,centrado_model) id_uno = xl_cell_to_rowcol('C29')[0]+1 for j in range(34-29+1): worksheet.write_number('C'+str(id_uno+j),1,centrado_model) id_consumo = xl_cell_to_rowcol('D22')[0]+1 for j in range(27-22+1): worksheet.write_blank('D'+str(id_consumo+j), None,centrado_model) worksheet.write('D28','=ROUND($F$27-$G$8*(C9-C8)/C10,0)',centrado_model) id_resta = xl_cell_to_rowcol('D29')[0]+1 for j in range(34-29+1): worksheet.write('D'+str(id_resta+j),'=ROUND($F$27-$G$8,0)',centrado_model) id_real = xl_cell_to_rowcol('E22')[0]+1 for j in range(27-22+1): worksheet.write('E'+str(id_real+j),'Real',centrado_model) id_estim = xl_cell_to_rowcol('E28')[0]+1 for j in range(34-28+1): worksheet.write('E'+str(id_estim+j),'Estimado',centrado_model) id_NA = xl_cell_to_rowcol('F22')[0]+1 for j in range(26-22+1): worksheet.write('F'+str(id_NA+j),'NA',centrado_model) id_prom = xl_cell_to_rowcol('F27')[0]+1 for j in range(34-27+1): worksheet.write('F'+str(id_prom+j),'=ROUND(AVERAGE(D'+str(id_prom-5+j)+':D'+str(id_prom+j)+'),0)',centrado_model) id_tot = xl_cell_to_rowcol('G22')[0]+1 for j in range(34-22+1): worksheet.write('G'+str(id_tot+j),'=IF(F'+str(id_tot+j)+'>499,"DAC",1)',centrado_model) worksheet.write('H'+str(id_tot+j),'=IF(G'+str(id_tot+j)+'="DAC",D'+str(id_tot+j)+'*5.55872+250.35,0.94772*149+1.14608*129+(D'+str(id_tot+j)+'-280)*3.3524)',dinero_centrado) worksheet.merge_range('F6:H6','Intervención Findero',background_lime) worksheet.write('F7','Variable',background_lime) worksheet.write('G7','Valor',background_lime) worksheet.write('H7','Unidades',background_lime) worksheet.write('F8','Ahorro estimado mínimo',centrado_model) worksheet.write('F9','Costo de medidas de ahorro',centrado_model) worksheet.write('F10','Costo de servicio',centrado_model) worksheet.write('F11','Bimestralidades Findero',centrado_model) worksheet.write('G8','=ROUND(Ahorro!D9,0)',centrado_model) worksheet.write_blank('G9',None,dinero_centrado) worksheet.write_number('G10',7800,centrado_model) worksheet.write('G11','=G10*1.12/6',centrado_model) worksheet.write('H8', 'KWh',centrado_model) worksheet.write('H9','$/medidas de ahorro',centrado_model) worksheet.write('H10','$/servicio',centrado_model) worksheet.write('H11','$/bimestre/servicio',centrado_model) worksheet.merge_range('J6:S6','Intervención paneles solares',background_silver1) worksheet.write('J7','Número de paneles solares',background_silver2) worksheet.write('K7','Precio de paneles solares',background_silver2) worksheet.write('L7','Generación bimestral',background_silver2) worksheet.write('M7','Bimestralidad (12 meses)',background_silver2) worksheet.write('N7','Bimestralidad (18 meses)',background_silver2) worksheet.write('O7','Bimestralidad (24 meses)',background_silver2) worksheet.write('P7','Bimestralidad (36 meses)',background_silver2) worksheet.write('Q7','Bimestralidad (60 meses)',background_silver2) worksheet.write('R7','Bimestralidad (84 meses)',background_silver2) worksheet.write('S7','Contratos de largo plazo',background_silver2) id_mens = xl_cell_to_rowcol('M8')[0]+1 for j in range(18-8+1): worksheet.write('M'+str(id_mens+j),'=-PMT(0,12/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write('N'+str(id_mens+j),'=-PMT(0,18/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write('O'+str(id_mens+j),'=-PMT(0,24/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write('P'+str(id_mens+j),'=-PMT(0.092/6,36/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write('Q'+str(id_mens+j),'=-PMT(0.092/6,60/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write('R'+str(id_mens+j),'=-PMT(0.092/6,84/2,K'+str(id_mens+j)+')',dinero_centrado) worksheet.write_number('J8',4,centrado_model) id_cmplt = xl_cell_to_rowcol('K8')[0]+1 for j in range(18-8+1): worksheet.write('K'+str(id_cmplt+j),'=J'+str(id_cmplt+j)+'*12500+6500',dinero_centrado)#Pendiente e intercepto de regresión lineal con datos Enlight worksheet.write('L'+str(id_cmplt+j),'=ROUND((J'+str(id_cmplt+j)+'*330*4.1/1.2)*60/1000,1)',centrado_model)#Numero de modulos por potencia de cada uno por mínima insolacion solar promedio por 60 días del bimestre id_panel = xl_cell_to_rowcol('J9')[0]+1 for j in range(18-9+1): worksheet.write('J'+str(id_panel+j),'=J'+str(id_panel+j-1)+'+2',centrado_model)
def desciframiento(datos,precio,inicio,final,cliente,mes,nombre,workbook,num_datos): titulos = ['Consumo (kWh)', 'Gasto', 'Ubicación', 'Equipo', 'Proporción (%)','Consumo (kWh)', 'Gasto','Gasto anual','Potencia (W)','Tiempo de uso','Hrs semana', 'Notas'] anchos = [12,12,15,19,15,15,15,15,15,15,15,80,10] celda_titulo = 'A1' celdas = 15+len(celdas_fugas) filas = len(datos.keys()) # bold_1 = workbook.add_format({'bold': True,'font_size':12}) bold_2 = workbook.add_format({'bold': True,'font_size':15}) bold_3 = workbook.add_format({'bold': True,'bg_color':'#E1E4EB','align':'center','border':1}) columna_gris = workbook.add_format({'bold': True,'bg_color':'#F2F2F2','border':1}) columna_blanca_1 = workbook.add_format({'align':'center','num_format': '0.0 %','border':1}) columna_blanca_2 = workbook.add_format({'align':'center','num_format': '#','border':1}) columna_blanca_3 = workbook.add_format({'align':'center','num_format': '$ #,### ','border':1}) columna_blanca_notas = workbook.add_format({'align':'left','num_format': '#','border':1}) columna_blanca_vacia = workbook.add_format({'align':'center','num_format': '#','border':1}) dinero_1 = workbook.add_format({'align':'center','num_format': '$ #,###.## ','border':1,'align':'center'}) centrado = workbook.add_format({'align': 'center', 'valign': 'center'}) worksheet = workbook.add_worksheet('Desciframiento') worksheet.write(celda_titulo,'Desciframiento del consumo de ' + nombre + ' (cifras bimestrales)', bold_2) worksheet.write('H1',f'Se analizaron {num_datos:,} datos',centrado) for idx,titulo in enumerate(titulos): skip = 0 reset = 0 if idx>1: skip = 4 reset = 2 worksheet.set_column(idx, idx, anchos[idx]) worksheet.write(xl_rowcol_to_cell(3+skip,idx-reset+2),titulo, bold_3) for i in range(0,celdas): worksheet.write_blank(xl_rowcol_to_cell(8+i,2),'', columna_gris) worksheet.write_blank(xl_rowcol_to_cell(8+i,3),'', columna_gris) worksheet.write_blank(xl_rowcol_to_cell(8+i,4),'', columna_blanca_1) worksheet.write_blank(xl_rowcol_to_cell(8+i,8),'', columna_blanca_2) worksheet.write_blank(xl_rowcol_to_cell(8+i,9),'', columna_blanca_2) worksheet.write_blank(xl_rowcol_to_cell(8+i,10),'', columna_blanca_2) worksheet.write_blank(xl_rowcol_to_cell(8+i,11),'', columna_blanca_notas) for i in range(0,celdas+4): if i in [1,5,9,13,17]: worksheet.write_formula(xl_rowcol_to_cell(8+i,5),'', columna_blanca_vacia) worksheet.write_formula(xl_rowcol_to_cell(8+i,6),'', columna_blanca_vacia) else: worksheet.write_formula(xl_rowcol_to_cell(8+i,5),'=' + xl_rowcol_to_cell(8+i,4) + '*$C$5', columna_blanca_2) worksheet.write_formula(xl_rowcol_to_cell(8+i,6),'=' + xl_rowcol_to_cell(8+i,4) + '*$D$5', columna_blanca_3) worksheet.write_formula(xl_rowcol_to_cell(8+i,7),'=' + xl_rowcol_to_cell(8+i,6) + '*6', columna_blanca_3) worksheet.write('F4','Tarifa DAC:',bold_3) worksheet.write_number('G4', 5.333, dinero_1) worksheet.write_formula('D5','=C5*G4',columna_blanca_3) worksheet.write_formula('C5','=Detalles!'+celda_consumo_bim,columna_blanca_2) worksheet.write('D9','Refrigerador', columna_gris) worksheet.write('M9','Cava [m3]:',columna_blanca_1) worksheet.write_formula('N9','IF(F9>162,((F9-162.912)/11.974)*0.0283168,0)',columna_blanca_2) worksheet.write('D11','Bomba de agua', columna_gris) worksheet.write('D12','Centro de lavado', columna_gris) worksheet.write('D13',"Tv's", columna_gris) worksheet.write('M13','TV ["]:',columna_blanca_1) worksheet.write_formula('N13','4.9012*(I13^0.4627)',columna_blanca_2) worksheet.write_formula('E11',crear_vlookup(filas,'Bomba'), columna_blanca_1) worksheet.write_formula('E12',crear_vlookup(filas,'Lavado'), columna_blanca_1) worksheet.write_formula('E13',crear_vlookup(filas,'TV'), columna_blanca_1) worksheet.write('D15',"Pendiente 1", columna_gris) worksheet.write('D16',"Pendiente 2", columna_gris) worksheet.write('D17',"Pendiente 3", columna_gris) global porcentajes_fugas porcentajes_fugas = [] for i, celda in enumerate(celdas_fugas): celda_ = xl_cell_to_rowcol(celda) celda_porcentaje = (celda_[0],celda_[1]+1) celda_porcentaje = xl_rowcol_to_cell(celda_porcentaje[0],celda_porcentaje[1]) celda_circuito = (celda_[0]-5,celda_[1]+1) celda_circuito = xl_rowcol_to_cell(celda_circuito[0],celda_circuito[1]) celda_notas = (celda_[0]+4,celda_[1]-1) celda_notas = xl_rowcol_to_cell(celda_notas[0],celda_notas[1]) worksheet.write_formula(xl_rowcol_to_cell(18+i,4), '=Detalles!'+celda_porcentaje, columna_blanca_1) worksheet.write(xl_rowcol_to_cell(18+i,3),'=CONCATENATE("Fuga ",MID(Detalles!'+celda+',FIND(" ",Detalles!'+celda+')+1,256))', columna_gris) worksheet.write(xl_rowcol_to_cell(18+i,2),'=CONCATENATE("C. ",Detalles!'+celda_circuito+')', columna_gris) worksheet.write(xl_rowcol_to_cell(18+i,7+4),'=Detalles!'+celda_notas,columna_blanca_notas) porcentajes_fugas.append(xl_rowcol_to_cell(18+i,4)) # for i in range(5): # row_fuga = 'ROW(INDIRECT(MID(FORMULATEXT('+xl_rowcol_to_cell(14+i,4)+'),FIND("!",FORMULATEXT('+xl_rowcol_to_cell(14+i,4)+'))+1,256)))' # column_fuga = 'COLUMN(INDIRECT(MID(FORMULATEXT('+xl_rowcol_to_cell(14+i,4)+'),FIND("!",FORMULATEXT('+xl_rowcol_to_cell(14+i,4)+'))+1,256)))-2' # argumento = 'FORMULATEXT(INDIRECT(ADDRESS('+row_fuga+','+column_fuga+',,,"Detalles")))' # worksheet.write_formula(xl_rowcol_to_cell(14+i,3),'IFERROR(CONCATENATE("Fuga ",'+'MID(LEFT('+argumento+',FIND("*",'+argumento+')-1),FIND("=",'+argumento+')+1,LEN('+argumento+'))),"Fuga")', columna_gris) worksheet.write(xl_rowcol_to_cell(18+i+2,3),'Luces', columna_gris) worksheet.write(xl_rowcol_to_cell(18+i+3,3),'Cómputo y cargadores', columna_gris) worksheet.write(xl_rowcol_to_cell(18+i+4,3),'Sin Identificar', columna_gris) worksheet.write_formula(xl_rowcol_to_cell(18+i+2,4),crear_vlookup(filas,'Luces'), columna_blanca_1) worksheet.write_formula(xl_rowcol_to_cell(18+i+3,4),crear_vlookup(filas,'Computo'), columna_blanca_1) worksheet.write_formula(xl_rowcol_to_cell(18+i+4,4),crear_vlookup(filas,'Sin ID'), columna_blanca_1) worksheet.write(xl_rowcol_to_cell(8+celdas,3),'Total',bold_3) worksheet.write(xl_rowcol_to_cell(8+celdas+1,3),'Total de Fugas',bold_3) worksheet.write(xl_rowcol_to_cell(8+celdas+2,3),'Fugas atacables',bold_3) worksheet.write(xl_rowcol_to_cell(8+celdas+3,3),'Total de pendientes',bold_3) worksheet.write_formula(xl_rowcol_to_cell(8+celdas,4),'=SUM('+xl_rowcol_to_cell(8,4)+ ':'+xl_rowcol_to_cell(8+celdas-1,4)+ ')',columna_blanca_1) try: worksheet.write_formula(xl_rowcol_to_cell(8+celdas+1,4),'=SUM('+porcentajes_fugas[0]+ ':'+porcentajes_fugas[-1]+ ')',columna_blanca_1) except: pass worksheet.write_formula(xl_rowcol_to_cell(8+celdas+2,4),'='+xl_rowcol_to_cell(8+celdas+1,4)+'-40/C5' ,columna_blanca_1) worksheet.write_formula(xl_rowcol_to_cell(8+celdas+3,4),'=SUM(E15:E17)',columna_blanca_1) global fugas_atacables fugas_atacables = xl_rowcol_to_cell(8+celdas+2,5)
def make_excel(header_data, header_default_style, table_start_cell, table_data): """ header_data - итерабл таких элементов: [ ['A1:B2', '№'], ['R2', 'забраковано', (опции)], ] опции - набор свойств: * целое число: ширина header_default_style - набор свойств общих для ячеек, поддерживаются: * center * vcenter table_start_cell - ячейка с которой начинается рисование таблицы (вправо-вниз) table_data - итерабл строк, каждая из которых - тоже итерабл по ячейкам """ # Create an in-memory output file for the new workbook. output = io.BytesIO() # он всё равно будет юзать временные файлы, если не нужно - сделать 'in_memory' workbook = xlsxwriter.Workbook(output, {"default_date_format": "dd.mm.yyyy"}) worksheet = workbook.add_worksheet() header_def_style = set() header_def_format = None if header_default_style: header_def_style = set(header_default_style) h = {} if "center" in header_def_style: h["align"] = "center" if "vcenter" in header_def_style: h["valign"] = "vcenter" header_def_format = workbook.add_format(h) for celldata in header_data: try: cell, title, options = celldata except ValueError as _: cell, title = celldata options = [] # prepare options width = None for option in options: if isinstance(option, int): width = option # cell if ":" in cell: # merged worksheet.merge_range(cell, title, cell_format=header_def_format) else: worksheet.write(cell, title, header_def_format) # width if width: # имя столбца, если несколько то первого, несколько букв тоже может быть firstwcell = re.findall("[a-zA-Z]+", cell)[0] # 'AA1:AA2' -> ['AA', 'AA'] worksheet.set_column("%s:%s" % (firstwcell, firstwcell), width) table_start_row, table_start_col = xl_cell_to_rowcol(table_start_cell) for table_row in table_data: # worksheet.write_row(table_start_row, table_start_col, table_row, cell_format=None) col = table_start_col for token in table_row: worksheet.write(table_start_row, col, token) col += 1 table_start_row += 1 # close workbook & rewind buffer workbook.close() output.seek(0) return output
def name_to_col(name): _, index = xl_cell_to_rowcol("{}1".format(name)) return index