How to use the xlsxwriter.utility.xl_rowcol_to_cell function in XlsxWriter
To help you get started, we’ve selected a few XlsxWriter examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
trailofbits / cb-multios / tools / tester.py View on Github 
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)# Conditionally format the unit price cell that contains the best price.
wks.conditional_format(row, unit_price_col, row, unit_price_col, {
'type': 'cell',
'criteria': '<=',
'value': xl_rowcol_to_cell(row, 7),
# This is the global data cell holding the minimum unit price for this part.
'format': wrk_formats['best_price']
})
# Enter the formula for the extended price = purch qty * unit price.
wks.write_formula(
row, ext_price_col,
'=iferror(if({purch_qty}="",{needed_qty},{purch_qty})*{unit_price},"")'.format(
needed_qty=xl_rowcol_to_cell(row, part_qty_col),
purch_qty=xl_rowcol_to_cell(row, purch_qty_col),
unit_price=xl_rowcol_to_cell(row, unit_price_col)),
wrk_formats['currency'])
# Conditionally format the extended price cell that contains the best price.
wks.conditional_format(row, ext_price_col, row, ext_price_col, {
'type': 'cell',
'criteria': '<=',
'value': xl_rowcol_to_cell(row, 8),
# This is the global data cell holding the minimum extended price for this part.
'format': wrk_formats['best_price']
})
# Finished processing distributor data for this part.
row += 1 # Go to next row.
# Sum the extended prices for all the parts to get the total cost from this distributor.
total_cost_col = start_col + columns['ext_price']['col']cell_value = cell_value and 'Oui' or 'Non'
cell_type = column_labels.get(column).get('type', '')
cell_format = column_labels.get(column).get(
'format', self.format_default)
need_to_write_summary_formula = \
is_sub_summary_section and cell_type == 'formula'
if is_sub_summary_section:
cell_format = self.format_table_bold
# Write formula
if need_to_write_summary_formula:
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)
# append cell to summary section info
self.summary_column_info[column].append(
xl_rowcol_to_cell(row_pos, col_pos)
)
else:
if column == 'date' and not is_sub_summary_section:
cell_format = self.format_table_date
cell_value = fields.Date.from_string(cell_value)
self.sheet.write(row_pos, col_pos, cell_value, cell_format)
row_pos += 1wks.conditional_format(
row, start_col + columns['purch']['col'],
row, start_col + columns['purch']['col'],
{
'type': 'cell',
'criteria': '>',
'value': xl_rowcol_to_cell(row, avail_qty_col),
'format': wrk_formats['order_too_much']
}
)
# Conditionally format the unit price cell that contains the best price.
wks.conditional_format(row, unit_price_col, row, unit_price_col, {
'type': 'cell',
'criteria': '<=',
'value': xl_rowcol_to_cell(row, 7),
# This is the global data cell holding the minimum unit price for this part.
'format': wrk_formats['best_price']
})
# Enter the formula for the extended price = purch qty * unit price.
wks.write_formula(
row, ext_price_col,
'=iferror(if({purch_qty}="",{needed_qty},{purch_qty})*{unit_price},"")'.format(
needed_qty=xl_rowcol_to_cell(row, part_qty_col),
purch_qty=xl_rowcol_to_cell(row, purch_qty_col),
unit_price=xl_rowcol_to_cell(row, unit_price_col)),
wrk_formats['currency'])
# Conditionally format the extended price cell that contains the best price.
wks.conditional_format(row, ext_price_col, row, ext_price_col, {
'type': 'cell',ben_col = 8
elif benefit_type == 'Logsum (No CEM)':
sum_indices = [sum_indices[1]] + [sum_indices[2]] + sum_indices[5:]
ben_col = 10
else:
sum_indices = sum_indices[3:]
ben_col = 11
worksheet.write(TABLE_HEADER_ROW-5, ben_col, benefit_type, format_bc_header)
format_bc_money = workbook.add_format({'bg_color':'#92D050','bold':True,
'num_format':'_(\$* #,##0.0"M"_);_(\$* (#,##0.0"M");_(\$* "-"??_);_(@_)'})
format_bc_ratio = workbook.add_format({'bg_color':'#92D050','bold':True,'num_format':'0.00'})
worksheet.write(TABLE_HEADER_ROW-4, ben_col, "=SUM(%s)" % str(",").join(sum_indices), format_bc_money)
worksheet.write(TABLE_HEADER_ROW-3, ben_col, "=%s" % ANNUAL_COSTS_CELL, format_bc_money)
worksheet.write(TABLE_HEADER_ROW-2, ben_col, "=%s/%s" % (xl_rowcol_to_cell(TABLE_HEADER_ROW-4, ben_col),
xl_rowcol_to_cell(TABLE_HEADER_ROW-3, ben_col)),
format_bc_ratio)
worksheet.set_column(ben_col,ben_col,15.0)
worksheet.set_column(0,0,40.0)
worksheet.set_column(1,8,13.0)
worksheet.set_column(5,5,2.0)
worksheet.set_column(7,7,2.0)
worksheet.set_column(9,9,2.0)
# THIS IS COBRA
format_red = workbook.add_format({'font_color':'white','bg_color':'#C0504D','align':'right','bold':True})
for row in range(1,9):
for col in range(10,13):
worksheet.write(row,col,"",format_red)
worksheet.write(1,10,"co",format_red)),
),
formats['entry_total_utilization'],
entry.total_utilization_a,
)
if report.split_by_field_name:
sheet.write_formula(
rows_emitted,
columns['utilization_b'],
'=%s/%s' % (
xl_rowcol_to_cell(
rows_emitted,
columns['unit_amount_b']
),
xl_rowcol_to_cell(
block_row_index,
columns['capacity']
),
),
formats['entry_total_utilization'],
entry.total_utilization_b,
)
sheet.write_number(
rows_emitted,
columns['unit_amount_a'],
self._convert_time_num_format(
report,
entry.total_unit_amount_a
),
formats['entry_total_amount']
))
# Conditionally format the unit price cell that contains the best price.
wks.conditional_format(row, unit_price_col, row, unit_price_col, {
'type': 'cell',
'criteria': '<=',
'value': xl_rowcol_to_cell(row, 7),
# This is the global data cell holding the minimum unit price for this part.
'format': wrk_formats['best_price']
})
# Enter the formula for the extended price = purch qty * unit price.
wks.write_formula(
row, ext_price_col,
'=iferror(if({purch_qty}="",{needed_qty},{purch_qty})*{unit_price},"")'.format(
needed_qty=xl_rowcol_to_cell(row, part_qty_col),
purch_qty=xl_rowcol_to_cell(row, purch_qty_col),
unit_price=xl_rowcol_to_cell(row, unit_price_col)),
wrk_formats['currency'])
# Conditionally format the extended price cell that contains the best price.
wks.conditional_format(row, ext_price_col, row, ext_price_col, {
'type': 'cell',
'criteria': '<=',
'value': xl_rowcol_to_cell(row, 8),
# This is the global data cell holding the minimum extended price for this part.
'format': wrk_formats['best_price']
})
# Finished processing distributor data for this part.
row += 1 # Go to next row.used_currencies.remove(CURRENCY_ALPHA3)
wks.write(next_line, start_col + columns['value']['col'],
'Used currency rates:', wrk_formats['description'])
next_line = next_line + 1
for used_currency in used_currencies:
if used_currency!=CURRENCY_ALPHA3:
wks.write(next_line, start_col + columns['value']['col'],
'{c}({c_s})/{d}({d_s}):'.format(c=CURRENCY_ALPHA3, d=used_currency, c_s=CURRENCY_SYMBOL,
d_s=numbers.get_currency_symbol(used_currency, locale=DEFAULT_LANGUAGE)
),
wrk_formats['description']
)
WORKBOOK.define_name('{c}_{d}'.format(c=CURRENCY_ALPHA3, d=used_currency),
'={wks_name}!{cell_ref}'.format(
wks_name="'" + WORKSHEET_NAME + "'",
cell_ref=xl_rowcol_to_cell(next_line, columns['value']['col'] + 1,
row_abs=True, col_abs=True)))
wks.write(next_line, columns['value']['col'] + 1,
currency_convert(1, used_currency, CURRENCY_ALPHA3)
)
next_line = next_line + 1
# Return column following the globals so we know where to start next set of cells.
# Also return the columns where the references and quantity needed of each part is stored.
return next_line, start_col + num_cols, start_col + columns['refs']['col'], start_col + columns['qty']['col'], columnscolumn_index,
'',
formats['cell_generic']
)
sheet.write(
rows_emitted,
columns['unit_amount_a'],
0,
formats['entry_total_amount'],
)
sheet.write_formula(
rows_emitted,
columns['total_unit_amount_a'],
'=%s' % (
xl_rowcol_to_cell(
rows_emitted,
columns['unit_amount_a']
),
),
formats['block_total_amount'],
self._convert_time_num_format(
report,
block.total_unit_amount_a
),
)
if report.utilization_format == 'percentage':
sheet.write(
rows_emitted,
columns['utilization_a'],
0,),
formats['section_total_amount'],
self._convert_time_num_format(
report,
group.total_unit_amount_b
),
)
sheet.write_formula(
rows_emitted,
columns['capacity'],
'=SUM(%s:%s)' % (
xl_rowcol_to_cell(
rows_emitted + 1,
columns['capacity']
),
xl_rowcol_to_cell(
rows_emitted + group_rows_count,
columns['capacity']
),
),
formats['section_total_capacity'],
self._convert_time_num_format(
report,
group.total_capacity
),
)
return 1XlsxWriter
A Python module for creating Excel XLSX files.
Package Health Score
90 / 100Popular XlsxWriter functions
- xlsxwriter.compatibility.force_unicode
- xlsxwriter.format.Format
- xlsxwriter.relationships.Relationships
- xlsxwriter.shape.Shape
- xlsxwriter.shape.Shape._get_fill_properties
- xlsxwriter.shape.Shape._get_font_latin_attributes
- xlsxwriter.shape.Shape._get_font_style_attributes
- xlsxwriter.shape.Shape._get_gradient_properties
- xlsxwriter.shape.Shape._get_line_properties
- xlsxwriter.shape.Shape._get_pattern_properties
- xlsxwriter.utility.get_rgb_color
- xlsxwriter.utility.xl_cell_to_rowcol
- xlsxwriter.utility.xl_col_to_name
- xlsxwriter.utility.xl_range
- xlsxwriter.utility.xl_range_abs
- xlsxwriter.utility.xl_rowcol_to_cell
- xlsxwriter.Workbook
- xlsxwriter.workbook.Workbook
- xlsxwriter.xmlwriter
- xlsxwriter.xmlwriter.XMLwriter