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 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 __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 _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
