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