def export_xlsx(self): for index, sheet_name in enumerate(self.sheet_names): ws = self.wb[str(sheet_name)] for cell in self.worksheet[index]: cell_location = str(cell['column'])+str(cell['row']) cell_value = cell['value'] if cell['value_type'] == "url" and cell_value is not None: cell_value = os.path.abspath("{}/{}".format(settings.MEDIA_ROOT, cell_value)) # cell_value = PilImage.open(cell_value) # # Reduced the size of the image by reducing its quality # result = cell_value.convert("P") img = Image(cell_value) img.anchor(ws[cell_location], anchortype='oneCell') ws.add_image(img) elif (cell['value_type'] == "multiple" and cell_value is not None): import ast try: cell_value = ast.literal_eval(str(cell_value)) ws.cell(row=cell['row'],column=cell['column']+1).value = ','.join(str(value) for value in cell_value) except: pass else: ws.cell(row=cell['row'],column=cell['column']+1).value=cell_value #ws['A1'] = self.__convert_to_type(cell_value, cell['value_type']) return self.wb
def _write_image(worksheet, a1, image_path, width_scale=0.75, height_scale=0.75): """ Write image to worksheet cell Parameters ---------- worksheet : openpyxl.worksheet.worksheet.Worksheet Worksheet object to write to a1 : str Cell in A1 notation image_path : str Local path to image width_scale : float Value to scale the image width (Default: 0.75) height_scale : float Value to scale the image height (Default: 0.75) """ # Create image image = Image(image_path) # Scale image.width *= width_scale image.height *= height_scale # Anchor the image to A1 image.anchor(worksheet[a1]) # Add image to the worksheet worksheet.add_image(image)
def add_signature_image(sheet): xfile = openpyxl.load_workbook(filepath) sheet = xfile.get_sheet_by_name('Time Card') img = Image('signature_scan.gif') img.drawing.width = 100 img.drawing.height = 50 img.anchor(sheet.cell('E35')) sheet.add_image(img)
def steps_table(bms_template): sheet_names = bms_template.get_sheet_names() step_sheet = bms_template.get_sheet_by_name(sheet_names[3]) img = Image('Deloitte_logo.png',size=[250,50]) img.anchor(step_sheet.cell('B1'),anchortype='oneCell') step_sheet.add_image(img) step_sheet.merge_cells(start_row=1, start_column=1, end_row=3, end_column=5) for col in range(1,6): for row in range(1,4): Styles.thick_border_white_cell_style(step_sheet.cell(column=col,row=row)) step_sheet.column_dimensions[get_column_letter(1)].width = 3 step_sheet.merge_cells(start_row=4, start_column=1, end_row=4, end_column=3) for col in range (1,6): Styles.thick_border_fill_cell_style(step_sheet.cell(column=col,row=4)) step_sheet.column_dimensions[get_column_letter(2)].width = 35 step_sheet.column_dimensions[get_column_letter(3)].width = 10 step_sheet.column_dimensions[get_column_letter(4)].width = 15 step_sheet.column_dimensions[get_column_letter(5)].width = 15 for row in range(1,5): step_sheet.row_dimensions[row].height = 13 lang = Properties.lang_from_template_type(bms_template) for col in range(1, 6): Styles.thin_border_fill_cell_style(step_sheet.cell(column=col, row=5)) step_sheet.row_dimensions[row].height = 50 step_sheet.cell(column=2, row=5, value=ManualStep.define_step_name_type(lang)[0]) Styles.thin_border_fill_cell_style(step_sheet.cell(column=col, row=6)) step_sheet.row_dimensions[row].height = 30 step_sheet.cell(column=2, row=6, value=ManualStep.define_step_name_type(lang)[1]) step_sheet['A5'] = 1 step_sheet['A6'] = 2 for row in range(7,14): step_sheet['A'+str(row)]=row-4 step_sheet.row_dimensions[row].height = 20 step_sheet.cell(column=2, row=row, value=ManualStep.define_step_name_type(lang)[row-5]) step_sheet.merge_cells(start_row=row, start_column=2, end_row=row, end_column=3) for col in range(1,6): Styles.thin_border_fill_cell_style(step_sheet.cell(column=col,row=row)) step_sheet.row_dimensions[14].height = 30 for col in range (1,6): Styles.header_style_light_blue(step_sheet.cell(column=col,row=14)) step_sheet['B14'] = ManualStep.define_step_name_type(lang)[9] step_sheet.merge_cells(start_row=14, start_column=2, end_row=14, end_column=3) print('manual steps added') return bms_template
def read_drawings(ws, drawings_path, archive, valid_files): """ Given a worksheet and the XML of its drawings file, links drawings to cells """ drawings_codename = os.path.split(drawings_path)[-1] rels_file = PACKAGE_DRAWINGS_RELS + '/' + drawings_codename + '.rels' if rels_file not in valid_files: return None rels_source = archive.read(rels_file) rels_root = fromstring(rels_source) root = fromstring(archive.read(drawings_path)) for node in root: col, row = 0, 0 name = u'' cell_from = node.find('{%s}from' % SHEET_DRAWING_NS) if cell_from is not None: col = cell_from.find('{%s}col' % SHEET_DRAWING_NS) if col is not None: col = int(col.text) row = cell_from.find('{%s}row' % SHEET_DRAWING_NS) if row is not None: row = int(row.text) cell = ws['%s%s' % (get_column_letter(col + 1), row + 1)] pic = node.find('{%s}pic' % SHEET_DRAWING_NS) if pic is not None: nv_pic_pr = pic.find('{%s}nvPicPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: nv_pic_pr = nv_pic_pr.find('{%s}cNvPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: name = nv_pic_pr.attrib.get('name', '') blip_fill = pic.find('{%s}blipFill' % SHEET_DRAWING_NS) if blip_fill is not None: blip = blip_fill.find('{%s}blip' % DRAWING_NS) if blip is not None: rid = blip.attrib.get('{%s}embed' % REL_NS) if rid is not None: image_file = read_image_file( rels_root, rid, valid_files, ) if image_file: readfile = archive.read(image_file) bytes = BytesIO(readfile) img = Image(bytes) img.name = name img.anchor = cell.coordinate ws.add_image(img)
def qrToExcel(qrLoc, anchorVal, i): imgAnchor = anchorVal imgA = Image(qrLoc) imgA.height = 50 imgA.width = 50 imgA.anchor = imgAnchor sh1.add_image(imgA) anchorFill = "G" + str(i) sh1[str(anchorFill)].fill = attendanceRed ################ PUSH EMAIL WITH ATTACHMENTS ####################### ################ CALL iVolunteer_PushNoti.py ####################### wb.save("dataSource\dummyData-iVolunteer.xlsx")
def addImage(self, position, imagePath): """ :param position: Position in 2-Dimensions (f. example: (1, 42)) :type position: tuple of [int] :param imagePath: Path to image file :type position: str """ address = helper.pos2address(position[0], position[1]) img = Image(imagePath) img.anchor = address self.worksheet.add_image(img)
def get_cover_page(wb, template_directory, date_end): wb_cur = wb ws_cur = wb_cur.create_sheet('Cover') ht_logo = Image(template_directory + 'ht_logo.png') ht_logo.anchor(ws_cur.cell(row=7, column=3), 'absolute') ws_cur.add_image(ht_logo) companies_logo = Image(template_directory + 'companies_logo.png') companies_logo.anchor(ws_cur.cell(row=31, column=1), 'absolute') ws_cur.add_image(companies_logo) for c in range(1, 13): ws_cur.column_dimensions[(dcc.get(c))].width = 8.5 c1 = ws_cur.cell(row=17, column=1, value='Consolidated Financial Results') c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true', size=22) ws_cur.merge_cells(start_row=17, end_row=17, start_column=1, end_column=12) c1 = ws_cur.cell(row=22, column=1, value=datetime.strftime(date_end, '%B %Y')) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true', size=22) ws_cur.merge_cells(start_row=22, end_row=22, start_column=1, end_column=12) ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = 1 ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5, footer=.5) return wb_cur
def find_images(archive, path): """ Given the path to a drawing file extract charts and images Ingore errors due to unsupported parts of DrawingML """ src = archive.read(path) tree = fromstring(src) try: drawing = SpreadsheetDrawing.from_tree(tree) except TypeError: warn( "DrawingML support is incomplete and limited to charts and images only. Shapes and drawings will be lost." ) return [], [] rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) charts = [] for rel in drawing._chart_rels: cs = get_rel(archive, deps, rel.id, ChartSpace) chart = read_chart(cs) chart.anchor = rel.anchor charts.append(chart) images = [] if not PILImage: # Pillow not installed, drop images return charts, images for rel in drawing._blip_rels: dep = deps[rel.embed] if dep.Type == IMAGE_NS: try: image = Image(BytesIO(archive.read(dep.target))) except OSError: msg = "The image {0} will be removed because it cannot be read".format( dep.target) warn(msg) continue if image.format.upper() == "WMF": # cannot save msg = "{0} image format is not supported so the image is being dropped".format( image.format) warn(msg) continue image.anchor = rel.anchor images.append(image) return charts, images
def setUp(self): """ Setting up test workbook :return: """ self.test_book = load_workbook(r'unittests\Testing_PrintSettings.xlsx') self.test_sheet = self.test_book.active logo = Image(r'images\SAVLandscape.jpg') logo.anchor = 'A1' logo.width = 250 logo.height = 40 self.test_sheet.add_image(logo) self.test_book.save(r'unittests\Testing_PrintSettings.xlsx')
def test_image_as_pic(self, SpreadsheetDrawing): src = """ <wsDr xmlns="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <twoCellAnchor> <from> <col>0</col> <colOff>0</colOff> <row>0</row> <rowOff>0</rowOff> </from> <to> <col>8</col> <colOff>158506</colOff> <row>10</row> <rowOff>64012</rowOff> </to> <pic> <nvPicPr> <cNvPr id="2" name="Picture 1"/> <cNvPicPr /> </nvPicPr> <blipFill> <a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1" > </a:blip> <a:stretch> <a:fillRect/> </a:stretch> </blipFill> <spPr> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </pic> <clientData/> </twoCellAnchor> </wsDr> """ node = fromstring(src) drawing = SpreadsheetDrawing.from_tree(node) anchor = drawing.twoCellAnchor[0] drawing.twoCellAnchor = [] im = Image(PIL.Image.new(mode="RGB", size=(1, 1))) im.anchor = anchor drawing.images.append(im) xml = tostring(drawing._write()) diff = compare_xml(xml, src) assert diff is None, diff
def fill_cross_section_data(self, report_ws, cross_dict, fill_row): col = 1 max_col = report_ws.max_column if str(fill_row).upper() != 'NONE': for key in cross_dict: for col in range(1, 16): if key.upper() in str( report_ws.cell(row=fill_row, column=col).value).upper(): fill_col = col break if key != 'Picture': if cross_dict[key] != 'N/A' and cross_dict[key] != '': report_ws.cell(row=fill_row + 1, column=fill_col).value = float( cross_dict[key]) else: report_ws.cell(row=fill_row + 1, column=fill_col).value = cross_dict[key] else: pic = str(cross_dict['Picture']) if pic.upper().endswith('.JPG'): # Call add image img = Image(pic) p2e = pixels_to_EMU c2e = cm_to_EMU # Assign picture size HEIGHT = 100 WIDTH = 100 # Function calculate offset cellh = lambda x: c2e((x * 49.77) / 99) cellw = lambda x: c2e((x * (18.65 - 1.71)) / 10) # Set Size and Postion colloff1 = cellw(0.5) rowoffset = cellh(0.5) marlker = AnchorMarker(col=fill_col, colOff=colloff1, row=fill_row, rowOff=rowoffset) size = XDRPositiveSize2D(p2e(HEIGHT), p2e(WIDTH)) # Paste Image to cell img.anchor = OneCellAnchor(_from=marlker, ext=size) report_ws.add_image(img)
def tearDown(self): """ Adds a second image, and adds some text to help any troubleshooting :return: """ logo = Image(r'images\SAVLandscape.jpg') logo.anchor = 'B10' logo.width = 250 logo.height = 40 self.test_sheet.add_image(logo) self.test_sheet['c5'] = 'There should be two SAV logos, one above this text and one below.' self.test_sheet['c6'] = 'The columns are set as [1, 2, 4, 8, 16, 32, 16, 21, 30, .5]' self.test_sheet['c7'] = 'The document should be landscape, and the printview should' \ 'go to column J' self.test_sheet['j2'] = 'filler' self.test_book.save(r'unittests\Testing_PrintSettings.xlsx') self.test_book.close()
def find_images(archive, path): src = archive.read(path) tree = fromstring(src) drawing = SpreadsheetDrawing.from_tree(tree) rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) images = [] for rel in drawing._image_rels: id = rel.embed path = deps[id].target image = Image(BytesIO(archive.read(path))) image.anchor = rel.anchor images.append(image) return images
def find_images(archive, path): """ Given the path to a drawing file extract anchors with images """ src = archive.read(path) tree = fromstring(src) drawing = SpreadsheetDrawing.from_tree(tree) rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) images = [] for rel in drawing._blip_rels: dep = deps[rel.embed] if dep.Type == IMAGE_NS: image = Image(BytesIO(archive.read(dep.target))) image.anchor = rel.anchor images.append(image) return images
def write_image(self, img_path, img_width=IMG_WIDTH, img_height=IMG_HEIGHT, img_row_height=IMG_ROW_HEIGHT, img_num_of_cols=IMG_NUM_OF_COLS): self.sheet.merge_cells(start_row=self.x, end_row=self.x, start_column=self.y, end_column=self.y + img_num_of_cols - 1) img_row = self.sheet.row_dimensions[self.x] img_row.height = img_row_height img = Image(img_path) img.anchor = self.sheet.cell(row=self.x, column=self.y).coordinate img.width = img_width img.height = img_height self.sheet.add_image(img) self._move_column(XlsxDoc.IMG_NUM_OF_COLS)
def find_images(archive, path): """ Given the path to a drawing file extract charts and images Ingore errors due to unsupported parts of DrawingML """ src = archive.read(path) tree = fromstring(src) try: drawing = SpreadsheetDrawing.from_tree(tree) except TypeError: warn( "DrawingML support is incomplete and limited to charts and images only. Shapes and drawings will be lost." ) return [], [] rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) charts = [] for rel in drawing._chart_rels: cs = get_rel(archive, deps, rel.id, ChartSpace) chart = read_chart(cs) chart.anchor = rel.anchor charts.append(chart) images = [] for rel in drawing._blip_rels: dep = deps[rel.embed] if dep.Type == IMAGE_NS: image = Image(BytesIO(archive.read(dep.target))) image.anchor = rel.anchor images.append(image) return charts, images
def batchGenerateQrcodes(): wb = load_workbook(xlsxFileName) sheet = wb.active rowNum = sheet.max_row colNum = sheet.max_column align = Alignment(horizontal='center', vertical='center', wrap_text=True) # Calculated number of cells width or height from cm into EMUs for i in range(2, rowNum + 1): if i > 0: for j in range(1, colNum): roomName = sheet.cell(row=i, column=j).value imageName = generateQrCode(roomName) if imageName: cell = 'B' + bytes(i) sheet.column_dimensions['B'].width = column_width sheet.row_dimensions[i].height = row_height sheet[cell].alignment = align img = Image(imageName) newsize = (90, 90) img.width, img.height = newsize column = 1 coloffset = cellw(0.05) row = i - 1 rowoffset = cellh(0.5) h, w = img.height, img.width size = XDRPositiveSize2D(p2e(h), p2e(w)) marker = AnchorMarker(col=column, colOff=coloffset, row=row, rowOff=rowoffset) img.anchor = OneCellAnchor(_from=marker, ext=size) sheet.add_image(img) wb.save(xlsxFileName)
def import_addition_pic_to_report(self, report_ws, region_no, fillpic_row, fillpic_col): addition_pic_path = self.get_addition_pic_path() pic_group_name = 'X' + str(region_no) picture_names = os.listdir(addition_pic_path) for picture_name in picture_names: picture_name_path = os.path.join(addition_pic_path, picture_name) if picture_name_path.upper().endswith( '.JPG') and pic_group_name in picture_name: # Call add image img = Image(picture_name_path) p2e = pixels_to_EMU c2e = cm_to_EMU # Assign picture size HEIGHT = 220 WIDTH = 250 # Function calculate offset cellh = lambda x: c2e((x * 49.77) / 99) cellw = lambda x: c2e((x * (18.65 - 1.71)) / 10) # Set Size and Postion col_offset = cellw(0.5) row_offset = cellh(0.75) first_marker = AnchorMarker(col=fillpic_col, colOff=col_offset, row=fillpic_row, rowOff=row_offset) size = XDRPositiveSize2D(p2e(WIDTH), p2e(HEIGHT)) # Paste Image to cell img.anchor = OneCellAnchor(_from=first_marker, ext=size) report_ws.add_image(img) # Prepare for next column fillpic_col += 3
def writeMeltCurves(sheet,r,c,imageFile): '''Adds a melt curve .jpeg file at the specified cell.''' image = Image(imageFile) image.anchor(sheet.cell(row=r,column=c),anchortype='oneCell') sheet.add_image(image)
def export_excel(_path, _df=None, _wb=None, _sheet_name='sheet1', _letter_fmt=None, _append=False, _frz='B2', _auto_flt=True, _auto_sz=False, _header_height=None, _col_width_=[20,20], _header_fmt=None, _header_rot=0, _zoom=100, _heatmap=0, _is_index=True, _index_name='Index', _header_txtcol='000000', _header_fillcol='d9f2f8', _img=None): import openpyxl as px from openpyxl.utils import get_column_letter time_start = time.perf_counter() if _path==None and _wb==None: print('[Error] Both of _path and _bw are None.') return print('[Exporting Excel file ...] Sheet : "{0}"'.format(_sheet_name)) #------------------------------------------- # 初期設定 #------------------------------------------- # Workbook作成 if _wb == None: if _append: # 既存ファイルにシート追加 try: wb = px.load_workbook(_path) except: _append = False # ファイルが存在しないときは新規作成 if not _append: # 新規ファイル wb = px.Workbook() else: wb = _wb _append = True # Worksheet作成 ws = wb.create_sheet(title=_sheet_name) #------------------------------------------- # DataFrameをWorksheetに書き込み #------------------------------------------- if _df is not None: #----- 作業用にDataFrameをコピー ----- df = _df.copy() # Timestampを文字列に変換(そのままだとエラーになるので) list_timestamp_col = list() # Timestampのセルが存在する列を探して文字列に変換する for col_name, col in df.iteritems(): for item in col: tp = type(item) if tp is pd._libs.tslibs.timestamps.Timestamp: list_timestamp_col.append(col_name) break for col in list_timestamp_col: df[col] = df[col].astype(str) df[col] = df[col].replace('NaT', '') #----- Excelファイル用フォーマットの作成 ----- base_font = '游ゴシック' from openpyxl.styles.fonts import Font from openpyxl.styles import PatternFill font_header_row = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_header_col = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_cell = Font(name=base_font, sz=10) align_header_row = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=_header_rot) align_header_col = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True) fill_header_row = PatternFill(patternType='solid', fgColor=_header_fillcol) fill_header_col = PatternFill(patternType='solid', fgColor=_header_fillcol) #----- データ出力 ----- # DataFrameをWorksheetにExport l = df.columns.tolist() if _is_index: l.insert(0, _index_name) # 行のindexを先頭列に追加 ws.append(l) count = 0 for i, row in df.iterrows(): # 一行ずつwsに追加していく l = row.values.tolist() if _is_index: l.insert(0, row.name) # 行のindexを先頭列に追加 ws.append(l) count += 1 print('\r - データコピー {0}/{1}'.format(count, len(df)), end="") print('') #----- Worksheetの書式設定 ----- # ヘッダー行(既定値) for cell in list(ws.rows)[0]: cell.font = font_header_row cell.alignment = align_header_row cell.fill = fill_header_row # ヘッダー行(個別) if _header_fmt != None: list_cell = list(ws.rows)[0] for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 cell = list_cell[index] except: continue # rotation try: rotation = fmt['rotation'] cell.alignment = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=rotation) except: pass # 文字色 try: text_color = fmt['txtcol'] cell.font = Font(name=base_font, b=True, sz=10, color=text_color) except: pass # 背景色 try: fill_color = fmt['fillcol'] cell.fill = PatternFill(patternType='solid', fgColor=fill_color) except: pass # 列ごとの書式設定用のリスト作成 list_dtxt_pat = list() list_dfill_pat = list() if _header_fmt != None: for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 except: continue # 文字色 try: text_color = fmt['dtxtcol'] list_dtxt_pat.append([index, Font(name=base_font, sz=10, color=text_color)]) except: pass # 背景色 try: dfill_color = fmt['dfillcol'] list_dfill_pat.append([index, PatternFill(patternType='solid', fgColor=dfill_color)]) except: pass # データ行書式設定 count = 0 for row in ws.iter_rows(min_row=2): # 書式設定 for cell in row: cell.font = font_cell # 列ごとの書式設定で上書き for list_pat in list_dtxt_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].font = list_pat[1] for list_pat in list_dfill_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].fill = list_pat[1] # Index列がある場合はIndex用設定 if _is_index: row[0].font = font_header_col # 先頭列のみ太字 row[0].alignment = align_header_col # 先頭列のみセンタリング row[0].fill = fill_header_col # 先頭列の塗りつぶし count += 1 print('\r - 書式設定 {0}/{1}'.format(count, len(df)), end="") print('') #----- セルの文字書式 ----- if type(_letter_fmt) is dict: # _header_fmtがあれば不要だが互換性のために残してある for col in ws.iter_cols(): col_name = col[0].value if col_name in _letter_fmt: num_format = _letter_fmt[col_name] for cell in col: cell.number_format = num_format elif type(_letter_fmt) is str: for col in ws.iter_cols(): for cell in col: cell.number_format = _letter_fmt # 列ごとの個別設定で上書き if _header_fmt != None: list_col = list(_header_fmt.keys()) for col in ws.iter_cols(): col_name = col[0].value if col_name in list_col: # 列書式一覧の辞書にこの列が存在する try: fmt = _header_fmt[col_name] num_format = fmt['dtxtformat'] for cell in col: cell.number_format = num_format except: pass # Worksheetの列幅調整 if _auto_sz: # 自動調整 for col in ws.columns: max_length = 0 column = col[0].column column = get_column_letter(column) # 数字をアルファベットに変換 cols = col if _header_rot!=90 else col[1:] for cell in cols: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) adjusted_width = (max_length + 2) * 1.1 ws.column_dimensions[column].width = adjusted_width else: for col in ws.columns: column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 width = _col_width_[0] if column == 1 else _col_width_[1] # 列幅 ws.column_dimensions[col_letter].width = width # 列ごとの個別調整 if _header_fmt != None: list_col = list(ws.columns) for head, fmt in _header_fmt.items(): try: width = fmt['width'] index = list(df.columns).index(head) if _is_index: index += 1 col = list_col[index] column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 ws.column_dimensions[col_letter].width = width except: pass # Worksheetの行の高さ調整 if _header_height != None: ws.row_dimensions[1].height = _header_height # ヒートマップ from openpyxl.formatting.rule import ColorScale, FormatObject from openpyxl.styles import Color if _heatmap == 1: # 赤 → 白 → 青 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('5A8AC6')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FCFCFF')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 2: # 白 → 橙 → 赤 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('FFFFFF'), Color('F8696B')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 3: # 赤 → 橙 → 白 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('FFFFFF')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=25) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) # 枠固定 if _frz != None: ws.freeze_panes = _frz # オートフィルタ if _auto_flt: ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column)+'1' # 表示倍率 ws.sheet_view.zoomScale = _zoom #------------------------------------------- # Worksheetに画像を挿入 #------------------------------------------- if _img != None: from openpyxl.drawing.image import Image for img in _img: fpath = img[0] # 挿入する画像ファイル anchor = img[1] # 挿入位置 px_img = Image(fpath) px_img.anchor = anchor ws.add_image(px_img) #------------------------------------------- # Excelファイルに書き込み #------------------------------------------- # 最後に不要なシートを削除 if 'Sheet' in wb.sheetnames: wb.remove(wb['Sheet']) print(' - ファイル書き込み...', end='') wb.save(_path) # 画像ファイル削除 if _img != None: for img in _img: is_delete = False # ファイルを削除するか否か if len(img) > 2: is_delete = img[2] if is_delete: # ファイル削除 os.remove(fpath) print ('\n ---> Finished. (処理時間:{0:.3f}[sec])'.format(time.perf_counter() - time_start )) return wb
def do_process_workbook(): print_if_debug("creating temp directory") if not args.keep_barcodes_in_home: tempdir = tempfile.mkdtemp() else: temp_dir_in_cwd = os.path.join(program_launch_cwd, 'barcode images') os.mkdir(temp_dir_in_cwd) tempdir = temp_dir_in_cwd print_if_debug("temp directory created as: " + tempdir) progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text="opening workbook") wb = openpyxl.load_workbook(old_workbook_path) ws = wb.worksheets[0] progress_numbers.configure(text="testing workbook save") wb.save(new_workbook_path) count = 0 save_counter = 0 progress_bar.configure(maximum=ws.max_row, value=count) progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) border_size = int(border_spinbox.get()) for _ in ws.iter_rows(): # iterate over all rows in current worksheet if not process_workbook_keep_alive: break try: count += 1 progress_bar.configure(maximum=ws.max_row, value=count, mode='determinate') progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) progress_bar.configure(value=count) # get code from column "B", on current row, add a zero to the end to make seven digits print_if_debug("getting cell contents on line number " + str(count)) upc_barcode_number = ws["B" + str(count)].value + "0" print_if_debug("cell contents are: " + upc_barcode_number) # select barcode type, specify barcode, and select image writer to save as png ean = barcode.get('ean8', upc_barcode_number, writer=ImageWriter()) # select output image size via dpi. internally, pybarcode renders as svg, then renders that as a png file. # dpi is the conversion from svg image size in mm, to what the image writer thinks is inches. ean.default_writer_options['dpi'] = int(dpi_spinbox.get()) # module height is the barcode bar height in mm ean.default_writer_options['module_height'] = float(height_spinbox.get()) # text distance is the distance between the bottom of the barcode, and the top of the text in mm ean.default_writer_options['text_distance'] = 1 # font size is the text size in pt ean.default_writer_options['font_size'] = int(font_size_spinbox.get()) # quiet zone is the distance from the ends of the barcode to the ends of the image in mm ean.default_writer_options['quiet_zone'] = 2 # save barcode image with generated filename print_if_debug("generating barcode image") with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as initial_temp_file_path: filename = ean.save(initial_temp_file_path.name[0:-4]) print_if_debug("success, barcode image path is: " + filename) print_if_debug("opening " + str(filename) + " to add border") barcode_image = pil_Image.open(str(filename)) # open image as pil object print_if_debug("success") print_if_debug("adding barcode and saving") img_save = pil_ImageOps.expand(barcode_image, border=border_size, fill='white') # add border around image width, height = img_save.size # get image size of barcode with border # resize cell to size of image ws.column_dimensions['A'].width = int(math.ceil(float(width) * .15)) ws.row_dimensions[count].height = int(math.ceil(float(height) * .75)) # write out image to file with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as final_barcode_path: img_save.save(final_barcode_path.name) print_if_debug("success, final barcode path is: " + final_barcode_path.name) # open image with as openpyxl image object print_if_debug("opening " + final_barcode_path.name + " to insert into output spreadsheet") img = OpenPyXlImage(final_barcode_path.name) print_if_debug("success") # attach image to cell print_if_debug("adding image to cell") img.anchor(ws.cell('A' + str(count)), anchortype='oneCell') # add image to cell ws.add_image(img) save_counter += 1 print_if_debug("success") # This save in the loop frees references to the barcode images, # so that python's garbage collector can clear them except Exception as barcode_error: print_if_debug(barcode_error) if save_counter >= file_limit - 50: print_if_debug("saving intermediate workbook to free file handles") progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text=str(count) + "/" + str(ws.max_row) + " saving") wb.save(new_workbook_path) print_if_debug("success") save_counter = 1 progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) progress_bar.configure(value=0) print_if_debug("saving workbook to file") progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text="saving") wb.save(new_workbook_path) print_if_debug("success") if not args.keep_barcode_files: print_if_debug("removing temp folder " + tempdir) shutil.rmtree(tempdir) print_if_debug("success") progress_bar.stop() progress_bar.configure(maximum=100, value=0, mode='determinate') progress_numbers.configure(text="")
def _main(): """ ∇ 以下1枚の画像を扱う """ """ 【分類器】 """ # { # "鉄骨工事":[[path1, path2, path3, path4, ...], [撮影日1, 撮影日2, None, 撮影日4, ...], [txt1, txt2, None, None, ...]], # "左官工事":[[path1, path2, path3, path4, ...], [撮影日1, None, 撮影日3, 撮影日4, ...], [txt1, None, None, txt4, ...]], # ..... # } classes = ['鉄骨工事', '左官工事', '塗装工事'] # 分類クラス book_dict = {} # 辞書の初期化 for i in range(len(classes)): book_dict[classes[i]] = [[], [], []] # 画像パス、撮影日、テキストを保持できる形にする """ ΔΔΔ ここから画像ごとの処理 ΔΔΔ for~ """ import glob #glob.glob("./*") image_pathes = glob.glob('./data/*.jpg') #image_path = './test/0010.jpg' for i, image_path in enumerate(image_pathes): print("") print("#" * 30) print("###" + str(i + 1) + "枚目の画像を読み込み中...") print("#" * 30) print("") print("【画像パスを取得:{}】".format(image_path)) label = resnet(image_path) print("【{}に分類】".format(label)) """ 【EXIF】 撮影日情報の取得 """ im = PILImage.open(image_path) # 画像ファイルを開く exif_dict = im.getexif() exif = {} for key, value in exif_dict.items(): exif[TAGS.get(key, key)] = value # 辞書の値を新たな辞書のキーとしている #pprint(exif) if "DateTime" in exif: datetime = exif["DateTime"] elif "DateTimeDigitized" in exif: datetime = exif["DateTimeDigitized"] elif "DateTimeOriginal" in exif: datetime = exif["DateTimeOriginal"] else: datetime = None if datetime is not None: #print("Datetime:{}".format(datetime)) ymd = datetime.split(" ")[0].split(":") #print("撮影日:{}年{}月{}日".format(ymd[0], ymd[1], ymd[2])) ymd = "撮影日:{}年{}月{}日".format(ymd[0], ymd[1], ymd[2]) print("【{}】".format(ymd)) else: ymd = None print("【!撮影日情報は取得されませんでした】") """ 【検出器】 PIL形式で画像そのものをdetect_imageの引数に渡し、 黒板を検出したバウンディングボックスの位置情報をタプルで返す bbox = (left, top, right, bottom) """ im = PILImage.open(image_path) bbox = YOLO.detect_image(YOLO(), im) print("黒板を検出:" + str(bbox)) # bboxが検出された場合のみ、OCR用に画像内の黒板を切り取る if bbox is not None: #im_crop = im.crop((40, 264, 275, 435)) im_crop = im.crop(bbox) #検出器のBBで黒板を切り抜く im_crop.save("./tmp1_crop.jpg") """ 【OCR】 """ # 前処理 width, height = im_crop.size expand_rate = 1.6 # 1. 拡大 (pillow) im_expand = im_crop.resize( (int(width * expand_rate), int(height * expand_rate))) im_expand.save("./tmp2_expand.jpg") # 2. 角度調整 (cv2) im_expand = cv2.imread("./tmp2_expand.jpg") im_rotate = rotate_img(im_expand) cv2.imwrite("./tmp3_rotate.jpg", im_rotate) # 3. グレースケール、大津の二値化 (cv2) #im_gray = cv2.cvtColor(im_rotate, cv2.COLOR_BGR2GRAY) im_gray = rgb2gray(im_rotate) cv2.imwrite("./tmp4_gray.jpg", im_gray) th, im_binary = otsu_thresh(im_gray) cv2.imwrite("./tmp5_binary.jpg", im_binary) # 4. 色彩反転(文字の黒色化、白背景化) (cv2) im_invert = cv2.bitwise_not(im_binary) #im_invert = optimize(im_binary) cv2.imwrite("./tmp6_invert.jpg", im_invert) # 5. 細線化 # 後で処理を加える im_thin = im_invert cv2.imwrite("./tmp7_thin.jpg", im_thin) # pyocr のツールをロード tools = pyocr.get_available_tools() tool = tools[0] # テキスト抽出 im_ = PILImage.open('./tmp7_thin.jpg') #im_ = PILImage.open('./tmp1_crop.jpg') txt = tool.image_to_string( im_, lang="jpn", builder=pyocr.builders.TextBuilder(tesseract_layout=3)) txt = txt.rsplit('\n\n') data_list_no_space = [a for a in txt if a != ' '] txt = '\n'.join(data_list_no_space) txt = txt.replace(' | ', ' ') print("") print("+" * 35) print(txt) print("+" * 35) print("") # bboxが検出されない場合 else: txt = None # ラベルの種類ごとに、imageを順に保持していく # クラス0 == 鉄骨工事の場合 for cl in classes: if label == cl: book_dict[cl][0].append(image_path) book_dict[cl][1].append(ymd) book_dict[cl][2].append(txt) ## クラス1 == 左官工事の場合 #if label == classes[1]: # book_dict[classes[1]][0].append(image_path) # book_dict[classes[1]][1].append(ymd) # book_dict[classes[1]][2].append(txt) # クラス2 == 塗装工事の場合 # if label == classes[2]: # book_dict[classes[2]][0].append(image_path) # book_dict[classes[2]][1].append(ymd) # book_dict[classes[2]][2].append(txt) print(book_dict) """ 【Openpyxl】 ラベル名ごとのkeyを持つ辞書を使いエクセルのワークブックを作成し、保持された順番で画像・テキスト等を貼り付けていく """ # 辞書のkeyをリスト化 keys = list(book_dict.keys()) # 下線を設定 border = Border(bottom=Side(style='thin', color='000000')) # 1. keys[0]=="鉄骨工事" のブック作成 book0 = Workbook() # ブック生成 sheet0 = book0['Sheet'] # シートを取得 sheet0.title = "Sheet1" # シート名の変更 # デフォルトの列幅は8.38 sheet0.column_dimensions['A'].width = 0.10 sheet0.column_dimensions['B'].width = 16.66 # デフォルトの列幅は8.38 sheet0.column_dimensions['J'].width = 0.10 sheet0.column_dimensions['K'].width = 16.66 # 1枚目の画像を挿入 img0 = XLImage(book_dict[classes[0]][0][0]) img0.height = 265 img0.width = 355 img0.anchor = 'B1' sheet0.add_image(img0) sheet0["G{}".format(1 + 0)] = book_dict[classes[0]][1][0] #1行目に日付 sheet0["G{}".format(2 + 0)] = keys[0] #2行目に工事種目 words = book_dict[classes[0]][2][0].split("\n") #4行目以降に施工状況 for word_id, word in enumerate(words): sheet0["G{}".format(word_id + 4)] = word # 2枚目の画像を挿入 img1 = XLImage(book_dict[classes[0]][0][1]) img1.height = 260 img1.width = 355 img1.anchor = 'A{}'.format(1 + 16) sheet0.add_image(img1) sheet0["G{}".format(1 + 16)] = book_dict[classes[0]][1][1] sheet0["G{}".format(2 + 16)] = keys[0] words = book_dict[classes[0]][2][1].split("\n") for word_id, word in enumerate(words): sheet0["G{}".format(word_id + 4 + 16)] = word # 3枚目の画像を挿入 img2 = XLImage(book_dict[classes[0]][0][2]) img2.height = 260 img2.width = 355 img2.anchor = 'A{}'.format(1 + 32) sheet0.add_image(img2) sheet0["G{}".format(1 + 32)] = book_dict[classes[0]][1][2] sheet0["G{}".format(2 + 32)] = keys[0] words = book_dict[classes[0]][2][2].split("\n") for word_id, word in enumerate(words): sheet0["G{}".format(word_id + 4 + 32)] = word # 下線を設定 #border = Border(bottom=Side(style='thin', color='000000')) for k in range(5): #A4版5ページ分の下線表示 for i in range(3): #A4版1ページ分の下線表示 for row_num in range(1 + (i * 16), 15 + (i * 16), 1): for col_num in range(7 + (k * 9), 10 + (k * 9)): sheet0.cell(row=row_num, column=col_num).border = border # for row_num in range(17, 31, 1): # for col_num in range(7, 10): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(33, 47, 1): # for col_num in range(7, 10): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(1, 15, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(17, 31, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(33, 47, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # 4枚目の画像を挿入 img0 = XLImage(book_dict[classes[0]][0][3]) img0.height = 265 img0.width = 355 img0.anchor = 'K1' sheet0.add_image(img0) sheet0["P{}".format(1 + 0)] = book_dict[classes[0]][1][3] #1行目に日付 sheet0["P{}".format(2 + 0)] = keys[0] #2行目に工事種目 words = book_dict[classes[0]][2][3].split("\n") #4行目以降に施工状況 for word_id, word in enumerate(words): sheet0["P{}".format(word_id + 4)] = word # for row_num in range(1, 15, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(17, 31, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # for row_num in range(33, 47, 1): # for col_num in range(16, 19): # sheet0.cell(row=row_num ,column=col_num).border = border # Sheet1の罫線を消す sheet0.sheet_view.showGridLines = False # 鉄骨工事のブックを保存 book0.save('./out/{}.xlsx'.format(classes[0]))
def append_data(num, ws, line): mp4_file = './data_mp4/%d-%d.mp4' % (num, 0) json_file = './data_json/%d-%d.json' % (num, 0) html_file = './data_html/%d.html' % (num) sl = str(line) if not os.path.exists(mp4_file): return None with open(json_file, 'r') as f: j = json.loads(f.read()) with open(html_file, 'r') as f: soup = BeautifulSoup(f.read(), 'html.parser') print("Add %d artichle" % num) ws['C' + sl].hyperlink = '%s/%d' % (cafe_base, num) ws['C' + sl].value = num ws['D' + sl].value = j['meta']['subject'] tbody = soup.select('.tbody') body = tbody[0].text.strip() if len(tbody) >= 1 else "" ws['E' + sl].value = exclude_template(body) reply = [x.text.strip() for x in soup.select('.box-reply2 .comm')] ws['F' + sl].value = '\n'.join(reply) tdate = soup.select('.tit-box .m-tcol-c.date') sdate = tdate[0].text.strip() if len(tdate) >= 1 else "" ws['G' + sl].value = sdate videos = j['videos']['list'] seq = [float(x['bitrate']['video']) for x in videos] max_value = max(seq) data = [(i, val) for (i, val) in enumerate(videos) if float(val['bitrate']['video']) == max_value] ws['H' + sl].value = data[0][1]['encodingOption']['width'] ws['I' + sl].value = data[0][1]['encodingOption']['height'] ws['J' + sl].value = data[0][1]['encodingOption']['name'] ws['K' + sl].value = data[0][1]['bitrate']['video'] ws['L' + sl].value = mp4_file # get imagePath vidcap = cv2.VideoCapture(mp4_file) frames = np.linspace(0, vidcap.get(cv2.CAP_PROP_FRAME_COUNT) - 1, 1, dtype=np.int) col = 13 cell_width = 17 ws.row_dimensions[line].height = 100 for f in frames: tname = '%d-%d.jpg' % (num, f) vidcap.set(cv2.CAP_PROP_POS_FRAMES, f) success, image = vidcap.read() if not success: print(" Bad Image") col += 1 continue height, width, layers = image.shape ratio = width / 128 new_h = int(height / ratio) new_w = int(width / ratio) resize = cv2.resize(image, (new_w, new_h)) cv2.imwrite(tname, resize) ws.column_dimensions[get_column_letter(col)].width = cell_width img = Image(tname, size=(width * 9, 10000)) c = ws[get_column_letter(col) + sl] img.anchor(c, 'oneCell') ws.add_image(img) # os.remove(tname) col += 1 return ws
def _put_chi_square_table(self, ws): img = Image('utils/probabilistic_tables/chi_square_table.png') img.anchor = 'I42' ws.add_image(img)
def create_front_sheet(village=False, no_print=False, nurses=False): """ Takes the fs_Res and fs_Con reports from eCase, and produces a formatted front sheet for use in admission files. Prints out 2 copies with banking account information, and 1 without for the admission filing. This is for the Admission officer and accountants """ sheet_book = Workbook() front_sheet = sheet_book.active basic_info_index = [ 'D6', 'D8', 'D9', 'D10', 'D12', 'D13', 'D14', 'D15', 'D16', 'I10', 'I13', 'I14', 'I15', 'I16' ] epoa_info_index = [ 'D21', 'D23', 'D24', 'D25', 'D26', 'I21', 'I23', 'I24', 'I25', 'I26' ] contact_info_index = [ 'D31', 'D33', 'D35', 'D36', 'D37', 'D40', 'D41', 'D42', 'D43', 'I31', 'I33', 'I35', 'I36', 'I37', 'I40', 'I41', 'I42', 'I43' ] funeral_info_index = [ 'D47', 'D48', 'I47', 'D51', 'D53', 'D54', 'D55', 'D57', 'D58', 'D59', 'D60', 'I51', 'I53', 'I54', 'I55', 'I57', 'I58', 'I59', 'I60' ] # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # headings main_heading_font = Font(size=14, bold=True, italic=True, color='000080') headings_font = Font(size=10, bold=True, italic=True, color='008000') sheet_titles_font = Font(size=10, bold=True, underline='single') sheet_headings = { 'RESIDENTS INFORMATION FRONT SHEET': 'B4', 'ENDURING POWER OF ATTORNEY DETAILS': 'B19', 'CONTACTS FOR HEALTH AND WELFARE DECISIONS': 'B29', 'FUNERAL DIRECTOR': 'B46' } sheet_titles = { 'Health and Welfare': 'B20', 'Property': 'G20', 'First Contact': 'B30', 'Second Contact': 'G30', 'Send Monthly SAV Account to': 'B50', 'Send Monthly Trust Account to': 'G50' } basic_info_fields = { 'Location at SAV': 'B6', 'Title': 'B8', 'Surname': 'B9', 'Forenames': 'B10', 'Preferred Name': 'B11', 'Date of Birth': 'B12', 'Place of Birth': 'B13', 'Religion': 'B14', 'Gender': 'B15', 'Marital Status': 'B16', 'Doctor at SAV': 'G10', 'Telephone No.': 'G11', 'NHI No': 'G13', 'Date Admitted': 'G14', 'Care Level': 'G15', 'Ethnic Group': 'G16' } # This is for two contacts epoa_info_fields = { 'B21': 'Name', 'B23': 'Home Phone', 'B24': 'Work Phone', 'B25': 'Mobile Phone', 'B26': 'E-mail', 'G21': 'Name', 'G23': 'Home Phone', 'G24': 'Work Phone', 'G25': 'Mobile Phone', 'G26': 'E-mail' } # This is for the Primary and secondary contacts contact_info_fields = { 'B31': 'Name', 'B33': 'Relationship', 'B35': 'Address', 'B40': 'Home Phone', 'B41': 'Work Phone', 'B42': 'Mobile Phone', 'B43': 'E-mail', 'G31': 'Name', 'G33': 'Relationship', 'G35': 'Address', 'G40': 'Home Phone', 'G41': 'Work Phone', 'G42': 'Mobile Phone', 'G43': 'E-mail' } # Funeral Director. Additional Monthly SAV and Trust account contact funeral_info_fields = { 'B47': 'Company Name', 'B48': 'Phone Number', 'G47': 'Type of Service', 'B51': 'Name', 'B53': 'Address', 'B57': 'Home Phone', 'B58': 'Work Phone', 'B59': 'Mobile Phone', 'B60': 'E-mail', 'G51': 'Name', 'G53': 'Address', 'G57': 'Home Phone', 'G58': 'Work Phone', 'G59': 'Mobile Phone', 'G60': 'E-mail' } front_sheet['B1'].font = main_heading_font # sheet headings writing to sheet & setting text styles for heading in sheet_headings: front_sheet[sheet_headings[heading]] = heading front_sheet[sheet_headings[heading]].font = headings_font # sheet titles writing to sheet & setting text style for title in sheet_titles: front_sheet[sheet_titles[title]] = title front_sheet[sheet_titles[title]].font = sheet_titles_font # Writing the basic info headers into the file for info in basic_info_fields: front_sheet[basic_info_fields[info]] = info # EPOA Details writing to sheet for epoa in epoa_info_fields: front_sheet[epoa] = epoa_info_fields[epoa] # Contact info writing to sheet# # # for contact in contact_info_fields: front_sheet[contact] = contact_info_fields[contact] # Funeral director info writing to sheet for funeral_info in funeral_info_fields: front_sheet[funeral_info] = funeral_info_fields[funeral_info] # sheet image writing to sheet & positioning logo = Image( r'J:\Quality Data\Data Technician\eCase Migration\ecase_automated\images/SAVLandscape.png' ) logo.anchor = 'A1' logo.width = 250 logo.height = 40 front_sheet.add_image(logo) sheet_book.save(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx') # Setting text borders for whole sheet styles.full_border(front_sheet, 'D6') styles.full_border(front_sheet, 'D8:D11') styles.full_border(front_sheet, 'D12:D17') styles.full_border(front_sheet, 'I10:I11') styles.full_border(front_sheet, 'I13:I17') styles.full_border(front_sheet, 'D21') styles.full_border(front_sheet, 'D23:D26') styles.full_border(front_sheet, 'I21') styles.full_border(front_sheet, 'I23:I26') styles.full_border(front_sheet, 'D31') styles.full_border(front_sheet, 'D33') styles.full_border(front_sheet, 'D35:D38') styles.full_border(front_sheet, 'D40:D43') styles.full_border(front_sheet, 'I31') styles.full_border(front_sheet, 'I33') styles.full_border(front_sheet, 'I35:I38') styles.full_border(front_sheet, 'I40:I43') styles.full_border(front_sheet, 'D47:D48') styles.full_border(front_sheet, 'I47') styles.full_border(front_sheet, 'D51') styles.full_border(front_sheet, 'I51') styles.full_border(front_sheet, 'D53:D55') styles.full_border(front_sheet, 'I53:I55') styles.full_border(front_sheet, 'D57:D60') styles.full_border(front_sheet, 'I57:I60') # Column widths styles.print_settings( front_sheet, widths=[0.15, 17.0, .15, 23.0, 4.15, 4.15, 16.0, .15, 28.0], landscape=False) respite = False # Basic Resident info Writing to sheet# # # doctors = ['Mascher', 'Jun', 'Mulgan', 'Hulley'] if os.path.isfile(rf'{constants.DOWNLOADS_DIR}\p_name.txt'): p_file = open(rf'{constants.DOWNLOADS_DIR}\p_name.txt') p_name = p_file.read() p_file.close() else: p_name = '' front_sheet['D11'] = p_name with open(rf'{constants.DOWNLOADS_DIR}\fs_Res.csv', newline='') as basic_info: basic_info_data = csv.reader(basic_info, delimiter=',', quotechar='"') basic_data = list(basic_info_data) try: if 'Andrew' in basic_data[1][0]: basic_data[1][0] = basic_data[1][0][20:len(basic_data[1][0])] if not any(substring in basic_data[1][9] for substring in doctors): respite = True month = [ '0', 'Jan', 'Feb', 'Mar', 'Apri', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ] for cell in basic_info_index: front_sheet[cell] = basic_data[1][basic_info_index.index(cell)] if cell == 'D12': front_sheet[cell] = ( f'{basic_data[1][4][8:10]}/' f'{month[int(basic_data[1][4][5:7])]}/' f'{basic_data[1][4][0:4]}') if cell == 'I14': front_sheet[cell] = ( f'{basic_data[1][11][8:10]}/' f'{month[int(basic_data[1][11][5:7])]}/' f'{basic_data[1][11][0:4]}') except IndexError: info_files_remover() return button_functions.popup_error( "9002: NHI is incorrect, please check you've entered it correctly " "and the resident is set up correctly with that NHI") except ValueError: #if date is empty front_sheet[cell] = "Not Available" pass for file in os.listdir(rf'{constants.DOWNLOADS_DIR}'): if re.match(r"^[A-Z]{3}[0-9]{4} Photo\.", file): photoname = file profile = Image(rf'{constants.DOWNLOADS_DIR}\{photoname}') profile.anchor = 'I2' profile.height = 140 profile.width = 100 front_sheet.add_image(profile) sheet_book.save(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx') with open(rf'{constants.DOWNLOADS_DIR}\fs_Con.csv', newline='') as contact_info: contact_info_data = csv.reader(contact_info, delimiter=',', quotechar='"') contact_data = list(contact_info_data) for row in contact_data[1:len(contact_data)]: if row[9] == 'First Contact': for cell in contact_info_index[0:9]: front_sheet[cell] = row[contact_info_index.index(cell)] elif row[9] == 'Second Contact': for cell in contact_info_index[9:18]: front_sheet[cell] = row[contact_info_index.index(cell) - 9] elif row[9] == 'EPA Welfare': front_sheet[epoa_info_index[0]] = row[0] front_sheet[epoa_info_index[1]] = row[5] front_sheet[epoa_info_index[2]] = row[6] front_sheet[epoa_info_index[3]] = row[7] front_sheet[epoa_info_index[4]] = row[8] elif row[9] == 'EPA Property': front_sheet[epoa_info_index[5]] = row[0] front_sheet[epoa_info_index[6]] = row[5] front_sheet[epoa_info_index[7]] = row[6] front_sheet[epoa_info_index[8]] = row[7] front_sheet[epoa_info_index[9]] = row[8] elif row[9] == 'Funeral Director': front_sheet[funeral_info_index[0]] = row[0] front_sheet[funeral_info_index[1]] = row[6] elif row[9] == 'Send Fees Account' or row[9] == 'Billing': front_sheet[funeral_info_index[3]] = row[0] front_sheet[funeral_info_index[4]] = row[2] front_sheet[funeral_info_index[5]] = row[3] front_sheet[funeral_info_index[6]] = row[4] front_sheet[funeral_info_index[7]] = row[5] front_sheet[funeral_info_index[8]] = row[6] front_sheet[funeral_info_index[9]] = row[7] front_sheet[funeral_info_index[10]] = row[8] elif row[9] == 'Send Trust Account' or row[9] == 'Guaranator': front_sheet[funeral_info_index[11]] = row[0] front_sheet[funeral_info_index[12]] = row[2] front_sheet[funeral_info_index[13]] = row[3] front_sheet[funeral_info_index[14]] = row[4] front_sheet[funeral_info_index[15]] = row[5] front_sheet[funeral_info_index[16]] = row[6] front_sheet[funeral_info_index[17]] = row[7] front_sheet[funeral_info_index[18]] = row[8] elif row[9] == 'Resident': front_sheet['B17'] = 'Email' front_sheet['D17'] = row[8] front_sheet['G17'] = 'Contact Number' front_sheet['I17'] = row[5] # Doctors numbers. SAV Drs dont want them on the front sheet anymore if respite: if row[9] == 'Medical Practitioner': if row[7] != '': front_sheet['I11'] = row[7] elif row[6] != '': front_sheet['I11'] = row[6] elif row[5] != '': front_sheet['I11'] = row[5] else: front_sheet['I11'] = 'No Number Present' try: # Printing out Frontsheet without monthly accounts fields # For Everyone front_sheet.print_area = 'B1:I48' sheet_book.save(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx') if not no_print: os.startfile(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx', 'print') # Second info copy just for village nurses if nurses: os.startfile(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx', 'print') # Printing out Frontsheet with monthly accounts fields # For Admissions and Village manager front_sheet.print_area = 'B1:I60' sheet_book.save(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx') if not no_print: if not nurses: os.startfile(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx', 'print') # Just for admissions if not no_print: if not village and not nurses: # print an extra accounts page if in the MCF os.startfile(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx', 'print') sheet_book.save(rf'{constants.OUTPUTS_DIR}\front_sheet.xlsx') sheet_book.close() info_files_remover() except PermissionError: info_files_remover() return button_functions.popup_error( "Could not print front sheets, as" " the file has been opened by someone")
def create_door_label(no_print=False): """ Takes the fs_Res and fs_Con reports from eCase, and prints a formatted Door Label to place on the front of the resident’s room """ try: sheet_book = Workbook() door_sheet = sheet_book.active if os.path.isfile(rf'{constants.DOWNLOADS_DIR}\p_name.txt'): p_file = open(rf'{constants.DOWNLOADS_DIR}\p_name.txt') p_name = p_file.read() p_file.close() else: p_name = '' with open(rf'{constants.DOWNLOADS_DIR}\fs_Res.csv', newline='') as basic_info: basic_info_data = csv.reader(basic_info, delimiter=',', quotechar='"') basic_data = list(basic_info_data) namecard_font = Font(size=36, bold=True, name='Arial') nhi_font = Font(size=28, bold=True, name='Copperplate Gothic Light') door_sheet['B6'] = basic_data[1][1] + ' ' + basic_data[1][ 3] + ' (' + p_name + ') ' + basic_data[1][2] door_sheet['B6'].font = namecard_font door_sheet['B6'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) door_sheet.merge_cells(start_row=6, start_column=2, end_row=18, end_column=10) door_sheet['C24'] = basic_data[1][2] door_sheet['C24'].font = nhi_font door_sheet['C27'] = basic_data[1][1] door_sheet['C27'].font = nhi_font door_sheet['C29'] = basic_data[1][3] door_sheet['C29'].font = nhi_font door_sheet['C35'] = 'NHI No:' door_sheet['C35'].font = nhi_font door_sheet['F35'] = basic_data[1][10] door_sheet['F35'].font = nhi_font # # # Inserting Resident Photo for file in os.listdir(rf'{constants.DOWNLOADS_DIR}'): if re.match(r"^[A-Z]{3}[0-9]{4} Photo\.", file): photoname = file profile = Image(rf'{constants.DOWNLOADS_DIR}\{photoname}') profile.anchor = 'H21' profile.height = 212 profile.width = 192 door_sheet.add_image(profile) sheet_book.save(rf'{constants.OUTPUTS_DIR}\door_label.xlsx') styles.full_border(door_sheet, 'B6:J18', border=['double']) styles.full_border(door_sheet, 'B21:J38', border=['double']) door_sheet.print_area = 'A5:K39' styles.print_settings(door_sheet, landscape=False) sheet_book.save(rf'{constants.OUTPUTS_DIR}\door_label.xlsx') sheet_book.close() if not no_print: os.startfile(rf'{constants.OUTPUTS_DIR}\door_label.xlsx', 'print') info_files_remover() except PermissionError: info_files_remover() return button_functions.popup_error( "Could not print Door Label, as" " the file has been opened by someone")
def test_image_as_group(self, SpreadsheetDrawing): src = """ <wsDr xmlns="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <twoCellAnchor> <from> <col>5</col> <colOff>114300</colOff> <row>0</row> <rowOff>0</rowOff> </from> <to> <col>8</col> <colOff>317500</colOff> <row>4</row> <rowOff>165100</rowOff> </to> <grpSp> <nvGrpSpPr> <cNvPr id="2208" name="Group 1" /> <cNvGrpSpPr> <a:grpSpLocks/> </cNvGrpSpPr> </nvGrpSpPr> <grpSpPr bwMode="auto"> </grpSpPr> <pic> <nvPicPr> <cNvPr id="2209" name="Picture 2" /> <cNvPicPr> <a:picLocks noChangeAspect="1" noChangeArrowheads="1"/> </cNvPicPr> </nvPicPr> <blipFill> <a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1" cstate="print"> </a:blip> <a:srcRect/> <a:stretch> <a:fillRect/> </a:stretch> </blipFill> <spPr bwMode="auto"> <a:xfrm> <a:off x="303" y="0"/> <a:ext cx="321" cy="88"/> </a:xfrm> <a:prstGeom prst="rect" /> <a:noFill/> <a:ln> <a:prstDash val="solid" /> </a:ln> </spPr> </pic> </grpSp> <clientData/> </twoCellAnchor> </wsDr> """ node = fromstring(src) drawing = SpreadsheetDrawing.from_tree(node) anchor = drawing.twoCellAnchor[0] drawing.twoCellAnchor = [] im = Image(PIL.Image.new(mode="RGB", size=(1, 1))) im.anchor = anchor drawing.images.append(im) xml = tostring(drawing._write()) diff = compare_xml(xml, src) assert diff is None, diff
# for data in data_list: # data_list에서 문장들을 하나씩 가지고 오기 img_list = data.find( 'img') # img태그(속성과 속성값이 없거나 필요없거나 하면 생략, img로도 충분히 가려낼수 있으니)값 img_src = img_list['src'] a_list = data.find('a') title = a_list['title'] title = re.sub('[^0-9a-zA-Zㄱ-힗]', '', title) link = "https://comic.naver.com" + a_list['href'] strong = data.find('strong').text #urlretrieve(img_src, './image/'+title+'.gif') img_file = Image('./image/' + title + '.gif') # pprint(img_file) #cell = sheet1.cell(row=row, column=1) img_file.anchor = 'A' + str(row) #pprint('A' + str(col)) sheet1.add_image(img_file) sheet1.cell(row=row, column=2).value = title sheet1.cell(row=row, column=3).value = strong sheet1.cell(row=row, column=4).value = link # col = col + 1 row = row + 1 wb.save("./webtoon.xlsx") #print(title, strong, link) ''' data2 = data1.findAll('dd') pprint(data2) data3 = data2[0].find('span') print(data3.text)
def run(): connection = None cursor = None try: connection = psycopg2.connect(user='******', password='******', host='127.0.0.1', port='5432', database='ksgmet') cursor = connection.cursor() workbook = Workbook() sheet = None for region in REGIONS: if sheet: sheet = workbook.create_sheet() else: sheet = workbook.active sheet.title = region.name sheet.page_setup.fitToWidth = 1 x, y = 1, 1 sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS)) desc_cell = sheet.cell(row=x, column=y, value=region.description) desc_cell.alignment = Alignment(wrap_text=True, vertical='top') row = sheet.row_dimensions[x] row.height = IMG_ROW_HEIGHT sheet.merge_cells(range_string='J1:L1') if region.map_file: img = Image('D:\\workspace\\MGR\\maps\\regions\\' + region.map_file) img.anchor = 'J1' img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) x += 1 for field in FIELDS: sheet.cell(row=x, column=y, value='Pole ' + field) sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + 5) field_row = sheet.row_dimensions[x] field_row.font = BOLD_FONT field_row.fill = PatternFill('solid', fgColor='FFFF00') x += 1 y = 1 for date_range in DATE_RANGES: # Header header = '{} - {}'.format(field, date_range.description) header_cell = sheet.cell(row=x, column=y, value=header) sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS)) header_cell.font = BOLD_FONT header_cell.alignment = Alignment(horizontal='center') # Bold the column col = sheet.column_dimensions[get_column_letter(y)] col.width = 40 x += 1 cell = sheet.cell(row=x, column=y, value='Odległość prognozy') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Ilość analizowanych prognoz') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Średnia różnica') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Średnia różnica wartości bezwzględnych') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Odchylenie standardowe') cell.font = BOLD_FONT # Go to data coll x -= 4 y += 1 for prediction in PREDICTIONS: cell = sheet.cell(row=x, column=y, value=prediction.description()) cell.font = BOLD_FONT x += 1 data = execute_query(cursor, region, date_range, field, prediction) for val in data: sheet.cell(row=x, column=y, value=val) x += 1 # Back up to data coll start and go to the next one y += 1 x -= 5 if region == ALL_DATA: img_row = x + 5 img_col = y - len(PREDICTIONS) - 1 sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 2) row = sheet.row_dimensions[img_row] row.height = IMG_ROW_HEIGHT img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_avg_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) img_col = img_col + 3 sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 5) img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_abs_avg_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) img_col = img_col - 3 img_row = img_row + 1 row = sheet.row_dimensions[img_row] row.height = IMG_ROW_HEIGHT sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 2) img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_std_dev_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) # Go back to header x -= 1 # Space between date ranges col = sheet.column_dimensions[get_column_letter(y)] col.fill = PatternFill('solid', fgColor='E0E0E0') y += 1 # New field, 8 = 5 data rows, 2 img row and one space x += 9 y = 1 workbook.save('D:\\workspace\\MGR\\first_analysis\\data.xlsx') finally: if cursor: cursor.close() if connection: connection.close()
def main(cell_w_str, cell_h_str, files): wb = Workbook() ws = wb.active # some constants, may be different from each languages and versions # cell native size multiplier (native cell size -> openpyxl cell size) # can be calibrated by checking applied cell size in excel cwnm, chnm = 24.3 / 23.6, 1 # cell pixel size multiplier (openpyxl cell size -> pixel cell size) # can be calibrated by checking displayed cell size in pixels on 100% zoom in excel cwm, chm = 1800 / 180, 582 / 350 # image pixel size multiplier (pixel image size -> openpyxl image size) # can be calibrated by checking displayed image size in pixels on 100% zoom in excel iwm, ihm = 4 / 5, 4 / 5 # image pixel offset multiplier (pixel image offset -> openpyxl image offset) # can be calibrated by checking displayed image offset in pixels on 100% zoom in excel iwom, ihom = 4 / 5, 4 / 5 cell_w_native = float(cell_w_str) * cwnm cell_h_native = float(cell_h_str) * chnm file_count = len(files) for i, file in enumerate(files): print('Image %02d/%02d' % (i + 1, file_count)) #set target row, col, and their size row, col = i, 0 ws.column_dimensions[get_column_letter(col + 1)].width = cell_w_native ws.row_dimensions[row + 1].height = cell_h_native cell_w, cell_h = cell_w_native * cwm, cell_h_native * chm #open image and get dimensions img = Image(file) img_w, img_h = img.width, img.height # calculate image dimensions to cell size cell_ratio = cell_w / cell_h image_ratio = img_w / img_h if image_ratio > cell_ratio: scale = cell_w / img_w x_offset = 0 y_offset = (cell_h - img_h * scale) / 2 else: scale = cell_h / img_h x_offset = (cell_w - img_w * scale) / 2 y_offset = 0 img_size = XDRPositiveSize2D(pixels_to_EMU(img_w * iwm * scale), pixels_to_EMU(img_h * ihm * scale)) # insert image using OneCellAnchor (no stretching error) marker = AnchorMarker(col=col, colOff=pixels_to_EMU(x_offset * iwom), row=row, rowOff=pixels_to_EMU(y_offset * ihom)) img.anchor = OneCellAnchor(_from=marker, ext=img_size) ws.add_image(img) # save file wb.save('images.xlsx')
def read_drawings(ws, drawings_path, archive, valid_files): """ Given a worksheet and the XML of its drawings file, links drawings to cells """ drawings_codename = os.path.split(drawings_path)[-1] rels_file = PACKAGE_DRAWINGS_RELS + '/' + drawings_codename + '.rels' if rels_file not in valid_files: return None rels_source = archive.read(rels_file) rels_root = fromstring(rels_source) root = fromstring(archive.read(drawings_path)) for node in root: col, row = 0, 0 posX = posY = cX = cY = None name = u'' cell_from = node.find('{%s}from' % SHEET_DRAWING_NS) if cell_from is not None: col = cell_from.find('{%s}col' % SHEET_DRAWING_NS) if col is not None: col = int(col.text) row = cell_from.find('{%s}row' % SHEET_DRAWING_NS) if row is not None: row = int(row.text) cell = ws['%s%s' % (get_column_letter(col + 1), row + 1)] pos = node.find('{%s}pos' % SHEET_DRAWING_NS) if pos is not None: posX, posY = _get_position(pos) ext = node.find('{%s}ext' % SHEET_DRAWING_NS) if ext is not None: cX, cY = _get_dimension(ext) pic = node.find('{%s}pic' % SHEET_DRAWING_NS) if pic is not None: sp_pr = pic.find('{%s}spPr' % SHEET_DRAWING_NS) if sp_pr is not None: xfrm = sp_pr.find('{%s}xfrm' % DRAWING_NS) if xfrm is not None: ext = xfrm.find('{%s}ext' % DRAWING_NS) if ext is not None: cX, cY = _get_dimension(ext) nv_pic_pr = pic.find('{%s}nvPicPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: nv_pic_pr = nv_pic_pr.find('{%s}cNvPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: name = nv_pic_pr.attrib.get('name', '') blip_fill = pic.find('{%s}blipFill' % SHEET_DRAWING_NS) if blip_fill is not None: blip = blip_fill.find('{%s}blip' % DRAWING_NS) if blip is not None: rid = blip.attrib.get('{%s}embed' % REL_NS) if rid is not None: image_file = read_image_file(rels_root, rid, valid_files) if image_file: img = Image(BytesIO(archive.read(image_file))) if posY is not None and posX is not None: img.drawing.top = posY img.drawing.left = posX else: img.anchor(cell, anchortype='oneCell') if cX is not None and cY is not None: img.drawing.width = cX img.drawing.height = cY img.drawing.name = name ws.add_image(img)
def pic_insert(self, rw=1, col=1, **kwargs): """Вставка картинки Keyword arguments: rw -- int номер строки col -- int номер колонки max_col -- int количество занимаемых колонок max_row -- int количество занимаемых строк path -- str путь к картинке align -- str буквенное обозначеение горизонтального выравнивания картинки: l - left r - right c - center valign -- str буквенное обозначеение вертикального выравнивания картинки: t - top c - center b - bottom cf -- float коэффициент размера картинки, 1 = 100% fit -- bool вписать картинку в пределы ячеек default(True) px -- int количество пикселей зазор до границы """ path = kwargs.get("path", "") cf = kwargs.get("cf", 1) max_col = kwargs.get("max_col", 1) max_row = kwargs.get("max_row", 1) align = kwargs.get("align", "l") valign = kwargs.get("valign", "t") fit = kwargs.get("fit", True) px = kwargs.get("px", 0) if not os.path.exists(path): return None img = Image(path) img_h, img_w = img.height, img.width p2e = pixels_to_EMU cell_h = self.get_row_size(rw, max_row)[1] cell_w = self.get_col_size(col, max_col)[1] if fit: img_ratio = img_w / img_h img_h = cell_h img_w = img_h * img_ratio if img_w > cell_w: img_w = cell_w img_h = img_w / img_ratio img_h *= cf img_w *= cf img_size = XDRPositiveSize2D(p2e(img_w), p2e(img_h)) d_h = cell_h - img_h d_w = cell_w - img_w hl = px hr = d_w - px hc = cell_w / 2 - img_w / 2 vt = px vb = d_h - px vc = d_h / 2 h_align = {"l": hl, "c": hc, "r": hr} v_align = {"t": vt, "c": vc, "b": vb} row_offset = p2e(v_align[valign]) col_offset = p2e(h_align[align]) marker = AnchorMarker(col=col - 1, colOff=col_offset, row=rw - 1, rowOff=row_offset) img.anchor = OneCellAnchor(_from=marker, ext=img_size) self.ws.add_image(img)
def hotbar_import_picture(self, report_ws, bga_pic_row, hotbar_pic_row, con_pic_row, import_name): b2b_pic_path, solder_pic_path = self.get_pic_folder() if import_name == 'BGA': row_export = bga_pic_row else: row_export = hotbar_pic_row # if b2b_pic_path == '' and solder_pic_path == '': # status = 'ไม่มีโฟลเดอร์รูปภาพใน Solder mask' # elif b2b_pic_path == '': # status = 'ไม่มีโฟลเดอร์ B2B ใน Solder mask' # elif solder_pic_path == '': # status = f'ไม่มีโฟลเดอร์ {import_name} ใน Solder mask' # else: # status = 'COMPLETE' status = 'COMPLETE' # Import Hot Bar& B2B Picture COLUMN_INSERT = 1 if solder_pic_path != '' and len(solder_pic_path) != 0: solder_pics = sorted(Path(solder_pic_path).iterdir(), key=os.path.getmtime) for pic in solder_pics: if not str(pic).upper().endswith('JPG'): solder_pics.remove(pic) for i in range(1, len(solder_pics), 2): if str(solder_pics[i]).upper().endswith('JPG') and str( solder_pics[i - 1]).upper().endswith('JPG'): # Get Picture first_pic = solder_pics[i - 1] second_pic = solder_pics[i] # Call add image first_img = Image(first_pic) second_img = Image(second_pic) p2e = pixels_to_EMU c2e = cm_to_EMU # Assign picture size HEIGHT = 50 WIDTH = 50 # Function calculate offset cellh = lambda x: c2e((x * 49.77) / 99) cellw = lambda x: c2e((x * (18.65 - 1.71)) / 10) # Set Size and Postion colloff1 = cellw(0.1) colloff2 = cellw(1) rowoffset = cellh(0.5) first_marker = AnchorMarker(col=COLUMN_INSERT, colOff=colloff1, row=row_export, rowOff=rowoffset) second_marker = AnchorMarker(col=COLUMN_INSERT, colOff=colloff2, row=row_export, rowOff=rowoffset) size = XDRPositiveSize2D(p2e(HEIGHT), p2e(WIDTH)) # Paste Image to cell first_img.anchor = OneCellAnchor(_from=first_marker, ext=size) report_ws.add_image(first_img) second_img.anchor = OneCellAnchor(_from=second_marker, ext=size) report_ws.add_image(second_img) # Prepare for next COLUMN_INSERT += 1 # Import Connector Picture COLUMN_INSERT = 1 if b2b_pic_path != '' and len(b2b_pic_path) != 0: b2b_pics = sorted(Path(b2b_pic_path).iterdir(), key=os.path.getmtime) for pic in b2b_pics: if not str(pic).upper().endswith('JPG'): b2b_pics.remove(pic) for i in range(1, len(b2b_pics), 2): if str(b2b_pics[i]).upper().endswith('JPG') and str( b2b_pics[i - 1]).upper().endswith('JPG'): # Get Picture first_pic = b2b_pics[i - 1] second_pic = b2b_pics[i] # Call add image first_img = Image(first_pic) second_img = Image(second_pic) p2e = pixels_to_EMU c2e = cm_to_EMU # Assign picture size HEIGHT = 50 WIDTH = 50 # Function calculate offset cellh = lambda x: c2e((x * 49.77) / 99) cellw = lambda x: c2e((x * (18.65 - 1.71)) / 10) # Set Size and Postion colloff1 = cellw(0.1) colloff2 = cellw(1) rowoffset = cellh(0.5) first_marker = AnchorMarker(col=COLUMN_INSERT, colOff=colloff1, row=con_pic_row, rowOff=rowoffset) second_marker = AnchorMarker(col=COLUMN_INSERT, colOff=colloff2, row=con_pic_row, rowOff=rowoffset) size = XDRPositiveSize2D(p2e(HEIGHT), p2e(WIDTH)) # Paste Image to cell first_img.anchor = OneCellAnchor(_from=first_marker, ext=size) report_ws.add_image(first_img) second_img.anchor = OneCellAnchor(_from=second_marker, ext=size) report_ws.add_image(second_img) # Prepare for next COLUMN_INSERT += 1 return status