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.

github trailofbits / cb-multios / tools / tester.py View on Github external
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)
github xesscorp / KiCost / kicost.py View on Github external
# 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']
github AwesomeFoodCoops / odoo-production / extra_addons / account_asset_management_xlsx / report / report_account_asset_xlsx.py View on Github external
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 += 1
github xesscorp / KiCost / kicost.py View on Github external
wks.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',
github BayAreaMetro / travel-model-one / utilities / PBA40 / metrics / RunResults.py View on Github external
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)
github OCA / timesheet / hr_utilization_report / report / hr_utilization_report.py View on Github external
),
                ),
                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']
        )
github xesscorp / KiCost / kicost.py View on Github external
)

            # 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.
github xesscorp / KiCost / kicost / spreadsheet.py View on Github external
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'], columns
github OCA / timesheet / hr_utilization_report / report / hr_utilization_report.py View on Github external
column_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,
github OCA / timesheet / hr_utilization_report / report / hr_utilization_report.py View on Github external
),
                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 1