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')