How to use the xlsxwriter.utility.xl_range function in XlsxWriter

qty_range=xl_range(PART_INFO_FIRST_ROW, qty_prj_col,
                                   PART_INFO_LAST_ROW, qty_prj_col)),
                wrk_formats['found_part_pct'])
            wks.write_comment(row, dist_cat_col, 'Number of parts found at this distributor for the project {}.'.format(i_prj))
        total_cost_row = PART_INFO_FIRST_ROW - 3 # Shift the total price in this distributor.
    
    # Sum the extended prices for all the parts to get the total cost from this distributor.
    wks.write(total_cost_row, total_cost_col, '=SUM({sum_range})'.format(
        sum_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                           PART_INFO_LAST_ROW, total_cost_col)),
              wrk_formats['total_cost_currency'])
    # Show how many parts were found at this distributor.
    wks.write(total_cost_row, dist_cat_col,
        '=(COUNTA({count_range})&" of "&ROWS({count_range})&" parts found")'.format(
        #'=COUNTIF({count_range},"<>")&" of "&ROWS({count_range})&" parts found"'.format(
            count_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                                 PART_INFO_LAST_ROW, total_cost_col)),
            wrk_formats['found_part_pct'])
    wks.write_comment(total_cost_row, dist_cat_col, 'Number of parts found at this distributor.')

    # Add list of part numbers and purchase quantities for ordering from this distributor.
    ORDER_START_COL = start_col + 1
    ORDER_FIRST_ROW = PART_INFO_LAST_ROW + 3
    ORDER_LAST_ROW = ORDER_FIRST_ROW + num_parts - 1

    # Write the header and how many parts are being purchased.
    purch_qty_col = start_col + columns['purch']['col']
    ext_price_col = start_col + columns['ext_price']['col']
    ORDER_HEADER =  PART_INFO_LAST_ROW + 2
    wks.write_formula( # Expended many in this distributor.
        ORDER_HEADER, ext_price_col,
        '=SUMIF({count_range},">0",{price_range})'.format(

def format_col(self, col, colWidth=None, colFormat=None):

        iCol, worksheetCol = self.convert_to_col_index(col)

        if colWidth is not None:
            self.worksheet.set_column(xl_range(1, worksheetCol, 1, worksheetCol), colWidth)
        if colFormat is not None:
            for rowIndex in range(self.nRows):
                self.formatTable[rowIndex][iCol].update(colFormat)

total_cost_col = start_col + columns['ext_price']['col']
    unit_cost_col = start_col + columns['unit_price']['col']
    dist_cat_col = start_col + columns['part_num']['col']
    
    # If more than one file (multi-files mode) show how many
    # parts of each BOM as found at this distributor and
    # the correspondent total price.
    if num_prj>1:
        for i_prj in range(num_prj):
            # Sum the extended prices (unit multiplied by quantity) for each file/BOM.
            qty_prj_col = part_qty_col - (num_prj - i_prj)
            row = total_cost_row + i_prj * 3
            wks.write(row, total_cost_col,
                      '=SUMPRODUCT({qty_range},{unit_price_range})'.format(
                            qty_range=xl_range(PART_INFO_FIRST_ROW, qty_prj_col,
                                            PART_INFO_LAST_ROW, qty_prj_col),
                            unit_price_range=xl_range(PART_INFO_FIRST_ROW, unit_cost_col,
                                            PART_INFO_LAST_ROW, unit_cost_col)),
                      wrk_formats['total_cost_currency'])
            # Show how many parts were found at this distributor.
            wks.write(row, dist_cat_col,
                '=COUNTIFS({price_range},"<>",{qty_range},"<>0",{qty_range},"<>")&" of "&COUNTIFS({qty_range},"<>0",{qty_range},"<>")&" parts found"'.format(
                price_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                                     PART_INFO_LAST_ROW, total_cost_col),
                qty_range=xl_range(PART_INFO_FIRST_ROW, qty_prj_col,
                                   PART_INFO_LAST_ROW, qty_prj_col)),
                wrk_formats['found_part_pct'])
            wks.write_comment(row, dist_cat_col, 'Number of parts found at this distributor for the project {}.'.format(i_prj))
        total_cost_row = PART_INFO_FIRST_ROW - 3 # Shift the total price in this distributor.
    
    # Sum the extended prices for all the parts to get the total cost from this distributor.

except ValueError:
                pass
        logger.log(DEBUG_OVERVIEW,
                "Add the {f} information for the {d} purchase list code.".format(
                    d=distributor_dict[dist]['label']['name'],
                    f=cols_user
                ))
        cols[idx:idx] = cols_user

    # Create the header of the purchase codes, if present the definition.
    try:
        wks.write_formula(ORDER_FIRST_ROW, ORDER_START_COL,
                         '=IFERROR(IF(COUNTIFS({count_range},">0",{count_range_price},"<>")>0,"{header}",""),"")'.format(
                            count_range=xl_range(PART_INFO_FIRST_ROW, purch_qty_col,
                                PART_INFO_LAST_ROW, purch_qty_col),
                            count_range_price=xl_range(PART_INFO_FIRST_ROW, ext_price_col,
                                PART_INFO_LAST_ROW, ext_price_col),
                            header=distributor_dict[dist]['order']['header'],
                         ),
                         wrk_formats['found_part_pct']
        )
        try:
            wks.write_comment(ORDER_FIRST_ROW, ORDER_START_COL, distributor_dict[dist]['order']['info'])
        except KeyError:
            pass
        ORDER_FIRST_ROW = ORDER_FIRST_ROW + 1 # Push all the code list one row.
        ORDER_LAST_ROW = ORDER_LAST_ROW + 1
    except KeyError:
        pass

    if not('purch' in cols and ('part_num' in cols or 'manf#' in cols)):
        logger.log(DEBUG_OVERVIEW, "Purchase list codes for {d} will not be generated: no stock# of manf# format defined.".format(

unit_cost_col = start_col + columns['unit_price']['col']
    dist_cat_col = start_col + columns['part_num']['col']
    
    # If more than one file (multi-files mode) show how many
    # parts of each BOM as found at this distributor and
    # the correspondent total price.
    if num_prj>1:
        for i_prj in range(num_prj):
            # Sum the extended prices (unit multiplied by quantity) for each file/BOM.
            qty_prj_col = part_qty_col - (num_prj - i_prj)
            row = total_cost_row + i_prj * 3
            wks.write(row, total_cost_col,
                      '=SUMPRODUCT({qty_range},{unit_price_range})'.format(
                            qty_range=xl_range(PART_INFO_FIRST_ROW, qty_prj_col,
                                            PART_INFO_LAST_ROW, qty_prj_col),
                            unit_price_range=xl_range(PART_INFO_FIRST_ROW, unit_cost_col,
                                            PART_INFO_LAST_ROW, unit_cost_col)),
                      wrk_formats['total_cost_currency'])
            # Show how many parts were found at this distributor.
            wks.write(row, dist_cat_col,
                '=COUNTIFS({price_range},"<>",{qty_range},"<>0",{qty_range},"<>")&" of "&COUNTIFS({qty_range},"<>0",{qty_range},"<>")&" parts found"'.format(
                price_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                                     PART_INFO_LAST_ROW, total_cost_col),
                qty_range=xl_range(PART_INFO_FIRST_ROW, qty_prj_col,
                                   PART_INFO_LAST_ROW, qty_prj_col)),
                wrk_formats['found_part_pct'])
            wks.write_comment(row, dist_cat_col, 'Number of parts found at this distributor for the project {}.'.format(i_prj))
        total_cost_row = PART_INFO_FIRST_ROW - 3 # Shift the total price in this distributor.
    
    # Sum the extended prices for all the parts to get the total cost from this distributor.
    wks.write(total_cost_row, total_cost_col, '=SUM({sum_range})'.format(
        sum_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,

wks.write_formula( # Expended many in this distributor.
        ORDER_HEADER, ext_price_col,
        '=SUMIF({count_range},">0",{price_range})'.format(
            count_range=xl_range(PART_INFO_FIRST_ROW, purch_qty_col,
                                 PART_INFO_LAST_ROW, purch_qty_col),
            price_range=xl_range(PART_INFO_FIRST_ROW, ext_price_col,
                                 PART_INFO_LAST_ROW, ext_price_col),
        ),
        wrk_formats['total_cost_currency']
    )
    wks.write_formula( # Quantity of purchased part in this distributor.
        ORDER_HEADER, purch_qty_col,
        '=IFERROR(IF(OR({count_range}),COUNTIFS({count_range},">0",{count_range_price},"<>")&" of "&(ROWS({count_range_price})-COUNTBLANK({count_range_price}))&" parts purchased",""),"")'.format(
            count_range=xl_range(PART_INFO_FIRST_ROW, purch_qty_col,
                                 PART_INFO_LAST_ROW, purch_qty_col),
            count_range_price=xl_range(PART_INFO_FIRST_ROW, ext_price_col,
                                 PART_INFO_LAST_ROW, ext_price_col)
        ),
        wrk_formats['found_part_pct']
    )
    wks.write_comment(ORDER_HEADER, purch_qty_col,
        'Copy the information below to the BOM import page of the distributor web site.')
    try:
        wks.write_url(ORDER_HEADER, purch_qty_col-1,
            distributor_dict[dist]['order']['url'],
            string='Buy here')
    except KeyError:
        pass # Not URL registered.


    # Write the spreadsheet code to multiple lines to create the purchase codes to
    # be used in this current distributor.

})

        # 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']
    wks.write(total_cost_row, total_cost_col, '=sum({sum_range})'.format(
        sum_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                           PART_INFO_LAST_ROW, total_cost_col)),
              wrk_formats['total_cost_currency'])

    # Show how many parts were found at this distributor.
    wks.write(unit_cost_row, total_cost_col,
        '=(ROWS({count_range})-COUNTBLANK({count_range}))&" of "&ROWS({count_range})&" parts found"'.format(
        count_range=xl_range(PART_INFO_FIRST_ROW, total_cost_col,
                           PART_INFO_LAST_ROW, total_cost_col)),
              wrk_formats['found_part_pct'])
    wks.write_comment(unit_cost_row, total_cost_col, 'Number of parts found at this distributor.')

    # Add list of part numbers and purchase quantities for ordering from this distributor.
    ORDER_START_COL = start_col + 1
    ORDER_FIRST_ROW = PART_INFO_LAST_ROW + 3
    ORDER_LAST_ROW = ORDER_FIRST_ROW + num_parts - 1

    # Each distributor has a different format for entering ordering information,
    # so we account for that here.
    order_col = {}
    order_col_numeric = {}
    order_delimiter = {}
    dist_col = {}
    for position, col_tag in enumerate(distributors[dist]['order_cols']):

order_part_info.append(order_info_func_parcial)
            else:
                order_part_info.append(order_info_func_model)
            # Look for the `col` name into the distributor spreadsheet part
            # with don't find, it belongs to the global part.
            if col in columns:
                info_range = start_col + columns[col]['col']
            elif col in columns_global:
                info_range = columns_global[col]['col']
            else:
                info_range = ""
                logger.warning("Not valid field `{f}` for purchase list at {d}.".format(
                            f=col,
                            d=distributor_dict[dist]['label']['name']
                        ))
            info_range =xl_range(PART_INFO_FIRST_ROW, info_range,
                                 PART_INFO_LAST_ROW, info_range)
            # If the correspondent information is some description, it is allow to add the general
            # purchase designator. it is placed inside the "not allow characters" restriction.
            if col not in ['part_num', 'purch', 'manf#']:
                info_range = 'IF(PURCHASE_DESCRIPTION<>"",PURCHASE_DESCRIPTION&"{}","")'.format(PURCHASE_DESCRIPTION_SEPRTR)+ '&' + info_range
            # Create the part of formula that refers with one specific information.
            order_part_info[-1] = order_part_info[-1].format(
                        get_range=info_range,
                        qty='{qty}', # keep all other for future replacement.
                        code='{code}',
                        order_first_row='{order_first_row}')
        # If already have some information, add the delimiter for
        # Microsoft Excel/LibreOffice Calc function.
        order_func = order_func.format( delimier.join(order_part_info) )

        # These are the columns where the part catalog numbers and purchase quantities can be found.