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 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 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 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 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 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_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_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_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 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
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')
# 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')