def passo8(): wb = load_workbook(filename=planilha) sheet = wb.active equipe(sheet, "D{0}", "F{0}", enumerate(EQUIPE[:6])) equipe(sheet, "H{0}", "J{0}", enumerate(EQUIPE[6:])) caminho = caminho_imagem("intelivix.png") intelivix = Image(caminho) intelivix.anchor(sheet["E4"]) intelivix.drawing.left = 300 intelivix.drawing.top = 100 intelivix.drawing.width = 200 intelivix.drawing.height = 180 caminho = caminho_imagem("pugpe.png") pug = Image(caminho) pug.drawing.left = 550 pug.drawing.top = 100 pug.drawing.width = 400 pug.drawing.height = 150 sheet.add_image(intelivix) sheet.add_image(pug) wb.save(filename=planilha)
def SetValuesInBlank(self,ValuesForCells, save_path): #wb = load_workbook(filename = 'tmp.xlsx') filename='tmp.xlsx' sheetname = u'справка' wb = load_workbook(filename) sheet_ranges = wb[sheetname]#u'справка' MapAllCells = { 'Identification_code':['22','N'],#Идентификационный код 'The_legal_status_of_the_subject':['24','N'],#Правовой статус субъекта #'Full_name_type':['19','A'],# #'Full_name':['20','A'],# 'Full_name':['19','A'],# 'Director':['26','I'],#Руководитель 'Phone_number':['28','I'],#Телефон 'Location_code':['32','L'],#Местонахождение (КОАТУУ) 'Location address':['32','T'],#Местонахождение 'The legal form code':['34','O'],#Организационно-правовая форма код 'The legal form type':['34','T'],#Организационно-правовая форма тип #'The state body code':['',''],#Орган государственного управления (КОГУ) #'State Body type':['',''],# 'Date of registration in the bodies of statistics':['42','O'],#Дата постановки на учет в органах статистики 'Number of registration in the bodies of statistics':['42','Y'],#Номер постановки на учет в органах статистики 'First_KVED_code':['48','B'],# 'First_KVED_value':['48','F'],# 'Second_KVED_code':['49','B'],# 'Second_KVED_value':['49','F'],# 'Third_KVED_code':['50','B'],# 'Third_KVED_value':['50','F'],# 'Fourth_KVED_code':['51','B'],# 'Fourth_KVED_value':['51','F'],# 'Fifth_KVED_code':['52','B'],# 'Fifth_KVED_value':['52','F'],# 'Sixth_KVED_code':['53','B'],# 'Sixth_KVED_value':['53','F'],# 'Seventh_KVED_code':['54','B'],# 'Seventh_KVED_value':['54','F'],# 'Parent_organization_ID':['57','A'],#Вышестоящая организация ID 'Parent_organization_name':['57','I'],#Вышестоящая организация имя 'The_public_registration':['59','T'],#Орган государственной регистрации 'Date_of_first_registration':['60','T'],#Дата первичной регистрации 'Latest_registration_steps':['63','T'],#Дата последнего регистрационного действия 'Registration_number_of_the_last_actions':['63','AB'],#Номер последнего регистрационного действия 'Place_the_last_steps_of_the_registration':['65','T'],#Место проведения последнего регистрационного действия 'Date_of_issuance_of_certificate':['66','L'],#Дата выдачи справки 'Performer':['67','H'],#Исполнитель 'Contractor_phone':['67','Y']#Исполнителя телефон, найди ты, и поймёшь, почему так, мой юный параван } img = Image('logo.png') img.anchor(sheet_ranges['O1'], anchortype='oneCell') sheet_ranges.add_image(img) for i in MapAllCells.keys(): params = MapAllCells[i] sheet_ranges[params[1]+params[0]].value = ValuesForCells[i] wb.save(save_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: img = Image(BytesIO(archive.read(image_file))) img.drawing.name = name img.anchor(cell, anchortype='oneCell') ws.add_image(img)
def test_write_anchor_onecell(self): from openpyxl.drawing import Image path = os.path.join(DATADIR, "plain.png") drawing = Image(path).drawing drawing.anchortype = "oneCell" drawing.anchorcol = 0 drawing.anchorrow = 0 root = Element("test") self.dw._write_anchor(root, drawing) xml = get_xml(root) expected = """<test><xdr:oneCellAnchor xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><xdr:from><xdr:col>0</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>0</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from><xdr:ext cx="1123950" cy="1123950"/></xdr:oneCellAnchor></test>""" diff = compare_xml(xml, expected) assert diff is None, diff
def main(): global wb wb = load_workbook(filename = fileName) for s1 in graphs['selection']: dataParam = {} if graphs['type'] == "sheets": dataParam['sheet'] = s1 elif graphs['type'] == "row": dataParam['row'] = s1 p.figure(figsize=(10.0, 5.0)) maxValue = -float("inf") minValue = float("inf") for line in lines: if 'row' in line: s2 = line['row'] dataParam['row'] = s2 elif 'sheet' in line: s2 = line['sheet'] dataParam['sheet'] = s2 print(dataParam) data = getData(**dataParam) p.plot(range(0, len(data)), data, color=line["color"], label = s2) #p.xlabel(timeLabels, rotation='vertical', size = 'x-small') ax = p.gca() ax.yaxis.label.set_size('x-small') p.xticks(range(len(data)), timeLabels, rotation='vertical', size = 'x-small', multialignment = 'center') #p.xticks(map(add, range(len(data)), [-0.5] * len(data)), timeLabels, rotation='vertical', size = 'x-small', ha = 'right') p.legend(loc='best', prop={"size": 'small'}).draw_frame(False) p.grid(True) p.tight_layout() p.savefig("tttt.png") img = Image('tttt.png') img.anchor(ws[dataParam['sheet']]['B%s'%(endRow + 1)], anchortype='oneCell') ws[dataParam['sheet']].add_image(img) wb.save(outFileName)
def test_ctor(self): Image = self.make_one() i = Image(img=self.img) assert i.nochangearrowheads == True assert i.nochangeaspect == True d = i.drawing assert d.coordinates == ((0, 0), (1, 1)) assert d.width == 118 assert d.height == 118
def test_write_hyperlink_image_rels(Workbook, Image): wb = Workbook() ws = wb.create_sheet() ws.cell('A1').value = "test" ws.cell('A1').hyperlink = "http://test.com/" img = os.path.join(DATADIR, "plain.png") i = Image(img) ws.add_image(i) raise ValueError("Resulting file is invalid")
def test_ctor(self, Image, datadir): datadir.chdir() i = Image(img="plain.png") assert i.nochangearrowheads == True assert i.nochangeaspect == True d = i.drawing assert d.coordinates == ((0, 0), (1, 1)) assert d.width == 118 assert d.height == 118
def test_write_anchor(self): from openpyxl.drawing import Image path = os.path.join(DATADIR, "plain.png") drawing = Image(path).drawing root = Element("test") self.dw._write_anchor(root, drawing) xml = get_xml(root) expected = """<test><xdr:absoluteAnchor xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><xdr:pos x="0" y="0"/><xdr:ext cx="1123950" cy="1123950"/></xdr:absoluteAnchor></test>""" diff = compare_xml(xml, expected) assert diff is None, diff
def addImageGraph(sheet, c): global dataCol dataCol = c + 1 #print dataCol dataParam = {} dataParam['sheet'] = sheet p.figure(figsize=(12.0, 5.0)) init(sheet = sheet) #print startRow, endRow for i in range(startRow + 1, endRow): dataParam['rowNumber'] = i dataParam['sheet'] = sheet data = getData(**dataParam) #p.plot(range(0, len(data)), data, color = line["color"], label = s2) #print data l = [] for j in range(1, dataCol): l.append(sheet.cell('%s%s'%( get_column_letter(j), i)).value) p.plot(range(0, len(data)), data, label = " - ".join(l)) #p.xlabel(timeLabels, rotation='vertical', size = 'x-small') ax = p.gca() ax.yaxis.label.set_size('x-small') p.xticks(range(len(timeLabels)), timeLabels, rotation='vertical', size = 'x-small', multialignment = 'center') #p.xticks(map(add, range(len(data)), [-0.5] * len(data)), timeLabels, rotation='vertical', size = 'x-small', ha = 'right') p.legend(loc='best', prop={"size": 'small'}).draw_frame(False) p.grid(True) p.tight_layout() imgName = os.path.join(Settings.tmpPath, "img" + sheet.title + ".png") p.savefig(imgName) img = Image(imgName) img.anchor(sheet['B%s'%(endRow + 1)], anchortype='oneCell') sheet.add_image(img)
def inserting_an_image(): wb = Workbook() ws = wb.active ws['A1'] = 'You should see three logos below' ws['A2'] = 'Resize the rows and cells to see anchor differences' # create image instances img = Image('logo.png') img2 = Image('logo.png') img3 = Image('logo.png') # place image relative to top left corner of spreadsheet img.drawing.top = 100 img.drawing.left = 150 # the top left offset needed to put the image # at a specific cell can be automatically calculated img2.anchor(ws['D12']) (('D', 12), ('D', 21)) # one can also position the image relative to the specified cell # this can be advantageous if the spreadsheet is later resized # (this might not work as expected in LibreOffice) img3.anchor(ws['G20'], anchortype='oneCell') ((6, 19), None) # afterwards one can still add additional offsets from the cell img3.drawing.left = 5 img3.drawing.top = 5 # add to worksheet ws.add_image(img) ws.add_image(img2) ws.add_image(img3) wb.save('logo.xlsx')
def test_write_images(self): from openpyxl.drawing import Image path = os.path.join(DATADIR, "plain.png") img = Image(path) root = Element("{%s}wsDr" % SHEET_DRAWING_NS) self.dw._write_image(root, img, 1) drawing_schema.assertValid(root) xml = get_xml(root) expected = """<xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"> <xdr:absoluteAnchor> <xdr:pos x="0" y="0"/> <xdr:ext cx="1123950" cy="1123950"/> <xdr:pic> <xdr:nvPicPr> <xdr:cNvPr id="2" name="Picture 1"/> <xdr:cNvPicPr> <a:picLocks noChangeArrowheads="1" noChangeAspect="1"/> </xdr:cNvPicPr> </xdr:nvPicPr> <xdr:blipFill> <a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" cstate="print" r:embed="rId1"/> <a:srcRect/> <a:stretch> <a:fillRect/> </a:stretch> </xdr:blipFill> <xdr:spPr bwMode="auto"> <a:xfrm> <a:off x="0" y="0"/> <a:ext cx="0" cy="0"/> </a:xfrm> <a:prstGeom prst="rect"> <a:avLst/> </a:prstGeom> <a:noFill/> <a:ln w="1"> <a:noFill/> <a:miter lim="800000"/> <a:headEnd/> <a:tailEnd len="med" type="none" w="med"/> </a:ln> <a:effectLst/> </xdr:spPr> </xdr:pic> <xdr:clientData/> </xdr:absoluteAnchor> </xdr:wsDr> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_images(): wb = Workbook() ew = ExcelWriter(workbook=wb) from openpyxl.drawing import Image imagepath = os.path.join(DATADIR, "plain.png") img = Image(imagepath) buf = BytesIO() archive = zipfile.ZipFile(buf, 'w') ew._write_images([img], archive, 1) archive.close() buf.seek(0) archive = zipfile.ZipFile(buf, 'r') zipinfo = archive.infolist() assert len(zipinfo) == 1 assert zipinfo[0].filename == 'xl/media/image1.png'
def test_anchor_onecell(self): Image = self.make_one() i = Image(self.img) c = DummyCell() vals = i.anchor(c, anchortype="oneCell") assert vals == ((0, 0), None)
def test_anchor(self): Image = self.make_one() i = Image(self.img) c = DummyCell() vals = i.anchor(c) assert vals == (('A', 1), (118, 118))
def test_import(self): Image = self.make_one() with pytest.raises(ImportError): Image._import_image(self.img)
def test_anchor_onecell(self, Image, datadir): datadir.chdir() i = Image("plain.png") c = DummyCell() vals = i.anchor(c, anchortype="oneCell") assert vals == ((0, 0), None)
def test_anchor(self, Image, datadir): datadir.chdir() i = Image("plain.png") c = DummyCell() vals = i.anchor(c) assert vals == (("A", 1), (118, 118))
def test_import(self, Image, datadir): datadir.chdir() with pytest.raises(ImportError): Image._import_image("plain.png")
def main(): global excel_name,file,row_shift,col_shift,file_num VT = [[0 for x in xrange(2)] for x in xrange(2000)] PG = [0 for x in xrange(20)] #*********************************************************************************** #Searching files within specified folder #*********************************************************************************** root = Tk() root.wm_title("Vt Loss Data Processing") w = Label(root, text="Please select the folder for the Vt Loss Data to be processed.") dirname = askdirectory() print "Selecting folder:",dirname fold=dirname.replace('/',' ').split() excel_name=fold[len(fold)-1]+' Vt Loss Data.xlsx' print "Please close this dialog by clicking X on the right-top" w.pack() root.mainloop() os.chdir(dirname) for file in glob.glob("*.txt"): VT_num=-1 file_num=file_num+1 PG_num=-1 line_num=0 with open(file) as f: #*********************************************************************************** #Copying data into VT 2-D array #*********************************************************************************** for lines in f: line_num=line_num+1 if (line_num % 122 >2): VT[line_num][0]=float(lines.split()[0]) pop=int(lines.split()[1]) VT[line_num][1]=pop elif(line_num % 122 ==2): PG_num=PG_num+1 VT[line_num][0]=lines.replace(':',' ').replace('h',' ').split()[11] PG[PG_num]=VT[line_num][0] #*********************************************************************************** # 1: draw diagram and save to excel file # 2: check head and tail, and write corresponding data into excel file #*********************************************************************************** for i in range(0,PG_num+1): VT_num=VT_num+1 HD=1 head=0 tail=0 VT1=np.array(VT)[i*122+3:i*122+122] dia_name=file[:-4]+' page '+PG[i] fig=plt.figure(figsize=(4,2), dpi=100) axes = fig.add_axes([0.2, 0.1, 0.8, 0.7]) # left, bottom, width, height (range 0 to 1) axes.plot(VT1[:,0],VT1[:,1],'r--') axes.set_xlabel('Voltage') axes.set_ylabel('Population') axes.set_title(dia_name) fig.savefig(dia_name+'.png',dpi=100) img = Image(dia_name+'.png') img.anchor(ws1.cell(row=1+10*VT_num,column=1+file_num*8)) ws1.add_image(img) for j in range(2,120): if (HD==1): if ( ((VT[i*122+j+1][1]<=10) and (VT[i*122+j][1]<=10) and (VT[i*122+j+2][1]>=10) and (VT[i*122+j+4][1]>10) ) or ((VT[i*122+j+1][1]<=10) and (VT[i*122+j+2][1]>=10)and(VT[i*122+j+3][1]>10)and (VT[i*122+j+1][0]<1.0)) ): head=head+1 print 'head',i*122+j+1,VT[i*122+j+1][0],VT[i*122+j+1][1] HD=0 ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift).value=file ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+1).value=PG[i] ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+2).value='head-'+str(head) ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+3).value=VT[i*122+j+1][0] else: if( (VT[i*122+j-2][1]>10)and (VT[i*122+j][1]>10) and (VT[i*122+j+1][1]<=10)and (VT[i*122+j+2][1]<=10)and (VT[i*122+j+1][0]>1.2)): print 'tail',i*122+j+1,VT[i*122+j+1][0],VT[i*122+j+1][1] tail=tail+1 HD=1 ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift).value=file ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+1).value=PG[i] ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+2).value='tail-'+str(tail) ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+3).value=VT[i*122+j+1][0] elif((VT[i*122+j][1]<50)and (VT[i*122+j][1]-VT[i*122+j+1][1]>5)and (VT[i*122+j+1][1]>10)and(VT[i*122+j+1][1]-VT[i*122+j+2][1]<-5)and (VT[i*122+j+2][1]<50)): if(VT[i*122+j][0]>1.5): print 'tail and head',VT[i*122+j+1][0],VT[i*122+j+1][1] tail=tail+1 ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift).value=file ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+1).value=PG[i] ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+2).value='tail-'+str(tail) ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+3).value=VT[i*122+j+1][0] head=head+1 ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift).value=file ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+1).value=PG[i] ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+2).value='head-'+str(head) ws0.cell(row = row_shift+i*6+head+tail+file_num*6*(PG_num+1), column = col_shift+3).value=VT[i*122+j+1][0] print 'head',head,'tail',tail
def test_anchor(self, Image, datadir): datadir.chdir() i = Image("plain.png") c = DummyCell() vals = i.anchor(c) assert vals == (('A', 1), (118, 118))
import os from openpyxl import Workbook from openpyxl.drawing import Image from openpyxl.tests.helper import DATADIR wb = Workbook() ws = wb.active ws.cell('A1').value = 'You should see a logo below' # create an image instance pth = os.path.split(__file__)[0] img = Image(os.path.join(DATADIR, 'plain.png')) # place it if required img.drawing.left = 200 img.drawing.top = 100 # you could also 'anchor' the image to a specific cell # img.anchor(ws.cell('B12')) # add to worksheet ws.add_image(img) wb.save(os.path.join(pth, 'files', 'logo.xlsx'))
def ImageFile(datadir, Image): datadir.chdir() return Image("plain.png")
# Start new event currentEvent = row['Event'] print(currentEvent) output = load_workbook('masters/{}.xlsx'.format( row['Event'].lower())) sheets = [sheet for sheet in output] wsCount = 0 if currentCategory != row['Category']: # Start new spreadsheet currentCategory = row['Category'] ws = sheets[wsCount] wsCount += 1 # Add image img = Image('nbta.png') # place image relative to top left corner of spreadsheet img.drawing.top = 10 img.drawing.left = 30 ws.add_image(img) ws.title = row['Category'][:26] ws['C3'] = row['Category'] i = 5 # Add names to worksheet ws['B{}'.format(i)] = row['Name'] i += 1 # Finish last event
## Excel # Don't use # https://pythonhosted.org/openpyxl/usage.html#inserting-an-image from openpyxl import Workbook from openpyxl.drawing import Image wb = Workbook() ws = wb.active ws['A1'] = 'You should see three logos below' ws['A2'] = 'Resize the rows and cells to see anchor differences' # create image instances img = Image('logo.png') img2 = Image('logo.png') img3 = Image('logo.png') # place image relative to top left corner of spreadsheet img.drawing.top = 100 img.drawing.left = 150 # the top left offset needed to put the image # at a specific cell can be automatically calculated img2.anchor(ws['D12']) # one can also position the image relative to the specified cell # this can be advantageous if the spreadsheet is later resized # (this might not work as expected in LibreOffice) img3.anchor(ws['G20'], anchortype='oneCell')