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)
