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():
"""
∇ 以下１枚の画像を扱う
"""
"""
【分類器】
"""
# {
# "鉄骨工事":[[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
# １枚目の画像を挿入
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
# ３枚目の画像を挿入
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版１ページ分の下線表示
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
