Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
# This puts the order column delimiter into a form acceptable in a spreadsheet formula.
if delimiter != '':
delimiter = '"{}"'.format(delimiter)
# These are the columns where the part catalog numbers and purchase quantities can be found.
purch_qty_col = start_col + columns['purch']['col']
part_num_col = start_col + columns['part_num']['col']
# Now write the order_info_func into every row of the order in the given column.
for r in range(ORDER_FIRST_ROW, ORDER_LAST_ROW + 1):
wks.write_array_formula(
xl_range(r, order_col, r, order_col),
'{{={func}}}'.format(func=order_info_func.format(
order_first_row=xl_rowcol_to_cell(ORDER_FIRST_ROW, 0,
row_abs=True),
sel_range1=xl_range_abs(PART_INFO_FIRST_ROW, purch_qty_col,
PART_INFO_LAST_ROW, purch_qty_col),
sel_range2=xl_range_abs(PART_INFO_FIRST_ROW, part_num_col,
PART_INFO_LAST_ROW, part_num_col),
get_range=xl_range_abs(PART_INFO_FIRST_ROW, info_col,
PART_INFO_LAST_ROW, info_col),
delimiter=delimiter,
num_to_text_func=num_to_text_func,
num_to_text_fmt=num_to_text_fmt)))
START_ROW = 4
LABEL_ROW = START_ROW + 1
COL_HDR_ROW = LABEL_ROW + 1
FIRST_PART_ROW = COL_HDR_ROW + 1
LAST_PART_ROW = COL_HDR_ROW + len(parts) - 1
# Load the global part information (not distributor-specific) into the sheet.
# next_col = the column immediately to the right of the global data.
# qty_col = the column where the quantity needed of each part is stored.
next_col, refs_col, qty_col = add_globals_to_worksheet(
wks, wrk_formats, START_ROW, START_COL, TOTAL_COST_ROW, parts, user_fields)
# Create a defined range for the global data.
workbook.define_name(
'global_part_data', '={wks_name}!{data_range}'.format(
wks_name= "'" + WORKSHEET_NAME + "'",
data_range=xl_range_abs(START_ROW, START_COL, LAST_PART_ROW,
next_col - 1)))
# Add project information to track the project (in a printed version
# of the BOM) and the date because of price variations.
wks.write(BOARD_QTY_ROW, START_COL, 'Proj:', wrk_formats['proj_info_field'])
wks.write(BOARD_QTY_ROW, START_COL+1, prj_info['title'], wrk_formats['proj_info'])
wks.write(TOTAL_COST_ROW, START_COL, 'Co.:', wrk_formats['proj_info_field'])
wks.write(TOTAL_COST_ROW, START_COL+1, prj_info['company'], wrk_formats['proj_info'])
wks.write(UNIT_COST_ROW, START_COL, 'Date:', wrk_formats['proj_info_field'])
wks.write(UNIT_COST_ROW, START_COL+1, datetime.now().strftime("%Y-%m-%d %H:%M:%S"), wrk_formats['proj_info'])
# Create the cell where the quantity of boards to assemble is entered.
# Place the board qty cells near the right side of the global info.
wks.write(BOARD_QTY_ROW, next_col - 2, 'Board Qty:',
wrk_formats['board_qty'])
wks.write(BOARD_QTY_ROW, next_col - 1, DEFAULT_BUILD_QTY,
delimiter = '"{}"'.format(delimiter)
# These are the columns where the part catalog numbers and purchase quantities can be found.
purch_qty_col = start_col + columns['purch']['col']
part_num_col = start_col + columns['part_num']['col']
# Now write the order_info_func into every row of the order in the given column.
for r in range(ORDER_FIRST_ROW, ORDER_LAST_ROW + 1):
wks.write_array_formula(
xl_range(r, order_col, r, order_col),
'{{={func}}}'.format(func=order_info_func.format(
order_first_row=xl_rowcol_to_cell(ORDER_FIRST_ROW, 0,
row_abs=True),
sel_range1=xl_range_abs(PART_INFO_FIRST_ROW, purch_qty_col,
PART_INFO_LAST_ROW, purch_qty_col),
sel_range2=xl_range_abs(PART_INFO_FIRST_ROW, part_num_col,
PART_INFO_LAST_ROW, part_num_col),
get_range=xl_range_abs(PART_INFO_FIRST_ROW, info_col,
PART_INFO_LAST_ROW, info_col),
delimiter=delimiter,
num_to_text_func=num_to_text_func,
num_to_text_fmt=num_to_text_fmt)))
# These are the columns where the part catalog numbers and purchase quantities can be found.
purch_qty_col = start_col + columns['purch']['col']
part_num_col = start_col + columns['part_num']['col']
# Now write the order_info_func into every row of the order in the given column.
for r in range(ORDER_FIRST_ROW, ORDER_LAST_ROW + 1):
wks.write_array_formula(
xl_range(r, order_col, r, order_col),
'{{={func}}}'.format(func=order_info_func.format(
order_first_row=xl_rowcol_to_cell(ORDER_FIRST_ROW, 0,
row_abs=True),
sel_range1=xl_range_abs(PART_INFO_FIRST_ROW, purch_qty_col,
PART_INFO_LAST_ROW, purch_qty_col),
sel_range2=xl_range_abs(PART_INFO_FIRST_ROW, part_num_col,
PART_INFO_LAST_ROW, part_num_col),
get_range=xl_range_abs(PART_INFO_FIRST_ROW, info_col,
PART_INFO_LAST_ROW, info_col),
delimiter=delimiter,
num_to_text_func=num_to_text_func,
num_to_text_fmt=num_to_text_fmt)))
local_dists = sorted([d for d in distributor_dict if distributor_dict[d]['type'] == 'local'])
dist_list = web_dists + local_dists
# Load the part information from each distributor into the sheet.
logger.log(DEBUG_OVERVIEW, 'Writing the distributor part information...')
for dist in dist_list:
dist_start_col = next_col
next_col = add_dist_to_worksheet(wks, wrk_formats, columns_global,
START_ROW, dist_start_col,
UNIT_COST_ROW, TOTAL_COST_ROW,
refs_col, qty_col, dist, parts, suppress_cat_url)
# Create a defined range for each set of distributor part data.
workbook.define_name(
'{}_part_data'.format(dist), '={wks_name}!{data_range}'.format(
wks_name="'" + WORKSHEET_NAME + "'",
data_range=xl_range_abs(START_ROW, dist_start_col,
LAST_PART_ROW, next_col - 1)))
# Add the KiCost package information at the end of the spreadsheet to debug
# information at the forum and "advertising".
wks.write(next_line+1, START_COL, ABOUT_MSG, wrk_formats['proj_info'])
# Make a list of alphabetically-ordered distributors with web distributors before locals.
web_dists = sorted([d for d in distributors if distributors[d]['scrape'] != 'local'])
local_dists = sorted([d for d in distributors if distributors[d]['scrape'] == 'local'])
dist_list = web_dists + local_dists
# Load the part information from each distributor into the sheet.
for dist in dist_list:
dist_start_col = next_col
next_col = add_dist_to_worksheet(wks, wrk_formats, START_ROW,
dist_start_col, UNIT_COST_ROW, TOTAL_COST_ROW,
refs_col, qty_col, dist, parts)
# Create a defined range for each set of distributor part data.
workbook.define_name(
'{}_part_data'.format(dist), '={wks_name}!{data_range}'.format(
wks_name="'" + WORKSHEET_NAME + "'",
data_range=xl_range_abs(START_ROW, dist_start_col,
LAST_PART_ROW, next_col - 1)))