def stickers_transform():
"""Transforms the CSV data into a DOCX file"""
document = Document('landscape.docx')
with open('master9.7.csv', 'rU') as infile:
reader = csv.reader(infile)
table = document.add_table(2,2)
i = 0
for row in reader:
index1, index2 = i / 2, i % 2
cell = table.cell(index1, index2)
prod = row[3]
write_line_in_cell(cell, '\n', 20)
write_line_in_cell(cell, row[1])
write_line_in_cell(cell, row[2])
write_line_in_cell(cell, row[0])
write_line_in_cell(cell, row[3])
write_line_in_cell(cell, '\n', 20)
i += 1
if i % 4 == 0:
table = document.add_table(2,2)
i = 0
document.save('labels.docx')
def create_completed_review_form(submission,review_id):
document = Document()
document.add_heading(submission.title, 0)
review_assignment = get_object_or_404(core_models.ReviewAssignment, pk=review_id)
if review_assignment.review_form:
relations = models.FormElementsRelationship.objects.filter(form=review_assignment.review_form).order_by('order')
else:
review_assignment.review_form = submission.review_form
review_assignment.save()
relations = models.FormElementsRelationship.objects.filter(form=submission.review_form).order_by('order')
if review_assignment.results:
p = document.add_paragraph('%s completed this review assignment form.'% review_assignment.user.profile.full_name())
data = json.loads(review_assignment.results.data)
for relation in relations:
v = data[relation.element.name]
document.add_heading(relation.element.name, level=1)
text = BeautifulSoup(str(v[0]),"html.parser").get_text()
document.add_paragraph(text).bold = True
recommendations = {'accept': 'Accept','reject': 'Reject', 'revisions':'Revisions Required'}
document.add_heading("Recommendation", level=1)
document.add_paragraph(recommendations[review_assignment.recommendation]).italic = True
document.add_heading("Competing Interests", level=1)
document.add_paragraph(review_assignment.competing_interests).italic = True
else:
p = document.add_paragraph('You should complete this form and then use the review assignment page to upload it.')
for relation in relations:
if relation.element.field_type in ['text', 'textarea', 'date', 'email']:
document.add_heading(relation.element.name+": _______________________________", level=1)
document.add_paragraph(relation.help_text).italic = True
if relation.element.field_type in ['select', 'check']:
document.add_heading(relation.element.name, level=1)
if relation.element.field_type == 'select':
choices = render_choices(relation.element.choices)
else:
choices = ['Y', 'N']
p = document.add_paragraph(relation.help_text)
p.add_run(' Mark your choice however you like, as long as it is clear.').italic = True
table = document.add_table(rows=2, cols=len(choices))
hdr_cells = table.rows[0].cells
for i, choice in enumerate(choices):
hdr_cells[i].text = choice[0]
table.style = 'TableGrid'
document.add_page_break()
if not os.path.exists(os.path.join(settings.BASE_DIR, 'files', 'forms')):
os.makedirs(os.path.join(settings.BASE_DIR, 'files', 'forms'))
path = os.path.join(settings.BASE_DIR, 'files', 'forms', '%s.docx' % str(uuid4()))
document.save(path)
return path
def mdoc():
document = Document()
document.add_heading("Document Title", 0)
p = document.add_paragraph("A plain paragraph having some ")
p.add_run("bold").bold = True
p.add_run(" and some ")
p.add_run("italic.").italic = True
document.add_heading("Heading, level 1", level=1)
document.add_paragraph("Intense quote", style="IntenseQuote")
document.add_paragraph("first item in unordered list", style="ListBullet")
document.add_paragraph("first item in ordered list", style="ListNumber")
document.add_picture("monty-truth.png", width=Inches(1.25))
table = document.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = "Qty"
hdr_cells[1].text = "Id"
hdr_cells[2].text = "Desc"
for item in recordset:
row_cells = table.add_row().cells
row_cells[0].text = str(item.qty)
row_cells[1].text = str(item.id)
row_cells[2].text = item.desc
document.add_page_break()
document.save("demo.docx")
def create_review_form(submission, review_form):
document = Document()
document.add_heading(submission.title, 0)
p = document.add_paragraph('You should complete this form and then use the review page to upload it.')
relations = models.FormElementsRelationship.objects.filter(form=review_form).order_by('order')
for relation in relations:
if relation.element.field_type in ['text', 'textarea', 'date', 'email']:
document.add_heading(relation.element.name+": _______________________________", level=1)
document.add_paragraph(relation.help_text).italic = True
if relation.element.field_type in ['select', 'check']:
document.add_heading(relation.element.name, level=1)
if relation.element.field_type == 'select':
choices = render_choices(relation.element.choices)
else:
choices = ['Y', 'N']
p = document.add_paragraph(relation.help_text)
p.add_run(' Mark your choice however you like, as long as it is clear.').italic = True
table = document.add_table(rows=2, cols=len(choices))
hdr_cells = table.rows[0].cells
for i, choice in enumerate(choices):
hdr_cells[i].text = choice[0]
table.style = 'TableGrid'
document.add_page_break()
if not os.path.exists(os.path.join(settings.BASE_DIR, 'files', 'forms')):
os.makedirs(os.path.join(settings.BASE_DIR, 'files', 'forms'))
path = os.path.join(settings.BASE_DIR, 'files', 'forms', '%s.docx' % str(uuid4()))
document.save(path)
return path
def createPatentTable(patentList, docFileName):
document = Document()
for section in document.sections:
section.orientation = WD_ORIENT.LANDSCAPE
table = document.add_table(rows=1, cols=5)
fillInPatentHeader(table)
fillInPatentListData(table, patentList)
document.save(docFileName)
def report(): document = Document() table = document.add_table(rows=1, cols=7) hdr_cells = table.rows[0].cells hdr_cells[0].text = 'Hote' hdr_cells[1].text = 'Systeme dexploitation' hdr_cells[2].text = 'Port' hdr_cells[3].text = 'Service' hdr_cells[4].text = 'Logiciel' hdr_cells[5].text = 'Version' hdr_cells[6].text = 'Information' table2 = document.add_table(rows=1, cols=1) #print data count = 0 for i in range(0,len(data),1): line = data.pop() #print line row_cells = table.add_row().cells row_cells[0].text = line[0] row_cells[1].text = line[1] count2 = 0 for i in range(len(line[2])): if count2 > 0: row_cells = table.add_row().cells row_cells[0].text = "" row_cells[1].text = "" row_cells[2].text = line[2][count2] row_cells[3].text = line[3][count2] row_cells[4].text = line[4][count2] row_cells[5].text = line[5][count2] row_cells[6].text = line[6][count2] count2+=1 if tablestyle: table.style=tablestyle document.add_page_break() document.save(o) print "[*] Jobs done\n"
def main():
reload(sys)
sys.setdefaultencoding('utf-8')
# 创建文档对象
document = Document()
# 设置文档标题，中文要用unicode字符串
document.add_heading(u'我的一个新文档',0)
# 往文档中添加段落
p = document.add_paragraph('This is a paragraph having some ')
p.add_run('bold ').bold = True
p.add_run('and some ')
p.add_run('italic.').italic = True
# 添加一级标题
document.add_heading(u'一级标题, level = 1',level = 1)
document.add_paragraph('Intense quote',style = 'IntenseQuote')
# 添加无序列表
document.add_paragraph('first item in unordered list',style = 'ListBullet')
# 添加有序列表
document.add_paragraph('first item in ordered list',style = 'ListNumber')
document.add_paragraph('second item in ordered list',style = 'ListNumber')
document.add_paragraph('third item in ordered list',style = 'ListNumber')
# 添加图片，并指定宽度
document.add_picture('e:/docs/pic.png',width = Inches(1.25))
# 添加表格: 1行3列
table = document.add_table(rows = 1,cols = 3)
# 获取第一行的单元格列表对象
hdr_cells = table.rows[0].cells
# 为每一个单元格赋值
# 注：值都要为字符串类型
hdr_cells[0].text = 'Name'
hdr_cells[1].text = 'Age'
hdr_cells[2].text = 'Tel'
# 为表格添加一行
new_cells = table.add_row().cells
new_cells[0].text = 'Tom'
new_cells[1].text = '19'
new_cells[2].text = '12345678'
# 添加分页符
document.add_page_break()
# 往新的一页中添加段落
p = document.add_paragraph('This is a paragraph in new page.')
# 保存文档
document.save('e:/docs/demo1.docx')
def given_a_run_inside_a_table_cell_from_source(context, cell_source):
document = Document()
table = document.add_table(rows=2, cols=2)
if cell_source == 'Table.cell':
cell = table.cell(0, 0)
elif cell_source == 'Table.row.cells':
cell = table.rows[0].cells[1]
elif cell_source == 'Table.column.cells':
cell = table.columns[1].cells[0]
run = cell.paragraphs[0].add_run()
context.document = document
context.run = run
def write(self,data):
""" Take a list of row and save into a file """
out = None
if(self.otype=='pkl'):
out = pickle.dumps(data)
with open(self.ofull_name,'wb') as f:
f.write(out)
if(self.otype=='xls'):
wb = Workbook()
ws = wb.active
ws.title = "range names"
for col_idx in range(0, len(data)):
col = get_column_letter(col_idx+1)
for row in range(0, len(data[col_idx])):
ws.cell('%s%s'%(col, row+1)).value = data[col_idx][row]
ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename = self.ofull_name)
if(self.otype=='docx'):
document = Document()
document.add_heading(self.ofull_name, 0)
table = document.add_table(rows=0, cols=len(data))
#hdr_cells = table.rows[0].cells
#hdr_cells[0].text = 'Qty'
#hdr_cells[1].text = 'Id'
#hdr_cells[2].text = 'Desc'
for row in data:
row_cells = table.add_row().cells
for col_idx in range(len(row)):
row_cells[col_idx].text = str(row[col_idx])
document.add_page_break()
document.save(self.ofull_name)
if(self.otype=='xls'):
pass
if(self.otype=='xls'):
pass
print 'Success:Write'
#test code..
#d =[['a','b','c','d'],['a','b','c','d'],['a','b','c','d'],['a','b','c','d']]
#b = BackUp('hello','docx')
#b.write(d)
#print b.read()
def doit():
host = '192.168.1.254'
user = '******'
password = '******'
database = 'renrenbx'
mysql_init(mysql_host = host, mysql_database = database, mysql_user = user, mysql_password = password)
tables = {}
for column in table_schema(database):
tables[column['TABLE_NAME']] = {'info':column,'columns':[]}
for column in table_colume(database):
tables[column['TABLE_NAME']]['columns'] += [column]
document = Document()
document.add_heading(database, 0)
i = 0
max = len(tables)
for key in sorted(tables.keys()):
i = i + 1
value = int(round((i * 1.0) / max * 100))
sys.stdout.write(' [' + '#' * i + '] %s%%' % value + '\r')
sys.stdout.flush()
document.add_heading(key, 1)
table_engine = tables[key]['info']['ENGINE']
paragraph = document.add_paragraph()
paragraph.add_run(table_engine).bold = True
table_comment = tables[key]['info']['TABLE_COMMENT']
paragraph = document.add_paragraph()
paragraph.add_run(table_comment if table_comment else u'无注释').bold = True
table = document.add_table(rows = 1, cols = 4)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = u'字段'
hdr_cells[1].text = u'主键'
hdr_cells[2].text = u'类型'
hdr_cells[3].text = u'注释'
for column in tables[key]['columns']:
row_cell = table.add_row().cells
row_cell[0].text = column['COLUMN_NAME']
row_cell[1].text = column['COLUMN_KEY'] if column['COLUMN_KEY'] else '-'
row_cell[2].text = column['COLUMN_TYPE']
row_cell[3].text = column['COLUMN_COMMENT'] if column['COLUMN_COMMENT'] else '-'
document.save('%s-%s.docx' % (database,datetime.datetime.now().strftime("%Y%m%d%H")))
def create_doc(heading_pre, date_as_string):
"""
Creates a document with an empty table and given heading
:param heading_pre: string, the prefix of the heading (e.g. 'Conflicts ')
:param date_as_string: string, the date as a string
:return:
"""
document = Document()
document.add_heading(heading_pre + date_as_string, 0)
table = document.add_table(rows=1, cols=4)
headings = table.rows[0].cells
create_headings(headings)
return document
def generatedocx(logs, quarter):
# This function will generate the .docx file for the report, given the list
# of logs in the form of the named tuple.
#create the document
report = Document()
logs.sort(key=lambda tup: tup[2])
testlogs = {}
for log in logs:
if log.psa not in testlogs:
testlogs[log.psa] = {}
if log.date not in testlogs[log.psa]:
testlogs[log.psa][log.date] = []
testlogs[log.psa][log.date].append(log.time)
#Generate the title
if date.today().month == 1:
report.add_heading('KTEQ-FM Quarterly Issues Report Q' + str(quarter) + ' '+ str(date.today().year-1), level=0)
else:
report.add_heading('KTEQ-FM Quarterly Issues Report Q' + str(quarter) + ' '+ str(date.today().year), level=0)
report.add_paragraph('This document is the quarterly Community Issues Report for KTEQ-FM. It details a number of community issues discussed during programming throughout the quarter, and lists public service announcements that support these issues. This list contains all of the public service announcements played on air by live DJs. For a complete list, including automated public service announcements, contact KTEQ-FM management at [email protected]')
table = report.add_table(rows=1, cols=3)
hdr = table.rows[0].cells
hdr[0].text = 'Date Played'
hdr[1].text = 'Time Played'
hdr[2].text = 'PSA Title'
for entry in logs:
row = table.add_row().cells
row[0].text = str(entry.date)
row[1].text = str(entry.time)
row[2].text = entry.psa
'''for psa in testlogs:
report.add_heading(psa, level=1)
table = report.add_table(rows=len(testlogs[psa]), cols=2)
for psadate in testlogs[psa]:
row = table.add_row().cells
row[0].text = str(psadate)
row[1].text = str(testlogs[psa][psadate])'''
reportdir = 'reports/' + str(date.today().year)
if not os.path.exists(reportdir):
os.makedirs(reportdir)
outputfile = reportdir + '/Q' + str(quarter) + '_Issues.docx'
report.save(outputfile)
def parseXML():
#tree = ET.parse('report.xml')
#root = tree.getroot()
#get the document
document = Document()
document.add_heading('Unit Testing Results Document', 0)
details_table = document.add_table(rows=5, cols=4)
p_name = details_table.cell(0,0).text = 'Project Name: '
version = details_table.cell(0,2).text = 'Version:'
author = details_table.cell(1,0).text = 'Written by: '
desc = details_table.cell(1,2).text = 'Description '
document.add_heading('Table showing results', level=2)
test_table = document.add_table(rows=5, cols=7)
# populate header row --------
heading_cells = test_table.rows[0].cells
heading_cells[0].text = '#'
heading_cells[1].text = 'Date Added'
heading_cells[2].text = 'Use case'
heading_cells[3].text = 'Expected Result'
heading_cells[4].text = 'Passed?'
heading_cells[5].text = 'Failed?'
heading_cells[6].text = 'Remarks'
input_cell = test_table.rows[1].cells
input_cell[0].text = '1'
input_cell[1].text = '7 September 2015'
input_cell[2].text = 'Create Group Chat'
document.save('Acceptance_Test_Results.docx')
def createDummyDocument():
document = Document(resource_path(os.path.join("assets","default.docx")))
document.add_heading('Document Title', 0)
p = document.add_paragraph('A plain paragraph having some ')
p.add_run('bold').bold = True
p.add_run(' and some ')
p.add_run('italic.').italic = True
document.add_heading('Heading, level 1', level=1)
document.add_paragraph('Intense quote', style='IntenseQuote')
document.add_paragraph(
'first item in unordered list', style='ListBullet'
)
document.add_paragraph(
'first item in ordered list', style='ListNumber'
)
# document.add_picture('monty-truth.png', width=Inches(1.25))
table = document.add_table(rows=1, cols=0, style='LightShading-Accent1')
column0 = table.add_column()
column0.width = 5000000
column1 = table.add_column()
column2 = table.add_column()
# print table.columns.cells
table.columns.width = 200000
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
tableData = [["5", "Apples", "yummy"], ["6", "Oranges", "they're orange"]]
for row in tableData:
row_cells = table.add_row().cells
row_cells[0].text = row[0]
row_cells[1].text = row[1]
row_cells[2].text = row[2]
document.add_page_break()
document.save('demo.docx')
def clean_write(answers):
"""Writes answers to word tables, original text,
POS-filtered words, Counts for each POS """
document = Document() #microsoft word format
document.add_heading('Listings Responses and Word Counts', 0)
document.add_paragraph(
'Image Descriptions and Reminded', 'ListBullet')
#def tag_count(answers):
image_counts = []
for i in range(len(answers)):
image_counts.append(Counter({}))
document.add_paragraph(
'Image Descriptions and Reminded', style='ListBullet')
for j in [0,1]: #do
table = document.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Cleaned Response Text'
hdr_cells[1].text = 'Usable Words'
hdr_cells[2].text = 'Word Counts'
for k in range(len(answers[i][j])):
row_cells = table.add_row().cells
txt = answers[i][j][k]
txt = txt.replace(',','')
txt = txt.replace('.','')
txt = txt.replace('-',' ')
txt = txt.replace('/',' ')
txt=txt.lower()
row_cells[0].text = txt
words = nltk.word_tokenize(txt)
tags= nltk.pos_tag(words)
word_bag = []
tag_bag = []
keep_tags = {'NN','NNS','NNP','NNPS','JJ','JJR','JJS','RB','RBR','RBS'}
for tag in tags:
if tag[1] in keep_tags:
word_bag.append(tag[0])
tag_bag.append(tag[1])
row_cells[1].text = str(word_bag)
counts = Counter(tag_bag)
POS = Counter(tag_bag).most_common(10)
image_counts[i] += counts
row_cells[2].text = str(POS)
answers[i][j][k] = word_bag
document.save('response_&_wordcount.docx')
def handleButton(self):
self.log.clear()
country = self.format(self.contryEdit.text(),3)
codeBusiness = self.format(self.idEdit.text(),4)
N = int(self.countEdit.text())
self.progress.setMaximum(N)
if not os.path.isdir("codes"):
try:
os.makedirs('codes')
except OSError:
pass
print "Error creating direcory [codes]"
index = 1
COL_SIZE = 4
document = Document()
tbl = document.add_table(rows=1, cols=4)
x = 0
while index <= N:
codeProduct = self.recAdd(str(index))#5
self.draw(str(country + codeBusiness + codeProduct + str(self.checksum(country + codeBusiness + codeProduct))), "codes\\" + str(index) + ".jpg")
self.progress.setValue(index)
if x % COL_SIZE == 0:
row_cells = tbl.add_row().cells
x = 0
paragraph = row_cells[x].paragraphs[0]
run = paragraph.add_run()
run.add_picture("codes\\" + str(index) + ".jpg", width=Inches(1.40))
index += 1
x += 1
self.image.setPixmap(QtGui.QPixmap("codes\\1.jpg"))
date = str(datetime.datetime.time(datetime.datetime.now())).replace(":","_").replace(".","_")
if not os.path.isdir("docs"):
try:
os.makedirs('docs')
except OSError:
print "Error creating direcory [docs]"
document.save("docs\\barcodes"+date+".docx")
os.startfile("docs\\barcodes"+date+".docx")
class hawkDOCX():
def __init__(self,name,template,hawk):
self.doc = Document(template)
self.secs = self.doc.sections
self.name = name
self.hlib = hawklib(hawk)
self.cur = None
def addPara(self,data):
self.cur = self.doc.add_paragraph(data)
def addHead(self,data):
self.doc.add_heading(data,level=1)
def addPageBreak(self):
self.doc.add_page_break()
def addImage(self,image,w=None):
if w == None:
self.doc.add_picture(image)
else:
self.doc.add_picture(image,width=Inches(w))
def addTable(self,data,nkeys=None):
if not nkeys:
keys = self.hlib.getKeys(data)
else:
keys = nkeys
table = self.doc.add_table(rows=1,cols=len(keys))
row = table.rows[0]
for i in range(len(keys)):
row.cells[i].text = str(keys[i])
for x in data:
nrow = table.add_row().cells
for b in range(len(keys)):
if keys[b] not in x:
outb = None
else:
outb = x[keys[b]]
nrow[b].text = str(outb)
def saveDoc(self):
self.doc.save(self.name)
def df2docx(df,tableName='itemName',tableLabel='itemLabel',doc=None,float_digit=0):
if doc is None:doc=Document()
doc.add_paragraph(u'%s: %s'%(tableName,tableLabel),style='Heading 1')
print u'%s: %s'%(tableName,tableLabel)
indexWidth=len(df.index[0]) if isinstance(df.index,pd.MultiIndex) else 1
dataWidth=df.shape[1]
tableWidth=indexWidth+dataWidth
colHeight=len(df.columns[0]) if isinstance(df.columns,pd.MultiIndex) or isinstance(df.columns[0],(tuple,list)) else 1
dataHeight=df.shape[0]
tableHeight=colHeight+dataHeight
table=doc.add_table(rows=tableHeight,cols=tableWidth,style='Table Grid')
#将df.columns写入Table
for j in range(df.columns.size):
col=df.columns[j]
if not isinstance(col,list) and not isinstance(col,tuple):col=[col]
for i in range(colHeight):
table.rows[i].cells[j+indexWidth].text=unicode(col[i])
#将df.index写入到Table
for j in range(df.index.size):
ind=df.index[j]
if not isinstance(ind,list) and not isinstance(ind,tuple):ind=[ind]
for i in range(indexWidth):
table.rows[j+colHeight].cells[i].text=unicode(ind[i])
#将data写入到Table
for j in range(dataHeight):
for i in range(dataWidth):
vl=df.values[j,i]
if type(vl) in (float,int):
vl=('%%.%df' % float_digit) % vl
elif type(vl) in (unicode,str):
pass
else:
print type(vl)
raise
table.rows[j+colHeight].cells[i+indexWidth].text=vl
#table.columns[0].width=Inches(0.1)
table.allow_autofit=False
col=table.columns[1]
for cell in col.cells:
cell.width=Inches(6)
#doc.save(docName)
return doc
def generate_random_docx(filename, paraph_size=30):
document = Document()
size = random.randint(5, paraph_size)
document.add_heading(generate_random_string(size), 0)
size = random.randint(5, paraph_size)
p = document.add_paragraph(
generate_random_string(size))
p.add_run('bold').bold = True
p.add_run(' and some ')
p.add_run('italic.').italic = True
size = random.randint(5, paraph_size)
document.add_heading(generate_random_string(size), level=1)
size = random.randint(5, paraph_size)
document.add_paragraph(generate_random_string(size), style='IntenseQuote')
for _ in range(random.randint(1, 10)):
size = random.randint(5, paraph_size)
document.add_paragraph(
generate_random_string(size), style='ListBullet')
for _ in range(random.randint(1, 10)):
size = random.randint(5, paraph_size)
document.add_paragraph(
generate_random_string(size), style='ListNumber')
table = document.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
for i in range(random.randint(1, 10)):
row_cells = table.add_row().cells
row_cells[0].text = str(i)
row_cells[1].text = generate_random_string(8)
row_cells[2].text = generate_random_string(30)
document.add_page_break()
document.save(filename)
def inert_table(file_name_path,cols_int,title_list,info_list,replace_str):
count = 0
document = Document(file_name_path)
for paragraph in document.paragraphs:
count += 1
if paragraph.text == replace_str:
table = document.add_table(rows=1, cols=cols_int)
p = paragraph._p
p.addprevious(table._tbl)
hdr_cells = table.rows[0].cells
for _title in xrange(cols_int):
hdr_cells[_title].text =title_list[_title]
for _index in xrange(len(info_list)):
row_cells = table.add_row().cells
for i in xrange(cols_int):
row_cells[i].text = info_list[_index][i]
document.save(file_name_path)
def html_to_docx(html_text):
tables = BeautifulSoup(html_text).findAll('table')
document = Document()
for table_index in range(len(tables)):
rows = tables[table_index].findAll('tr')
max_columns = max([len(rows[row_index].findAll(['th', 'td'])) for row_index in range(len(rows))])
column_shifter = [[None for elem_index in range(max_columns)] for row_index in range(len(rows))]
column_seeker = [0 for row_index in range(len(rows))]
table = document.add_table(rows=len(rows), cols=max_columns)
table.style = 'TableGrid'
for row_index in range(len(rows)):
cols = rows[row_index].findAll(['th', 'td'])
for col_index in range(len(cols)):
rowspan = int(cols[col_index].get('rowspan', 1))
colspan = int(cols[col_index].get('colspan', 1))
if rowspan == 0: rowspan = 1
if colspan == 0: colspan = 1
for rspan in range(rowspan):
if rspan == 0:
column_shifter[row_index + rspan][column_seeker[row_index + rspan]] = col_index
# Shift columns only on right side, do not touch columns on left side, they are not affected by colspan
if column_seeker[row_index] - colspan <= column_seeker[row_index + rspan]:
column_seeker[row_index + rspan] += colspan
if rowspan != 1 or colspan != 1:
top_left = table.cell(row_index, column_seeker[row_index] - colspan)
bottom_right = table.cell(row_index + rowspan - 1, column_seeker[row_index] - 1)
current_cell = top_left.merge(bottom_right)
else:
current_cell = table.cell(row_index, column_shifter[row_index].index(col_index))
try:
result = float(cols[col_index].getText())
if result.is_integer():
result = int(cols[col_index].getText())
except ValueError:
result = cols[col_index].getText()
finally:
current_cell.text = str(result)
return document
def create_otc_document(doc_json, mess_id):
arr = list()
document = Document()
res = json.loads(doc_json)
document.add_heading(u'Отчет о распространении сообщения в соцсетях (по репостам)', 0)
document.add_paragraph(res["1"]["author"] + " " +res["1"]["date"])
document.add_paragraph(res["1"]["text"])
document.add_page_break() #Закончен первый лист - заголовок, текст
#Гистограммы сюда
document.add_page_break() #Закончен второй лист лист - гистограммы
while res:
key, value = res.popitem()
arr.append(value)
arr = sorted(arr, key=lambda k: k['date'], reverse=True)
table = document.add_table(rows=1, cols=6)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = u'Дата'
hdr_cells[1].text = u'Автор'
hdr_cells[2].text = u'Лайки'
hdr_cells[3].text = u'Репосты'
hdr_cells[4].text = u'Комментарии'
hdr_cells[5].text = u'Оригинал'
while arr:
elem = arr.pop()
row_cells = table.add_row().cells
row_cells[0].text = unicode(elem["date"])
row_cells[1].text = unicode(elem["author"])
row_cells[2].text = unicode(elem["likes"])
row_cells[3].text = unicode(elem["comments"])
row_cells[4].text = unicode(elem["reposts"])
row_cells[5].text = unicode(elem["original"])
document.add_page_break() #Закончен третий лист - таблица
document.save("demo.docx")
def export(self, filename):
from docx import Document
if filename.split('.')[-1] != '.docx':
filename += '.docx'
document = Document()
h = document.add_heading(self.title, 0)
h.add_run('bold').bold = True
if self.text:
for p in self.data:
document.add_paragraph(p)
else:
table = document.add_table(rows=len(self.data), cols=len(self.data[0]))
r, c = -1, -1
for row in self.data:
r += 1
c = 0
for col in row:
hdr_cells = table.rows[0].cells
hdr_cells[c].text = col
c += 1
document.add_page_break()
document.save(filename)
# Reduce index entries "1:1,1:2,1:3" to 1:1-3"
print("Creating index reference ranges.")
index = indexreduce(index)
# Sort the reduced index entries numerically
for page in index:
index[page] = sorted(index[page], key=indexsort)
# With index list created, make the Word document
print("Creating index document.")
document = Document(args.template)
#if templatefile != None:
# document.add_page_break()
table = document.add_table(rows=0, cols=2, style="Light Shading")
l2marker=""
for entry in sorted(index.keys(), key=str.lower):
if entry == '': continue
#pdb.set_trace()
currentmarker = ord(entry[0].upper())
if currentmarker > 64: # "A" or after
if l2marker != currentmarker:
document.add_heading(entry[0].upper(), level=1)
table = document.add_table(rows=0, cols=2, style="Light Shading")
l2marker=currentmarker
row_cells = table.add_row().cells
row_cells[0].text = entry
row_cells[1].text = ", ".join(index[entry])
args.outfile.close()
def run(self, input_path, output_path):
files_to_process = set()
files_to_transform = set()
pdf_to_process = set()
for file in os.listdir(input_path):
absolute_file_path = os.path.join(input_path, file)
if file.endswith(".doc"):
files_to_transform.add(absolute_file_path)
elif file.endswith(".docx"):
files_to_process.add(absolute_file_path)
elif file.endswith(".pdf"):
pdf_to_process.add(absolute_file_path)
files_to_delete = util.batch_doc_to_docx(files_to_transform)
files_to_process = files_to_process.union(files_to_delete)
for file in files_to_process:
docx = Document(file)
records = list()
table2 = docx.tables[-2]
table3 = docx.tables[-1]
conclusion = self.get_conclusion(docx)
self.get_record_table3(records, table3)
self.get_record_table2(records, table2)
self.get_record_conclusion(records, conclusion)
# 图片提取
pics = Picture(Processor.name, file.split("\\")[-1], docx)
self.save_fig(output_path, pics, docx)
self.save(output_path, records)
print("提取完成" + file)
for file in pdf_to_process:
docx = Document()
with plb.open(file) as pdf:
tables = []
content = ""
for i in range(len(pdf.pages)):
table = pdf.pages[i].extract_tables()
content += pdf.pages[i].extract_text()
if not len(table) == 0:
tables.append(table)
tb2 = tables[-2]
table2 = docx.add_table(len(tb2[0]), len(tb2[0][0]))
table2 = self.traverse_table(tb2, table2)
tb3 = tables[-1]
table3 = docx.add_table(len(tb3[0]), len(tb3[0][0]))
table3 = self.traverse_table(tb3, table3)
records = list()
conclusion = self.get_pdf_conclusion(content)
self.get_record_table3(records, table3)
self.get_record_table2(records, table2)
self.get_record_conclusion(records, conclusion)
self.save(output_path, records)
# 提取PDF图片
pics_PDF = PicturePDF(Processor.name, file.split("\\")[-1], file)
self.save_fig_PDF(output_path, pics_PDF)
print("提取完成" + file)
for file in files_to_delete:
if os.path.exists(file):
os.remove(file)
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml.ns import nsdecls
from docx.oxml import parse_xml
# Set a cell background (shading) color to RGB D9D9D9.
shading_elm = parse_xml(r'<w:shd {} w:fill="D9D9D9"/>'.format(nsdecls('w')))
print shading_elm
recordset = [
{'qty': 'q1', 'id': 'id1', 'desc': 'desc1'},
{'qty': 'q2', 'id': 'id2', 'desc': 'desc2'},
{'qty': 'q3', 'id': 'id3', 'desc': 'desc3'}
]
document = Document()
table1 = document.add_table(rows=1, cols=3)
# table.style = ("ColorfulList", "TableGrid")
table1.style = 'TableGrid'
table1.alignment = WD_TABLE_ALIGNMENT.CENTER
table1.direction = WD_TABLE_DIRECTION.LTR
hdr_cells = table1.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
for cs in hdr_cells:
cs.paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
for item in recordset:
row_cells = table1.add_row().cells
row_cells[0].text = str(item.get('qty'))
def __init__(self):
super(MyWindow, self).__init__()
file_path, filetype = QFileDialog.getOpenFileName(
self, "选择文件", "/", "All Files (*);;Text Files (*.txt)")
print(file_path) # 打印文件全部路径（包括文件名和后缀名）
# 获取文件名
file_name = re.findall(r'[^\\/:*?"<>|\r\n]+$', file_path)
file_name = re.findall(r'(.+?)\.xlsx', file_name[0])
print(file_name[0] + '.xlsx')
try:
reload(sys)
# 开始时间
startTime = time.time()
# 读取excel xlsx文件
wb = load_workbook(file_path)
# 获取所有sheet页名字
xl_sheet_names = wb.get_sheet_names()
# 定位到相应sheet页,[0]为sheet页索引
xl_sheet = wb.get_sheet_by_name(xl_sheet_names[0])
# 获取行列数
excel_row = xl_sheet.max_row
excel_column = xl_sheet.max_column
# word文档
document = Document()
# 添加段落
p = document.add_paragraph('附件：核查名单')
table = document.add_table(rows=1,
cols=excel_column,
style="Table Grid")
# hdr_cells = table.rows[0].cells
# for num in range(0, excel_column):
# hdr_cells[num].text = u'' + str(xl_sheet.rows[num].value)
# 取excel第一页第一张表
i = 0
# 写入word
# 将excel表格装入itercars操作
itercars = iter(xl_sheet.rows)
# 列名
hdr_cells = table.rows[0].cells
for row in xl_sheet.rows:
for num in range(0, excel_column):
hdr_cells[num].text = u'' + str(row[num].value)
break
# 进入第二行开始循环插入
next(itercars)
for row in itercars:
row_cells = table.add_row().cells
for num in range(0, excel_column):
content = str(row[num].value)
content = "" if content == "None" else content
row_cells[num].text = u'' + content
# 创建一行三列的表格
table = document.add_table(rows=1, cols=3)
# 获取第一行的所有列数
hdr_cells = table.rows[0].cells
# 给第一行的各个列添加内容
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
# 给table表格添加新行，并给各列添加内容
for qty, id, desc in records:
row_cells = table.add_row().cells
row_cells[0].text = str(qty)
row_cells[1].text = id
row_cells[2].text = desc
docx_path = file_path.replace('xlsx', 'docx')
document.save(docx_path)
title = g.msgbox(msg=" 成功！",
title="Success",
ok_button="确定")
# 读取Excel中内容，以学校为key生成字典{"graduate_institutions":{"name","cert_no","major","education","award_year","award_detail"},...}
# 以学校为文件名输出各高校的核查文件
except Exception as e:
print(e)
title = g.msgbox(msg=" 生成失败:" + e,
title="Error",
ok_button="确定")
meu_word.add_paragraph('Primeiro item em uma lista numerada ',
style='List Number')
meu_word.add_picture('russian_skull.jpg', width=Cm(5.25))
# tabela = meu_word.add_table(rows=3, cols=2)
# celula = tabela.cell(0,0)
# celula.text = 'Nome'
registros = (
(3, '101', 'Maça'),
(7, '422', 'Ovos'),
(4, '631', 'Banana'),
)
meu_word.add_page_break()
tabela = meu_word.add_table(rows=1, cols=3)
cabecalho = tabela.rows[0].cells
cabecalho[0].text = 'Quantidade'
cabecalho[1].text = 'Id'
cabecalho[2].text = 'Descrição'
for quantidade, id, descricao in registros:
dados_por_linha = tabela.add_row().cells
dados_por_linha[0].text = str(quantidade)
dados_por_linha[1].text = str(id)
dados_por_linha[2].text = str(descricao)
meu_word.save('teste.docx')
class CSVToplineReport(object):
def __init__(self, questions, path_to_template, years):
self.doc = Document(path_to_template)
try:
self.line_break = self.doc.styles['LineBreak']
except KeyError:
self.line_break = None
self.questions = questions
self.years = years
names = questions.list_names()
for name in names:
self.write_question(name)
print("Finished!")
def write_question(self, name):
question = self.questions.get(name)
paragraph = self.doc.add_paragraph(
) # each question starts a new paragraph
self.write_name(name, paragraph)
self.write_prompt(question.prompt, paragraph)
self.write_n(question.n, paragraph)
if len(question.responses) > 0:
self.write_responses(question.responses, question.stat)
new = self.doc.add_paragraph("") # space between questions
if self.line_break is not None:
new.style = self.line_break
else:
run = paragraph.add_run()
run.add_break(WD_BREAK.LINE)
self.doc.add_paragraph("") # space between questions
def write_name(self, name, paragraph):
paragraph.add_run(name + ".")
def write_prompt(self, prompt, paragraph):
paragraph_format = paragraph.paragraph_format
paragraph_format.keep_together = True # question prompt will all be fit in one page
paragraph_format.left_indent = Inches(1)
paragraph.add_run("\t" + prompt)
paragraph_format.first_line_indent = Inches(
-1) # hanging indent if necessary
def write_n(self, n, paragraph):
paragraph.add_run(" (n = " + str(n) + ")")
def write_responses(self, responses, stat):
if len(self.years) > 0:
self.write_trended_responses(responses, stat)
else:
table = self.doc.add_table(rows=1, cols=5)
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[2])
response_cells[1].text = response.name
for year, response in response.frequencies.items():
if stat == 'percent':
response_cells[3].text = self.freqs_percent(
response, first_row)
else:
response_cells[3].text = str(response)
if response_cells[3].text != "*":
first_row = False
def write_trended_responses(self, responses, stat):
headers = self.max_years(responses)
table = self.doc.add_table(rows=1, cols=len(headers) + 4)
titles_row = table.add_row().cells
titles_row[1].merge(titles_row[2])
headers_index = 0
while headers_index < len(headers):
header_text = "Total %s" % headers[headers_index]
titles_row[headers_index + 4].text = header_text
headers_index += 1
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[3])
response_cells[1].text = response.name
freq_col = 4
for header in headers:
if response.frequencies.get(header) is not None:
freq = response.frequencies.get(header)
if stat == 'percent':
text = self.freqs_percent(freq, first_row)
else:
text = str(freq)
response_cells[freq_col].text = text
first_row = False
freq_col += 1
def max_years(self, responses):
years_used = []
for response in responses:
for year in self.years:
if response.frequencies.get(year) is not None:
if year not in years_used:
years_used.append(year)
return years_used
def save(self, path_to_output):
self.doc.save(path_to_output)
def freqs_percent(self, freq, is_first=False):
result = 0
if freq == "NA":
return
if float(freq) >= 1.0:
result = int(freq) * 100
else:
percent = float(freq)
percent = percent * 100
if percent > 0 and percent < 1:
result = "<1"
elif percent == 0:
result = "*"
else:
result = int(round(percent))
if is_first and result != "*":
result = str(result) + "%"
return str(result)
def result():
if request.method == 'POST':
result = request.form
clg_name1 = request.form['clg name']
print(clg_name1)
department = request.form['department']
dispach_date = request.form['dispach date']
start_date = request.form['start date']
end_date = request.form['end date']
class_name = request.form['class name']
df1 = pd.read_excel(request.files.get('file'))
print("Loading....")
#print(list(df1.columns[3]))
sub_name = list(df1.columns)
sub_1_name = sub_name[3]
sub_2_name = sub_name[5]
sub_3_name = sub_name[7]
sub_4_name = sub_name[9]
sub_5_name = sub_name[11]
#print(sub_1_name)
sub_1_UT1 = sub_name[4]
sub_2_UT1 = sub_name[6]
sub_3_UT1 = sub_name[8]
sub_4_UT1 = sub_name[10]
sub_5_UT1 = sub_name[12]
maindata = []
for i in range(0, len(df1)):
data = {}
total = 0
data['Sr No'] = df1['Sr No'][i]
data['Roll No'] = df1['Roll No'][i]
data['Name of Students'] = df1['Name of Students'][i]
# Reading Subject Attendance
data['Sub1'] = round(df1[sub_1_name][i])
data['Sub2'] = round(df1[sub_2_name][i])
data['Sub3'] = round(df1[sub_3_name][i])
data['Sub4'] = round(df1[sub_4_name][i])
data['Sub5'] = round(df1[sub_5_name][i])
data['Total'] = round(df1['Total'][i])
# Reading UT1 marks
data['Sub1_UT1'] = df1[sub_1_UT1][i]
data['Sub2_UT1'] = df1[sub_2_UT1][i]
data['Sub3_UT1'] = df1[sub_3_UT1][i]
data['Sub4_UT1'] = df1[sub_4_UT1][i]
data['Sub5_UT1'] = df1[sub_5_UT1][i]
data['TG_Name'] = df1['TG_Name'][i]
data['TG_Phone'] = df1['TG_Phone'][i]
data['HOD'] = df1['HOD'][i]
data['Principal'] = df1['Principal'][i]
data['Parents_Name'] = df1['Parents Name'][i]
data['Address'] = df1['Address'][i]
data['Parents_Phone'] = df1['Parents Phone'][i]
# print(data['Sub1-UT1'])
maindata.append(data)
# pprint.pprint(maindata)
# -----Main Program----------------------
Sr_No, Roll_No, Name_of_Students, Sub1, Sub2, Sub3, Sub4, Sub5, Total = [
[] for bb in range(9)
]
Sub1_UT1, Sub2_UT1, Sub3_UT1, Sub4_UT1, Sub5_UT1 = [[]
for aa in range(5)]
TG, Phone, HOD1, Principal1, Parents1, Address1, ParentsPhone1 = [
[] for cc in range(7)
]
for i in range(0, len(maindata)):
Sr_No.append(maindata[i]['Sr No'])
Roll_No.append(maindata[i]['Roll No'])
Name_of_Students.append(maindata[i]['Name of Students'])
Sub1.append(maindata[i]['Sub1'])
Sub2.append(maindata[i]['Sub2'])
Sub3.append(maindata[i]['Sub3'])
Sub4.append(maindata[i]['Sub4'])
Sub5.append(maindata[i]['Sub5'])
Total.append(maindata[i]['Total'])
Sub1_UT1.append(maindata[i]['Sub1_UT1'])
Sub2_UT1.append(maindata[i]['Sub2_UT1'])
Sub3_UT1.append(maindata[i]['Sub3_UT1'])
Sub4_UT1.append(maindata[i]['Sub4_UT1'])
Sub5_UT1.append(maindata[i]['Sub5_UT1'])
TG.append(maindata[i]['TG_Name'])
Phone.append(maindata[i]['TG_Phone'])
HOD1.append(maindata[i]['HOD'])
Principal1.append(maindata[i]['Principal'])
Parents1.append(maindata[i]['Parents_Name'])
Address1.append(maindata[i]['Address'])
ParentsPhone1.append(maindata[i]['Parents_Phone'])
# print(Principal1)
# print("done")
# -----Creating Word Document------------------------
document = Document()
sections = document.sections
for section in sections:
section.top_margin = Cm(1.0)
section.bottom_margin = Cm(1.0)
section.left_margin = Cm(1.75)
section.right_margin = Cm(1.75)
for (Sr, Roll, Name, S1, S2, S3, S4, S5, Total1, ST1, ST2, ST3, ST4,
ST5, TG2, Phone2, HOD2, Principal2, Parents2, Address2,
ParentsPhone2) in zip(Sr_No, Roll_No, Name_of_Students, Sub1,
Sub2, Sub3, Sub4, Sub5, Total, Sub1_UT1,
Sub2_UT1, Sub3_UT1, Sub4_UT1, Sub5_UT1, TG,
Phone, HOD1, Principal1, Parents1, Address1,
ParentsPhone1):
clg_name = document.add_paragraph(clg_name1)
clg_name.alignment = WD_ALIGN_PARAGRAPH.CENTER
dept_name = document.add_paragraph(department)
dept_name.alignment = WD_ALIGN_PARAGRAPH.CENTER
date1 = document.add_paragraph('Date' + dispach_date)
date1.alignment = WD_ALIGN_PARAGRAPH.RIGHT
ar1 = document.add_paragraph('ACADEMIC REPORT')
ar1.alignment = WD_ALIGN_PARAGRAPH.CENTER
period = document.add_paragraph('Period :' + start_date + ' to ' +
end_date)
period.alignment = WD_ALIGN_PARAGRAPH.CENTER
sname = document.add_paragraph('Students Name : ')
sname.add_run(str(Name)).bold = True
roll = document.add_paragraph('Class : ' + class_name)
roll.add_run('\t\t\t\t\t\t\tRoll No. : ' + str(Roll)).bold = True
tg = document.add_paragraph()
tg.add_run('Name of Teacher Guardian :' + TG2 +
'\n Teacher Guardian Phone :' + str(Phone2)).bold = True
ar = document.add_paragraph('ATTENDANCE RECORD')
ar.alignment = WD_ALIGN_PARAGRAPH.CENTER
overallatt = document.add_paragraph()
overallatt.add_run('\t\t\t\t\tTotal Attendance : ' + str(Total1) +
'%').bold = True
# --------table---------------
table = document.add_table(rows=6, cols=5, style='Table Grid')
table.cell(0, 0).width = 731520 # 0.8 * 914400
table.cell(1, 0).width = 731520
table.cell(2, 0).width = 731520
table.cell(3, 0).width = 731520
table.cell(4, 0).width = 731520 # 1.2 * 914400
table.cell(5, 0).width = 731520
table.cell(0, 1).width = 4389120 # 4.8 * 914400
table.cell(1, 1).width = 4389120
table.cell(2, 1).width = 4389120
table.cell(3, 1).width = 4389120
table.cell(4, 1).width = 4389120 # 1.2 * 914400
table.cell(5, 1).width = 4389120
table.cell(0, 2).width = 1097280 # 1.2 * 914400
table.cell(1, 2).width = 1097280
table.cell(2, 2).width = 1097280
table.cell(3, 2).width = 1097280
table.cell(4, 2).width = 1097280 # 1.2 * 914400
table.cell(5, 2).width = 1097280
table.cell(0, 3).width = 1828800 # 1.2 * 914400
table.cell(1, 3).width = 1828800
table.cell(2, 3).width = 1828800
table.cell(3, 3).width = 1828800
table.cell(4, 3).width = 1828800 # 1.5 * 914400
table.cell(5, 3).width = 1828800
table.cell(0, 4).width = 1097280 # 1.2 * 914400
table.cell(1, 4).width = 1097280
table.cell(2, 4).width = 1097280
table.cell(3, 4).width = 1097280
table.cell(4, 4).width = 1097280 # 1.2 * 914400
table.cell(5, 4).width = 1097280
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Sr. No.'
hdr_cells[1].text = 'Subject Name'
hdr_cells[2].text = 'Attendance in %'
hdr_cells[3].text = 'Unit Test 1 Marks (out of 25)'
hdr_cells[4].text = 'Remark'
hdr_cells = table.rows[1].cells
hdr_cells[0].text = '1'
hdr_cells[1].text = sub_1_name
hdr_cells[2].text = str(S1)
hdr_cells[3].text = str(ST1)
if str(ST1) == 'AB':
hdr_cells[4].text = 'Absent'
elif str(ST1) == '':
hdr_cells[4].text = 'Not Applicable'
else:
if ST1 >= 10:
hdr_cells[4].text = 'Pass'
else:
hdr_cells[4].text = 'Fail'
hdr_cells = table.rows[2].cells
hdr_cells[0].text = '2'
hdr_cells[1].text = sub_2_name
hdr_cells[2].text = str(S2)
hdr_cells[3].text = str(ST2)
if str(ST2) == 'AB':
hdr_cells[4].text = 'Absent'
else:
if ST2 >= 10:
hdr_cells[4].text = 'Pass'
else:
hdr_cells[4].text = 'Fail'
hdr_cells = table.rows[3].cells
hdr_cells[0].text = '3'
hdr_cells[1].text = sub_3_name
hdr_cells[2].text = str(S3)
hdr_cells[3].text = str(ST3)
if str(ST3) == 'AB':
hdr_cells[4].text = 'Absent'
else:
if ST3 >= 10:
hdr_cells[4].text = 'Pass'
else:
hdr_cells[4].text = 'Fail'
hdr_cells = table.rows[4].cells
hdr_cells[0].text = '4'
hdr_cells[1].text = sub_4_name
hdr_cells[2].text = str(S4)
hdr_cells[3].text = str(ST4)
if str(ST4) == 'AB':
hdr_cells[4].text = 'Absent'
else:
if ST4 >= 10:
hdr_cells[4].text = 'Pass'
else:
hdr_cells[4].text = 'Fail'
hdr_cells = table.rows[5].cells
hdr_cells[0].text = '5'
hdr_cells[1].text = sub_5_name
hdr_cells[2].text = str(S5)
hdr_cells[3].text = str(ST5)
if str(ST5) == 'AB':
hdr_cells[4].text = 'Absent'
else:
if ST5 >= 10:
hdr_cells[4].text = 'Pass'
else:
hdr_cells[4].text = 'Fail'
remark = document.add_paragraph('\nREMARK')
remark.alignment = WD_ALIGN_PARAGRAPH.CENTER
rm = document.add_paragraph()
rm.add_run(
'1) Your ward is not attending lectures.\n2) Performance of ward in attendance is very poor. \n3) Attendance of your ward is below 75%; his term is liable to be detained.'
)
rm1 = document.add_paragraph()
rm1.add_run(
'In case of any queries about the performance of your ward, Please visit personally to the college or contact the concerned teacher guardian.\n\n'
).bold = True
table1 = document.add_table(rows=2, cols=3)
hdr_cells = table1.rows[0].cells
hdr_cells[0].text = TG2
hdr_cells[1].text = HOD2
hdr_cells[2].text = Principal2
hdr_cells = table1.rows[1].cells
hdr_cells[0].text = 'Teacher Guradian'
hdr_cells[1].text = ' HOD'
hdr_cells[2].text = ' Principal\n\n\n'
pname = document.add_paragraph()
pname.add_run(' \tTo \n \t' + Parents2 + '\n\t' + Address2 +
'\n\t' + 'Mobile No.:' +
str(ParentsPhone2)).bold = True
document.add_page_break()
document.save('Parents_Letter.docx')
print("well done, Letters generated")
return render_template("result.html",
result=result,
department=department,
start_date=start_date,
end_date=end_date,
class_name=class_name,
dispach_date=dispach_date)
def on_post(self, req, resp):
params = req.params
samplename = params["sampleName"]
title = params["title"]
if title:
pass
# if samplename:
# """先假设接收的参数 进行进一步的处理"""
# pass
resp.status = falcon.HTTP_200
resp._headers["Access-Control-Allow-Origin"] = "*"
document = Document()
document.add_heading(u'普通的段落文字，这是 ', 0)
p = document.add_paragraph(u'普通的段落文字，这是 ')
p.add_run(u'加粗').bold = True
p.add_run(u' 这是 ')
p.add_run(u'斜体').italic = True
document.add_heading(u'这是1级标题', level=1)
document.add_paragraph(u'引用', style='IntenseQuote')
document.add_paragraph(u'无序列表', style='ListBullet')
document.add_paragraph(u'有序列表', style='ListNumber')
# document.add_picture('qr.png', width=Inches(1.25))
table = document.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
recordset = [{
'qty': 100,
'id': 15,
'desc': 'saksfkjsakj'
}, {
'qty': 99,
'id': 11,
'desc': '9fgduieef'
}, {
'qty': 87,
'id': 13,
'desc': 'uiiogdsagw'
}, {
'qty': 69,
'id': 14,
'desc': 'hgjhshsd'
}]
for item in recordset:
row_cells = table.add_row().cells
row_cells[0].text = str(item['qty'])
row_cells[1].text = str(item['id'])
row_cells[2].text = item['desc']
document.add_page_break()
report_name = "%s.docx" % samplename
# writer = csv.writer(response)
document.save('/home/khl/web/dist/downloads/tmp/%s' %
report_name) # 生成文件放到该文件夹下面
files = '/home/khl/web/dist/downloads/tmp/%s' % report_name
if os.path.exists(files):
result = {
'success': True,
'info':
'http://192.168.1.144:8096/downloads/tmp/%s' % report_name,
"reportName": report_name
}
else:
result = {'success': False, 'info': '报告生成错误!'}
print(result)
resp.body = json.dumps(result, ensure_ascii=False)
class Dtexm(object):
def __init__(self, filename, cols_list: list = None):
self.filename = filename
self.doc = Document()
# 设置默认字体
self.doc.styles['Normal'].font.name = u'微软雅黑'
self.doc.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
self.doc.styles['Normal'].font.size = Pt(10.5)
self.doc.styles['Normal'].font.color.rgb = RGBColor(0, 0, 0)
self.doc.add_heading('数据检测报告', 0)
if isinstance(self.filename, str):
self.add_intense_quote(f'Document：{self.filename}')
self.df = rdf(self.filename)
elif isinstance(self.filename, pd.DataFrame):
self.df = self.filename
else:
raise ValueError('请输入Dataframe或路径')
if cols_list != None:
cols_list = ensure_list(cols_list)
self.df = self.df[[cols_list]]
self.lenth = len(self.df)
def add_df2table(self, table_df):
'''将dataframe作为表格写入'''
t = self.doc.add_table(table_df.shape[0] + 1, table_df.shape[1], style='Table Grid')
for j in range(table_df.shape[-1]):
t.cell(0, j).text = table_df.columns[j]
for i in range(table_df.shape[0]):
for j in range(table_df.shape[-1]):
t.cell(i + 1, j).text = str(table_df.values[i, j])
def add_normal_p(self, text):
'''添加普通段落'''
self.doc.add_paragraph(text)
def add_bullet_list(self, text):
'''添加无序列表'''
self.doc.add_paragraph(text, style='List Bullet')
def add_order_list(self, text):
'''添加有序列表'''
self.doc.add_paragraph(text, style='List Number')
def add_intense_quote(self, text):
self.doc.add_paragraph(text, style='Intense Quote')
def add_title(self, text, n):
'''添加标题'''
self.doc.add_heading(text, level=n)
def serise_describe(self, col):
'''数值型列的描述性统计'''
desc = pd.DataFrame(self.df[col].describe().reset_index())
desc = desc.rename(columns={'index': '分类', col: '值'})
desc['分类'] = desc['分类'].replace(to_replace=class_dic)
skew_add = pd.DataFrame({'分类': '偏度系数',
'值': [self.df[col].skew()]})
kurt_add = pd.DataFrame({'分类': '峰度系数',
'值': [self.df[col].kurt() - 3]})
null_num = self.lenth - desc.loc[desc['分类'] == '计数', '值'][0]
null_rate = null_num / self.lenth
null_add = pd.DataFrame({'分类': '缺失数',
'值': [null_num]})
null_rate_add = pd.DataFrame({'分类': '缺失率',
'值': [null_rate]})
desc = desc.append(skew_add, sort=False)
desc = desc.append(kurt_add, sort=False)
desc = desc.append(null_add, sort=False)
desc = desc.append(null_rate_add, sort=False)
desc = col_round(desc, '值')
self.add_df2table(desc)
def object_describe(self, col):
desc = pd.DataFrame(self.df[col].value_counts().reset_index())
if len(desc) > 20:
desc = desc.rename(columns={'index': '分类_Top15', col: '数量'})
desc['分类_Top15'] = desc['分类_Top15'].replace(to_replace=class_dic)
res = desc.head(15)
else:
desc = desc.rename(columns={'index': '分类', col: '数量'})
desc['分类'] = desc['分类'].replace(to_replace=class_dic)
res = desc
self.add_df2table(res)
def is_float(self, col):
def try2float(x):
try:
return float(x)
except ValueError:
return None
length = len(self.df[~self.df[col].isna()])
null_num = len(self.df[~self.df[col].apply(lambda x: try2float(x)).isna()])
rates = null_num / length
if rates >= 0.8:
text = f' {col} 列有超过{int(rates * 100)}%的值为数值型的数据'
p = self.doc.add_paragraph('')
p.add_run(text).font.color.rgb = RGBColor(250, 0, 0)
def is_date(self, col):
length = len(self.df[~self.df[col].isna()])
null_num = len(self.df[~pd.to_datetime(self.df[col], errors='coerce').isna()])
rates = null_num / length
if rates >= 0.8:
text = f' {col} 列有超过{int(rates * 100)}%的值为日期格式的数据'
p = self.doc.add_paragraph('')
p.add_run(text).font.color.rgb = RGBColor(250, 0, 0)
def hist_plot(self, col):
plt.figure(figsize=(12, 4))
plt.style.use('seaborn')
data = self.df[~self.df[col].isna()][col].values
plt.hist(data)
plt.savefig('image.png')
self.doc.add_picture('image.png', width=Inches(6))
if os.path.exists('image.png'):
os.remove('image.png')
def basic(self):
'''数据基础信息描述'''
# 列名
self.add_bullet_list('列名：')
self.add_normal_p('，'.join(self.df.columns))
# 文件size
self.add_bullet_list('文件尺寸：')
self.add_normal_p(f'行：{self.df.shape[0]}，列{self.df.shape[1]}')
# 列类型
self.add_bullet_list('数据类型：')
self.col_types = pd.DataFrame(self.df.dtypes, columns=['类型']).reset_index().rename(columns={'index': '列名'})
self.add_df2table(self.col_types)
def col_by_col(self):
'''逐列检测'''
for col in self.df.columns:
self.add_bullet_list(col)
if (self.df[col].dtype == 'int64') | (self.df[col].dtype == 'float64'):
self.serise_describe(col)
self.hist_plot(col)
self.add_normal_p('')
else:
self.is_float(col)
self.is_date(col)
self.add_normal_p('')
def save(self, savefilename: str = None):
'''保存文件至word文档'''
if savefilename == None:
if isinstance(self.filename, pd.DataFrame):
raise FileNotFoundError('请输入要保存的文件路径')
savefilename = fn(self.filename) + '-' '检测报告.docx'
self.doc.save(savefilename)
print('文件保存至', os.path.abspath(savefilename))
def examine_all(self):
'''整套流程'''
self.basic()
self.col_by_col()
self.save()
def summ_corr_apa_table_word(mod_raw_data_df, output_df):
# unique vars in var1 given by user's order and here ONLY adding vars from col 2 that are NOT in var 1
variables_list = list(output_df[global_vars.summ_corr_varOne].unique()) + list(set(output_df[global_vars.summ_corr_varTwo].unique()) - set(output_df[global_vars.summ_corr_varOne].unique()))
if global_vars.corr_table_triangle == "Upper triangle":
header_cols = variables_list[1:]
header_rows = variables_list[:-1]
elif global_vars.corr_table_triangle == "Lower triangle":
header_cols = variables_list[:-1]
header_rows = variables_list[1:]
elif global_vars.corr_table_triangle == "Both":
header_cols = [x for x in variables_list] # not straight up variables_list; pass by value vs pass by reference
header_rows = [x for x in variables_list]
# this adds an empty column where the significance signs will be placed for better presentation
# the code looks slightly wrong as it return a None array but it works
[header_cols.insert(x, "") for x in range(1, len(header_cols)*2, 2)]
doc = Document()
table_rows_len = len(header_rows) + 1
table_cols_len = len(header_cols) + 1
table = doc.add_table(rows=table_rows_len, cols=table_cols_len)
for ind, var in enumerate([""] + header_cols):
table.cell(row_idx=0, col_idx=ind).text = var
for ind, var in enumerate([""] + header_rows):
table.cell(row_idx=ind, col_idx=0).text = var
if global_vars.corr_table_triangle == "Upper triangle":
inside_loop_ind_start = 1
for outside_loop_ind in range(1, table_rows_len):
outside_loop_var = table.cell(row_idx=outside_loop_ind, col_idx=0).text
for inside_loop_ind in range(inside_loop_ind_start, table_cols_len):
inside_loop_var = table.cell(row_idx=0, col_idx=inside_loop_ind).text
if inside_loop_var == "" or outside_loop_var == "": # this allows to skip the columns designed for the significance signs
continue
else:
# here query method is not preferred as it is not only slower as it is much smaller dataset but also cannot refer to two different vaiables (colname and val)
df_filtered = output_df[((output_df[global_vars.summ_corr_varOne]==outside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==inside_loop_var)) | ((output_df[global_vars.summ_corr_varOne]==inside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==outside_loop_var))].iloc[0]
r = df_filtered[global_vars.summ_corr_coeff]
p = df_filtered["adjusted_pvalues"]
r = helper_funcs.correlations_format_val(r, p)
if "_" in r:
r, sign = r.split("_")
else:
sign = ""
table.cell(row_idx=outside_loop_ind, col_idx=inside_loop_ind).text = r
table.cell(row_idx=outside_loop_ind, col_idx=inside_loop_ind+1).text = sign
inside_loop_ind_start += 2
elif global_vars.corr_table_triangle == "Lower triangle":
inside_loop_ind_start = 1
for outside_loop_ind in range(1, table_cols_len):
outside_loop_var = table.cell(row_idx=0, col_idx=outside_loop_ind).text
if outside_loop_var == "":
continue
else:
for inside_loop_ind in range(inside_loop_ind_start, table_rows_len):
inside_loop_var = table.cell(row_idx=inside_loop_ind, col_idx=0).text
# here query method is not preferred as it is not only slower as it is much smaller dataset but also cannot refer to two different vaiables (colname and val)
df_filtered = output_df[((output_df[global_vars.summ_corr_varOne]==outside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==inside_loop_var)) | ((output_df[global_vars.summ_corr_varOne]==inside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==outside_loop_var))].iloc[0]
r = df_filtered[global_vars.summ_corr_coeff]
p = df_filtered["adjusted_pvalues"]
r = helper_funcs.correlations_format_val(r, p)
if "_" in r:
r, sign = r.split("_")
else:
sign = ""
table.cell(row_idx=inside_loop_ind, col_idx=outside_loop_ind).text = r
table.cell(row_idx=inside_loop_ind, col_idx=outside_loop_ind+1).text = sign
inside_loop_ind_start += 1
elif global_vars.corr_table_triangle == "Both":
inside_loop_ind_start = 1
for outside_loop_ind in range(1, table_cols_len):
outside_loop_var = table.cell(row_idx=0, col_idx=outside_loop_ind).text
if outside_loop_var == "":
continue
else:
for inside_loop_ind in range(inside_loop_ind_start, table_rows_len):
inside_loop_var = table.cell(row_idx=inside_loop_ind, col_idx=0).text
if inside_loop_var == "": # this allows to skip the columns designed for the significance signs
continue
else:
if outside_loop_var == inside_loop_var:
table.cell(row_idx=inside_loop_ind, col_idx=outside_loop_ind).text = "1"
else:
# here query method is not preferred as it is not only slower as it is much smaller dataset but also cannot refer to two different vaiables (colname and val)
df_filtered = output_df[((output_df[global_vars.summ_corr_varOne]==outside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==inside_loop_var)) | ((output_df[global_vars.summ_corr_varOne]==inside_loop_var) & (output_df[global_vars.summ_corr_varTwo]==outside_loop_var))].iloc[0]
r = df_filtered[global_vars.summ_corr_coeff]
p = df_filtered["adjusted_pvalues"]
r = helper_funcs.correlations_format_val(r, p)
if "_" in r:
r, sign = r.split("_")
else:
sign = ""
table.cell(row_idx=inside_loop_ind, col_idx=outside_loop_ind).text = r
table.cell(row_idx=inside_loop_ind, col_idx=outside_loop_ind+1).text = sign
for cell in table.rows[0].cells:
helper_funcs.set_cell_border(cell, top=global_vars.border_APA_word, bottom=global_vars.border_APA_word)
for cell in table.rows[table_rows_len-1].cells:
helper_funcs.set_cell_border(cell, bottom=global_vars.border_APA_word)
for i in range(1, table_cols_len, 2):
table.cell(row_idx=0, col_idx=i).merge(table.cell(row_idx=0, col_idx=i+1))
table.cell(row_idx=0, col_idx=i).text = table.cell(row_idx=0, col_idx=i).text[:-1] # meging cells adds a paragraph break at the end; this gets rid of it
for cell in table.rows[0].cells:
helper_funcs.word_style(cell, italic=True)
cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
for cell in table.columns[0].cells:
helper_funcs.word_style(cell, italic=True)
cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
for row in range(0, table_rows_len):
for cell in table.rows[row].cells:
cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
cell.paragraphs[0].paragraph_format.space_after = Inches(0)
cell.paragraphs[0].paragraph_format.space_before = Inches(0)
cell.paragraphs[0].paragraph_format.line_spacing = 1
for row in range(1, table_rows_len):
for col in range(1, table_cols_len, 2):
table.cell(row_idx=row, col_idx=col).paragraphs[0].paragraph_format.right_indent = Inches(-0.08)
table.cell(row_idx=row, col_idx=col+1).paragraphs[0].paragraph_format.left_indent = Inches(-0.06)
table.cell(row_idx=row, col_idx=col).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.RIGHT
table.cell(row_idx=row, col_idx=col+1).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.LEFT
doc = helper_funcs.set_autofit(doc)
para = doc.add_paragraph("** ")
para.add_run("p").italic = True
para.add_run(" < 0.01")
para.add_run().add_break()
para.add_run("* ")
para.add_run("p").italic = True
para.add_run(" < {}".format(global_vars.alpha_threshold))
helper_funcs.add_correction_message_word(doc)
helper_funcs.savefile(doc=doc)
usecols=[0, 1, 2, 3],
names=None) # 读取项目名称列,不要列名
df_li = df.values.tolist()
result = []
for s_li in df_li:
result.append(s_li)
document = Document(
"/Users/wangsiren/Downloads/ForHBNUSU/LiteratureandSportsDept/空白A4.docx")
document.styles['Normal'].font.name = u'宋体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
style = document.styles['Normal']
font = style.font
font.size = Pt(14)
table0 = document.add_table(rows=1, cols=1)
cell0 = table0.cell(0, 0)
cell0.text = result[0][2] + "\n" + "带队老师：" + result[0][3]
cell0.paragraphs[0].paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
cell0.paragraphs[0].paragraph_format.alignment = WD_ALIGN_VERTICAL.CENTER
run = table0.cell(0, 0).paragraphs[0].add_run('smida')
run.font.name = '宋体'
run.font.size = 14
total = len(result)
print(len(result))
C = 3
R = total // C * 2
n = 0
table = document.add_table(rows=R, cols=C)
for i in range(0, R // 2):
class _QMSDocx:
"""A class to generate QMS summary report templates given total pass and failure case numbers.
Args:
total_pass: Total number of passing QMS tests.
total_fail: Total number of failing QMS tests.
"""
def __init__(self, total_pass: int, total_fail: int) -> None:
self.doc = Document()
self._write_static_p1()
self._write_test_result(total_pass, total_fail)
self._write_static_p2()
def save(self, output_path: str) -> None:
"""Save document object to disk.
Args:
output_path: Saving path.
"""
self.doc.save(output_path)
def _write_test_result(self, total_pass: int, total_fail: int) -> None:
"""Write the test result table.
Args:
total_pass: Total number of passing QMS tests.
total_fail: Total number of failing QMS tests.
"""
total_test = total_pass + total_fail
table = self.doc.add_table(rows=2, cols=4)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
if i == 0:
run.bold = True
table.style = "Table Grid"
self.fill_table(table,
[["Model", "Total Tests", "Tests Passed", "Tests Failed"],
["Model#1", str(total_test), str(total_pass), str(total_fail)]])
def _write_static_p1(self) -> None:
"""Write the first part of the report before test result table.
"""
para = self.doc.add_paragraph()
self.add_line_break(para, 9, font_size=Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(22)
run.add_text("Verification Summary report for Model")
self.add_line_break(para, 4, font_size=Pt(14))
para = self.doc.add_paragraph()
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("Record of changes")
run.add_break()
table = self.doc.add_table(rows=2, cols=4)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
run.bold = True
run.font.size = Pt(14)
table.style = "Table Grid"
self.fill_table(table, [["Rev number", "Date", "Author", "Comments"], ["1", "", "<Name>", "Initial revision"]])
para = self.doc.add_paragraph()
self.add_line_break(para, 3, Pt(14))
run = para.add_run()
run.bold = True
run.add_text("NOTE:")
run = para.add_run()
run.add_text(" Copies for use are available via the MyWorkshop system. Any printed copies are considered"
"uncontrolled. All approval signatures are captured electronically in MyWorkshop.")
self.add_line_break(para, 9, Pt(14))
para = self.doc.add_paragraph()
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("1 Introdction")
para = self.doc.add_paragraph()
run = para.add_run()
run.bold = True
run.add_text("1.1 Purpose & Scope")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text(
"This document contains the results of the verification for model for <name>. Tests were executed in "
"accordance with the associated Verification Plan (DOC). ")
para = self.doc.add_paragraph()
run = para.add_run()
run.bold = True
run.add_text("1.2 References")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text("Find below all the relevant documents related to any tools used during verification. ")
table = self.doc.add_table(rows=4, cols=3)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
if i == 0:
run.bold = True
table.style = "Table Grid"
self.fill_table(
table,
[["Location", "Reference", "Document Name"], ["Myworkshop", "<DOC>", "Edison AI Model Verification Plan"], [
"Myworkshop", "<DOC>", "Model Evaluation Tool Validation"
], ["Myworkshop", "<DOC>", "The CRS documents are in Approved state"]])
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("2 Infrastructure Details")
table = self.doc.add_table(rows=6, cols=2)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
if i == 0:
run.bold = True
table.style = "Table Grid"
self.fill_table(table,
[["", "Details"], ["GPU Architecture", ""], ["OS environment", ""], [
"Collection ID of test data set in Edison AI Workbench", ""
], ["Model Artifact ID (s)", ""], ["Location of model test scripts", ""]])
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("3 Verification Tools")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text("Tools used for verification are listed in Model Evaluation Tool Validation <DOC>")
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("4 Results of Verification")
table = self.doc.add_table(rows=2, cols=3)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
table.style = "Table Grid"
self.fill_table(table,
[["Document", "Location", "Comments"],
["<DOC>", "Myworkshop", "Verification Procedure is in Approved state"]])
para = self.doc.add_paragraph()
self.add_line_break(para, 1, Pt(14))
run = para.add_run()
run.bold = True
run.add_text("4.1 Functional and Performance Tests")
def _write_static_p2(self) -> None:
"""Write the second part of the report after test result table.
"""
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("5 Verification Details")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text("Below table details the summary of the completion of verification cycle. ")
table = self.doc.add_table(rows=9, cols=2)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
if i == 0 or j == 0:
run.bold = True
table.style = "Table Grid"
self.fill_table(
table,
[["Activity", "Details"],
[
"Test set location in ALM",
"URL: http://hc-alm12.health.ge.com/qcbin/start_a.jsp "
"Domain: SWPE / Project: HealthCloud \n ALM\Test Lab\<location of ALM test set>"
], ["Verification Cycle Start Date", ""], ["Verification Cycle End Date", ""],
["Name of the Tester(s)", ""], ["Total # of test cases executed", ""], ["Total # of Defects Filed", ""],
["Total # of Tests Passed", ""], ["Total # of Tests Failed", ""]])
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("6 Defect Summary List")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text("Below table summarizes the defects found during verification cycle."
"The defects are tracked in ALM: http://hc-alm12.health.ge.com/qcbin/start_a.jsp"
"Domain: SWPE / Project: HealthCloud")
table = self.doc.add_table(rows=2, cols=5)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
if i == 0:
run.bold = True
table.style = "Table Grid"
self.fill_table(table,
[["Defect ID", "Summary", "Classification", "Status", "Justification"], ["", "", "", "", ""]])
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("7 Verification Deviations")
table = self.doc.add_table(rows=2, cols=1)
for i in range(len(table.rows)):
for j in range(len(table.columns)):
run = table.rows[i].cells[j].paragraphs[0].add_run()
table.style = "Table Grid"
self.fill_table(table,
[["There were no deviations from the verification plan."],
["There were deviations from the verification plan as follows"]])
para = self.doc.add_paragraph()
self.add_line_break(para, 2, Pt(14))
run = para.add_run()
run.bold = True
run.font.size = Pt(14)
run.add_text("8 Conclusion")
para = self.doc.add_paragraph()
run = para.add_run()
run.add_text("The acceptance criteria identified in the Verification plan have been met. All activities "
"supporting this verification activity are complete.")
@staticmethod
def fill_table(table: Table, content: List[List[str]]) -> None:
"""Fill input `table` object with given `content`.
Args:
table: 2-D table object to be filled.
content: 2-D content to fill the table.
Raises:
AssertionError: If the table and content shapes are inconsistent.
"""
assert len(table.rows) == len(content)
assert len(table.columns) == len(content[0])
for i in range(len(table.rows)):
for j in range(len(table.columns)):
table.rows[i].cells[j].paragraphs[0].runs[0].add_text(content[i][j])
@staticmethod
def add_line_break(paragraph: Paragraph, num: int, font_size=None) -> None:
"""Add a number of line breaks into the target `paragraph` object.
Args:
paragraph: Target paragraph.
num: Number of line breaks.
font_size: Font size of the line break.
"""
run = paragraph.add_run()
if font_size:
run.font.size = font_size
for i in range(num):
run.add_break()
m='月',
d='日')
# contract_name = input('请输入合同名称：') #后续从数据库内筛选
#编辑word内容
p = document.add_paragraph()
run_01 = p.add_run('xxxxxx有限公司\n合同款付款说明')
run_01.font.name = u'方正小标宋_GBK'
run_01.element.rPr.rFonts.set(qn('w:eastAsia'), '方正小标宋_GBK')
run_01.font.size = Pt(22) # 字体大小
p.paragraph_format.line_spacing = Pt(28) #行间距设置
p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
#创建表格,并将表格在word页面中居中
table = document.add_table(rows=5, cols=4, style="TableGrid")
table.alignment = WD_TABLE_ALIGNMENT.CENTER
# table.style.font.name = u'方正仿宋_GBK'
textlist = [
'合同/征收单位', '合同/规费名称', '合同/规费总价', '合同编号', '上期累计支付', '本期申请支付', '本期累计支付',
'本期支付说明'
]
n = 0
for i in range(4):
for j in (0, 2):
table.cell(i, j).text = textlist[n]
n += 1
# print(table.cell(i, j).text, n)
table.cell(0, 1).text = wb2_data.cell(3, 3).value #收款单位
table.cell(0, 3).text = wb2_data.cell(1, 3).value #合同名称
table.cell(1,
def main(input_file, output_file):
input_xlsx_file = load_workbook(filename=input_file, read_only=True)
ws = input_xlsx_file['Items']
row_count = ws.max_row + 1
document = Document(output_file)
red_1 = parse_xml(r'<w:shd {} w:fill="FF0000"/>'.format(nsdecls('w')))
red_2 = parse_xml(r'<w:shd {} w:fill="FF0000"/>'.format(nsdecls('w')))
red_3 = parse_xml(r'<w:shd {} w:fill="FF0000"/>'.format(nsdecls('w')))
orange_1 = parse_xml(r'<w:shd {} w:fill="FFA500"/>'.format(nsdecls('w')))
orange_2 = parse_xml(r'<w:shd {} w:fill="FFA500"/>'.format(nsdecls('w')))
orange_3 = parse_xml(r'<w:shd {} w:fill="FFA500"/>'.format(nsdecls('w')))
yellow_1 = parse_xml(r'<w:shd {} w:fill="FFFF00"/>'.format(nsdecls('w')))
yellow_2 = parse_xml(r'<w:shd {} w:fill="FFFF00"/>'.format(nsdecls('w')))
yellow_3 = parse_xml(r'<w:shd {} w:fill="FFFF00"/>'.format(nsdecls('w')))
green_1 = parse_xml(r'<w:shd {} w:fill="00FF00"/>'.format(nsdecls('w')))
green_2 = parse_xml(r'<w:shd {} w:fill="00FF00"/>'.format(nsdecls('w')))
green_3 = parse_xml(r'<w:shd {} w:fill="00FF00"/>'.format(nsdecls('w')))
blue_1 = parse_xml(r'<w:shd {} w:fill="00BFFF"/>'.format(nsdecls('w')))
blue_2 = parse_xml(r'<w:shd {} w:fill="00BFFF"/>'.format(nsdecls('w')))
blue_3 = parse_xml(r'<w:shd {} w:fill="00BFFF"/>'.format(nsdecls('w')))
current_row = 2
document.add_heading('Findings Summary')
if float(ws.cell(row=current_row, column=6).value) >= 9:
document.add_heading('Critical Findings', level=2)
table = document.add_table(rows=1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0]._tc.get_or_add_tcPr().append(red_1)
table.rows[0].cells[1]._tc.get_or_add_tcPr().append(red_2)
table.rows[0].cells[2]._tc.get_or_add_tcPr().append(red_3)
table.cell(0, 0).text = 'No'
table.cell(0, 1).text = 'Findings'
table.cell(0, 2).text = 'Affected IP / URL / Host'
count = 1
for i in range(current_row, row_count):
if float(ws.cell(row=i, column=6).value) < 9:
break
else:
table.add_row()
table.cell(count, 0).text = str(count)
table.cell(count, 1).text = ws.cell(row=i, column=2).value
if len(ws.cell(row=i, column=4).value.split('\r\n')) == 1:
table.cell(count, 2).text = ws.cell(row=i, column=4).value
else:
for ip in ws.cell(row=i, column=4).value.split('\r\n'):
table.cell(count, 2).text += ip+'\n'
table.cell(count, 2).text = table.cell(count, 2).text[:-1]
count += 1
current_row = i
widths = (Inches(0.4), Inches(3.7), Inches(2))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
document.add_page_break()
if float(ws.cell(row=current_row, column=6).value) >= 7 and float(ws.cell(row=current_row, column=6).value) < 9:
document.add_heading('High Findings', level=2)
table = document.add_table(rows=1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0]._tc.get_or_add_tcPr().append(orange_1)
table.rows[0].cells[1]._tc.get_or_add_tcPr().append(orange_2)
table.rows[0].cells[2]._tc.get_or_add_tcPr().append(orange_3)
table.cell(0, 0).text = 'No'
table.cell(0, 1).text = 'Findings'
table.cell(0, 2).text = 'Affected IP / URL / Host'
count = 1
for i in range(current_row, row_count):
if float(ws.cell(row=i, column=6).value) < 7:
break
else:
table.add_row()
table.cell(count, 0).text = str(count)
table.cell(count, 1).text = ws.cell(row=i, column=2).value
if len(ws.cell(row=i, column=4).value.split('\r\n')) == 1:
table.cell(count, 2).text = ws.cell(row=i, column=4).value
else:
for ip in ws.cell(row=i, column=4).value.split('\r\n'):
table.cell(count, 2).text += ip+'\n'
table.cell(count, 2).text = table.cell(count, 2).text[:-1]
count += 1
current_row = i
widths = (Inches(0.4), Inches(3.7), Inches(2))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
document.add_page_break()
if float(ws.cell(row=current_row, column=6).value) >= 4 and float(ws.cell(row=current_row, column=6).value) < 7:
document.add_heading('Medium Findings', level=2)
table = document.add_table(rows=1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0]._tc.get_or_add_tcPr().append(yellow_1)
table.rows[0].cells[1]._tc.get_or_add_tcPr().append(yellow_2)
table.rows[0].cells[2]._tc.get_or_add_tcPr().append(yellow_3)
table.cell(0, 0).text = 'No'
table.cell(0, 1).text = 'Findings'
table.cell(0, 2).text = 'Affected IP / URL / Host'
count = 1
for i in range(current_row, row_count):
if float(ws.cell(row=i, column=6).value) < 4:
break
else:
table.add_row()
table.cell(count, 0).text = str(count)
table.cell(count, 1).text = ws.cell(row=i, column=2).value
if len(ws.cell(row=i, column=4).value.split('\r\n')) == 1:
table.cell(count, 2).text = ws.cell(row=i, column=4).value
else:
for ip in ws.cell(row=i, column=4).value.split('\r\n'):
table.cell(count, 2).text += ip+'\n'
table.cell(count, 2).text = table.cell(count, 2).text[:-1]
count += 1
current_row = i
widths = (Inches(0.4), Inches(3.7), Inches(2))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
document.add_page_break()
if float(ws.cell(row=current_row, column=6).value) > 0 and float(ws.cell(row=current_row, column=6).value) < 4:
document.add_heading('Low Findings', level=2)
table = document.add_table(rows=1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0]._tc.get_or_add_tcPr().append(green_1)
table.rows[0].cells[1]._tc.get_or_add_tcPr().append(green_2)
table.rows[0].cells[2]._tc.get_or_add_tcPr().append(green_3)
table.cell(0, 0).text = 'No'
table.cell(0, 1).text = 'Findings'
table.cell(0, 2).text = 'Affected IP / URL / Host'
count = 1
for i in range(current_row, row_count):
if float(ws.cell(row=i, column=6).value) == 0:
break
else:
table.add_row()
table.cell(count, 0).text = str(count)
table.cell(count, 1).text = ws.cell(row=i, column=2).value
if len(ws.cell(row=i, column=4).value.split('\r\n')) == 1:
table.cell(count, 2).text = ws.cell(row=i, column=4).value
else:
for ip in ws.cell(row=i, column=4).value.split('\r\n'):
table.cell(count, 2).text += ip+'\n'
table.cell(count, 2).text = table.cell(count, 2).text[:-1]
count += 1
current_row = i
widths = (Inches(0.4), Inches(3.7), Inches(2))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
document.add_page_break()
if row_count - current_row > 1:
document.add_heading('Info Findings', level=2)
table = document.add_table(rows=1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0]._tc.get_or_add_tcPr().append(blue_1)
table.rows[0].cells[1]._tc.get_or_add_tcPr().append(blue_2)
table.rows[0].cells[2]._tc.get_or_add_tcPr().append(blue_3)
table.cell(0, 0).text = 'No'
table.cell(0, 1).text = 'Findings'
table.cell(0, 2).text = 'Affected IP / URL / Host'
count = 1
for i in range(current_row, row_count):
table.add_row()
table.cell(count, 0).text = str(count)
table.cell(count, 1).text = ws.cell(row=i, column=2).value
if len(ws.cell(row=i, column=4).value.split('\r\n')) == 1:
table.cell(count, 2).text = ws.cell(row=i, column=4).value
else:
for ip in ws.cell(row=i, column=4).value.split('\r\n'):
table.cell(count, 2).text += ip+'\n'
table.cell(count, 2).text = table.cell(count, 2).text[:-1]
count += 1
widths = (Inches(0.4), Inches(3.7), Inches(2))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
document.add_page_break()
document.save(output_file)
document.add_heading('Finding Details', level=1)
for i in range(2, row_count):
name = ws.cell(row=i, column=2).value
description = ws.cell(row=i, column=3).value
affected_host = ws.cell(row=i, column=4).value
remediation = ws.cell(row=i, column=5).value
cvss_score = 'CVSS Score: '+str(ws.cell(row=i, column=6).value)
severity = ws.cell(row=i, column=7).value
status = ws.cell(row=i, column=8).value
document.add_heading(name, level=2)
document.add_heading('Vulnerability Description', level=3)
for desc in description.split('\n\n'):
document.add_paragraph(desc)
document.add_paragraph()
document.add_heading('Risk Level', level=3)
document.add_paragraph(severity)
document.add_paragraph(cvss_score)
document.add_paragraph()
document.add_heading('Affected IP / URL / Host', level=3)
for hosts in affected_host.split('\r\n'):
document.add_paragraph(hosts)
document.add_paragraph()
document.add_heading('Remediation', level=3)
for solution in remediation.split('\n\n'):
document.add_paragraph(solution)
document.add_paragraph()
document.add_heading('Status', level=3)
document.add_paragraph(status)
document.add_paragraph()
document.add_heading('Proof of Concept', level=3)
num = 9
while isinstance(ws.cell(row=i, column=num).value, str):
plugin_output = ws.cell(row=i, column=num).value
for output in plugin_output.split('\r\n'):
if '\n ' in output:
output = output.replace('\n ', '', 1)
if '\n ' in output:
output = output.replace('\n ', '\n')
document.add_paragraph(output)
num += 1
document.add_page_break()
document.save(output_file)
print('Convert done! Documentation saved to '+output_file)
def writedocxwithrealxls(file_path, filename, orders):
document = Document()
style = document.styles['Normal']
font = style.font
font.name = 'Times New Roman'
font.size = Pt(9)
for section in document.sections:
section.orientation = 1 # 1 is LANDSCAPE, 0 is POTRAIT
section.page_width = Mm(297) # for A4 Paper
section.page_height = Mm(210)
section.left_margin = Inches(0.5)
section.right_margin = Inches(0.5)
section.top_margin = Inches(0.5)
section.bottom_margin = Inches(0.5)
for item in orders:
table = document.add_table(rows=0, cols=16)
table.columns[0].width = Inches(0.45)
table.columns[1].width = Inches(1.25)
table.columns[2].width = Inches(1.65)
table.columns[3].width = Inches(0.50)
table.columns[4].width = Inches(0.55)
table.columns[5].width = Inches(0.65)
table.columns[6].width = Inches(0.75)
table.columns[7].width = Inches(0.05)
table.columns[8].width = Inches(0.05)
table.columns[9].width = Inches(0.45)
table.columns[10].width = Inches(1.25)
table.columns[11].width = Inches(1.65)
table.columns[12].width = Inches(0.50)
table.columns[13].width = Inches(0.55)
table.columns[14].width = Inches(0.65)
table.columns[15].width = Inches(0.75)
#Delivery Notes Title
row_one = table.add_row().cells
row_one[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_one[0].merge(row_one[6])
row_one[0].paragraphs[0].add_run(item[0][0]).bold = True
# Copy
row_one[9].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_one[9].merge(row_one[15])
row_one[9].paragraphs[0].add_run(item[0][0]).bold = True
#Distributor Name, Customer Name, Order Number
row_two = table.add_row().cells
row_two[0].merge(row_two[1])
row_two[0].text = item[1][0]
row_two[0].paragraphs[0].add_run('\n' + item[2][0])
row_two[2].text = item[1][1]
row_two[2].paragraphs[0].add_run('\n' + item[2][1])
row_two[2].paragraphs[0].add_run('\n' + item[3][0])
row_two[2].paragraphs[0].add_run('\n' + item[4][0])
row_two[3].merge(row_two[6])
row_two[3].text = item[1][2]
row_two[3].paragraphs[0].add_run('\n' + item[2][2])
row_two[3].paragraphs[0].add_run('\n' + item[3][1])
row_two[3].paragraphs[0].add_run('\n' + item[4][1])
row_two[3].paragraphs[0].add_run('\n' + item[5][0])
#Copy
row_two[9].merge(row_two[10])
row_two[9].text = item[1][0]
row_two[9].paragraphs[0].add_run('\n' + item[2][0])
row_two[11].text = item[1][1]
row_two[11].paragraphs[0].add_run('\n' + item[2][1])
row_two[11].paragraphs[0].add_run('\n' + item[3][0])
row_two[11].paragraphs[0].add_run('\n' + item[4][0])
row_two[12].merge(row_two[15])
row_two[12].text = item[1][2]
row_two[12].paragraphs[0].add_run('\n' + item[2][2])
row_two[12].paragraphs[0].add_run('\n' + item[3][1])
row_two[12].paragraphs[0].add_run('\n' + item[4][1])
row_two[0].paragraphs[0].add_run('\n' + item[5][0])
#Driver Message
row_seven = table.add_row().cells
row_seven[0].merge(row_seven[6])
row_seven[0].text = item[6][0]
#Copy
row_seven[9].merge(row_seven[15])
row_seven[9].text = item[6][0]
# Product Detail Title
row_nine = table.add_row().cells
row_nine[0].merge(row_nine[6])
row_table1 = row_nine[0].add_table(rows=0, cols=6)
row_table1.style = 'TableGrid'
row_table1.columns[0].width = Inches(0.45)
row_table1.columns[1].width = Inches(2.0)
row_table1.columns[2].width = Inches(0.50)
row_table1.columns[3].width = Inches(0.55)
row_table1.columns[4].width = Inches(0.65)
row_table1.columns[5].width = Inches(0.75)
row_table_cells1 = row_table1.add_row().cells
row_table_cells1[0].paragraphs[0].add_run('Code').bold = True
row_table_cells1[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_table_cells1[1].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_table_cells1[1].paragraphs[0].add_run('Description').bold = True
row_table_cells1[2].paragraphs[0].add_run('UOM').bold = True
row_table_cells1[3].paragraphs[0].add_run('QTY').bold = True
row_table_cells1[4].paragraphs[0].add_run('Price').bold = True
row_table_cells1[5].paragraphs[0].add_run('Amount').bold = True
#Copy
row_nine[9].merge(row_nine[15])
row_table2 = row_nine[9].add_table(rows=0, cols=6)
row_table2.style = 'TableGrid'
row_table2.columns[0].width = Inches(0.45)
row_table2.columns[1].width = Inches(2.0)
row_table2.columns[2].width = Inches(0.50)
row_table2.columns[3].width = Inches(0.55)
row_table2.columns[4].width = Inches(0.65)
row_table2.columns[5].width = Inches(0.75)
row_table_cells2 = row_table2.add_row().cells
row_table_cells2[0].paragraphs[0].add_run('Code').bold = True
row_table_cells2[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_table_cells2[1].alignment = WD_ALIGN_PARAGRAPH.CENTER
row_table_cells2[1].paragraphs[0].add_run('Description').bold = True
row_table_cells2[2].paragraphs[0].add_run('UOM').bold = True
row_table_cells2[3].paragraphs[0].add_run('QTY').bold = True
row_table_cells2[4].paragraphs[0].add_run('Price').bold = True
row_table_cells2[5].paragraphs[0].add_run('Amount').bold = True
for products in item[8]:
#Product Detail
row_product1 = row_table1.add_row().cells
row_product1[0].text = str(products[0]).replace('.0','')
row_product1[1].text = str(products[1])
row_product1[2].text = str(products[2])
row_product1[3].text = str(products[3]).replace('.0','')
row_product1[4].text = str(products[4]).replace('.0','')
row_product1[5].text = str(products[5]).replace('.0','')
#Copy
row_product2 = row_table2.add_row().cells
row_product2[0].text = str(products[0]).replace('.0','')
row_product2[1].text = str(products[1])
row_product2[2].text = str(products[2])
row_product2[3].text = str(products[3]).replace('.0','')
row_product2[4].text = str(products[4]).replace('.0','')
row_product2[5].text = str(products[5]).replace('.0','')
#Total Products
row_eleven1 = row_table1.add_row().cells
row_eleven1[0].text = ''
row_eleven1[1].text = item[9][0]
row_eleven1[2].text = ''
row_eleven1[3].text = str(item[9][1]).replace('.0','')
row_eleven1[4].text = ''
row_eleven1[5].text = str(item[9][2]).replace('.0','')
#Copy
row_eleven2 = row_table2.add_row().cells
row_eleven2[0].text = ''
row_eleven2[1].text = item[9][0]
row_eleven2[2].text = ''
row_eleven2[3].text = str(item[9][1]).replace('.0','')
row_eleven2[4].text = ''
row_eleven2[5].text = str(item[9][2]).replace('.0','')
#Tax Informaiton
row_twelve = table.add_row().cells
row_twelve[0].merge(row_twelve[6])
row_twelve[0].text = item[10][0]
#Copy
row_twelve[9].merge(row_twelve[15])
row_twelve[9].text = item[10][0]
row_thirteen = table.add_row().cells
row_fourteen = table.add_row().cells
row_fifteen = table.add_row().cells
#Buyer, Seller
row_sixteen = table.add_row().cells
row_sixteen[0].merge(row_sixteen[2])
row_sixteen[0].text = item[11][0]
row_sixteen[4].merge(row_sixteen[6])
row_sixteen[4].text = item[11][1]
#Copy
row_sixteen[9].merge(row_sixteen[11])
row_sixteen[9].text = item[11][0]
row_sixteen[13].merge(row_sixteen[15])
row_sixteen[13].text = item[11][1]
document.add_page_break()
document.save('%s%s.docx' % (file_path, filename))
# 반드시 python-docs를 설치할것.
from docx import Document
from docx.shared import Inches
document = Document()
with open('law.txt', 'r', encoding='utf-8') as f:
file_list = list()
for line in f:
if line != '\n':
file_list.append(line[:-1])
table = document.add_table(rows=len(file_list), cols=1)
for index, file_element in enumerate(file_list):
row_cell = table.rows[index].cells
row_cell[0].text = file_element
document.add_page_break()
document.save("law_table.docx")
class ToWord:
document = None
def __init__(self, Host=None, User=None, Password=None, Database=None, Charset=None):
self.turnOjb = DbInfo(
host=Host,
user=User,
password=Password,
database=Database,
charset=Charset
)
self.createDoc()
def createDoc(self):
self.document = Document()
self.document.add_heading('数据库文档', 0)
self.p = self.document.add_paragraph('Yearning')
self.p.add_run('Yearning').bold = True
self.p = self.document.add_paragraph('导出日期: %s' % datetime.now())
self.document.add_picture('libs/logo.png', width=Inches(8))
def exportTables(self, Conn=None, Schemal=None, TableList=None):
'''导出指定的一些表，TableList 为表名称列表 '''
for tableName in TableList:
tabSet = self.turnOjb.getTableName(
ConnName=Conn,
SchemalName=Schemal,
TableName=tableName)
self.document.add_page_break()
self.document.add_heading(
'%s' % [TB[0] for TB in tabSet][0], level=2
)
table = self.document.add_table(rows=1, cols=5)
table.style = 'LightShading-Accent1'
table.rows[0].cells[0].text = '字段名'
table.rows[0].cells[1].text = '类型'
table.rows[0].cells[2].text = '备注'
columnSet = self.turnOjb.getTableInfo(ConnName=Conn, SchemalName=Schemal,
TableName='%s' % [TB[0] for TB in tabSet][0])
for index, column in enumerate(columnSet):
cells = table.add_row().cells
cells[0].text = '%s' % column[4]
cells[1].text = '%s' % column[5]
cells[2].text = '%s' % column[6]
time = datetime.now()
self.document.save('./exportData/%s_%s_Dictionary_%s.docx' % (Conn, Schemal, time))
return time
def exportSchemal(self, Conn=None, Schemal=None):
tabSet = self.turnOjb.getTableName(ConnName=Conn, SchemalName=Schemal)
for tableName in tabSet:
self.document.add_page_break()
self.document.add_heading('%s : %s' % (tableName[0], tableName[1]), level=2)
table = self.document.add_table(rows=1, cols=5)
table.rows[0].cells[0].text = '字段名'
table.rows[0].cells[1].text = '类型'
table.rows[0].cells[2].text = '是否可以为空'
table.rows[0].cells[3].text = '默认值'
table.rows[0].cells[4].text = '备注'
columnSet = self.turnOjb.getTableInfo(
ConnName=Conn,
SchemalName=Schemal,
TableName='%s' % tableName[0])
for index, column in enumerate(columnSet):
cells = table.add_row().cells
cells[0].text = '%s' % column[4]
cells[1].text = '%s' % column[5]
cells[2].text = '%s' % column[6]
cells[3].text = '%s' % column[7]
cells[4].text = '%s' % column[8]
self.document.save('./exportData/%s_%s_数据字典.docx' % (Conn, Schemal))
class classMenu_Spider:
table_Type = ''
doc_name = ''
table_Element = ''
table_TrList = ''
teacher_Name = ''
select_row_1 = ''
select_row_2 = ''
select_row_3 = ''
driver = ''
info = ''
url = 'https://sss.must.edu.tw/RWD_CosInfo/service.asp#teachertab'
def __init__(self) -> None:
chrome_options = webdriver.ChromeOptions()
#chrome_options.add_argument('--headless')
chrome_options.add_argument('--disable-gpu')
if __name__ == "__main__":
if getattr(sys, 'frozen', False):
chrome_driver_path = os.path.join(sys._MEIPASS,
'chromedriver.exe')
print(chrome_driver_path)
self.driver = webdriver.Chrome(
executable_path=chrome_driver_path, options=chrome_options)
else:
self.driver = webdriver.Chrome(options=chrome_options)
try:
self.driver.get(self.url)
self.driver.maximize_window()
self.driver.set_page_load_timeout(10)
except selenium.common.exceptions.WebDriverException or selenium.common.TimeoutException:
sg.popup_error(f'建立網頁驅動器時發生問題！請檢查網路連線與網頁 {self.url} 的狀態！')
os._exit(0)
def creating_Word(self): #建立 Word 檔
self.driver.minimize_window()
self.doc = Document()
style = self.doc.styles['Normal']
font = style.font
font.size = Pt(12)
p = self.doc.add_paragraph(self.info)
p.alignment = WD_ALIGN_PARAGRAPH.CENTER
p.style = self.doc.styles['Normal']
table = self.doc.add_table(rows=1, cols=8)
table.style = 'Light Grid'
table.alignment = WD_TABLE_ALIGNMENT.CENTER
hdr_cells = table.rows[0].cells
hdr_cells[0].text = '節次'
hdr_cells[1].text = '星期一'
hdr_cells[2].text = '星期二'
hdr_cells[3].text = '星期三'
hdr_cells[4].text = '星期四'
hdr_cells[5].text = '星期五'
hdr_cells[6].text = '星期六'
hdr_cells[7].text = '星期日'
hdr_cells[0].width = Cm(.5)
for i in range(1, 8):
hdr_cells[i].width = Cm(3)
#date_heading=[['時段'],['星期一'],['星期二'],['星期三'],['星期四'],['星期五'],['星期六'],['星期日']]
#print(date_heading)
first_row = True
for row in self.table_TrList:
if (first_row):
first_row = False
continue
tdlist = row.find_elements_by_tag_name('td')
sg.one_line_progress_meter(f'匯出成 Word 檔中...',
self.table_TrList.index(row),
len(self.table_TrList) - 1, 'Progress',
self.info)
row_cells = table.add_row().cells
td_count = 0
for td in tdlist:
temp_text = td.text.split("\n")
row_cells[td_count].text = str("\n".join(temp_text))
td_count += 1
#print('\n')
sections = self.doc.sections
for section in sections: #調整邊界
section.top_margin = Cm(1)
section.bottom_margin = Cm(1)
section.left_margin = Cm(1)
section.right_margin = Cm(1)
self.doc.save(self.doc_name)
pass
def classroom_table(self):
try:
wait = ui.WebDriverWait(self.driver, 0.1)
wait.until(lambda driver: driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[5]/div/center/div/table'
))
self.table_Element = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[5]/div/center/div/table'
)
self.table_TrList = self.driver.find_elements_by_tag_name('tr')
self.table_Type = '教室課表'
self.info = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[5]/div/center/div/table/caption'
).text.split("\n")
self.info = "\n".join(self.info)
self.info = self.info.replace('搜尋條件 - ', '')
select_row_1 = Select(self.driver.find_element_by_id('year_r'))
select_row_2 = Select(self.driver.find_element_by_id('Divi_r'))
select_row_3 = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[5]/form/div[3]/div/button/div/div/div'
).text
self.select_row_1 = select_row_1.first_selected_option.text
self.select_row_2 = select_row_2.first_selected_option.text
self.doc_name = (
f'.\{self.select_row_1} - {self.select_row_2} - {select_row_3} - 課表.docx'
)
return True
except selenium.common.exceptions.TimeoutException:
print('尚未找到教室課表元素！')
return False
pass
def class_table(self):
try:
wait = ui.WebDriverWait(self.driver, 0.1)
wait.until(lambda driver: driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[4]/div/center/table[2]')
)
self.table_Element = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[4]/div/center/table[2]')
self.table_TrList = self.driver.find_elements_by_tag_name('tr')
self.table_Type = '班級課表'
self.info = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[4]/div/center/table[2]/caption'
).text.split("\n")
self.info = "\n".join(self.info)
self.info = self.info.replace('搜尋條件 - ', '')
select_row_1 = Select(self.driver.find_element_by_id('year_c'))
select_row_2 = Select(self.driver.find_element_by_id('Divi_c'))
select_row_3 = Select(
self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[4]/form/div[3]/select'
))
select_row_4 = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[4]/form/div[4]/div/button/div/div/div'
).text
self.select_row_1 = select_row_1.first_selected_option.text
self.select_row_2 = select_row_2.first_selected_option.text
self.select_row_3 = select_row_3.first_selected_option.text
self.doc_name = (
f'.\{self.select_row_1} - {self.select_row_2} - {self.select_row_3} - {select_row_4} - 課表.docx'
)
return True
except selenium.common.exceptions.TimeoutException:
print('尚未找到班級課表元素！')
return False
def teacher_table(self):
try:
wait = ui.WebDriverWait(self.driver, 0.1)
wait.until(lambda driver: driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[6]/div/center/div/table'
))
self.table_Element = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[6]/div/center/div/table'
)
self.table_TrList = self.driver.find_elements_by_tag_name('tr')
self.table_Type = '教師課表'
self.info = self.driver.find_element_by_xpath(
'/html/body/div[1]/div/div/div/div/div[6]/div/center/div/div'
).text.split("\n")
self.info = "\n".join(self.info)
self.info = self.info.replace('搜尋條件 - ', '')
select_row_1 = Select(self.driver.find_element_by_id('ysList'))
select_row_2 = Select(self.driver.find_element_by_id('Divi_t'))
self.select_row_3 = self.driver.find_element_by_id(
'itea').get_attribute('value')
self.select_row_1 = select_row_1.first_selected_option.text
self.select_row_2 = select_row_2.first_selected_option.text
self.doc_name = (
f'.\{self.select_row_1} - {self.select_row_2} - {self.select_row_3} - 課表.docx'
)
return True
except selenium.common.exceptions.TimeoutException:
print('尚未找到教師課表元素！')
return False
def check_table(self):
try:
if (self.teacher_table()):
return True
if (self.class_table()):
return True
if (self.classroom_table()):
return True
return False
except selenium.common.exceptions.UnexpectedAlertPresentException:
return False
pass
except selenium.common.exceptions.WebDriverException:
sys.exit()
def showing_data(self):
sg.popup_notify(f'{self.info}',
title='已找到課表！',
display_duration_in_ms=150,
fade_in_duration=150)
pass
def waiting_Input(self):
ready_Window = None
ready_Window = spider_Gui.set_Input_Ready_Window()
running_Window = None
finish_Window = None
while True:
window, event, values = sg.read_all_windows(500)
state = self.check_table()
if window == finish_Window:
if event == WIN_CLOSED:
self.driver.quit()
window.close()
break
if window == ready_Window:
if event == WIN_CLOSED:
self.driver.quit()
window.close()
break
if state:
if (finish_Window != None):
finish_Window.close()
self.showing_data()
ready_Window.close()
running_Window = spider_Gui.set_running_Window(self)
self.creating_Word()
running_Window.close()
self.driver.get(self.url)
self.driver.maximize_window()
docx = (self.doc_name)
os.startfile(docx)
finish_Window = spider_Gui.set_finish_Window(self)
class Document(object):
def save(self, path_to_output):
self.write_questions()
self.save_file(path_to_output)
print("Finished!")
def write_questions(self, questions, path_to_template, groups):
self.doc = Document(path_to_template)
self.questions = questions
self.groups = groups
for question in self.questions:
to_print = "Writing question: %s" % question.name
print(to_print)
if question.parent == 'CompositeQuestion':
pass
#self.write_composite_question(question)
elif question.type == 'TE':
self.write_open_ended(question)
else:
self.write_question(question)
def save_file(self, path_to_output):
self.doc.save(path_to_output)
def write_question(self, question):
paragraph = self.doc.add_paragraph()
self.write_name(question.name, paragraph)
self.write_prompt(question.prompt, paragraph)
self.write_n(question.n, paragraph)
if question.type == 'RO':
self.write_rank(question.responses)
else:
self.write_responses(question.responses)
new = self.doc.add_paragraph("") # space between questions
self.doc.add_paragraph("") # space between questions
def write_composite_question(self, question):
paragraph = self.doc.add_paragraph()
self.write_name(question.name, paragraph)
self.write_prompt(question.prompt, paragraph)
if question.type == 'CompositeMatrix':
self.write_matrix(question.questions)
elif question.type == 'CompositeConstantSum':
self.write_allocate(question.questions)
else:
self.write_binary(question.questions)
new = self.doc.add_paragraph("") # space between questions
self.doc.add_paragraph("")
def write_open_ended(self, question):
paragraph = self.doc.add_paragraph(
) # each question starts a new paragraph
self.write_name(question.name, paragraph)
self.write_prompt(question.prompt, paragraph)
self.write_n(question.n, paragraph)
paragraph.add_run(' (OPEN-ENDED RESPONSES VERBATIM IN APPENDIX)')
new = self.doc.add_paragraph("") # space between questions
self.doc.add_paragraph("")
def write_name(self, name, paragraph):
paragraph.add_run(name + ".")
def write_prompt(self, prompt, paragraph):
paragraph_format = paragraph.paragraph_format
paragraph_format.keep_together = True # question prompt will all be fit in one page
paragraph_format.left_indent = Inches(1)
paragraph.add_run("\t" + prompt)
paragraph_format.first_line_indent = Inches(
-1) # hanging indent if necessary
def write_n(self, n, paragraph):
if n != 0:
paragraph.add_run(" (n = " + str(n) + ")")
def write_responses(self, responses):
if len(self.groups) > 0:
self.write_trended_responses(responses)
else:
table = self.doc.add_table(rows=1, cols=5)
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[2])
response_cells[1].text = response.response
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(nsdecls('w')))
response_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for group, frequency in response.frequencies.items():
if frequency.stat == 'percent':
freq = self.freqs_percent(frequency.result, first_row)
else:
freq = str(frequency.result)
response_cells[3].text = freq
first_row = False
def write_trended_responses(self, responses):
headers = self.max_groups(responses)
table = self.doc.add_table(rows=1, cols=len(headers) + 4)
titles_row = table.add_row().cells
titles_row[1].merge(titles_row[2])
headers_index = 0
while headers_index < len(headers):
header_text = "Total %s" % headers[headers_index]
titles_row[headers_index + 4].text = header_text
headers_index += 1
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[3])
response_cells[1].text = response.response
freq_col = 4
if not response.frequencies:
shading_elm = parse_xml(r'<w:shd {} w:fill="FFF206"/>'.format(
nsdecls('w')))
response_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for header in headers:
if response.frequencies.frequencies.get(header) is not None:
freq = response.frequencies.frequencies.get(header)
if freq.stat == 'percent':
text = self.freqs_percent(freq.result, first_row)
else:
text = str(freq.result)
response_cells[freq_col].text = text
first_row = False
freq_col += 1
def write_rank(self, responses):
if len(self.groups) > 0:
self.write_trended_rank(responses)
else:
table = self.doc.add_table(rows=1, cols=5)
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[2])
response_cells[1].text = response.response
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(nsdecls('w')))
response_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for group, average in response.frequencies.items():
response_cells[3].text = self.avg_float(average, first_row)
first_row = False
def write_trended_rank(self, responses):
headers = self.max_groups(responses)
table = self.doc.add_table(rows=1, cols=len(headers) + 4)
titles_row = table.add_row().cells
titles_row[1].merge(titles_row[2])
headers_index = 0
while headers_index < len(headers):
header_text = "Total %s" % headers[headers_index]
titles_row[headers_index + 4].text = header_text
headers_index += 1
first_row = True
for response in responses:
response_cells = table.add_row().cells
response_cells[1].merge(response_cells[3])
response_cells[1].text = response.response
freq_col = 4
if not response.frequencies:
shading_elm = parse_xml(r'<w:shd {} w:fill="FFF206"/>'.format(
nsdecls('w')))
response_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for header in headers:
if response.frequencies.get(header) is not None:
avg = response.frequencies.get(header)
text = self.avg_float(avg, first_row)
response_cells[freq_col].text = text
first_row = False
freq_col += 1
def write_binary(self, sub_questions):
if len(self.groups) > 0:
self.write_trended_binary(sub_questions)
else:
table = self.doc.add_table(rows=1, cols=5)
first_row = True
for sub_question in sub_questions:
cells = table.add_row().cells
cells[1].merge(cells[2])
cells[1].text = "%s (n=%s)" % (sub_question.prompt,
sub_question.n)
response = next((response
for response in sub_question.responses
if response.code == '1'), None)
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(nsdecls('w')))
cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for group, frequency in response.frequencies.items():
cells[3].text = self.freqs_percent(frequency, first_row)
first_row = False
def write_trended_binary(self, sub_questions):
headers = self.max_groups_subquestions(sub_questions)
table = self.doc.add_table(rows=1, cols=len(headers) + 4)
titles_row = table.add_row().cells
titles_row[1].merge(titles_row[2])
headers_index = 0
while headers_index < len(headers):
header_text = "Total %s" % headers[headers_index]
titles_row[headers_index + 4].text = header_text
headers_index += 1
first_row = True
for sub_question in sub_questions:
response = next((response for response in sub_question.responses
if response.code == '1'), None)
region_cells = table.add_row().cells
region_cells[1].merge(region_cells[3])
region_cells[1].text = "%s (n=%s)" % (sub_question.prompt,
sub_question.n)
freq_col = 4
if not response.frequencies:
shading_elm = parse_xml(r'<w:shd {} w:fill="FFF206"/>'.format(
nsdecls('w')))
region_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for header in headers:
if response.frequencies.get(header) is not None:
freq = response.frequencies.get(header)
text = self.freqs_percent(freq, first_row)
region_cells[freq_col].text = text
else:
if first_row is True:
region_cells[freq_col].text = "--%"
else:
region_cells[freq_col].text = "--"
first_row = False
freq_col += 1
def write_allocate(self, sub_questions):
if len(self.groups) > 0:
self.write_trended_allocate(sub_questions)
else:
table = self.doc.add_table(rows=1, cols=5)
first_row = True
for sub_question in sub_questions:
cells = table.add_row().cells
cells[1].merge(cells[2])
cells[1].text = "%s (n=%s)" % (sub_question.prompt,
sub_question.n)
for response in sub_question.responses:
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(
nsdecls('w')))
cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for group, frequency in response.frequencies.items():
cells[3].text = self.avgs_percent(frequency, first_row)
first_row = False
def write_trended_allocate(self, sub_questions):
headers = self.max_groups_subquestions(sub_questions)
table = self.doc.add_table(rows=1, cols=len(headers) + 4)
titles_row = table.add_row().cells
titles_row[1].merge(titles_row[2])
headers_index = 0
while headers_index < len(headers):
header_text = "Total %s" % headers[headers_index]
titles_row[headers_index + 4].text = header_text
headers_index += 1
first_row = True
for sub_question in sub_questions:
for response in sub_question.responses:
region_cells = table.add_row().cells
region_cells[1].merge(region_cells[3])
region_cells[1].text = "%s (n=%s)" % (sub_question.prompt,
sub_question.n)
freq_col = 4
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(nsdecls('w')))
region_cells[1]._tc.get_or_add_tcPr().append(shading_elm)
for header in headers:
if response.frequencies.get(header) is not None:
freq = response.frequencies.get(header)
text = self.avgs_percent(freq, first_row)
region_cells[freq_col].text = text
else:
if first_row is True:
region_cells[freq_col].text = "$--"
else:
region_cells[freq_col].text = "--"
first_row = False
freq_col += 1
def write_matrix(self, sub_questions):
if len(self.groups) > 0:
self.write_trended_matrix(sub_questions)
else:
table = self.doc.add_table(rows=1, cols=0)
table.add_column(width=Inches(1))
table.add_column(width=Inches(1))
first_row = True
header_cells = table.add_row().cells
for sub_question in sub_questions:
if first_row == True:
for response in sub_question.responses:
response_cells = table.add_column(
width=Inches(1)).cells
response_cells[1].text = response.response
question_cells = table.add_row().cells
question_cells[1].text = "%s (n=%s)" % (sub_question.prompt,
sub_question.n)
index = 2
for response in sub_question.responses:
if response.has_frequency is True:
for group, frequency in response.frequencies.items():
question_cells[index].text = self.freqs_percent(
frequency, first_row)
else:
if not response.frequencies:
shading_elm = parse_xml(
r'<w:shd {} w:fill="FFF206"/>'.format(
nsdecls('w')))
question_cells[1]._tc.get_or_add_tcPr().append(
shading_elm)
if first_row is True:
question_cells[index].text = "--%"
else:
question_cells[index].text = "--"
first_row = False
index += 1
def write_trended_matrix(self, sub_questions):
self.doc.add_paragraph("") # space between questions
for sub_question in sub_questions:
paragraph = self.doc.add_paragraph(
) # each question starts a new paragraph
self.write_name(sub_question.name, paragraph)
self.write_prompt(sub_question.prompt, paragraph)
self.write_n(sub_question.n, paragraph)
self.write_responses(sub_question.responses, sub_question.stat)
self.doc.add_paragraph("") # space between questions
def max_groups(self, responses):
groups_used = []
for response in responses:
for group in self.groups:
if response.frequencies.frequencies.get(group) is not None:
if group not in groups_used:
groups_used.append(group)
return groups_used
def max_groups_subquestions(self, sub_questions):
groups_used = []
for question in sub_questions:
for response in question.responses:
for group in self.groups:
if response.frequencies.get(group) is not None:
if group not in groups_used:
groups_used.append(group)
return groups_used
def avg_float(self, average, is_first):
if average == "NA":
return average
average = float(average)
if average >= 0 and average < 1:
result = '<1'
else:
result = average
if is_first is True:
result = str(result)
return str(result)
def freqs_percent(self, freq, is_first):
if freq == 'NA':
return freq
result = 0
if float(freq) >= 1.0:
result = int(freq) * 100
else:
percent = float(freq)
percent = percent * 100
if percent >= 0 and percent < 1:
result = "<1"
else:
result = int(round(percent))
if is_first:
result = str(result) + "%"
return str(result)
def avgs_percent(self, average, is_first):
if average == "NA":
return average
average = float(average)
if average >= 0 and average < 1:
result = '<1'
else:
result = int(round(average))
if is_first is True:
result = "$" + str(result)
return str(result)
table = None
para = None
for line in sys.stdin:
line.lstrip()
if line[0] == '*':
n = 0
while line[0] == '*':
n += 1
line = line[1:]
doc.add_heading(line, level=n)
doc.add_paragraph('')
table = None
elif line[0] == '|':
cn = line.count('|') - 1
if table == None:
table = doc.add_table(rows=1, cols=cn, style='Light Grid')
# table.alignment = WD_TABLE_ALIGNMENT.LEFT
table.allow_auto_fit = False
columns = cn
row = 0
else:
if cn != columns:
print(line, 'wrong # of columns')
table.add_row()
for c, f in enumerate(line.split('|')[1:-1]):
table.cell(row, c).text = f
row += 1
elif line == '\n':
table = None
if para != None:
doc.add_paragraph(para)
def doc5(self):
document = Document()
section = document.sections[-1]
section.left_margin = Cm(1)
section.right_margin = Cm(1)
obj_styles = document.styles
obj_charstyle = obj_styles.add_style('CommentsStyle',
WD_STYLE_TYPE.CHARACTER)
obj_font = obj_charstyle.font
obj_font.size = Pt(8)
obj_font.name = 'Times New Roman'
obj_charstyle = obj_styles.add_style('CommentsStyle2',
WD_STYLE_TYPE.CHARACTER)
obj_font = obj_charstyle.font
obj_font.size = Pt(10)
obj_font.name = 'Times New Roman'
header = document.sections[0].header
paragraph = header.add_paragraph()
paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
paragraph.add_run(self.env.user.company_id.name).bold = True
paragraph = header.add_paragraph()
paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.RIGHT
paragraph.add_run('Registration No: ')
paragraph.add_run(self.id_number or ' ').bold = True
# paragraph = header.add_paragraph('Transcript', style='Intense Quote')
# paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
header_table = header.add_table(1, 3, width=500)
header_table.autofit = False
A = header_table.cell(0, 0)
pt = A.paragraphs[0]
t = pt.text = ''
pt.add_run("Student's Name: ", style='CommentsStyle').bold = True
pt.add_run(self.name, style='CommentsStyle').bold = False
pt.paragraph_format.space_before = Pt(0)
pt.paragraph_format.space_after = Pt(0)
paragraph = A.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run("Program: ", style='CommentsStyle').bold = True
paragraph.add_run(self.program_id.name,
style='CommentsStyle').bold = False
paragraph = A.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run("Plan: ", style='CommentsStyle').bold = True
paragraph.add_run(self.program_id.name + ' Major',
style='CommentsStyle').bold = False
B = header_table.cell(0, 2)
pt = B.paragraphs[0]
t = pt.text = ''
pt.add_run("Father's Name: ", style='CommentsStyle').bold = True
pt.add_run(self.father_name, style='CommentsStyle').bold = False
pt.paragraph_format.space_before = Pt(0)
pt.paragraph_format.space_after = Pt(0)
cells = header_table.add_row().cells
cells[0]._element.clear_content()
table = cells[0].add_table(rows=0, cols=4)
table.style = 'Table Grid'
table.autofit = False
set_table_width(table, [1.8, 5.3, 1.0, 1.0])
add_row(table, ['Code', 'Title', 'CH', 'Grd'])
cells[2]._element.clear_content()
table = cells[2].add_table(rows=0, cols=4)
table.alignment = WD_TABLE_ALIGNMENT.RIGHT
table.style = 'Table Grid'
table.autofit = False
set_table_width(table, [1.8, 5.3, 1.0, 1.0])
add_row(table, ['Code', 'Title', 'CH', 'Grd'])
set_table_width(header_table, [9.4, 1.0, 9.4])
footer = document.sections[0].footer
footer.is_linked_to_previous = False
pt = footer.paragraphs[0]
pt.add_run(
'"The Official Transcript carries the embossed stamp of the University"',
style='CommentsStyle').bold = True
pt.paragraph_format.space_before = Pt(0)
pt.paragraph_format.space_after = Pt(0)
paragraph = footer.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run(
'Transcript Prepared By: ---------------------------------------------',
style='CommentsStyle').bold = False
paragraph = footer.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run(
'Transcript Checked By: ---------------------------------------------',
style='CommentsStyle').bold = False
paragraph = footer.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run('Date of Issue: ' + str(fields.Date.today()),
style='CommentsStyle').bold = False
paragraph = footer.add_paragraph()
paragraph.paragraph_format.space_before = Pt(0)
paragraph.paragraph_format.space_after = Pt(0)
paragraph.add_run(
'"Errors and Omissions are subject to Subsequent rectification"',
style='CommentsStyle').bold = True
paragraph.add_run("\t\t\tController of Examinations",
style='CommentsStyle2').bold = True
big_table = document.add_table(0, 1)
big_table.autofit = False
set_table_width(big_table, [9.5])
for semester in self.semester_ids:
row = big_table.add_row()
tag = row._tr
child = OxmlElement('w:cantSplit') # Create arbitrary tag
tag.append(child)
cells = row.cells
cells[0]._element.clear_content()
# label = cells[0].add_paragraph()
# label.paragraph_format.keep_with_next = True
# label.paragraph_format.space_before = Pt(0)
# label.paragraph_format.space_after = Pt(0)
table = cells[0].add_table(rows=1, cols=4)
table.style = 'Table Grid'
a = table.cell(0, 0)
b = table.cell(0, 3)
A = a.merge(b)
A.text = semester.academic_semester_id.name
A.paragraphs[
0].paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
for subject in semester.student_subject_ids:
add_row(table, [
subject.subject_id.subject_id.code,
subject.subject_id.subject_id.name,
subject.subject_id.weightage, subject.grade
])
set_table_width(table, [1.8, 5.5, 1.0, 1.0])
transcript_border(table)
row = table.add_row()
row_border(row, b=False)
a = table.cell(len(table.rows) - 1, 0)
b = table.cell(len(table.rows) - 1, 3)
A = a.merge(b)
tb_cell_run = A.paragraphs[0].add_run()
tb_cell_run.add_text("\tSCH: " + str(semester.credits))
tb_cell_run.add_text("\t\tSGP: " +
'{0:,.2f}'.format(semester.grade_points))
tb_cell_run.add_text("\tSGPA: " + '{0:,.2f}'.format(semester.sgpa))
tb_cell_run.font.size = Pt(8)
row = table.add_row()
# row_border(table.rows[len(table.rows)-1],t=False)
row_border(row, t=False)
a = table.cell(len(table.rows) - 1, 0)
b = table.cell(len(table.rows) - 1, 3)
A = a.merge(b)
tb_cell_run = A.paragraphs[0].add_run()
tb_cell_run.add_text("\tCCH: " + str(semester.cch))
tb_cell_run.add_text("\t\tCGP: " + '{0:,.2f}'.format(semester.cgp))
tb_cell_run.add_text("\tCGPA: " + '{0:,.2f}'.format(semester.cgpa))
tb_cell_run.font.size = Pt(8)
for row in table.rows:
row.height = Cm(0.4)
label = cells[0].paragraphs[0]
label.paragraph_format.keep_with_next = True
label.paragraph_format.space_before = Pt(0)
label.paragraph_format.space_after = Pt(0)
sectPr = document.sections[-1]._sectPr
cols = sectPr.xpath('./w:cols')[0]
cols.set(qn('w:num'), '2')
preventDocumentBreak(document)
# document.save('demo.docx')
temporary_files = []
doc_report_fd, doc_report_path = tempfile.mkstemp(suffix='.docx',
prefix='report.tmp.')
os.close(doc_report_fd)
temporary_files.append(doc_report_path)
pdf_report_fd, pdf_report_path = tempfile.mkstemp(suffix='.pdf',
prefix='report.tmp.')
os.close(pdf_report_fd)
temporary_files.append(pdf_report_path)
document.save(doc_report_path)
#send to server
headers = {
'Content-Type': 'multipart/form-data',
}
response = requests.put(endpoint,
files={'file': open(doc_report_path, 'rb')})
if response.status_code == 200:
print(response.text)
response = requests.get(endpoint2)
print(response.status_code)
if response.status_code == 200:
with open(pdf_report_path,
'wb') as out_file: # change file name for PNG images
out_file.write(response.content)
#
# try:
# # wkhtmltopdf = [_get_wkhtmltopdf_bin()] + command_args + files_command_args + paths + [pdf_report_path]
# wkhtmltopdf = ["/usr/bin/unoconv", "-f", "pdf", "-o", pdf_report_path, doc_report_path]
# process = subprocess.Popen(wkhtmltopdf, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
# out, err = process.communicate()
# except:
# raise
with open(pdf_report_path, 'rb') as pdf_document:
pdf_content = pdf_document.read()
# Manual cleanup of the temporary files
for temporary_file in temporary_files:
try:
os.unlink(temporary_file)
except (OSError, IOError):
_logger.error('Error when trying to remove file %s' %
temporary_file)
return pdf_content
border_elm_2_str = '''<w:tcBorders {}>
<w:top w:val="single" w:color="BBBBBB" w:space="0" w:sz="6"/>
<w:left w:val="single" w:color="BBBBBB" w:space="0" w:sz="6"/>
<w:bottom w:val="single" w:color="BBBBBB" w:space="0" w:sz="6"/>
<w:right w:val="single" w:color="BBBBBB" w:space="0" w:sz="6"/>
</w:tcBorders>'''.format(nsdecls('w'))
cell_spacing_1_str = '''
<w:tblCellSpacing {} w:w="15" w:type="dxa"/>
'''.format(nsdecls('w'))
year = 2019
for month in range(1, 13):
table = document.add_table(rows=12, cols=7)
# print(table._tbl)
cell_spacing_1 = parse_xml(cell_spacing_1_str)
table._tbl.tblPr.append(cell_spacing_1)
# header
hdr_cells = table.rows[0].cells
hdr_cells[0].merge(hdr_cells[6])
hdr_cells[0].text = '%d-%d' % (year, month)
# Monday to Sunday
week_cells = table.rows[1].cells
for i, m in enumerate(['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']):
week_cells[i].text = m.decode('utf-8')
# Others
def printing(quarter_output):
'''function that compiles the summary sheet'''
master_list = list_of_masters_all[0:4]
milestone_master = all_milestone_data_bulk(list_of_masters_all[0].projects, list_of_masters_all[0])
for project_name in list_of_masters_all[0].projects:
doc = Document()
print(project_name)
heading = str(project_name)
name = str(project_name)
# TODO: change heading font size
# todo be able to change text size and font
intro = doc.add_heading(str(heading), 0)
intro.alignment = 1
intro.bold = True
y = doc.add_paragraph()
a = list_of_masters_all[0].data[project_name]['Senior Responsible Owner (SRO)']
if a == None:
a = 'TBC'
else:
a = a
b = list_of_masters_all[0].data[project_name]['SRO Phone No.']
if b == None:
b = 'TBC'
else:
b = b
y.add_run('SRO name: ' + str(a) + ', Tele: ' + str(b))
y = doc.add_paragraph()
a = list_of_masters_all[0].data[project_name]['Project Director (PD)']
if a == None:
a = 'TBC'
else:
a = a
b = list_of_masters_all[0].data[project_name]['PD Phone No.']
if b == None:
b = 'TBC'
else:
b = b
y.add_run('PD name: ' + str(a) + ', Tele: ' + str(b))
'''Start of table with DCA confidence ratings'''
table1 = doc.add_table(rows=1, cols=5)
table1.cell(0, 0).width = Cm(7)
'''quarter information in top row of table is here'''
for i, quarter in enumerate(quarter_list):
table1.cell(0, i+1).text = quarter
# '''setting row height - partially working'''
# # todo understand row height better
# row = table1.rows[0]
# tr = row._tr
# trPr = tr.get_or_add_trPr()
# trHeight = OxmlElement('w:trHeight')
# trHeight.set(qn('w:val'), str(200))
# trHeight.set(qn('w:hRule'), 'atLeast')
# trPr.append(trHeight)
SRO_conf_table_list = ['SRO DCA', 'Finance DCA', 'Benefits DCA', 'Resourcing DCA', 'Schedule DCA']
SRO_conf_key_list = ['Departmental DCA', 'SRO Finance confidence', 'SRO Benefits RAG', 'Overall Resource DCA - Now',
'SRO Schedule Confidence']
'''All SRO RAG rating placed in table'''
for i in range(0, len(master_list)+1):
table = doc.add_table(rows=1, cols=5)
table.cell(0, 0).width = Cm(7)
table.cell(0, 0).text = SRO_conf_table_list[i]
for x, master in enumerate(master_list):
try:
rating = convert_rag_text(master.data[project_name][SRO_conf_key_list[i]])
table.cell(0, x + 1).text = rating
cell_colouring(table.cell(0, x + 1), rating)
except (KeyError, TypeError):
table.cell(0, x + 1).text = 'N/A'
'''DCA Narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Overall DCA Narrative'
y.add_run(str(heading)).bold = True
dca_a = list_of_masters_all[0].data[project_name]['Departmental DCA Narrative']
try:
dca_b = list_of_masters_all[1].data[project_name]['Departmental DCA Narrative']
except KeyError:
dca_b = dca_a
'''comparing text options'''
# compare_text_showall(dca_a, dca_b, doc)
compare_text_newandold(dca_a, dca_b, doc)
'''Finance section'''
y = doc.add_paragraph()
heading = 'Financial information'
y.add_run(str(heading)).bold = True
'''Financial Meta data'''
table1 = doc.add_table(rows=2, cols=5)
table1.cell(0, 0).text = 'Forecast Whole Life Cost (£m):'
table1.cell(0, 1).text = 'Percentage Spent:'
table1.cell(0, 2).text = 'Source of Funding:'
table1.cell(0, 3).text = 'Nominal or Real figures:'
table1.cell(0, 4).text = 'Full profile reported:'
wlc = round(list_of_masters_all[0].data[project_name]['Total Forecast'], 1)
table1.cell(1, 0).text = str(wlc)
# str(list_of_masters_all[0].data[project_name]['Total Forecast'])
#a = list_of_masters_all[0].data[project_name]['Total Forecast']
b = list_of_masters_all[0].data[project_name]['Pre 19-20 RDEL Forecast Total']
if b == None:
b = 0
c = list_of_masters_all[0].data[project_name]['Pre 19-20 CDEL Forecast Total']
if c == None:
c = 0
d = list_of_masters_all[0].data[project_name]['Pre 19-20 Forecast Non-Gov']
if d == None:
d = 0
e = b + c + d
try:
c = round(e / wlc * 100, 1)
except (ZeroDivisionError, TypeError):
c = 0
table1.cell(1, 1).text = str(c) + '%'
a = str(list_of_masters_all[0].data[project_name]['Source of Finance'])
b = list_of_masters_all[0].data[project_name]['Other Finance type Description']
if b == None:
table1.cell(1, 2).text = a
else:
table1.cell(1, 2).text = a + ' ' + str(b)
table1.cell(1, 3).text = str(list_of_masters_all[0].data[project_name]['Real or Nominal - Actual/Forecast'])
table1.cell(1, 4).text = ''
'''Finance DCA Narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Finance Narrative'
y.add_run(str(heading)).bold = True
#TODO further testing on code down to 308. current hard code solution not ideal, plus not sure working properly yet
gmpp_narrative_keys = ['Project Costs Narrative', 'Cost comparison with last quarters cost narrative',
'Cost comparison within this quarters cost narrative']
fin_text_1 = combine_narrtives(project_name, list_of_masters_all[0], gmpp_narrative_keys)
try:
fin_text_2 = combine_narrtives(project_name, list_of_masters_all[1], gmpp_narrative_keys)
except KeyError:
fin_text_2 = fin_text_1
# if narrative == 'NoneNoneNone':
# fin_text = combine_narrtives(name, dictionary_1, bicc_narrative_keys)
# else:
# fin_text = narrative
compare_text_newandold(fin_text_1, fin_text_2, doc)
#compare_text_showall()
'''financial chart heading'''
y = doc.add_paragraph()
heading = 'Financial Analysis - Cost Profile'
y.add_run(str(heading)).bold = True
y = doc.add_paragraph()
y.add_run('{insert chart}')
'''milestone section'''
y = doc.add_paragraph()
heading = 'Planning information'
y.add_run(str(heading)).bold = True
'''Milestone Meta data'''
table1 = doc.add_table(rows=2, cols=4)
table1.cell(0, 0).text = 'Project Start Date:'
table1.cell(0, 1).text = 'Latest Approved Business Case:'
table1.cell(0, 2).text = 'Start of Operations:'
table1.cell(0, 3).text = 'Project End Date:'
key_dates = milestone_master[project_name]
#c = key_dates['Start of Project']
try:
c = tuple(key_dates['Start of Project'])[0]
c = datetime.datetime.strptime(c.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
except (KeyError, AttributeError):
c = 'Not reported'
table1.cell(1, 0).text = str(c)
table1.cell(1, 1).text = str(list_of_masters_all[0].data[project_name]['IPDC approval point'])
try:
a = tuple(key_dates['Start of Operation'])[0]
a = datetime.datetime.strptime(a.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
table1.cell(1, 2).text = str(a)
except (KeyError, AttributeError):
table1.cell(1, 2).text = 'Not reported'
#b = key_dates['Project End Date']
try:
b = tuple(key_dates['Project End Date'])[0]
b = datetime.datetime.strptime(b.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
except (KeyError, AttributeError):
b = 'Not reported'
table1.cell(1, 3).text = str(b)
# TODO: workout generally styling options for doc, paragraphs and tables
'''milestone narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Milestone Narrative'
y.add_run(str(heading)).bold = True
mile_dca_a = list_of_masters_all[0].data[project_name]['Milestone Commentary']
if mile_dca_a == None:
mile_dca_a = 'None'
try:
mile_dca_b = list_of_masters_all[1].data[project_name]['Milestone Commentary']
if mile_dca_b == None:
mile_dca_b = 'None'
except KeyError:
mile_dca_b = mile_dca_a
# compare_text_showall()
compare_text_newandold(mile_dca_a, mile_dca_b, doc)
'''milestone chart heading'''
y = doc.add_paragraph()
heading = 'Project reported high-level milestones and schedule changes'
y.add_run(str(heading)).bold = True
y = doc.add_paragraph()
some_text = 'The below table presents all project reported remaining high-level milestones, with six months grace ' \
'from close of the current quarter. Milestones are sorted in chronological order. Changes in milestones' \
' dates in comparison to last quarter and baseline have been calculated and are provided.'
y.add_run(str(some_text)).italic = True
y = doc.add_paragraph()
y.add_run('{insert chart}')
doc.save(root_path/'output/{}_summary.docx'.format(project_name + quarter_output))
def showDialog2(self):
self.all_form = self.all_form+self.text_lst+self.text_lst_2
document = Document()
p = document.add_paragraph('ДОГОВОР купли – продажи квартиры № %(10)s '''% {"10":self.all_form[10]})
p = document.add_paragraph('г. Санкт-Петербург «__» ____________ 201__ года')
p = document.add_paragraph('Общество с ограниченной ответственностью «Карат», являющееся юридическим лицом по законодательству'
' Российской Федерации, зарегистрированное в МИ МНС № 11 по Санкт-Петербургу Свидетельством '
'о государственной регистрации ЮЛ серия 78 № 004629954 от 31.10.2003 года, ОГРН 1037869011421, '
'ИНН 7842003710, КПП 780601001, местонахождение: 195112, г. Санкт-Петербург, Малоохтинский пр., д. 61,'
' литера А, пом. 61, в лице генерального директора Осипова Дмитрия Вячеславовича, действующего на'
' основании Устава, именуемое далее «Продавец», и')
p = document.add_paragraph('Гражданин Российской Федерации %(2)s %(3)s %(4)s %(19)s года рождения, место рождения:%(5)s, пол: %(0)s, паспорт %(6)s %(7)s выдан %(20)s г. '
'%(8)s, зарегистрированный по адресу (адрес для уведомлений): %(9)s, именуемый далее «Покупатель», с другой стороны, '
'заключили настоящий Договор о нижеследующем'% {"0":self.all_form[0], "2":self.all_form[2],"3":self.all_form[3],
"4":self.all_form[4],"5":self.all_form[5],"6":self.all_form[6],
"7":self.all_form[7],"8":self.all_form[8],"9":self.all_form[9],
"19":self.all_form[19],"20":self.all_form[20]})
p = document.add_paragraph('Продавец продал, а Покупатель купил %(1)sкомнатную квартиру, находящуюся по адресу: гор. Санкт-Петербург, г. Сестрорецк,'
'Приморское шоссе, д. 293, кв. %(10)s'%{"1":self.all_form[1],"10":self.all_form[10]},style='ListNumber')
p = document.add_paragraph('Указанная квартира (кадастровый номер: %(16)s) расположена на %(11)s этаже жилом 10-17-ти этажном доме'
'со встроенными помещениями 2014 года постройки. Общая площадь квартиры составляет – %(13)s кв.м.;'
'из нее жилая площадь – %(14)s кв.м.'%{"16":self.all_form[16], "11":self.all_form[11],"13":self.all_form[13],
"14":self.all_form[14]},style='ListNumber')
p = document.add_paragraph('Отчуждаемая квартира принадлежит Обществу с ограниченной ответственностью «Карат» на праве собственности,'
'о чем в Едином государственном реестре прав на недвижимое имущество и сделок с ним'
'%(21)s года сделана запись регистрации № %(17)s.'% {"17":self.all_form[17], "21":self.all_form[21]},style='ListNumber')
p = document.add_paragraph('Указанная квартира по договоренности сторон продается и покупается за сумму %(18)s рублей 00 копеек. Взаиморасчеты произведены'
'в полном объеме между Сторонами до подписания настоящего Договора. Стороны финансовых и иных претензий друг к другу не имеют.'%
{"18":self.all_form[18]},style='ListNumber')
p = document.add_page_break()
p = document.add_paragraph('АКТ ПРИЕМА-ПЕРЕДАЧИ КВАРТИРЫ № %(10)s '''% {"10":self.all_form[10]})
p = document.add_paragraph('г. Санкт-Петербург «__» ____________ 201__ года')
p = document.add_paragraph('Общество с ограниченной ответственностью «Карат», являющееся юридическим лицом по законодательству'
' Российской Федерации, зарегистрированное в МИ МНС № 11 по Санкт-Петербургу Свидетельством '
'о государственной регистрации ЮЛ серия 78 № 004629954 от 31.10.2003 года, ОГРН 1037869011421, '
'ИНН 7842003710, КПП 780601001, местонахождение: 195112, г. Санкт-Петербург, Малоохтинский пр., д. 61,'
' литера А, пом. 61, в лице генерального директора Осипова Дмитрия Вячеславовича, действующего на'
' основании Устава, именуемое далее «Продавец», и')
p = document.add_paragraph('Гражданин Российской Федерации %(2)s %(3)s %(4)s %(19)s года рождения, место рождения:%(5)s, пол: %(0)s, паспорт %(6)s %(7)s выдан %(20)s г. '
'%(8)s, зарегистрированный по адресу (адрес для уведомлений): %(9)s, именуемый далее «Покупатель», с другой стороны, '
'другой стороны, составили настоящий акт приема-передачи квартиры № %(10)s в соответствии с договором купли-продажи '
'квартиры № %(10)s от «__» ______ 201__ года.'% {"0":self.all_form[0], "2":self.all_form[2],"3":self.all_form[3],
"4":self.all_form[4],"5":self.all_form[5],"6":self.all_form[6],
"7":self.all_form[7],"8":self.all_form[8],"9":self.all_form[9],
"10":self.all_form[10],"19":self.all_form[19],"20":self.all_form[20]})
p = document.add_paragraph('Квартира передается в том виде и состоянии, в котором она находится на момент передачи ее Покупателю. С момента подписания'
'настоящего акта сторонами Продавец передает Покупателю, а Покупатель принимает квартиру, расположенную по адресу: '
'г. Санкт-Петербург, г. Сестрорецк, Приморское шоссе, д. 293, следующих характеристик:',style='ListNumber')
table = document.add_table(rows=2, cols=6)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Номер квартиры'
hdr_cells[1].text = 'Количество комнат'
hdr_cells[2].text = 'Этаж'
hdr_cells[3].text = 'Общая жилая площадь по данным ПИБ, кв.м.'
hdr_cells[4].text = 'Общая площадь с учетом приведенной площади балкона, лоджии, кв.м.'
hdr_cells[5].text = 'Общая внутренняя площадь квартиры, кв.м.'
hdr_cells = table.rows[1].cells
hdr_cells[0].text = ' '
hdr_cells[1].text = ' '
hdr_cells[2].text = ' '
hdr_cells[3].text = ' '
hdr_cells[4].text = ' '
hdr_cells[5].text = ' '
document.save('demo.docx')
self.reply3 = QtWidgets.QMessageBox.question(self, 'Сохранение документа','Документ успешно сохранен!', QMessageBox.Yes)
obj_charstyle = obj_styles.add_style('CommentsStyle', WD_STYLE_TYPE.CHARACTER)
obj_font = obj_charstyle.font
obj_font.size = Pt(16)
#obj_font.name = 'Times New Roman'
obj_font.name = '標楷體'
obj_font.color.rgb = RGBColor(0x0, 0x0, 0xff)
#文件內文段落
paragraph = document.add_paragraph()
paragraph.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
#文件抬頭
run = paragraph.add_run(str(yyy) + '年度生產資訊處工作實績一覽表', style='CommentsStyle')
#加入表格
t = document.add_table(rows=row_cnt + 1, cols=col_cnt, style='Light List')
#t.style = 'Table Grid'
#t.style = 'Medium Shading 2 Accent 1'
#t.style = 'Medium List 2'
#t.style = 'Light List'
# add the header rows.
for j in range(df.shape[-1]):
t.cell(0, j).text = df.columns[j]
# add the rest of the data frame
prev_sysid = ''
curr_sysid = ''
for i in range(df.shape[0]):
curr_sysid = str(df.values[i, 0])
#print(curr_sysid)
def run(fileName, dico):
docIn = Document(fileName)
template = Document('./../template.docx')
template.save('./../new.docx')
docOut = Document('./../new.docx')
delete = False
for para in docOut.paragraphs:
if 'TITRE1' in para.text:
delete = True
if delete:
remove_paragraph(para)
delete = False
for table in docOut.tables:
if 'Table' in table.cell(0, 0).text:
delete = True
if delete:
remove_table(table)
# for para in template.paragraphs:
# if 'TITRE1' in para.text:
# titre1_style = docOut.styles.add_style('titre1', WD_STYLE_TYPE.PARAGRAPH)
# titre1_style = changeStyle(docOut.styles['Heading 1'], para.style)
# elif 'TITRE2' in para.text:
# titre2_style = para.style
# # titre2_style = changeStyle(docOut.styles['Heading 2'], para.style)
# elif 'TEST' in para.text:
# test_style = para.style
# test_style = changeStyle(docOut.styles['Heading 2'], para.style)
# table = iter(template.tables)
# label_style = table.next().style
# changeStyle(docOut.styles[label_style.name], label_style)
# statut_style = table.next().style
# docOut.styles.add_style(statut_style.name, WD_STYLE_TYPE.TABLE)
# changeStyle(docOut.styles[statut_style.name], statut_style)
# testTable_style = table.next().style
# changeStyle(docOut.styles[testTable_style.name], testTable_style)
for elem in dico:
docOut.add_paragraph(elem, style='Heading 2')
table = ''
for sousElem in dico[elem]:
if (dico[elem][sousElem]
and not isinstance(dico[elem][sousElem], dict)):
docOut.add_paragraph('\t' + sousElem)
docOut.add_paragraph('\t' + dico[elem][sousElem])
if isinstance(dico[elem][sousElem], dict):
if not table:
table = docOut.add_table(rows=1, cols=3)
table.columns[0].width = 10000
table.columns[1].width = 3828800
table.columns[2].width = 3828800
table.autofit = False
hdr_cells = table.rows[0].cells
hdr_cells[1].text = 'Action'
hdr_cells[2].text = 'Resultat attendu'
row_cells = table.add_row().cells
i = 1
row_cells[0].text = str(i)
else:
row_cells = table.add_row().cells
i += 1
row_cells[0].text = str(i)
for index, etape in enumerate(dico[elem][sousElem]):
if index == 2:
break
row_cells[index + 1].text = dico[elem][sousElem][etape]
table.columns[0].width = 2
docOut.save('./../wordOut.generated.docx')
def __init__(self, path_to_folder, meeting_date=datetime.now()):
document = Document()
db = DB()
'''you can only modify font/font size/bold through a run'''
document.styles['Normal'].font.name = 'Times New Roman'
document.styles['Normal'].font.size = Pt(12)
document.styles['Normal'].paragraph_format.line_spacing = 1
document.styles['Normal'].paragraph_format.space_after = Pt(0)
'''heading'''
header_paragraph = document.add_paragraph()
header_paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER
header_paragraph.paragraph_format.line_spacing = 1
header_paragraph.paragraph_format.space_after = Pt(
1) #WTF WHY DID THIS WORK
header_run = header_paragraph.add_run()
header_run.bold = True
header_run.font.name = 'Georgia'
header_run.font.size = Pt(16)
header_run.add_picture('secret/Alpha_sig.png', width=Inches(1.52))
header_run.add_break()
'''possibly change this to work with API call'''
header_run.add_text('ZETA GAMMA CHAPTER')
header_run.add_break()
'''possibly change this to work with API call'''
header_run.add_text('UNIVERSITY OF CALIFORNIA, DAVIS')
'''two breaks in a row doesn't work aparently?'''
header_run.add_break(WD_BREAK.LINE)
header_run.add_break(WD_BREAK.LINE)
header_run.add_break(WD_BREAK.LINE)
'''MM DD, YYYY'''
#not sure about %d and %-d
#"Prudential meeting minutes" for prudential
date_string = 'Chapter Meeting Minutes - ' + meeting_date.strftime(
'%B %-d, %Y')
date_run = header_paragraph.add_run()
date_run.bold = True
date_run.font.size = Pt(16)
date_run.add_text(date_string)
date_run.add_break(WD_BREAK.LINE)
date_run.add_break(WD_BREAK.LINE)
'''
body paragraph contains:
'''
body_paragraph = document.add_paragraph()
body_paragraph.alignment = WD_ALIGN_PARAGRAPH.LEFT
#body_paragraph.paragraph_format.line_spacing = 0 doesn't work
body_paragraph.line_spacing_rule = WD_LINE_SPACING.EXACTLY
body_paragraph.paragraph_format.space_after = Pt(0)
'''
meetings usually start at 5PM, which is 1700.
im assuming is doc is generated after 5, meeting isn't starting at 5:00 pm
'''
time = meeting_date.strftime('%I:%M%p')
attendance_run = body_paragraph.add_run()
attendance_run.font.size = Pt(12)
attendance_run.bold = True
attendance_run.add_text('MEETING TO ORDER: ')
attendance_run.add_text(time)
attendance_run.add_break(WD_BREAK.LINE)
attendance_run.add_break(WD_BREAK.LINE)
attendance_run.add_text('ROLL CALL: ')
attendance_run.add_break(WD_BREAK.LINE)
Attendance = Attendance_API()
#box says "PRUDENTIAL BOARD" for 10 cells of 2 rows
#hen same but "OPEN MEMBERS"
today_attendance = Attendance.get_attendance()
table = document.add_table(10, 3)
table.style = 'Table Grid'
table_cells = []
for colIndex, col in enumerate(table.columns):
for rowIndex, cell in enumerate(col.cells):
text = str(colIndex * 10 + rowIndex + 1)
cell.text = text + '.'
table_cells.append(cell)
for (num, member) in zip(table_cells, today_attendance):
num.text = num.text + ' ' + member[0] + ' ' + member[1]
prudential_paragraph = document.add_paragraph()
quorum_run = prudential_paragraph.add_run()
#quorum_run.font.size = Pt(12)
quorum_run.bold = True
quorum_run.add_break(WD_BREAK.LINE)
quorum_run.add_text('Quorum: Met/Not Met')
quorum_run.add_break(WD_BREAK.LINE)
'''
prudential_underline = prudential_paragraph.add_run('PRUDENTIAL REPORTS')
#prudential_underline.font.size = Pt(12)
prudential_underline.bold = True
prudential_underline.underline = True
#prudential_underline.add_break(WD_BREAK.LINE)
'''
#prudentiaL: OPEN MEMBER FORUM
officer_title = Title(document,
'PRUDENTIAL REPORTS',
font='Arial',
bold=True,
underline=True)
officer_response = db.get_officers()
officer_list = []
for officer in officer_response:
officer_list.append(Position(document, officer))
'''now begin actual reports'''
director_title = Title(document,
'DIRECTOR REPORTS',
font='Arial',
bold=True,
underline=True)
director_response = db.get_directors()
director_list = []
for director in director_response:
director_list.append(Position(document, director))
#prudential has this too
VP_title = Title(document,
'VICE PRESIDENT\'S REPORT',
font='Arial',
bold=True,
underline=True)
vp = Position(document, ('Vice President', 'Max'))
president_title = Title(document,
'PRESIDENT\'S REPORT',
font='Arial',
bold=True,
underline=True)
vp = Position(document, ('President', 'Alex'))
old_biz = Title(document,
string='OLD BUSINESS',
font='Arial',
bold=True,
underline=True)
new_biz = Title(document,
string='NEW BUSINESS',
font='Arial',
bold=True,
underline=True)
poll = Title(document, string='Move to Poll', font='Arial', bold=True)
calendar = Title(document,
string='Move to Amend the Calendar',
font='Arial',
bold=True,
underline=False)
call_outs = Title(document,
string='CALL OUTS',
font='Arial',
bold=True)
comments = Title(document,
string='COMMENTS FOR THE GOOD OF SOCIETY',
font='Arial',
bold=True)
adjourned = Title(document,
string='MEETING ADJOURNED: Time',
font='Arial',
bold=True)
print 'just saved to: ', path_to_folder, meeting_date.strftime(
'%Y.%m.%d') + '.docx'
document.save(path_to_folder + meeting_date.strftime('%Y.%m.%d') +
'.docx')
def cikti():
liste1=liste.get(ACTIVE)
vt = sqlite3.connect(str(liste1)+'.sq3')
im= vt.cursor()
im.execute(""" SELECT * FROM diploma""")
rows = im.fetchall()
data_str = ""
sf = "{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}{}"
for row in rows:
data_str += sf.format(row[0], row[1], row[2],row[3], row[4], row[5], row[6], row[7],row[8], row[9], row[10], row[11], row[12],row[13], row[14],row[15], \
row[16], row[17],row[18], row[19])
document = Document()
style = document.styles['Normal']
font = style.font
font.name = 'Times New Roman'
font.size = Pt(12)
paragraph = document.add_paragraph(row[0]+" MÜDÜRLÜĞÜNE")
paragraph.alignment = 1
paragraph = document.add_paragraph("\t\t\t\t\t\t\t"+row[1]+"\n\n")
paragraph = document.add_paragraph("\t"+row[2]+" Eğitim-Öğretim yılı sonunda almış olduğum İlkokul diplomamı zayi ettim. \
Diploma kayıt örneğimin çıkartılarak tarafıma verilmesi hususunda;")
paragraph = document.add_paragraph("\tGereğini bilgilerinize arz ederim.")
table = document.add_table(rows=1, cols=4)
row1=table.add_row().cells
row1[3].paragraphs[0].add_run(row[3]+"\n"+row[4]+"\n")
row1[3].paragraphs[0].alignment=WD_ALIGN_PARAGRAPH.CENTER
paragraph = document.add_paragraph("Adres: "+row[5]+"\n\t"+row[6]+"/"+row[7]+"\n")
paragraph = document.add_paragraph("Belge No: "+row[8]+"\n\n")
paragraph = document.add_paragraph("DİPLOMA KAYIT ÖRNEĞİ\n")
paragraph.alignment = 1
paragraph = document.add_paragraph("\tDilekçe sahibi "+row[9]+" T.C. Kimlik No.lu "+row[10]+" doğumlu "+row[11]+" "+row[12]+" "+row[4]+"' in "+row[0]+ \
" 'ndan -"+row[13]+"- derece ile "+row[14]+" tarih ve "+row[15]+" sayılı diplomayı almaya hak kazandığı resmî kayıtların \
incelenmesinden anlaşılmıştır.\n")
paragraph = document.add_paragraph("\t\t\t\t\t\t\tKAYITLARIMIZA UYGUNDUR")
table = document.add_table(rows=0, cols=3)
row2=table.add_row().cells
row2[2].paragraphs[0].add_run(row[3]+"\n\n")
row2[2].paragraphs[0].alignment=WD_ALIGN_PARAGRAPH.CENTER
table = document.add_table(rows=0, cols=3)
row3=table.add_row().cells
row3[0].paragraphs[0].add_run(row[16]+"\n"+row[17])
row3[2].paragraphs[0].add_run(row[18]+"\n"+row[19])
row3[0].paragraphs[0].alignment=WD_ALIGN_PARAGRAPH.CENTER
row3[2].paragraphs[0].alignment=WD_ALIGN_PARAGRAPH.CENTER
document.save('DiplomaKayitOrnegi.docx')
os.system("libreoffice --writer DiplomaKayitOrnegi.docx")
def convert(self, tables, out_filepath):
doc = Document()
layout_settings = tables[0]['settings']
self._setup_styles(doc)
self._setup_page(doc, layout_settings)
sect = doc.sections[0]
avail_width = Emu(sect.page_width - sect.left_margin - sect.right_margin).cm
for data in tables:
tp = data['type']
table = data['table']
if tp == 'layout':
layout = table
if layout['title']:
doc.add_paragraph(layout['title'], 'listconv.title')
if layout['subtitle']:
doc.add_paragraph(layout['subtitle'], 'listconv.subtitle')
if layout['description']:
doc.add_paragraph(layout['description'], 'listconv.bottom_indent')
continue
doc.add_paragraph(table.title, 'listconv.h2')
if table.description:
doc.add_paragraph(table.description)
defs = data['settings']
if defs['template'] == 'table' and defs['columns']:
tbl = doc.add_table(
rows=0, cols=len(defs['columns']),
style='Table Grid')
tbl.autofit = False
total_width = Cm(0)
for i, col in enumerate(defs['columns']):
m = re.match('^([0-9]+(\.[0-9]+)?)cm$', col['width'])
if m is not None:
col['width'] = Cm(float(m.group(1)))
else:
raise RuntimeError('Incorrect width "%s" for column "%s". Specify column '
'width in form <X>cm, where <X> is number.'
% (col['width'], col['name']))
total_width += col['width'].cm
if total_width > avail_width:
raise RuntimeError('Total columns width of %.2f cm exceeded available width of'
'%.2f cm for page.'
% (total_width, avail_width))
tbl.add_row()
for i, col in enumerate(defs['columns']):
cell = tbl.cell(0, i)
cell.text = col['name'].strip()
cell.width = col['width']
cell.paragraphs[0].style = 'listconv.bold_center'
tbl.columns[i].width = col['width']
for i, item in enumerate(table.rows):
row = tbl.add_row()
for j, col in enumerate(defs['columns']):
if col['field'] == 'INDEX':
row.cells[j].text = str(i+1)
row.cells[j].paragraphs[0].style = 'listconv.center'
else:
val = item[col['field']]
if val:
row.cells[j].text = str(val).strip()
doc.add_paragraph('', 'listconv.bottom_indent')
elif defs['template'] == 'list' and defs['items']:
for it in table.rows:
vals = []
for field in defs['items']:
val = it[field].strip() or ''
if val:
vals.append(val)
doc.add_paragraph(', '.join(vals), style='List Number')
doc.save(out_filepath)
__author__ = 'maikflfrom'
from docx import Document
from docx.shared import Inches
document = Document()
document.add_heading('Document Title', 0)
p = document.add_paragraph('A plain paragraph having some ')
p.add_run('bold').bold = True
p.add_run(' and some ')
p.add_run('italic.').italic = True
document.add_heading('Heading, level 1', level=1)
document.add_paragraph('Intense quote', style='IntenseQuote')
document.add_paragraph('first item in unordered list', style='ListBullet')
document.add_paragraph('first item in ordered list', style='ListNumber')
table = document.add_table(rows=1, cols=3)
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
document.add_page_break()
document.save('demo.docx')
'Medium Grid 1',
'Medium Grid 2',
'Medium Grid 3',
'Medium List 1',
'Medium List 2',
'Medium Shading 1',
'Medium Shading 2',
]
# Create an example of all the tables
for table_type in table_types:
for idx in range(1, 6):
style = '%s - %s' % (table_type, 'Accent %d' % (idx))
document.add_heading(style, level=2)
try:
table = document.add_table(rows=1, cols=3, style=style.replace(' ', ''))
hdr_cells = table.rows[0].cells
hdr_cells[0].text = 'Qty'
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
for item in recordset:
row_cells = table.add_row().cells
row_cells[0].text = str(item[0])
row_cells[1].text = str(item[1])
row_cells[2].text = str(item[2])
except:
error = 'Failed to process: %s' % (style)
document.add_paragraph(error)
print(error)
document.add_page_break()
document.add_paragraph('Intense quote',
style='IntenseQuote') # 这里是添加段落，style后面则是样式
document.add_paragraph(
'first item in unordered list',
style='ListBullet' # 添加段落，样式为unordered list类型
)
document.add_paragraph(
'first item in ordered list',
style='ListNumber' # 添加段落，样式为ordered list数字类型
)
document.add_picture('C:\\Users\\chenzq\\OneDrive\\图片\\1.jpg',
width=Inches(1.25)) # 添加图片
table = document.add_table(rows=1, cols=3) # 添加一个表格，每行三列
hdr_cells = table.rows[0].cells # 表格第一行的所含有的所有列数
hdr_cells[0].text = 'Qty' # 第一行的第一列,给这行里面添加文字
hdr_cells[1].text = 'Id'
hdr_cells[2].text = 'Desc'
for item in recordset:
row_cells = table.add_row().cells # 这是在这个表格第一行 (称作最后一行更好) 下面再添加新的一行
row_cells[0].text = str(item.qty)
row_cells[1].text = str(item.id)
row_cells[2].text = item.desc
document.add_page_break() # 添加分页符
document.save('demo.docx') # 保存这个文档
[s.name for s in document.styles if s.type == 1]
def write_report(file_name):
document = Document()
document.add_heading('User Stories', level=1)
styles = document.styles
us_style = styles.add_style("Panalysis User Stories", WD_STYLE_TYPE.TABLE)
us_style.base_style = styles["Medium Grid 1 Accent 1"]
rr_style = styles.add_style("Panalysis Reporting Requirement", WD_STYLE_TYPE.TABLE)
rr_style.base_style = styles["Medium List 2 Accent 1"]
table1 = document.add_table(rows=1, cols=4, style=us_style)
table1.columns[0].width = Cm(3)
table1.columns[1].width = Cm(12)
table1.columns[2].width = Cm(5)
table1.columns[3].width = Cm(4)
hdr_cells = table1.rows[0].cells
hdr_cells[0].text = 'Number'
hdr_cells[1].text = 'Details'
hdr_cells[2].text = 'Priority'
hdr_cells[3].text = 'Reporting Requirements'
for i in doc_items['user_stories']:
row_cells = table1.add_row().cells
row_cells[0].text = str(i.id)
row_cells[1].text = str(i.description)
row_cells[2].text = str(i.priority)
row_cells[3].text = str(i.reqs)
document.add_page_break()
document.add_heading('Reporting Requirements', level=1)
for i in doc_items['reporting_requirements']:
document.add_heading(str(i.id) + ": " + i.title, level=2)
table2 = document.add_table(rows=1, cols=2, style=rr_style)
table2.columns[0].width = Cm(2)
table2.columns[1].width = Cm(15)
requirements_table = ["Description","Purpose","User Stories","Priority","Data Source","Dimensions","Metrics","Segments","Aggregation","Requires Custom Report"]
for x in requirements_table:
row_cells = table2.add_row().cells
row_cells[0].text = str(x) + ":"
if x == "Description":
row_cells[1].text = i.description
elif x == "Purpose":
row_cells[1].text = i.purpose
elif x == "User Stories":
row_cells[1].text = str(i.user_story_id)
elif x == "Priority":
row_cells[1].text = i.priority
elif x == "Data Source":
row_cells[1].text = i.data_source
elif x == "Dimensions":
row_cells[1].text = i.dimensions
elif x == "Metrics":
row_cells[1].text = i.metrics
elif x == "Segments":
row_cells[1].text = i.segment
elif x == "Aggregation":
row_cells[1].text = i.aggregation
elif x == "Requires Custom Report":
row_cells[1].text = i.custom_report
document.save(file_name)
fieldLen = abs(int(
row[idxMap[excelHead[4]]])) if type(0.0) == type(row[
idxMap[excelHead[4]]]) else row[idxMap[excelHead[4]]]
val = row[idxMap[excelHead[5]]]
required = "M" if row[idxMap[excelHead[6]]] == "是" else row[
idxMap[excelHead[6]]]
required = "O" if required == "否" else required
remark = row[idxMap[excelHead[7]]]
d[fieldName] = [
fieldName, cnName, fieldType,
str(fieldLen).replace("(", "").replace(")", ""), required,
str(remark + " " + str(val)).strip()
]
document = Document()
table = document.add_table(rows=1, cols=6, style='Table Grid')
# 获取第一行的单元格列表
hdr_cells = table.rows[0].cells
# 下面三行设置上面第一行的三个单元格的文本值
hdr_cells[0].text = '字段名'
hdr_cells[1].text = '中文名称'
hdr_cells[2].text = '数据类型'
hdr_cells[3].text = '长度'
hdr_cells[4].text = '是否可为空'
hdr_cells[5].text = '描述'
for key in inputDict.keys():
# 表格添加行，并返回行所在的单元格列表
record = inputDict[key]
row_cells = table.add_row().cells
row_cells[0].text = record[0]
row_cells[1].text = record[1]
class GoogleAnalyticsReport:
def __init__(self, startDate, endDate, heading, query=""):
credentials = json.load(open('secret/credentials.json'))
accounts = ga.authenticate(**credentials)
self.profile = accounts[0].webproperties[0].profile
self.document = Document('secret/default.docx')
self.document.add_heading(heading, 0)
self.startDate = startDate
self.endDate = endDate
self.sessions = 0
self.query_path = query
"""
Basic Metrics
"""
def getBasicMetrics(self):
self.document.add_heading('Basic Metrics', level=1)
query = ""
if (self.query_path != ""):
query = self.profile.core.query.set(metrics=[
'ga:users', 'ga:sessions', 'ga:pageviews',
'ga:uniquePageviews', 'ga:pageviewsPerSession',
'ga:bounceRate', 'ga:percentNewSessions'
]).set('start_date', self.startDate).set({
'end_date': self.endDate
}).filter(pagepathlevel1=self.query_path)
else:
query = self.profile.core.query.set(metrics=[
'ga:users', 'ga:sessions', 'ga:pageviews',
'ga:uniquePageviews', 'ga:pageviewsPerSession',
'ga:bounceRate', 'ga:percentNewSessions'
]).set('start_date',
self.startDate).set({'end_date': self.endDate})
rows = query.get().rows[0]
users = rows[0]
self.sessions = rows[1]
pageviews = rows[2]
uniquePageviews = rows[3]
pageviewsPerSession = rows[4]
bounceRate = rows[5]
paragraph = self.document.add_paragraph("Users: " + str(users) + "\n" +
"Sessions: " +
str(self.sessions) + "\n" +
"Pageviews:" + str(pageviews) +
"\n" + "UniquePageviews: " +
str(uniquePageviews) + "\n" +
"PageviewsPerSession: " +
str(pageviewsPerSession) +
"\n" + "BounceRate: " +
str(bounceRate))
"""
New and Returning Sessions
"""
self.document.add_heading('New and Returning Sessions', level=1)
percentNewSessions = rows[6]
percentReturningSessions = 100 - rows[6]
paragraph1 = self.document.add_paragraph("PercentNewSessions: " +
str(percentNewSessions) +
"\n" +
"PercentReturningSessions: " +
str(percentReturningSessions))
"""
Function to Print Tables
"""
def __print_table(self, noRows, noColumns, columnNames, rows):
if len(rows) < noRows:
noRows = len(rows)
table = self.document.add_table(
rows=noRows + 1,
cols=noColumns,
style=self.document.styles['TableGrid'])
hdr_cells = table.rows[0].cells
for i in xrange(noColumns):
hdr_cells[i].text = columnNames[i]
for i, item in enumerate(rows):
for j in xrange(noColumns):
table.rows[i + 1].cells[j].text = item[j]
"""
Function to get correct rows from the received query. Also adds column number and stringifies everything
"""
def __get_correct_rows(self, query):
values = query.get().rows
corrected_values = []
for i, row in enumerate(values):
corrected_values_each = [str(i + 1) + "."]
for row_val in row:
corrected_values_each.append(str(row_val))
corrected_values.append(corrected_values_each)
return corrected_values
"""
Top 10 pages
"""
def getTopTenPages(self):
if (self.query_path != ""):
self.document.add_heading("Top 10 pages within the given path",
level=1)
query = self.profile.core.query.metrics(
'ga:pageviews',
'ga:uniquePageviews').dimensions('ga:pageTitle').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).sort('ga:pageviews', descending=True).filter(
pagepathlevel1__contains=self.query_path).limit(10)
else:
self.document.add_heading("Top 10 pages", level=1)
query = self.profile.core.query.metrics(
'ga:pageviews',
'ga:uniquePageviews').dimensions('ga:pageTitle').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).sort('ga:pageviews', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(
10, 4, ["No.", "Page Path", "Pageviews", "UniquePageviews"],
corrected_values)
"""
Technology used to view the website
"""
def getTechUsedViewWebsite(self):
self.document.add_heading("Technology used to view the website",
level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:deviceCategory').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).filter(
pagepathlevel1=self.query_path).limit(10)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:deviceCategory').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(3, 3, ["No.", "Device", "Session"],
corrected_values)
"""
Audience
"""
def getAudience(self):
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:country').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions',
descending=True).filter(pagepathlevel1=self.query_path)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:country').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True)
rows = query.get().rows
numberOfCountries = len(rows)
self.document.add_heading("Audience", level=1)
paragraph1 = self.document.add_paragraph(
"Total Number of Countries : " + str(numberOfCountries))
corrected_values = []
for i, country in enumerate(rows[:10]):
corrected_values.append([
str(i + 1) + ".", country[0],
("%.2f" % (float(country[1] * 100) / self.sessions)) + "%"
])
self.__print_table(10, 3, ["No.", "Country", "% of Sessions"],
corrected_values)
"""
Traffic Sources
"""
def getTrafficSources(self):
self.document.add_heading("Traffic Sources", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:medium').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).filter(
pagepathlevel1=self.query_path).limit(10)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:medium').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Source", "Sessions"],
corrected_values)
"""
Search Terms
"""
def getSearchTerms(self):
self.document.add_heading("Search Terms", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:keyword').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).filter(
pagepathlevel1=self.query_path).limit(10)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:keyword').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Search Term", "Sessions"],
corrected_values)
"""
Organic Sources
"""
def getOrganicSources(self):
self.document.add_heading("Organic Sources", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(medium='organic').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).filter(pagepathlevel1=self.query_path).limit(10).sort(
'ga:sessions', descending=True)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(medium='organic').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).limit(10).sort('ga:sessions', descending=True)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Organic Sources", "Sessions"],
corrected_values)
"""
Referral Sources
"""
def getReferralSources(self):
self.document.add_heading("Referral Sources", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(medium='referral').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).filter(pagepathlevel1=self.query_path).limit(10).sort(
'ga:sessions', descending=True)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(medium='referral').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).limit(10).sort('ga:sessions', descending=True)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Referral Source", "Sessions"],
corrected_values)
"""
Non Buffalo Referrals
"""
def getNonBuffaloReferrals(self):
self.document.add_heading("Non Buffalo Referral Sources", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(
medium='referral').filter(source__ncontains="buffalo").set(
'start_date', self.startDate).set({
'end_date':
self.endDate
}).filter(
pagepathlevel1=self.query_path).limit(10).sort(
'ga:sessions', descending=True)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:source').filter(
medium='referral').filter(source__ncontains="buffalo").set(
'start_date', self.startDate).set({
'end_date':
self.endDate
}).limit(10).sort('ga:sessions', descending=True)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Source", "Sessions"],
corrected_values)
"""
Top Pages Visited as a Result of Direct Traffic
"""
def getTopPagesFromDirectTraffic(self):
if (self.query_path != ""):
self.document.add_heading(
"Top Pages Visited as a Result of Direct Traffic (within the given path)",
level=1)
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:pageTitle').filter(medium='(none)').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).sort('ga:sessions', descending=True).filter(
pagepathlevel1__contains=self.query_path).limit(10)
else:
self.document.add_heading(
"Top Pages Visited as a Result of Direct Traffic", level=1)
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:pageTitle').filter(medium='(none)').set(
'start_date', self.startDate).set({
'end_date': self.endDate
}).sort('ga:sessions', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Pages", "Sessions"],
corrected_values)
"""
Social Network
"""
def getSocialNetwork(self):
self.document.add_heading("Social Network", level=1)
if (self.query_path != ""):
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:socialNetwork').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).filter(
pagepathlevel1=self.query_path).limit(10)
else:
query = self.profile.core.query.metrics('ga:sessions').dimensions(
'ga:socialNetwork').set('start_date', self.startDate).set({
'end_date':
self.endDate
}).sort('ga:sessions', descending=True).limit(10)
corrected_values = self.__get_correct_rows(query)
self.__print_table(10, 3, ["No.", "Social Networks", "Sessions"],
corrected_values)
def buildDoc(self):
self.getBasicMetrics()
self.getTopTenPages()
self.getTechUsedViewWebsite()
self.getAudience()
self.getTrafficSources()
self.getSearchTerms()
self.getOrganicSources()
self.getReferralSources()
self.getNonBuffaloReferrals()
self.getTopPagesFromDirectTraffic()
self.getSocialNetwork()
self.document.save('demo.docx')
def make_puzzle(self, filename = 'puzzle'):
difficulty = self.diff
option = self.option
if difficulty == 1:
self.difficulty = 'random.choice([self.col, self.row])(word)'
elif difficulty == 2:
self.difficulty = "random.choice([self.col, self.col_rev, self.row, self.row_rev])(word)"
elif difficulty == 3:
self.difficulty = "random.choice([self.col, self.row, self.diagup, self.diagdown])(word)"
elif difficulty == 4:
self.difficulty = "random.choice([self.col, self.col_rev, self.row, self.row_rev, self.diagup, self.diagup_rev, self.diagdown, self.diagdown_rev])(word)"
self.puzzle_origin = []
for i in range(self.height):
self.puzzle_origin.append([])
for j in range(self.width):
self.puzzle_origin[i].append('0')
print("퍼즐 만드는 중")
words = [word[0] for word in self.word_image]
for word in words:
exec(self.difficulty)
string_words = ''.join(words)
from collections import Counter
count_alpha = Counter(string_words)
common_alph = ''
for alph in count_alpha.most_common(5):
common_alph += alph[0]
data = ''
if self.korean:
f = open("random_words.txt", 'r')
data = f.read()
regex_f = r'[가-힣]+'
search_target_f = data
data = ''.join(list(set(re.findall(regex_f, search_target_f))))
printed_words = ''
puzzle = copy.deepcopy(self.puzzle_origin)
for i in range(self.height):
for j in range(self.width):
if self.puzzle_origin[i][j] == "0":
fill_alph = random.choice(string.ascii_lowercase)
if self.korean:
fill_alph = random.choice(data)
#글자들 되도록 겹치지 않게 하기 위해서 많이 나오는 글자 한번쯤은 피할 수 있도록 한다.
if option == 0:
puzzle[i][j] = fill_alph
elif option == 1:
if fill_alph in common_alph:
fill_alph = random.choice(string.ascii_lowercase)
if self.korean:
fill_alph = random.choice(data)
puzzle[i][j] = fill_alph
printed_words += puzzle[i][j]
#글자가 겹치도록 하기 위해서 많이 나온 글자와 무작위 글자들 중에서 고르도록 한다.
elif option == 2:
common_alph_list = []
puzzle[i][j] = random.choice([fill_alph, random.choice(count_alpha.most_common(7))[0]])
printed_words += puzzle[i][j]
# write to docx file
# Write to docx to puzzle.docx
document = Document()
#changing the page margins
sections = document.sections
for section in sections:
section.top_margin = Cm(1)
section.bottom_margin = Cm(0.8)
section.left_margin = Cm(2.3)
section.right_margin = Cm(2.3)
heading = 'Word Puzzle'
if self.korean:
heading = "낱말 찾기"
head = document.add_heading(heading, 0)
head.alignment = WD_ALIGN_PARAGRAPH.CENTER
if os.path.exists('hwp_settings.json'):
with open('hwp_settings.json') as f:
data = json.load(f)
para_belong = document.add_paragraph('{}학년 {}반 이름: _______'.format(data['grade'], data['class']))
else:
para_belong = document.add_paragraph('__학년 __반 이름: _______')
para_belong.alignment = WD_ALIGN_PARAGRAPH.RIGHT
puzzle_table = document.add_table(rows=self.height, cols=self.width, style='Table Grid')
puzzle_table.alignment = WD_TABLE_ALIGNMENT.CENTER
self.set_height = 7200 / self.height
for i, row in enumerate(puzzle_table.rows):
#######################세로 길이 정하기!
# accessing row xml and setting tr height
tr = row._tr
trPr = tr.get_or_add_trPr()
trHeight = OxmlElement('w:trHeight')
trHeight.set(qn('w:val'), str(self.set_height))
trHeight.set(qn('w:hRule'), "atLeast")
trPr.append(trHeight)
for j, cell in enumerate(row.cells):
#####가로 길이 정하기!
cell.width = Inches(5)
if self.uppercase and not self.korean:
cell.text = puzzle[i][j].upper()
else:
cell.text = puzzle[i][j]
for paragraph in cell.paragraphs:
#####가운데 정렬!!
paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER
paragraph.style.font.bold = True
#####상하 방향에서 가운데 정렬
tc = cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
# 힌트 테이블 만들기
# 사진이 들어가는 경우
if self.pic_on:
word_num = len(words)
if word_num <= 15:
size = 5
elif word_num <= 21:
size = (word_num+2)//3
else:
size = 7
hint_table = document.add_table(rows = (len(words)+size-1)//size * 2, cols = size, style = 'Table Grid')
hint_table.alignment = WD_TABLE_ALIGNMENT.CENTER
for i, row in enumerate(hint_table.rows):
#######################세로 길이 정하기!
if i%2 == 0:
# accessing row xml and setting tr height
tr = row._tr
trPr = tr.get_or_add_trPr()
trHeight = OxmlElement('w:trHeight')
trHeight.set(qn('w:val'), '1000')
trHeight.set(qn('w:hRule'), "atLeast")
trPr.append(trHeight)
elif i%2 == 1:
# accessing row xml and setting tr height
tr = row._tr
trPr = tr.get_or_add_trPr()
trHeight = OxmlElement('w:trHeight')
trHeight.set(qn('w:val'), '60')
trHeight.set(qn('w:hRule'), "atLeast")
trPr.append(trHeight)
for j, cell in enumerate(row.cells):
index = i//2*size + j
#단어 수 만큼 반복하기
if index < len(words):
for paragraph in cell.paragraphs:
if i % 2 == 1:
# 초성 또는 scramble이 켜져 있는 경우
if self.chosung_scramable:
word = words[index]
if self.korean:
cho_word = ''
for chr in word:
chosung_scramable = hgtk.letter.decompose(chr)[0]
cho_word += chosung_scramable
run = paragraph.add_run(cho_word)
else:
# 사진 있고 영어고 scramble인 경우
spelling = [i for i in word]
shuffle(spelling)
scrambled_word = ''.join(spelling)
if self.uppercase:
run = paragraph.add_run(scrambled_word.upper())
else:
run = paragraph.add_run(scrambled_word)
else:
if self.uppercase and not self.korean:
run = paragraph.add_run(words[index].upper())
else:
run = paragraph.add_run(words[index])
font = run.font
font.name = 'Arial'
font.size = Pt(15)
elif i % 2 == 0:
try:
run = paragraph.add_run()
run.add_picture(self.word_image[index][1], width=cell.width *95/100,
height=cell.width)
except:
paragraph.add_run("에러 발생. 다른 사진 선택해주세요.")
#####가운데 정렬!!
paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER
paragraph.style.font.bold = True
#####상하 방향에서 가운데 정렬
tc = cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
# 사진이 들어가지 않는 경우
else:
# 사진이 안 들어가고 영어인 경우
if not self.korean:
hint_table = document.add_table(rows=1, cols=1, style='Table Grid')
hint_table.alignment = WD_TABLE_ALIGNMENT.CENTER
hint_table_row = hint_table.rows[0]
hint_tr = hint_table_row._tr
hint_trPr = hint_tr.get_or_add_trPr()
hint_trHeight = OxmlElement('w:trHeight')
hint_trHeight.set(qn('w:val'), '1000')
hint_trHeight.set(qn('w:hRule'), "atLeast")
hint_trPr.append(hint_trHeight)
hint_table_cell = hint_table_row.cells[0]
hint = ''
parenthesis = re.compile(r'(\s)?\(.*\)(\s)?')
bracket = re.compile(r'(\s)?\[.*\](\s)?')
for word in words:
print("사전에 찾는중... " + word)
req = requests.get('http://endic.naver.com/small_search.nhn?query=' + word)
# 국어사전은 'http://ko.dict.naver.com/small_search.nhn?query='
html = req.text
soup = BeautifulSoup(html, 'html.parser')
meanings = soup.select('span.fnt_k05')
if self.uppercase:
word = word.upper()
if self.chosung_scramable:
spelling = [i for i in word]
shuffle(spelling)
word = ''.join(spelling)
if meanings:
text = meanings[0].text
text = re.sub(parenthesis, '', text)
text = re.sub(bracket, '', text)
print(text)
hint += word + "({})".format(text) + ', '
hint_table_cell.width = Inches(100)
for paragraph in hint_table_cell.paragraphs:
paragraph.add_run(hint.strip(', '))
paragraph.alignment = WD_ALIGN_PARAGRAPH.JUSTIFY
tc = hint_table_cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
else:
# 사진이 안 들어가고 한글인 경우
if self.chosung_scramable:
hint_table = document.add_table(rows=1, cols=1, style='Table Grid')
hint_table.alignment = WD_TABLE_ALIGNMENT.CENTER
hint_table_row = hint_table.rows[0]
hint_tr = hint_table_row._tr
hint_trPr = hint_tr.get_or_add_trPr()
hint_trHeight = OxmlElement('w:trHeight')
hint_trHeight.set(qn('w:val'), '1000')
hint_trHeight.set(qn('w:hRule'), "atLeast")
hint_trPr.append(hint_trHeight)
hint_table_cell = hint_table_row.cells[0]
hint = ''
for word in words:
cho_word = ''
for chr in word:
chosung_scramable = hgtk.letter.decompose(chr)[0]
cho_word += chosung_scramable
hint += cho_word + ', '
hint_table_cell.width = Inches(100)
for paragraph in hint_table_cell.paragraphs:
paragraph.add_run(hint.strip(', '))
paragraph.alignment = WD_ALIGN_PARAGRAPH.JUSTIFY
tc = hint_table_cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
else:
hint_table = document.add_table(rows=1, cols=1, style='Table Grid')
hint_table.alignment = WD_TABLE_ALIGNMENT.CENTER
hint_table_row = hint_table.rows[0]
hint_tr = hint_table_row._tr
hint_trPr = hint_tr.get_or_add_trPr()
hint_trHeight = OxmlElement('w:trHeight')
hint_trHeight.set(qn('w:val'), '1000')
hint_trHeight.set(qn('w:hRule'), "atLeast")
hint_trPr.append(hint_trHeight)
hint_table_cell = hint_table_row.cells[0]
hint = ''
for word in words:
hint += word + ', '
hint_table_cell.width = Inches(100)
for paragraph in hint_table_cell.paragraphs:
paragraph.add_run(hint.strip(', '))
paragraph.alignment = WD_ALIGN_PARAGRAPH.JUSTIFY
tc = hint_table_cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
# 정답 파일 쓰기
answ_doc = Document()
answer_table = answ_doc.add_table(rows=self.height, cols=self.width, style='Table Grid')
answer_table.alignment = WD_TABLE_ALIGNMENT.CENTER
for i, row in enumerate(answer_table.rows):
#######################세로 길이 정하기!
# accessing row xml and setting tr height
tr = row._tr
trPr = tr.get_or_add_trPr()
trHeight = OxmlElement('w:trHeight')
trHeight.set(qn('w:val'), str(self.set_height))
trHeight.set(qn('w:hRule'), "atLeast")
trPr.append(trHeight)
for j, cell in enumerate(row.cells):
#####가로 길이 정하기!
cell.width = Inches(8)
cell.text = self.puzzle_origin[i][j]
for paragraph in cell.paragraphs:
#####가운데 정렬!!
paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER
paragraph.style.font.bold = True
if cell.text == '0':
for run in paragraph.runs:
run.font.color.rgb = RGBColor(255, 255, 255)
else:
for run in paragraph.runs:
run.font.color.rgb = RGBColor(255, 0, 0)
#####상하 방향에서 가운데 정렬
tc = cell._tc
tcPr = tc.get_or_add_tcPr()
tcVAlign = OxmlElement('w:vAlign')
tcVAlign.set(qn('w:val'), "center")
tcPr.append(tcVAlign)
answ_doc.save(str(self.desktop) + '\{}_정답.hwp'.format(filename))
document.save(str(self.desktop) +'\{}.hwp'.format(filename))
print("바탕화면에 puzzle.docx와 puzzle.hwp 로 저장")
def createTopicTable(patentList, docFileName):
document = Document()
for section in document.sections:
section.orientation = WD_ORIENT.LANDSCAPE
table = document.add_table(rows=1, cols=len(patentList) + 1)
fillInTopicData(table, patentList)
document.save(docFileName)
