def add_image(path_src, path_file):
path_image = "C:/Users/liang-jiashun/Desktop/Excel模板/image"
shutil.rmtree(path_image)
os.makedirs(path_image)
files = os.listdir(path_src) # 文件遍历
for file in files:
if file != "test":
shutil.copy(path_src + "/" + file, path_image)
wb = openpyxl.load_workbook(filename=path_file)
ws = wb.worksheets[0]
ws1 = wb.worksheets[1]
files = os.listdir(path_image) # 文件遍历
index = 4
for file in files:
if not os.path.isdir(path_image + "/" + file):
file_name = os.path.basename(path_image + "/" + file) # 获取文件名
new_name = str(
file_name.split('.')[0]) + ".zip" # 新的文件名，命名为：xxx.zip
dir_path = os.path.dirname(path_image + "/" + file) # 获取文件所在目录
new_path = dir_path + "/" + new_name # 新的文件路径
os.rename(path_image + "/" + file, new_path)
file_zip = zipfile.ZipFile(new_path, 'r')
zipdir = path_image + "/" + str(file.split('.')[0]) # 获取文件所在目录
for files in file_zip.namelist():
file_zip.extract(files, zipdir) # 解压到指定文件目录
file_zip.close()
os.remove(new_path)
pic_dir = 'xl' + "/" + 'media' # excel变成压缩包后，再解压，图片在media目录
pic_path = path_image + "/" + str(
file.split('.')[0]) + "/" + pic_dir
image = pic_path + "/" + "image1.jpeg"
print(image)
ws.column_dimensions['A'].width = 9.1
ws.row_dimensions[index].height = 57
ws1.column_dimensions['A'].width = 9.1
ws1.row_dimensions[index].height = 57
img1 = Image(image)
img2 = Image(image)
img1.width, img1.height = 73, 73
img2.width, img2.height = 73, 73
ws.add_image(img1, 'A' + str(index))
ws1.add_image(img2, 'A' + str(index))
index += 1
wb.save(filename=path_file)
wb.close()
def writeCSVImage(inputImage, data1):
filename = "trailSaveImg_3.xlsx"
width = 100
height = 70
try:
wb = load_workbook(filename)
except FileNotFoundError:
print("-----no file foud-----")
createNewFileImage(filename)
wb = load_workbook(filename)
ws = wb.worksheets[0]
latestCol = ws.max_column + 1
colmnLetter = get_column_letter(latestCol)
print(latestCol, colmnLetter, "<-- col anchors.............")
ws.row_dimensions[2].height = height
ws.column_dimensions[colmnLetter].width = width
for row, entry in enumerate(data1, start=3):
ws.cell(row=row, column=latestCol, value=entry)
img = Image(inputImage)
img.width = 650
img.height = 110
print(img.width)
print(img.height)
#img.resize((width,height),Image.NEAREST)
ws.add_image(img, anchor=colmnLetter + str(1))
wb.save(filename)
def _create_excel(self):
# 上部部分記載
self.write_estimated_content_rows()
# 作業詳細記載
self.create_project_detail_rows()
# 作業内容～備考
self.create_contract_content_rows()
# ロゴ画像貼り付け
img = Image('excel/templates/tp_logo.png')
img.width = img.width * 0.63
img.height = img.height * 0.63
self.ws.add_image(img, 'I10')
# 余白調整
self.ws.page_margins.top = 0.7
self.ws.page_margins.left = 0.3
self.ws.page_margins.right = 0.26
self.ws.page_margins.bottom = 0.2
self.ws.page_margins.header = 0.7
self.ws.page_margins.footer = 0.22
# 倍率の調整
self.ws.page_setup.scale = 86
# エクセルを一時フォルダに保存
self.excel.save('01_見積書（{}）_{}.xlsx'.format(
self.project.estimation_no,
datetime.today().strftime("%Y%m%d")))
def image_column_xl(worksheet, image_column, image_width=300):
ws = worksheet
# get row number
R_NUM = len(list(ws.rows))
# insert images
for i in range(1, R_NUM):
idx = i + 1
cell_name = image_column + str(idx)
c = ws[cell_name]
try:
# load the image file
img = Image(c.value)
# change the image width
aspect = float(img.height) / img.width
img.width = image_width
img.height = aspect * image_width
# paste the image
ws.add_image(img, cell_name)
# adjust cell size
ws.row_dimensions[idx].height = img.height
c.value = ""
except FileNotFoundError:
pass
# adjust cell width
ws.column_dimensions[image_column].width = image_width / 6
def do_sth(self):
# 插入数据
self.ws['A1'] = 66
self.ws['A2'] = '你好'
for row in self.ws_two['A1:E5']:
for cell in row:
cell.value = 2
#对数据进行求和
self.ws_two['G1'] = '=SUM(A1:E1)'
# 插入当前时间
self.ws['A3'] = datetime.datetime.now()
# 设置字体大小和颜色
font = Font(sz=18, color='000FFF')
self.ws['A2'].font = font
# 插入图片
img = Image('./static/temp.jpg')
# 改变图片大小
img.newSize = (360, 280)
img.width, img.height = img.newSize
self.ws.add_image(img, 'C1')
# 合并单元格
self.ws.merge_cells('H1:K2')
# 取消合并
# self.ws.unmerge_cells('H1:K2')
# 保存
self.wb.save('./static/test.xlsx')
def insertImage(sheet):
# fileName = os.path.join(os.getcwd(), './img.png')
# img = Image.open(path).convert("RGB")
# sheet.add_image(fileName , 'E1')
img = Image('./logo.png')
img.width, img.height = 830, 135
sheet.add_image(img, 'E1')
def resize_image(img: Image, max_height=320, max_width=200):
cell_ratio = float(max_height) / max_width
img_ratio = float(img.height) / img.width
if cell_ratio < img_ratio:
h_percent = max_height / float(img.height)
w_size = int(float(img.width) * float(h_percent))
img.height = max_height - 1
img.width = w_size
else:
w_percent = max_width / float(img.width)
h_size = int(float(img.height) * float(w_percent))
img.height = h_size
img.width = max_width - 1
return img
def generate_excel(self, order, client, supplier, filename,
number_of_products):
wb = load_workbook(app.config["EXCEL_FOLDER"] + '/order_template.xlsx')
#wb.template = False #To change if we want a real template
img = Image('public/excels/header_2.png')
img.width = 563
img.height = 110
ws = wb.active
ws.title = "Order_info"
cells_order = [
'A3', 'I24', 'F15', 'C14', 'F13', 'C55', 'C56', 'C57', 'F54',
'K15', 'C9'
]
cells_client = ['A20', 'A21', 'C12']
cells_supplier = ['B20', 'B21', 'F9']
letter_product = ['B', 'K', 'C', 'D', 'F', 'G', 'I', 'J']
for i in range(number_of_products):
row_index = 18 + i
new_cp = [letter + str(row_index) for letter in letter_product]
cells_order.append(new_cp)
#cells_shipment=['E1','E2','E3','E4','E5']
ws.add_image(img, 'B2')
client.print_to_cell(ws, cells_client)
order.print_to_cell(ws, cells_order)
supplier.print_to_cell(ws, cells_supplier)
#shipment.print_to_cell(ws, cells_shipment)
wb.save(filename)
def save_excel(self, i: int, data_list: List) -> None:
"""
保存成Excel 由于openpyxl 必须从本地读取图片
:param i: 写入的行
:param data_list:写入数据
:return:
"""
self.ws.cell(row=i + 1, column=2).value = data_list[0] # asin
self.ws.column_dimensions['C'].width = 8
self.ws.row_dimensions[i + 1].height = 50
try:
f_name = self.save_img(data_list[1])
img = Image(f_name)
newsize = (50, 50)
img.width, img.height = newsize # 这两个属性分别是对应添加图片的宽高
self.ws.add_image(img, 'C' + str(i + 1)) # 插入图片
except Exception as e:
pass
self.ws.cell(row=i + 1, column=4).value = data_list[2] # 品牌名
self.ws.cell(row=i + 1, column=5).value = data_list[3] # 店铺名
self.ws.cell(row=i + 1, column=6).value = data_list[4] # 产品评论数
self.ws.cell(row=i + 1, column=7).value = data_list[5] # 大类排名
self.ws.cell(row=i + 1, column=8).value = data_list[6] # 小类排名
self.ws.cell(row=i + 1, column=9).value = data_list[13] # 店铺30天feeback数
self.ws.cell(row=i + 1, column=10).value = data_list[16] # 店铺总feeback数
self.ws.cell(row=i + 1, column=11).value = data_list[18] # 店铺产品数
self.ws.cell(row=i + 1, column=12).value = "" # 品牌名搜索结果数
self.ws.cell(row=i + 1, column=13).value = data_list[17] # 店铺链接
self.ws.cell(row=i + 1, column=14).value = data_list[14] # 90天
self.ws.cell(row=i + 1, column=15).value = data_list[15] # 1年
self.ws.cell(row=i + 1, column=16).value = data_list[11] # 店铺信息
self.ws.cell(row=i + 1, column=17).value = data_list[19] # 品牌列表
self.ws.cell(row=i + 1, column=18).value = data_list[7] # 价格
self.ws.cell(row=i + 1, column=19).value = data_list[9] # 评分
self.wb.save(self.save_name)
def img_into_excel(resultwb, sheet_name, row, column, img_path):
"""
往excel中插入图片
:return:
"""
# resultwb = load_workbook(filename)
ws = resultwb[sheet_name]
# 设置文字图片单元格的行高列宽
column_width = 12.25
row_height = 80.10
ws.column_dimensions[column].width = column_width # 修改列的列宽
ws.row_dimensions[row].height = row_height # 修改行的行高
# 插入图片的单元格
target_cell = column + str(row)
if os.path.exists(img_path):
img = Image(img_path)
# 设置图片的宽高
newsize = (90, 90)
img.width, img.height = newsize
# 插入图片
ws.add_image(img, target_cell)
def downloadImg(url):
http = urllib3.PoolManager()
r = http.request('GET', url)
image_file = io.BytesIO(r.data)
img = Image(image_file)
img.width = 60
img.height = 60
return img
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 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 add_img(self, ws, ColRow, path_img):
"""Add an image
The image size is automatically adjusted.
Parameters
-------
ws : openpyxl.worksheet.worksheet.Worksheet
worksheet
ColRow : list[int]
range of the cell
path_img : str
the path of the image data to be added
"""
#Calculating the height of cells in a range with
#the number of cells and the default cell height
height_pt = (ColRow[3] - ColRow[1] + 1) * self.unit_pt_height
height_pix = points_to_pixels(height_pt)
#Calculating the width of cells in a range with
#the number of cells and the default cell width
width_pix = (ColRow[2] - ColRow[0] + 1) * self.unit_pix_width
tmp_img = Image(path_img)
aspct = tmp_img.height / tmp_img.width
if aspct >= 1:
tmp_img.height = height_pix
tmp_img.width = height_pix / aspct
if tmp_img.width > width_pix:
tmp_img.width = width_pix
tmp_img.height = tmp_img.width * aspct
else:
tmp_img.width = width_pix
tmp_img.height = width_pix * aspct
if tmp_img.height > height_pix:
tmp_img.height = height_pix
tmp_img.width = tmp_img.height / aspct
anc = ws.cell(column=ColRow[0], row=ColRow[1]).coordinate
ws.add_image(tmp_img, anc)
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 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 __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 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 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 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 addImg(r):
#ws.cell(row=r, column=1).value=r
# 设置文字图片单元格的行高列宽
column_width = 170
row_height = 300 # 设置行高，该设置的行高与excel文件中设置的行高值是一样的
# r = 2 #3行
colName = u'B'
# 下面代码中的[]括号中可以输入colName或者colName
sheetImg.row_dimensions[r].height = row_height * 0.8 # 修改行3的行高
sheetImg.column_dimensions[colName].width = 22 # 设置字符宽度
img = Image(imgPath + u'/test.png')
newSize = (column_width, row_height)
img.width, img.height = newSize # 这两个属性分别是对应添加图片的宽高
sheetImg.add_image(img, colName + str(r)) # 向M列中的单元格内指定添加图片
def InsertImg(imgPath, excelPath, insertPath):
imgsize = (720 / 12, 1280 / 20) # 设置一个图像缩小的比例
wb = load_workbook(excelPath)
ws = wb.active
width = 10.0
height = 8 * (2.2862 / 0.3612)
for i in range(1, ws.max_row + 1):
ws.row_dimensions[i].height = height
for i in range(1, ws.max_column + 1):
ws.column_dimensions[get_column_letter(i)].width = width
img = Image(imgPath) # 缩放图片
img.width, img.height = imgsize
ws.add_image(img, insertPath) # 图片 插入 A1 的位置上
wb.save('1.xlsx') # 新的结果保存输出
def start_excel(key):
a = jumia.objects(key=key).order_by("-comment")[0:300]
print(a)
photo = []
A = 1
wb = Workbook()
for i in a:
while True:
try:
response = requests.get(i.photo)
img = response.content
with open('./img/{0}.jpg'.format(i.photo.split('/')[3]),
'wb') as f:
f.write(img)
break
except:
break
print(i.comment, i.price, i.photo)
ws = wb.active
ws.column_dimensions['A'].width = 45 # 修改列A的列宽
ws.row_dimensions[A].height = 45 # 修改行3的行高
price = i.price
ws['D{0}'.format(A)] = price
ws['A{0}'.format(A)] = str(i.id)
ws['B{0}'.format(A)] = i.name
ws['C{0}'.format(A)] = i.comment
ws['G{0}'.format(A)] = i.url
# Rows can also be appended
# ws.append([1, 2, 3])
# Python types will automatically be converted
if i.photo == 'XXXX':
import requests
# requests.get()
pass
else:
try:
if imghdr.what('./img/{0}.jpg'.format(
i.photo.split('/')[3])) == 'jpeg':
img = Image('./img/{0}.jpg'.format(i.photo.split('/')[3]))
img.width, img.height = (60, 60)
# 这两个属性分别是对应添加图片的宽高
ws.add_image(img, 'F{0}'.format(A))
except:
pass
# Save the file
A += 1
wb.save("./log/{0}.xlsx".format(key + str(time.time())))
def InsertImage(self,
sheet,
strCell,
sFilePath,
fWidth,
fHeight,
bSave=False):
tempSheet = self._getSheet(sheet)
tempCells = self._getCells(strCell)
column, row = utils.cell.coordinate_from_string(tempCells)
img = Image(sFilePath)
img.width, img.height = (fWidth, fHeight)
tempSheet.column_dimensions[column].width = fWidth
tempSheet.row_dimensions[row].height = fHeight
tempSheet.add_image(img, tempCells)
self.Save(bSave)
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 insert_image():
path = r'./data/pyxl-base.xlsx'
# 加载workbook
workbook = openpyxl.load_workbook(path)
sheet = workbook.create_sheet('insert_image')
# 设置图像
img = Image(r'./data/01.jpg')
# img.width, img.height这两个属性分别是对应添加图片的宽高
newsize = (90, 90)
img.width, img.height = newsize
# 设置图像单元格说明
sheet['A1'] = 'you are my angel'
# 插入图片
sheet.add_image(img, 'A1')
# # 移除sheet
# workbook.remove(sheet)
# 保存
workbook.save(path)
def _create_excel(self):
# 17行目までの値代入・書式設定
self.write_top_part()
# 請求明細部分を作成
self.create_billing_details()
# 備考・お支払先部分を作成
self.create_bottom_part()
# ロゴ画像貼り付け
img = Image('excel/templates/tp_logo.png')
img.width = img.width * 0.75
img.height = img.height * 0.75
self.ws.add_image(img, 'K7')
# 倍率の調整
self.ws.page_setup.scale = 85
def merge_pic(col, row):
wb = openpyxl.load_workbook('RK_Subjective_Standard_空白.xlsx')
ws1 = wb['白平衡&色彩还原']
w = ws1.column_dimensions[col].width
w_x = 8
h_x = 1.328
file_list = os.listdir('./CWF/')
num = 0
for i in file_list:
if os.path.splitext(i)[1] == '.jpg':
s = './CWF/' + i
h = ws1.row_dimensions[row + num].height
img = Image(s)
img.width = w * w_x
img.height = h * h_x
ws1.add_image(img, col + str(row + num))
num += 1
wb.save('RK_Subjective_Standard_OK.xlsx')
return
def toGetImagePath(path):
wb = workbook.Workbook()
sheet1 = wb.active
# 第一个表头：文件ID+主图名称
sheet1["A1"] = "图片信息"
sheet1["B1"] = "图片"
sheet1["C1"] = "OCR"
# 拿到图片数量
filecount = 2
for root,dirs,files in os.walk(path):
filecount+=len(files)
# 拿到图片路径
for root,dirs,files in os.walk(path):
# 拿到图片路径
for name in files:
# 拿到图片路径
imagePath = os.path.join(root,name)
#print(imagePath)
# 文件id标识+文件名=商品信息
commodityInformation = root.split("\\")[-1].split("_")[0] + os.path.splitext(name)[0]
#print(commodityInformation)
# 第一列插入商品信息
sheet1["A{}".format(filecount)] = commodityInformation
# 第二列插入图片，首先调整行高和列宽
sheet1.column_dimensions['B'].width = 80
sheet1.row_dimensions[filecount].height = 80
img = Image(imagePath)
# 生成新的图片的宽和高
newsize = (160,160)
img.width,img.height = newsize
# 将图片添加到excel中
sheet1.add_image(img,'B{}'.format(filecount))
# 第三列为OCR结果
sheet1["C{}".format(filecount)] = ocr.picture_Path(imagePath)
filecount+=1
wb.save(filename=os.path.join(r"D:\atnight\蓝禾科技","result.xlsx"))
def addImg(sheetImg, r):
path = u'{}/img_{}.png'.format(imgPath, r)
if not os.path.exists(path):
return False
#ws.cell(row=r, column=1).value=r
# 设置文字图片单元格的行高列宽
column_width = 255
row_height = 450 # 设置行高，该设置的行高与excel文件中设置的行高值是一样的
# r = 2 #3行
colName = u'B'
# 下面代码中的[]括号中可以输入colName或者colName
sheetImg.row_dimensions[r].height = row_height * 0.8 # 修改行3的行高
sheetImg.column_dimensions[colName].width = 22 # 设置字符宽度
img = Image(path)
newSize = (column_width, row_height)
img.width, img.height = newSize # 这两个属性分别是对应添加图片的宽高
sheetImg.add_image(img, colName + str(r)) # 向M列中的单元格内指定添加图片
# -------写入图片 结束---------
return True
