def add_chart(self, chart):
chart.anchor = AbsoluteAnchor()
self._charts.append(chart)
self.parent._charts.append(ref(chart))
def add_chart(self, chart):
chart.anchor = AbsoluteAnchor()
self._charts.append(chart)
def create_excel(traveler_dictionary, folder_path):
"""
Creates an excel template that mimics the customer traveler so that we can pass in information from the PDF.
:traveler_dictionary: dictionary with information from traveler sorted by category.
:folder_path: path to the directory directly containing the traveler
:return: None
"""
# Gets the directory of the executable script, which contains the template we will open.
script_path = getattr(sys, '_MEIPASS',
os.path.dirname(os.path.abspath(__file__)))
os.chdir(script_path)
wb = openpyxl.load_workbook(
resource_path('templates/TravelerTemplate.xlsx'))
# Transfer the traveler dictionary values into the excel template.
sheet = wb['Sheet1']
sheet['A1'] = f'CT {traveler_dictionary["job_number"]}'
sheet['A6'] = traveler_dictionary['po_number']
sheet['B6'] = traveler_dictionary['due_date']
sheet['C6'] = 'CUSTOMER'
sheet['A8'] = traveler_dictionary['job_number']
sheet['B8'] = traveler_dictionary['part_file'].strip('\n')
sheet['C8'] = traveler_dictionary['quantity']
sheet['A10'] = traveler_dictionary['finish'].strip('\n')
sheet['B10'] = traveler_dictionary['material'].strip('\n')
sheet['C10'] = traveler_dictionary['certifications'].strip('\n')
sheet['A12'] = traveler_dictionary['inspection'].strip('\n')
# Replace all of Xometry mentions with 'CUSTOMER'
no_customer = re.sub(r'xometry|Xometry|XOMETRY', 'CUSTOMER',
traveler_dictionary['notes'].strip('\n'))
no_trademark = replace_trademark(no_customer)
no_fluid = replace_fluid(no_trademark)
final_notes = process_notes(no_fluid)
sheet['A14'] = final_notes
# Date modifications according to customer instructions
date = traveler_dictionary['due_date']
date_time_obj = datetime.strptime(date, "%m/%d/%Y")
# 1) If “Finish” block says “Standard”, change due date to two business days before current due date.
if traveler_dictionary['finish'] == 'Standard':
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=2):%m/%d/%Y}'
# 2) If anything in the pdf mentions “mask” or “masking”, “heat treat”, “heat treating”, “harden”,
# or “through harden” change due date to 7 business days before current due date
post_process_pattern = re.compile(
r'(mask|masking|heat treat|heat treating|harden|through harden)')
matches = post_process_pattern.search(traveler_dictionary['finish'])
if matches is not None:
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
matches = post_process_pattern.search(traveler_dictionary['material'])
if matches is not None:
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
matches = post_process_pattern.search(traveler_dictionary['notes'])
if matches is not None:
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
# 3) If “Finish” block says “Custom” but there’s no mention of masking in rest of pdf,
# change due date to 5 business days before current due date
finish_pattern = re.compile(r'custom|CUSTOM|Custom')
mask_pattern = re.compile(r'mask|MASK|masking|MASKING')
finish_matches = finish_pattern.search(traveler_dictionary['finish'])
if finish_matches is not None:
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=5):%m/%d/%Y}'
mask_matches = mask_pattern.search(traveler_dictionary['finish'])
if mask_matches is not None:
sheet[
'B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
mask_matches = mask_pattern.search(traveler_dictionary['material'])
if mask_matches is not None:
sheet[
'B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
mask_matches = mask_pattern.search(traveler_dictionary['notes'])
if mask_matches is not None:
sheet[
'B6'] = f'{date_time_obj.date() - timedelta(days=7):%m/%d/%Y}'
# 4) If the pdf is absent of any of the phrases in a,b, or c and “Finish” block mentions any other kind of finish,
# change due date to three business days before current due date.
elif traveler_dictionary['finish'] is not None:
sheet['B6'] = f'{date_time_obj.date() - timedelta(days=3):%m/%d/%Y}'
# 5) If in following the rules, the resulting date is less than today’s date,
# replace the date with the text “ASAP”
compare_today = sheet['B6'].value
compare_time_obj = datetime.strptime(compare_today, "%m/%d/%Y")
if compare_time_obj.date() < datetime.today().date():
sheet['B6'] = 'ASAP'
# Change to folder path to grab image and save excel file.
os.chdir(folder_path)
# Opens image and converts to RGBA format
im = Image.open(f'{traveler_dictionary["job_number"]}.png')
im = im.convert('RGBA')
# Uses numpy to convert img background to white
data = np.array(im) # "data" is a height x width x 4 numpy array
red, green, blue, alpha = data.T # Temporarily unpack the bands for readability
# Replace black with white... (leaves alpha values alone...)
black_areas = (red == 0) & (blue == 0) & (green == 0)
data[..., :-1][black_areas.T] = (255, 255, 255) # Transpose back needed
# Closes original image, than saves new image with white background
im2 = Image.fromarray(data)
im.close()
im2.save(f'{traveler_dictionary["job_number"]}.png')
# Opens image and resizes it to fit the template.
im = Image.open(f'{traveler_dictionary["job_number"]}.png')
resized_im = im.resize(
(round(im.size[0] * 0.75), round(im.size[1] * 0.75)))
resized_im.save(f'{traveler_dictionary["job_number"]}.png')
# Anchors the image in the template excel according to absolute values to horizontally align center.
img = openpyxl.drawing.image.Image(
f'{traveler_dictionary["job_number"]}.png')
p2e = pixels_to_EMU
h, w = img.height, img.width
position = XDRPoint2D(p2e(210), p2e(80))
size = XDRPositiveSize2D(p2e(h), p2e(w))
img.anchor = AbsoluteAnchor(pos=position, ext=size)
sheet.add_image(img)
# Saves the template file as the new traveler name. Customer can now open and print as PDF.
wb.save(f'CT {traveler_dictionary["job_number"]}.xlsx')
# Deletes the image file since we no longer need it.
os.remove(f'{traveler_dictionary["job_number"]}.png')