def insertPngFile(self,tab_name,image_name,anchore):
writer = pd.ExcelWriter(self._sFileName, engine='openpyxl')
writer.book = load_workbook(self._sFileName)
ws = writer.book[tab_name]
img=Image(image_name+'.png')
ws.add_image(img,anchore)
writer.save()
def insert_image_xlsx(self, sheet_name, rownum, col_letter, img_file):
'''inserts image into specified sheet and cell within Excel workbook'''
ws = self.xl_workbook[sheet_name]
cell = '{}{}'.format(
col_letter,
rownum) # will be where upper left corner of image placed
img_obj = Image(img_file)
ws.add_image(img_obj, cell)
def insertpic(ws, picname='a留空', position='C22', width=70, heigh=25):
width = 85
heigh = 30
path = getpicpath(picname)
img = Image(path)
img.width = width
img.height = heigh
ws.add_image(img, position)
return ws
def save_two_random(self, y1, y2, itreation, mse_loss):
two_random_path = saving_path + "\\result_random.xlsx"
sheet_name = str(itreation)
if itreation == 0:
wb = Workbook()
wb.save(two_random_path)
wb = load_workbook(two_random_path)
wb.create_sheet(sheet_name, 0)
color = "YlGnBu"
size = 5
cluster = CN_Cluster(color)
# Create first matrix
# plt.close()
# plt.title('First Matrix')
# npimg = y1.cpu().numpy()
# plt.imshow(npimg, cmap="YlGnBu") # YlGnBu
# plt.savefig(saving_path + 'original_matrix_images\\' + sheet_name + '1.png', dpi=50)
title = 'First Matrix'
cluster.plot_corr(y1.cpu().numpy(), size, title, saving_path + 'original_matrix_images\\' + sheet_name + '1.png')
# Create second matrix
# plt.close()
# plt.title('Second Matrix')
# npimg = y2.cpu().numpy()
# plt.imshow(npimg, cmap="YlGnBu") # YlGnBu
# plt.savefig(saving_path + 'original_matrix_images\\' + sheet_name + '2.png', dpi=50)
title = 'Second Matrix'
cluster.plot_corr(y2.cpu().numpy(), size, title, saving_path + 'original_matrix_images\\' + sheet_name + '2.png')
# Add photos to excel sheet
# Activate worksheet
active = wb[sheet_name]
active.append([mse_loss])
# Insert plot into worksheet
# Select active sheet and cell reference
img = Image(saving_path + 'original_matrix_images\\' + sheet_name + '1.png')
active.add_image(img, 'A3')
img_second_matrix = Image(saving_path + 'original_matrix_images\\' + sheet_name + '2.png')
active.add_image(img_second_matrix, 'F3')
wb.save(two_random_path)
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 post(self, request, format=None):
"""
Return excel file
"""
serializer = self.serializer_class(data=request.data)
if serializer.is_valid():
serializer.save()
wb = Workbook()
ws = wb.active
ws['A1'] = 'TEE SHIRT DESIGN'
if serializer.data['photo']:
http = urllib3.PoolManager()
r = http.request('GET', serializer.data['xls_photo'])
image_file = io.BytesIO(r.data)
img = Image(image_file)
ws.add_image(img, 'A2')
ws['B1'] = 'BRAND NAME'
ws['B2'] = serializer.data['brand_name']
ws['C1'] = 'PRODUCT TITLE'
ws['C2'] = serializer.data['title']
ws['D1'] = 'BULLET POINT ONE'
ws['D2'] = serializer.data['tags']
ws['E1'] = 'BULET POINT TWO'
ws['E2'] = serializer.data['main_tags']
ws['F1'] = 'DESCRIPTION BOX'
ws['F2'] = serializer.data['description']
ws['G1'] = 'SELECTED KEYWORDS FROM WC'
ws['G2'] = serializer.data['keywords']
ws.column_dimensions["A"].width = 60
ws.column_dimensions["B"].width = 60
ws.column_dimensions["C"].width = 60
ws.column_dimensions["D"].width = 60
ws.column_dimensions["E"].width = 60
ws.column_dimensions["F"].width = 60
ws.column_dimensions["G"].width = 60
ws.row_dimensions[2].height = 100
filename = int(time.time())
s3c = boto3.client(
's3',
aws_access_key_id=settings.AWS_ACCESS_KEY_ID,
aws_secret_access_key=settings.AWS_SECRET_ACCESS_KEY)
handle_xls = StringIO(save_virtual_workbook(wb))
xls_file = s3c.put_object(Bucket=settings.AWS_STORAGE_BUCKET_NAME,
Key='exel/{0}.xlsx'.format(filename),
Body=handle_xls.read())
result = {
'data': serializer.data,
'file': '{0}exel/{1}.xlsx'.format(settings.AWS_S3_ROOT,
filename)
}
return Response(result)
else:
return Response(serializer.errors,
status=status.HTTP_400_BAD_REQUEST)
def img_output(pdf_path, output_path):
wb = xlsxwriter.Workbook(output_path)
doc = fitz.open(pdf_path)
for i in range(len(doc)):
img_index = 1
ws = wb.add_worksheet(str(i + 1))
im_list = doc.getPageImageList(i)
for img in doc.getPageImageList(i):
xref = img[0]
pix = fitz.Pixmap(doc, xref)
if pix.width < 100 or pix.height < 100:
pix = None
continue
print('Page: %d, Xref: %d' % (i, xref))
if pix.n < 5: # this is GRAY or RGB
try:
img_name = IMG_PATH + "p%s-%s.png" % (i, xref)
pix.writePNG(img_name)
img = Image(img_name)
cell_name = 'B' + str(img_index * 10)
ws.insert_image(cell_name, img_name, {'positioning': 1})
img_index += 1
print('Add image: %s to Page: %s in cell: %s' %
(img_name, str(i), cell_name))
except RuntimeError:
pix = None
continue
else: # CMYK: convert to RGB first
img_name = IMG_PATH + "p%s-%s.png" % (i, xref)
pix1 = fitz.Pixmap(fitz.csRGB, pix)
pix1.writePNG(img_name)
img = Image(img_name)
cell_name = 'B' + str(img_index * 10)
ws.insert_image(cell_name, img_name, {'positioning': 1})
img_index += 1
print('Add image: %s to Page: %s in cell: %s' %
(img_name, str(i), cell_name))
pix1 = None
pix = None
wb.close()
def insert_img():
print('===insert_img===')
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
img = Image('frog.png')
ws.add_image(img, 'A2')
wb.save("insert_img.xlsx")
def test_check_anchor_image(datadir):
datadir.chdir()
from ..spreadsheet_drawing import _check_anchor
from PIL.Image import Image as PILImage
im = Image(PILImage())
anc = _check_anchor(im)
assert anc._from.row == 0
assert anc._from.col == 0
assert anc.ext.height == 0
assert anc.ext.width == 0
def paste_image(ws2, right_num_groups):
before_cell_num = 0
for num in range(len(right_num_groups)):
capture_num = 2
if num != 0:
cell_size = check_img_size(right_num_groups[num - 1])
capture_num = before_cell_num + cell_size + 2
capture_area = 'A' + str(capture_num)
before_cell_num = capture_num
ws2.add_image(Image(right_num_groups[num]), capture_area)
def insertinexcel(img_name, column, row, sheet):
try:
print(img_name)
img = Image(img_name)
except FileNotFoundError:
return '[에러 3] 사진을 찾을 수 없습니다.'
else:
img.width, img.height = FIXED_SIZE
sheet.add_image(img, column + str(row))
return '[완료] ' + str(column) + str(row) + '에 사진을 넣었습니다.'
def main():
book = Workbook()
sheet = book.active
img = Image('dream_lake_colorado.png')
sheet['A1'] = 'Beautiful scenery'
sheet.add_image(img, 'B2')
book.save('sheet_image.xlsx')
def beautify_excel():
'''
美化输出的excel文件
'''
from openpyxl.styles import Font, Border
from openpyxl.formatting.rule import DataBarRule
from openpyxl.drawing.image import Image
# 打开文件，读取输出
result = openpyxl.load_workbook(f[:-4] + ' [OUTPUT].xlsx')
table = result.worksheets[0]
nrows = table.max_row
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min',
start_value=0,
end_type='max',
end_value=90,
color="FFFF0000",
showValue="None",
minLength=None,
maxLength=None)
#设置 Sheet name，添加列名
table.title = '收益统计'
table['A1'].value = 'Open Signal'
table['B1'].value = 'Close Signal'
# 去除重复的列名，去除杂乱的边框
for row in range(nrows + 1, 0, -1):
for j in range(len(table[row])):
table[row][j].font = font
table[row][j].border = Border(outline=False)
if table[row][j].value == table[1][j].value and row > 1:
table[row][j].value = None
# 加入数据条
table.conditional_formatting.add('C1:C' + str(nrows), rule)
# 设置列宽
table.column_dimensions['A'].width = 13
table.column_dimensions['B'].width = 13
table.column_dimensions['C'].width = 14
# 插入图表
wg = result.create_sheet(title='收益率平稳性')
# wg.active
graph = Image(f[:-4] + 'AC figure.png')
wg.append([' '])
wg.add_image(graph, 'A1')
result.save(f[:-4] + ' [OUTPUT].xlsx')
print('已完成excel输出 文件路径 ' + f[:-4] + ' [OUTPUT].xlsx')
return 0
def write_sheet4_autocorrelation_graph():
# 输出自相关系数曲线并保存
import matplotlib.pyplot as plt
plt.figure()
pd.plotting.autocorrelation_plot(
ans['accumulated_profit'].pct_change().dropna(), c='r').get_figure().savefig(folder+f+'AC figure.png')
# 插入图表
wg = result.create_sheet(title='收益率平稳性')
graph = Image(folder+f+'AC figure.png')
wg.append([' '])
wg.add_image(graph, 'A1')
def get_signature_image():
"""電子印鑑の画像を取得する。
:return:
"""
from django.conf import settings
img_path = os.path.join(settings.STATICFILES_DIRS[0], 'admin/img/signature.png')
img = Image(img_path)
img.width = 90
img.height = 90
return img
def insert_picture(self, range, image):
"""
插入图片
:param range: 需要插入图片的位置
:param image: 插入的图片
:return:
"""
from openpyxl.drawing.image import Image
ws[range] = "You should see three logos below"
img = Image(image)
ws.add_image(img, range)
def set_mast_header(dealer, logo_name):
"""
place logo, deaelername and date on first page of sheet
"""
date = "Report Date: %s " % (
datetime.datetime.today().strftime('%m/%d/%Y'))
img = Image(logo_name)
dealer['wsNew'].add_image(img, 'B1')
dealer['wsNew']['B5'] = dealer['name']
dealer['wsNew'].cell(column=(len(dealer['columns']) - 2),
row=5).value = date
def create_excel_workbook(self, the_date, graph_1_path, graph_2_path): # {
# TRY THE FOLLOWING
try: # {
print(self.desktop_dir)
# CREATE STR VAR FOR filename convention
ts_str = str(the_date)[:10]
# CREATE FILENAME VAR
filename_var = str("QC_TR_Metrics_" + str(ts_str) + ".xlsx")
# CREATE FULL WORKBOOK PATH
workbook_path = os.path.join(self.desktop_dir, str(filename_var))
print("WORKBOOK PATH == " + str(workbook_path))
# CREATE NEW WORKBOOK
wb = Workbook()
wb.save(workbook_path)
# ADD SHEETS TO WORKBOOK
# DESIGNATE SHEET NAME AND POSITION
sheet1 = wb.create_sheet('Graphs', 0)
# ACTIVATE WORKSHEET
active = wb['Graphs']
# INSERT PLOT INTO WORKSHEET
# Select Active sheet and cell reference
img_graph_1 = Image(graph_1_path)
active.add_image(img_graph_1, 'A1')
# INSERT PLOT INTO WORKSHEET
# Select Active sheet and cell reference
img_graph_2 = Image(graph_2_path)
active.add_image(img_graph_2, 'N1')
"""
# INSERT PLOT INTO WORKSHEET
# Select Active sheet and cell reference
img_graph_3 = Image(graph_3_path)
active.add_image(img_graph_3, 'Z1')
"""
# SAVE WORKBOOK
wb.save(workbook_path)
# }
except: # {
pass
# }
else: # {
print("Operation Completed Successfully...")
def uploadPhoto(self):
wb = openpyxl.load_workbook(self.filename)
try:
ws = wb["违规烟草照片"]
except Exception as e:
print(e)
ws = wb.create_sheet(title="违规烟草照片")
self.pil_image.save("./tmp.jpg")
img_file = Image("./tmp.jpg")
ws.add_image(img_file, "A" + str(self.photo_index * 30 + 1))
wb.save(self.filename)
self.photo_index += 1
def writeGraph(markets, graph_ws, start_row):
market_images = [
Image(market_file, size=[900, 1200]) for market_file in markets
]
start_column = 1
for image in market_images:
graph_ws.add_image(
image,
convertColumnNumToLetter(start_column) + str(start_row))
start_column += 13
graph_ws.append(['\n'] * 2)
def scrape_and_run(mybook):
# scrape on goodreads.com using desire genre type or key word
# and save the titles and autors in a csv file
page = requests.get("https://www.goodreads.com/search?q=" + mybook)
soup = bs(page.content, 'html.parser')
titles = soup.find('a', class_='bookTitle')
authors = soup.find('a', class_='authorName')
image_dir = os.getcwd() + "/images/mybook"
## check if the desire genre path exists
## create a new one if it doesnt
if not os.path.exists(image_dir):
os.makedirs(image_dir)
try:
book_page = requests.get("https://www.goodreads.com" + titles['href'])
soup = bs(book_page.content, 'html.parser')
ratingVal = soup.find('span', itemprop='ratingValue').get_text()
genres = soup.find_all('a', class_='actionLinkLite bookPageGenreLink')
genre = ""
for g in genres:
genre += (g.get_text())
http = urllib3.PoolManager()
imgUrl = soup.find('img', id='coverImage')
r = http.request('GET', imgUrl['src'])
image_file = io.BytesIO(r.data)
image_file.width = 4
image_file.height = 5
img = Image(image_file)
try:
nofPages = soup.find('span', itemprop='numberOfPages').get_text()
except AttributeError as exc:
print(exc)
return None
nrating = soup.find('meta', itemprop='ratingCount')['content']
nreview = soup.find('meta', itemprop='reviewCount')['content']
title_name = titles.get_text()
author_name = authors.get_text()
# print(nofPages)
# print(nrating)
# print(nreview)
book = Book(title_name, author_name, ratingVal, genre, nofPages,
nrating, nreview, img)
return book
except TypeError as exc:
print(exc)
return None
def writeRowToFile(ws, data, current_row, export_location):
columns = ['A', 'B', 'C', 'D', 'E']
for column in columns:
ws["%s%s" % (column, current_row)] = data[column]
location = export_location + "/images/" + data['E'] + ".jpeg"
qr_img = qrcode.make(data['E'])
qr_img.save(location)
img = Image(location)
ws.add_image(img, ('F%s' % current_row))
def __set_excel_image(self, url: str, cell: str):
image = PIL.Image.open(requests.get(url, stream=True).raw)
with NamedTemporaryFile(delete=False) as tmp:
image.save(tmp, image.format)
tmp.seek(0)
sheet_image = Image(tmp.name)
sheet_image.width = 322.393700787818
sheet_image.height = 202.20472440971
self.work_sheet.add_image(sheet_image, cell)
def make_summary_table(forest_change, carbon_change, area_missing, area_water,
area_non_forest, area_site, initial_forest_area,
initial_carbon_total, year_start, year_end, out_file):
def tr(s):
return QtWidgets.QApplication.translate("LDMP", s)
wb = openpyxl.load_workbook(
os.path.join(os.path.dirname(os.path.realpath(__file__)), 'data',
'summary_table_tc.xlsx'))
##########################################################################
# SDG table
ws_summary = wb.get_sheet_by_name('Total Carbon Summary Table')
ws_summary.cell(6, 3).value = initial_forest_area
ws_summary.cell(7, 3).value = area_non_forest
ws_summary.cell(8, 3).value = area_water
ws_summary.cell(9, 3).value = area_missing
#ws_summary.cell(10, 3).value = area_site
ws_summary.cell(18, 2).value = initial_forest_area
ws_summary.cell(18, 4).value = initial_carbon_total
write_col_to_sheet(ws_summary, np.arange(year_start, year_end + 1), 1,
18) # Years
write_table_to_sheet(ws_summary, forest_change, 19, 3)
write_table_to_sheet(ws_summary, carbon_change, 19, 5)
try:
ws_summary_logo = Image(
os.path.join(os.path.dirname(__file__), 'data',
'trends_earth_logo_bl_300width.png'))
ws_summary.add_image(ws_summary_logo, 'E1')
except ImportError:
# add_image will fail on computers without PIL installed (this will be
# an issue on some Macs, likely others). it is only used here to add
# our logo, so no big deal.
pass
try:
wb.save(out_file)
log(u'Summary table saved to {}'.format(out_file))
QtWidgets.QMessageBox.information(
None, QtWidgets.QApplication.translate("LDMP", "Success"),
QtWidgets.QApplication.translate(
"LDMP", u'Summary table saved to {}'.format(out_file)))
except IOError:
log(u'Error saving {}'.format(out_file))
QtWidgets.QMessageBox.critical(
None, QtWidgets.QApplication.translate("LDMP", "Error"),
QtWidgets.QApplication.translate(
"LDMP",
u"Error saving output table - check that {} is accessible and not already open."
.format(out_file)))
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 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 write_header(self, sheet):
sheet.column_dimensions["A"].width = 10
sheet.column_dimensions["B"].width = 55
sheet.column_dimensions["C"].width = 33
sheet.row_dimensions[1].height = self.__row_height
sheet["A1"].fill = self.__header_fill
sheet["B1"].fill = self.__header_fill
sheet["C1"].fill = self.__header_fill
logo = Image(self.__image_path)
sheet.add_image(logo, "C1")
def add_image(wb, ws, pos, key, img):
if img is None: return
w, h, margin, scale = eval(key[2])
img = trans(img, w, h, margin, scale == 0)
img = PImage.fromarray(img)
image_file = BytesIO()
img.save(image_file, 'png')
ref = BytesIO(image_file.getvalue())
image = Image(img)
image.ref = ref
image.height = EMU_to_pixels(cm_to_EMU(h))
image.width = EMU_to_pixels(cm_to_EMU(w))
wb[ws].add_image(image, wb[ws].cell(*pos).coordinate)
def write_header(ws):
ws.row_dimensions[2].height = 65
ws.merge_cells(start_row=2, end_row=4, start_column=2, end_column=3)
img = Image("../../Document/Images/logo_circle.png")
img.height = 140
img.width = 140
ws.add_image(img, "C2")
img = Image("owasp-masvs/Document/images/OWASP_logo.png")
img.height = img.height * 0.1
img.width = img.width * 0.1
ws.add_image(img, "H2")
ws["D2"].value = "Mobile Application Security Verification Standard"
ws["D2"].style = "big_title"
ws["D3"].value = f'=HYPERLINK("https://github.com/OWASP/owasp-mstg/releases/tag/{MSTGVERSION}", "OWASP MSTG {MSTGVERSION} (commit: {MSTGCOMMIT})")'
ws["D3"].font = Font(name=excel_styles_and_validation.FONT, color="00C0C0C0")
ws["D4"].value = f'=HYPERLINK("https://github.com/OWASP/owasp-masvs/releases/tag/{MASVSVERSION}", "OWASP MASVS {MASVSVERSION} (commit: {MASVSCOMMIT})")'
ws["D4"].font = Font(name=excel_styles_and_validation.FONT, color="00C0C0C0")
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)
