"""Execute XML Test Cases without a pass verdict"""
from time import sleep
import win32com.client as win32
import configparser
config = configparser.RawConfigParser()
config.read('usecase02_configuration.properties')
configurationPath = config.get('TESTCONFIGURATION', 'configurationpath')
testspec = config.get('TESTCONFIGURATION', 'testspecification')
CANoe = win32.DispatchEx("CANoe.Application")
CANoe.Open(configurationPath)
testSetup = CANoe.Configuration.TestSetup
testSetup.TestEnvironments.Add(testspec)
test_env = testSetup.TestEnvironments.Item('Test Environment')
report = test_env.Report
report = win32.CastTo(test_env, "ITestReport5")
test_env = win32.CastTo(test_env, "ITestEnvironment2")
print(report.FullName)
# Get the XML TestModule (type <TSTestModule>) in the test setup
test_module = test_env.TestModules.Item('Tester')
print(test_module.Path)
report = win32.CastTo(test_module.Report, "ITestReport5")
print(report.FullName)
# {.Sequence} property returns a collection of <TestCases> or <TestGroup>
# or <TestSequenceItem> which is more generic
#!/usr/bin/env python
# encoding: utf-8
import win32com.client as win32
excel = win32.DispatchEx('Excel.Application')
excel.Visible = 1
workBook = excel.Workbooks.Add()
sheet = workBook.Sheets(1)
start_row = 2
row = 5
sheet.Cells(1, 1).Value = 'Number'
for i in range(start_row, start_row + row):
sheet.Cells(i, 1).Value = i - 1
summed = sheet.Range(sheet.Cells(start_row, 1),
sheet.Cells(start_row + row - 1, 1))
# color
#summed.Interior.ColorIndex = 6 # yellow
summed.Interior.Color = 65535
# where 7 through 13 correspond to borders for (xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft, xlInsideHorizontal, xlInsideVertical)
# LineStyle of 1 = xlContinous
# Weight of 2 = xlThin
XlBorderWeight = {"xlHairline": 1, "xlThin": 2, "xlMedium": 3, "xlThick": 4}
XlBordersIndex = {
"xlDiagonalDown": 5,
directory = os.getcwd()
if n_files(directory) == 0:
print('There are no files to convert')
exit()
createFolder(directory)
print('Starting conversion... \n')
# Opens each file with Microsoft Word and saves as a PDF
try:
if(is_tool('libreoffice') == False):
from win32com import client
word = client.DispatchEx('Word.Application')
for file in os.listdir(directory):
if (file.endswith('.doc') or file.endswith('.docx') or file.endswith('.tmd')):
ending = ""
if file.endswith('.doc'):
ending = '.doc'
if file.endswith('.docx'):
ending = '.docx'
if file.endswith('.tmd'):
ending = '.tmd'
if is_tool('libreoffice'):
in_file = os.path.abspath(directory + '/' + file)
new_file = os.path.abspath(directory + '/PDFs')
doc2pdf_libreoffice(in_file, ending, new_file)
if(is_tool('libreoffice') == False):
print("\nThe specified folder does not contain docx,doc or pdf files.\n")
print("There are no files to convert. BYE, BYE!.")
exit()
else:
print("\nNumber of doc,docx and pdf files: ", num_docx + num_doc + num_pdf,
"\n")
print("Starting to convert files ...\n")
try:
word = client.DispatchEx("Word.Application")
for files in listdir(getcwd()):
if files.endswith(".docx"):
new_name = files.replace(".docx", r".txt")
in_file = path.abspath(folder + "\\" + files)
new_file = path.abspath(folder + "\\" + new_name)
doc = word.Documents.Open(in_file)
print(strftime("%H:%M:%S"), " docx -> txt ",
path.relpath(new_file))
def __init(self):
pythoncom.CoInitialize()
self.__client = client.DispatchEx("Excel.Application")
self.__client.DisplayAlerts = False
def main():
"""Create a usage report for the current week."""
# Get connection object for FTP queries.
connection = mal.setup_FTP()
# Get week start and end dates for report based on today's date.
dates = setup_dates()
# Set up Excel file and name it.
name = str(dates["This Week"][1]) + " Weekly Usage Report.xlsx"
path = os.path.join(os.getcwd(), "Usage Reports", name)
if os.path.exists(path):
os.remove(path)
writer = setup_dest_file(path)
# Grab reference to the workbook and add some formats.
workbook = writer.book
f_week = workbook.add_format({
'bold': True,
'font_color': 'white',
'bg_color': '#4f81bd'
})
f_purple = workbook.add_format({
'bold': True,
'font_color': 'white',
'bg_color': '#8064a2'
})
f_header = workbook.add_format({
'bold': True,
'border': 0
})
f_percent = workbook.add_format({'num_format': '0.00%'})
f_header_percent = workbook.add_format({
'bold': True,
'border': 0,
'num_format': '0%'
})
# Create a dictionary to label our weeks.
weekname = ["This Week", "Last Week", "Last Year"]
# Initialize a bunch of counters for column placement.
weekcount, C1, C2, C3, C4, C5, C6 = 0, 0, 0, 0, 0, 0, 0
# Initialize a bunch of offsets for row placement.
R1A, R1B = 1, 17
R2 = 1
R3 = 1
R4A, R4B = 2, 14
R5 = 2
R6A, R6B = 2, 14
# Store some pre-written chunks of excel formula for later.
weekly_change = ["Weekly Change", "=(B10-G10)/G10",
"=(C10-H10)/H10", "=(D10-I10)/I10"]
yearly_change = ["Yearly Change", "=(B10-L10)/L10",
"=(C10-M10)/M10", "=(D10-N10)/N10"]
# Some bits of SQL code, saved to variables to keep our code cleaner
sql_1A = """---- # of Test Results by Date
select CONVERT(varchar(10), tr.UpdatedDate,101) as Date,
COUNT(tr.testresultid) as Total,
sum(case when tr.qtionlinetestsessionid is not null then 1
else 0 end) as OnlineTests, sum(case when tr.BubbleSheetID
is not null then 1 else 0 end) as BubbleSheets
from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart
and tr.UpdatedDate<@weekend and d.Name not like '%demo%'
and (tr.BubbleSheetID is not null
or tr.QTIOnlineTestSessionID is not null)
group by CONVERT(varchar(10), tr.UpdatedDate, 101)
order by CONVERT(varchar(10), tr.UpdatedDate, 101)"""
sql_1B = """
select CONVERT(varchar(10), tr.UpdatedDate,101) as Date,
COUNT(tr.testresultid) as Total, sum(case when
tr.qtionlinetestsessionid is not null then 1 else 0 end)
as OnlineTests, sum(case when tr.BubbleSheetID is not null
then 1 else 0 end) as BubbleSheets from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart and
tr.UpdatedDate<@weekend and d.Name not like '%demo%'
and (tr.BubbleSheetID is not null or
tr.QTIOnlineTestSessionID is not null) and d.DistrictID
not in (2680, 2479) and d.DistrictGroupID not in (112,114)
and d.name not like '%frog street%'
group by CONVERT(varchar(10), tr.UpdatedDate, 101)
order by CONVERT(varchar(10), tr.UpdatedDate, 101)"""
sql_2 = """---- # of Test Results by Client
select st.Name as State, d.Name as District, count(1) TotalResults,
sum(case when tr.qtionlinetestsessionid is not null then 1
else 0 end) as OnlineTests, sum(case when tr.BubbleSheetID is not
null then 1 else 0 end) as BubbleSheets from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart and tr.UpdatedDate<@weekend
and d.Name not like '%demo%'
and (tr.BubbleSheetID is not null
or tr.QTIOnlineTestSessionID is not null)
group by st.Name, d.Name
order by count(1) desc"""
sql_3 = """---- # of LinkIt Benchmarks by Client
select st.Name as State, d.Name as District, count(1) TotalResults,
sum(case when tr.qtionlinetestsessionid is not null then 1 else 0
end) as OnlineTests, sum(case when tr.BubbleSheetID is not null
then 1 else 0 end) as BubbleSheets from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart and tr.UpdatedDate<@weekend
and d.Name not like '%demo%' and (tr.BubbleSheetID is not null
or tr.QTIOnlineTestSessionID is not null)
and vt.Name like '%linkit%form%'
group by st.Name, d.Name
order by count(1) desc"""
sql_4A = """---- # of Online Test Sessions by Start Time ---
select CONVERT(varchar(10), qots.startdate,101) as [Date Started],
count(1) as [Total # of Online Tests], sum(case when qots.statusid=1
then 1 else 0 end) as [# of Created], sum(case when qots.statusid=2
then 1 else 0 end) as [# of Started], sum(case when qots.statusid=3
then 1 else 0 end) as [# of Paused], sum(case when qots.statusid=5
then 1 else 0 end) as [# of Pending Review],
sum(case when qots.statusid=4 then 1 else 0 end) as [# of Completed]
from QTIOnlineTestSession qots With (nolock)
join student s With (nolock) on s.studentid=qots.studentid
join district d With (nolock) on d.DistrictID=s.districtid
where d.name not like '%demo%' and qots.StartDate>@weekstart
and qots.StartDate<@weekend
group by CONVERT(varchar(10), qots.startdate,101)
order by CONVERT(varchar(10), qots.startdate,101)"""
sql_4B = """---- # of Online Test Sessions by Last Log In Time ---
select CONVERT(varchar(10), qots.LastLoginDate,101) as
[Date Last Log In], count(1) as [Total # of Online Tests],
sum(case when qots.statusid=1 then 1 else 0 end) as [# of Created],
sum(case when qots.statusid=2 then 1 else 0 end) as [# of Started],
sum(case when qots.statusid=3 then 1 else 0 end) as [# of Paused],
sum(case when qots.statusid=5 then 1 else 0 end) as
[# of Pending Review], sum(case when qots.statusid=4 then 1 else 0
end) as [# of Completed]
from QTIOnlineTestSession qots With (nolock)
join student s With (nolock) on s.studentid=qots.studentid
join district d With (nolock) on d.DistrictID=s.districtid
where d.name not like '%demo%' and qots.LastLoginDate>@weekstart
and qots.LastLoginDate<@weekend
group by CONVERT(varchar(10), qots.LastLoginDate,101)
order by CONVERT(varchar(10), qots.LastLoginDate,101)"""
sql_5 = """-- # of Online Test Sessions by Hour by Last Log In Time
select CONVERT(varchar(13), dateadd(hour, -4,qots.LastLoginDate),
120) as [Hour], count(1) as [Number of Sessions],
SUM(case when d.DistrictID=2479 then 1 else 0 end) as [A Beka],
sum(case when d.districtgroupid=112 then 1 else 0 end) as BEC,
sum(case when d.name like '%frog street%' then 1 else 0 end) as
[Frogstreet] from QTIOnlineTestSession qots With (nolock)
join student s With (nolock) on s.studentid=qots.studentid
join district d With (nolock) on d.DistrictID=s.districtid
where d.name not like '%demo%' and qots.LastLoginDate>@weekstart
and qots.LastLoginDate<@weekend
group by CONVERT(varchar(13),
dateadd(hour, -4,qots.LastLoginDate),120)
order by count(1) desc"""
sql_6A = """---- # of Results Entry by Date
select CONVERT(varchar(10), tr.UpdatedDate,101) as Date,
COUNT(tr.testresultid) as Total from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart and tr.UpdatedDate<@weekend
and d.Name not like '%demo%' and vt.virtualtestsourceid=3
and vt.virtualtesttype in (1,5)
group by CONVERT(varchar(10), tr.UpdatedDate, 101)
order by CONVERT(varchar(10), tr.UpdatedDate, 101)"""
sql_6B = """---- # of Results Entry by District
select st.Name as State, d.Name as District,
COUNT(tr.testresultid) as Total from TestResult tr
join VirtualTest vt on vt.VirtualTestID=tr.VirtualTestID
join Student s on s.StudentID=tr.StudentID
join District d on d.DistrictID=s.DistrictID
join State st on st.StateID=d.StateID
where tr.UpdatedDate>@weekstart and tr.UpdatedDate<@weekend
and d.Name not like '%demo%' and vt.virtualtestsourceid=3
and vt.virtualtesttype in (1,5)
group by st.Name, d.Name
order by COUNT(tr.testresultid) desc"""
for week in dates.values():
# Part 1A
sql = sql_1A
R = R1A
C = C1
N = "# of Results by Date"
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
# Then formatted headers
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
# Add week label above table
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
# Add 'Total' row at bottom of table
worksheet.write_string(R+8, C, 'Total', f_header)
worksheet.write(R+8, C+1, df['Total'].sum(), f_header)
worksheet.write(R+8, C+2, df['OnlineTests'].sum(), f_header)
worksheet.write(R+8, C+3, df['BubbleSheets'].sum(), f_header)
# Add weekly/yearly change
if weekcount is 0:
for i in range(4):
worksheet.write(R+10, C+i, weekly_change[i], f_header_percent)
worksheet.write(R+11, C+i, yearly_change[i], f_header_percent)
# Part 1B
sql = sql_1B
R = R1B
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
# Add week label above table
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
# Add 'Total' row at bottom of table
worksheet.write_string(R+8, C, 'Total', f_header)
worksheet.write(R+8, C+1, df['Total'].sum(), f_header)
worksheet.write(R+8, C+2, df['OnlineTests'].sum(), f_header)
worksheet.write(R+8, C+3, df['BubbleSheets'].sum(), f_header)
# Add weekly/yearly change
if weekcount is 0:
for i in range(4):
worksheet.write(R+10, C+i,
weekly_change[i].replace("10", "26"),
f_header_percent)
worksheet.write(R+11, C+i,
yearly_change[i].replace("10", "26"),
f_header_percent)
if weekcount is 2:
# Not counted for column widths, so we do it at the end
long_cell = "Without BEC, A Beka, A List, CEE, Frog Street"
worksheet.write(R1B-2, 0, long_cell, f_purple)
worksheet.write(R1B-2, 1, "", f_purple)
worksheet.write(R1B-2, 2, "", f_purple)
worksheet.write(R1B-2, 3, "", f_purple)
C1 = C1 + 5
# Part 2
sql = sql_2
R = R2
C = C2
N = "# of Results by Client"
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Add '%' column
tr_sum = df.TotalResults.sum(axis=0)
df['%'] = (df['TotalResults']/tr_sum)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
# Apply percent format to '%' column
worksheet.set_column(C+5, C+5, None, f_percent)
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
C2 = C2 + 7
# Part 3
sql = sql_3
R = R3
C = C3
N = "# of LinkIt Benchmarks"
# worksheet = writer.sheets[N]
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Add 'Total' row at top
df.loc[-1] = ['Total', '', df['TotalResults'].sum(),
df['OnlineTests'].sum(), df['BubbleSheets'].sum()]
df.index = df.index + 1 # shifting index
df = df.sort_index() # sorting by index
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
worksheet.set_row(R+1, None, f_header)
C3 = C3 + 6
# Part 4A
sql = sql_4A
R = R4A
C = C4
N = "# of Online by Date"
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-2, C, weekname[weekcount], f_week)
worksheet.write_string(R-1, C, "By Start Date", f_header)
# Part 4B
sql = sql_4B
R = R4B
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-2, C, weekname[weekcount], f_week)
worksheet.write_string(R-1, C, "By Last Login Date", f_header)
C4 = C4 + 8
# Part 5
sql = sql_5
R = R5
C = C5
N = "# of Online by Hour"
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Add some columns at the end
df['Others'] = (df['Number of Sessions']
- (df['A Beka'] + df['BEC'] + df['Frogstreet']))
df['% of A Beka'] = df['A Beka'] / df['Number of Sessions']
df['% of BEC'] = df['BEC'] / df['Number of Sessions']
df['% of Frog Street'] = df['Frogstreet'] / df['Number of Sessions']
df['% of Others'] = df['Others'] / df['Number of Sessions']
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-2, C, weekname[weekcount], f_week)
worksheet.write_string(R-1, C, "By Last Login Date", f_header)
C5 = C5 + 11
# Part 6A
sql = sql_6A
R = R6A
C = C6
N = "# of Data Locker"
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
worksheet = writer.sheets[N]
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
# Add Total to bottom
worksheet.write_string(R+8, C, "Total", f_header)
worksheet.write(R+8, C+1, df["Total"].sum(), f_header)
if weekcount == 0:
worksheet.write_string(R-2, C, "By Date", f_week)
# Part 6B
sql = sql_6B
R = R6B
# Get data from database
df = pd.read_sql(sql_week(sql, week), connection)
# Write the data to the file, then formatted headers
df.to_excel(writer, sheet_name=N, index=False, header=False,
startrow=R+1, startcol=C)
for col_num, value in enumerate(df.columns.values):
worksheet.write(R, C+col_num, value, f_header)
worksheet.write_string(R-1, C, weekname[weekcount], f_week)
if (weekcount == 0):
worksheet.write_string(R-2, C, "By Client", f_week)
# Adjust column widths
C6 = C6 + 4
weekcount = weekcount + 1
writer.save()
# Handle some special formatting by hijacking Excel.
excel = win32.DispatchEx('Excel.Application')
wb = excel.Workbooks.Open(path)
for ws in wb.Worksheets:
ws.Columns.AutoFit()
ws = wb.Worksheets("# of Results by Date")
ws.Columns(1).ColumnWidth = 15
ws = wb.Worksheets("# of Online by Hour")
ws.Range('G:J,R:U,AC:AF').NumberFormat = '0%'
ws = wb.Worksheets("# of Data Locker")
ws.Range("A4:J10")
wb.Save()
excel.Application.Quit()
def arc():
destino = str(formato.get())
if destino == "Arquivo do Word": destino = "docx"
if destino == "Arquivo do Power-Point": destino = "ppt"
if destino == "Arquivo do Excel": destino = "xlsx"
if destino == "Arquivo de Texto": destino = "txt"
import win32com.client as win32
from os import path
in_file = path.abspath(diretorio)
out_file = path.abspath(filename)
if destino == "docx":
if file_extension in ArqDOCX or file_extension.lower(
) == ".pdf" or file_extension.lower() == ".txt":
word = win32.DispatchEx("Word.Application")
word.Visible = 0
word.DisplayAlerts = 0
doc = word.Documents.Open(in_file)
doc.SaveAs(out_file, FileFormat=16)
doc.Close()
word.Quit()
elif destino.lower() == "pdf":
if file_extension.lower() in ArqPPT:
word = win32.DispatchEx("PowerPoint.Application")
word.Visible = 0
word.DisplayAlerts = 0
doc = word.Presentations.Open(in_file)
doc.SaveAs(out_file, FileFormat=32)
doc.Close()
word.Quit()
elif file_extension.lower() in ArqXLSX:
word = win32.DispatchEx("Excel.Application")
word.Visible = 0
word.DisplayAlerts = 0
doc = word.Workbooks.Open(in_file)
doc.ExportAsFixedFormat(0, out_file)
doc.Close()
word.Quit()
elif file_extension.lower() in ArqDOCX or file_extension.lower(
) == ".txt":
word = win32com.client.Dispatch('Word.Application')
word.Visible = 0
word.DisplayAlerts = 0
doc = word.Documents.Open(in_file)
doc.SaveAs(in_file, FileFormat=17)
doc.Close()
word.Quit()
elif destino.lower() == "xlsx":
if file_extension.lower() == ".pdf":
import pdftables_api
c = pdftables_api.Client('to7jluln0hvr')
c.xlsx(diretorio, filename + '.xlsx')
elif file_extension.lower() == ".txt" or file_extension.lower(
) in ArqDOCX:
import pandas as pd
df = pd.read_csv(diretorio, header=None, delim_whitespace=True)
df.to_excel(filename + '.xlsx', index=False, header=None)
elif destino.lower() == "txt":
if file_extension in ArqDOCX:
import docx2txt
text = docx2txt.process(diretorio)
with open(filename + ".txt", "w") as file:
print(text, file=file)
elif file_extension.lower() == ".pdf":
from io import StringIO
from pdfminer.pdfparser import PDFParser
from pdfminer.pdfdocument import PDFDocument
from pdfminer.pdfinterp import PDFResourceManager, PDFPageInterpreter
from pdfminer.converter import TextConverter
from pdfminer.layout import LAParams
from pdfminer.pdfpage import PDFPage
output_string = StringIO()
with open(diretorio, 'rb') as in_file:
parser = PDFParser(in_file)
doc = PDFDocument(parser)
rsrcmgr = PDFResourceManager()
device = TextConverter(rsrcmgr,
output_string,
laparams=LAParams())
interpreter = PDFPageInterpreter(rsrcmgr, device)
for page in PDFPage.create_pages(doc):
interpreter.process_page(page)
with open(filename + ".txt", "w") as final:
final.write(output_string.getvalue())
elif file_extension.lower() in ArqXLSX:
import pandas as pd
read_file = pd.read_excel(diretorio, header=None)
read_file.to_csv(filename + ".txt", index=None, header=True)
messagebox.showinfo(
"Formato convertido",
"Formato de ficheiro convertido com sucesso.\n\n" +
file_extension[1:].upper() + " para " + destino.upper() +
"\n\nSalvo em: " + out_file + "." + destino)
root.destroy()
def weeklyreport():
#create db connection
cnx = msql.connect(user='******',
password='******',
port='3305',
host='192.168.88.88',
database='fintronx_llc_live')
#store sql file
weeklysales = open(
r"C:\Users\dhumm\OneDrive\Documents\SQL_FB_Queries\weeklySalesReport.sql"
)
#run/read the sql file
wsales = pd.read_sql_query(weeklysales.read(), cnx)
#write to excel sheet and format columns and data accordingly, save and close book
new_file = pd.ExcelWriter(
'C:\\Users\\dhumm\\automate\\Weekly Sales For Week ' +
dt.date.today().strftime("%W") + '.xlsx',
datetime_format='mm/dd/yy')
wsales.to_excel(new_file,
sheet_name='Weekly Sales For Week ' +
dt.date.today().strftime("%W"),
index=False,
startrow=3)
wb = new_file.book
ws = new_file.sheets['Weekly Sales For Week ' +
dt.date.today().strftime("%W")]
ws.set_zoom(90)
center = wb.add_format({'align': 'center'})
money_fmt = wb.add_format({'num_format': '$#,##0.00', 'align': 'center'})
title_fmt = wb.add_format({
'bold': True,
'font_size': 20,
'align': 'center',
'valign': 'vcenter'
})
ws.merge_range('A1:E3', 'Fintronx Weekly Sales Report', title_fmt)
total_fmt = wb.add_format({
'bold': True,
'font_size': 14,
'align': 'center',
'bg_color': 'yellow',
'top': 1
})
total_amt_fmt = wb.add_format({
'bold': True,
'num_format': '$#,##0.00',
'font_size': 14,
'align': 'center',
'bg_color': 'yellow',
'top': 1
})
ws.merge_range('F2:G2', 'Date: ' + dt.date.today().strftime("%m/%d/%Y"))
ws.set_zoom(90)
ws.set_column('A:C', 20, center)
ws.set_column('D:E', 30, center)
ws.set_column('F:F', 20, money_fmt)
ws.set_column('G:G', 15, center)
ws.write_formula('F' + str(len(wsales) + 6),
'=sum(F5:F' + str(len(wsales) + 5) + ')', total_amt_fmt)
ws.write('E' + str(len(wsales) + 6), 'Total For The Week', total_fmt)
ws.set_landscape()
ws.center_horizontally()
ws.fit_to_pages(1, 0)
new_file.save()
new_file.close()
#open excel wb from above, save as pdf, close excel
xlapp = win32.DispatchEx('Excel.Application')
xlwb = xlapp.Workbooks.Open(
'C:\\Users\\dhumm\\automate\\Weekly Sales For Week ' +
dt.date.today().strftime("%W") + '.xlsx')
xlwb.ExportAsFixedFormat(
0, "C:\\Users\\dhumm\\automate\\Weekly Sales For Week " +
dt.date.today().strftime("%W") + ".pdf")
xlwb.Close(True)
#send email to desired receipients
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
#mail.To = '*****@*****.**'
mail.To = '''[email protected]; [email protected]; [email protected];
[email protected]; [email protected]; [email protected]; [email protected]; [email protected];
[email protected]; [email protected]; [email protected]'''
mail.Subject = 'Fintronx Weekly Sales Report'
mail.Body = 'See attached weekly sales report. Please let me know if you no longer wish to receive this email.'
#attach excel report to the email
attachment = r"C:\Users\dhumm\automate\Weekly Sales For Week " + dt.date.today(
).strftime("%W") + ".pdf"
mail.Attachments.Add(attachment)
#send email
mail.Send()
def pptTOPDF(PPT_name, pdf_name):
PPT = client.DispatchEx('PowerPoint.Application')
ppt = PPT.Presentations.Open(PPT_name)
ppt.ExportAsFixedFormat(pdf_name, 2, PrintRange=None)
ppt.Close()
PPT.Quit()
cnxn = pyodbc.connect(
"Driver={SQL Server Native Client 11.0};" #requires explicitily stating the sql driver
"Server=overlook;"
"Database=re_racc;"
"Trusted_Connection=yes;") #use windows integrated security
cursor = cnxn.cursor()
#note: this should be a stored procedure
#cursor.execute("select a.DESCRIPTION, a.FUND_ID, b.LONGDESCRIPTION as Category, d.CONSTITUENT_ID, d.ORG_NAME, e.num as Email, f.LONGDESCRIPTION as Type from FUND a join TABLEENTRIES b on (a.FUND_CATEGORY = b.TABLEENTRIESID) join FUND_ORG_RELATIONSHIPS c on (a.ID = c.FUND_ID) join RECORDS d on (c.CONSTIT_ID = d.ID) join PHONES e on (e.CONSTIT_ID = d.ID) join TABLEENTRIES f on (e.PHONETYPEID = f.TABLEENTRIESID) where b.LONGDESCRIPTION = 'Designated' and f.LONGDESCRIPTION like 'E-Mail%' and e.CONSTIT_RELATIONSHIPS_ID is null order by a.DESCRIPTION, f.LONGDESCRIPTION")
cursor.execute("sp_getdgfundemails")
data = [
] #grab results, put into a list, put list into numpy array, and then put numpy array into pandas dataframe
for row in cursor:
data.append(tuple(row))
contactlist = pd.DataFrame.from_records(np.array(data))
outlook = win32.DispatchEx('outlook.application')
#file paths
path_root = "C:\\Users\\skirkpatrick\\Coding\\Python\\"
path_input = path_root + "Outgoing\\"
path_output = path_root + "Processed\\"
path_errors = path_root + "Errors\\"
file_ACH = path_root + "RACC AUTHORIZATION FOR DIRECT DEPOSIT.pdf"
file_emailbody = path_root + "dg payment email.txt"
file_emailbody2 = path_root + "dg payment email - no ach.txt"
message_body = open(file_emailbody).read() #load message body from file
message_body2 = open(file_emailbody2).read() #load message body from file
#initialize lists
files_good = []
files_error = []
#email loop
for f in os.listdir(path_input):
def home(request):
"""
Get fee payment for a particular student and generate a fee slip.
Input: Class, Amount, Payment Method, Admission Number, Months paid for and fee slip template
Issue: Fee slip is generated but not exported to user in pdf.
"""
if request.method == 'POST':
classSection = request.POST.get("class_room")
payment_method = request.POST.get("payment_method")
amount = request.POST.get("amount")
reg_no = request.POST.get("register_number")
months = request.POST.get("months")
add_number = request.POST.get("add_number")
if "excel" in request.FILES:
fee_slip_excell = request.FILES["excel"]
if add_number and fee_slip_excell:
classRoom = ClassRoom.objects.get(classSection=classSection)
student = ClassRoomStudent.objects.get(
student__admissionNumber=add_number)
wb = load_workbook(fee_slip_excell)
sheet = wb.get_sheet_by_name('Sheet2')
# set slip no, name, class, date, months, class, amount
# 1st copy
sheet["B9"] = reg_no
sheet["B10"] = add_number
sheet["B12"] = student.student.admissionNumber
sheet["G10"] = date.today().strftime("%B %d, %Y")
sheet["G11"] = months
sheet["E15"] = amount
sheet["C20"] = amount
# 2nd copy
sheet["I9"] = reg_no
sheet["I10"] = add_number
sheet["I12"] = student.student.admissionNumber
sheet["N10"] = date.today().strftime("%B %d, %Y")
sheet["N11"] = months
sheet["L15"] = amount
sheet["J20"] = amount
# wb.save('fee-slip.xlsx')
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
Fee.objects.create(regNo=reg_no,
student=student,
classSection=classRoom,
submissionDate=date.today(),
monthsPaid=months,
payment_method=payment_method,
amount=amount)
fee = Fee.objects.get(student=student,
regNo=reg_no,
submissionDate=date.today())
fee.feeSlip.save("fee-slip", File(tmp), True)
fee.save()
try:
from win32com import client
import win32api
excel = client.DispatchEx("Excel.Application")
excel.Visible = 0
wb = excel.Workbooks.Open(tmp)
ws = wb.Worksheets[1]
try:
wb.SaveAs('c:\\targetfolder\\feeslip.pdf',
FileFormat=57)
except:
print("Failed to convert")
finally:
wb.Close()
excel.Quit()
except:
pass
return render(request, 'fees/home.html',
{"class_rooms": ClassRoom.objects.all()})
from win32com import client
word = client.DispatchEx('Excel.Application')
import os
from optparse import OptionParser
if __name__ == '__main__':
command = 'taskkill /F /IM et.exe'
os.system(command)
command = 'taskkill /F /IM excel.exe'
os.system(command)
parser = OptionParser(usage='%prog [options]')
parser.add_option('-i', '--in', dest='input', help='input file')
parser.add_option('-o', '--out', dest='output', help='output file')
(options, args) = parser.parse_args()
input = options.input
output = options.output
input = os.path.abspath(input)
output = os.path.abspath(output)
excel = client.DispatchEx("Excel.Application")
excel.Visible = 0
wb = excel.Workbooks.Open(Filename=input, ReadOnly=1)
ws = excel.Worksheets[0]
wb.ExportAsFixedFormat(0, str(output))
wb.Close()
excel.Quit()
def get(self):
# ----------------------------------------------------------------------------------------
# code to convert word to pdf
def count_files(filetype):
''' (str) -> int
Returns the number of files given a specified file type.
>>> count_files(".docx")
11
'''
count_files = 0
for files in listdir(folder):
if files.endswith(filetype):
count_files += 1
return count_files
# Function "check_path" is used to check whether the path the user provided does
# actually exist. The user is prompted for a path until the existence of the
# provided path has been verified.
def check_path(folder):
''' (str) -> str
Verifies if the provided absolute path does exist.
'''
abs_path = input(folder)
while path.exists(abs_path) != True:
print("\nThe specified path does not exist.\n")
abs_path = input(folder)
return abs_path
print("\n")
folder = "C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/resume/Reject cases"
# Change the directory.
chdir(folder)
# Count the number of docx and doc files in the specified folder.
num_docx = count_files(".docx")
num_doc = count_files(".doc")
# Check if the number of docx or doc files is equal to 0 (= there are no files
# to convert) and if so stop executing the script.
if num_docx + num_doc == 0:
print(
"\nThe specified folder does not contain docx or docs files.\n"
)
print(strftime("%H:%M:%S"),
"There are no files to convert. BYE, BYE!.")
else:
print("\nNumber of doc and docx files: ", num_docx + num_doc, "\n")
print(strftime("%H:%M:%S"), "Starting to convert files ...\n")
# Try to open win32com instance. If unsuccessful return an error message.
try:
pythoncom.CoInitialize()
word = client.DispatchEx("Word.Application")
for files in listdir(getcwd()):
if files.endswith(".docx"):
new_name = files.replace(".docx", r".pdf")
in_file = path.abspath(folder + "\\" + files)
new_file = path.abspath(folder + "\\" + new_name)
doc = word.Documents.Open(in_file)
print
strftime("%H:%M:%S"), " docx -> pdf ", path.relpath(
new_file)
doc.SaveAs(new_file, FileFormat=17)
doc.Close()
if files.endswith(".doc"):
new_name = files.replace(".doc", r".pdf")
in_file = path.abspath(folder + "\\" + files)
new_file = path.abspath(folder + "\\" + new_name)
doc = word.Documents.Open(in_file)
print
strftime("%H:%M:%S"), " doc -> pdf ", path.relpath(
new_file)
doc.SaveAs(new_file, FileFormat=17)
doc.Close()
except Exception as e:
print(e)
finally:
word.Quit()
print("\n", strftime("%H:%M:%S"), "Finished converting files.")
# Count the number of pdf files.
num_pdf = count_files(".pdf")
print("\nNumber of pdf files: ", num_pdf)
# Check if the number of docx and doc file is equal to the number of files.
if num_docx + num_doc == num_pdf:
print(
"\nNumber of doc and docx files is equal to number of pdf files."
)
else:
print(
"\nNumber of doc and docx files is not equal to number of pdf files."
)
# ----------------------------------------------------------------------------------------
dir_name = "C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/resume/Reject cases"
test = os.listdir(dir_name)
for item in test:
if item.endswith(".doc"):
os.remove(os.path.join(dir_name, item))
elif item.endswith(".docx"):
os.remove(os.path.join(dir_name, item))
else:
continue
# Function to read resumes from the folder one by one
mypath = 'C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/resume/Reject cases' # enter your path here where you saved the resumes
onlyfiles = [
os.path.join(mypath, f) for f in os.listdir(mypath)
if os.path.isfile(os.path.join(mypath, f))
]
def pdfextract(file):
fileReader = PyPDF2.PdfFileReader(open(file, 'rb'))
countpage = fileReader.getNumPages()
count = 0
text = []
while count < countpage:
pageObj = fileReader.getPage(count)
count += 1
t = pageObj.extractText()
print(t)
text.append(t)
return text
# function to read resume ends
# function that does phrase matching and builds a candidate profile
def create_profile(file):
text = pdfextract(file)
text = str(text)
text = text.replace("\\n", "")
text = text.lower()
# below is the csv where we have all the keywords, you can customize your own
keyword_dict = pd.read_csv(
'C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/csv_database/res_lower.csv'
)
dotnet_assi = [
nlp(text)
for text in keyword_dict['.NetAssisted'].dropna(axis=0)
]
dotnet_dire = [
nlp(text)
for text in keyword_dict['.NetDirectProducts'].dropna(axis=0)
]
dotnet_sql = [
nlp(text)
for text in keyword_dict['.NetSDLLeadDeveloper'].dropna(axis=0)
]
datastage_mode = [
nlp(text)
for text in keyword_dict['Datastage+DataModelling'].dropna(
axis=0)
]
datastage = [
nlp(text) for text in keyword_dict['Datastage'].dropna(axis=0)
]
datastage_flink = [
nlp(text)
for text in keyword_dict['Datastage+Flink'].dropna(axis=0)
]
fullstack = [
nlp(text)
for text in keyword_dict['FullstackJava'].dropna(axis=0)
]
java_sql = [
nlp(text)
for text in keyword_dict['Java+SDLLead'].dropna(axis=0)
]
java = [nlp(text) for text in keyword_dict['Java'].dropna(axis=0)]
java_backend = [
nlp(text)
for text in keyword_dict['JavaBackend'].dropna(axis=0)
]
java_frontend = [
nlp(text)
for text in keyword_dict['JavaFrontend'].dropna(axis=0)
]
devops = [
nlp(text) for text in keyword_dict['DevOps'].dropna(axis=0)
]
etl_dev = [
nlp(text)
for text in keyword_dict['ETLDeveloper'].dropna(axis=0)
]
lsa = [nlp(text) for text in keyword_dict['LSA'].dropna(axis=0)]
pega = [
nlp(text) for text in keyword_dict['PegaCSSA'].dropna(axis=0)
]
ops_eng = [
nlp(text)
for text in keyword_dict['OpsEngineer'].dropna(axis=0)
]
pentester = [
nlp(text) for text in keyword_dict['Pentester'].dropna(axis=0)
]
data_tester = [
nlp(text) for text in keyword_dict['DataTester'].dropna(axis=0)
]
datamodel_dev = [
nlp(text)
for text in keyword_dict['DataModeler_DevEngineer'].dropna(
axis=0)
]
datatester_dev = [
nlp(text)
for text in keyword_dict['DataTester_DevOps'].dropna(axis=0)
]
matcher = PhraseMatcher(nlp.vocab)
matcher.add('.NetAssisted', None, *dotnet_assi)
matcher.add('.NetDirectProducts', None, *dotnet_dire)
matcher.add('.NetSDLLeadDeveloper', None, *dotnet_sql)
matcher.add('Datastage+DataModelling', None, *datastage_mode)
matcher.add('Datastage', None, *datastage)
matcher.add('Datastage+Flink', None, *datastage_flink)
matcher.add('FullstackJava', None, *fullstack)
matcher.add('Java+SDLLead', None, *java_sql)
matcher.add('Java', None, *java)
matcher.add('JavaBackend', None, *java_backend)
matcher.add('JavaFrontend', None, *java_frontend)
matcher.add('DevOps', None, *devops)
matcher.add('ETLDeveloper', None, *etl_dev)
matcher.add('LSA', None, *lsa)
matcher.add('PegaCSSA', None, *pega)
matcher.add('OpsEngineer', None, *ops_eng)
matcher.add('Pentester', None, *pentester)
matcher.add('DataTester', None, *data_tester)
matcher.add('DataModeler_DevEngineer', None, *datamodel_dev)
matcher.add('DataTester_DevOps', None, *datatester_dev)
doc = nlp(text)
d = []
matches = matcher(doc)
for match_id, start, end in matches:
rule_id = nlp.vocab.strings[
match_id] # get the unicode ID, i.e. 'COLOR'
span = doc[start:end] # get the matched slice of the doc
d.append((rule_id, span.text))
keywords = "\n".join(f'{i[0]} {i[1]} ({j})'
for i, j in Counter(d).items())
## convertimg string of keywords to dataframe
df = pd.read_csv(StringIO(keywords), names=['Keywords_List'])
df1 = pd.DataFrame(df.Keywords_List.str.split(' ', 1).tolist(),
columns=['Subject', 'Keyword'])
df2 = pd.DataFrame(df1.Keyword.str.split('(', 1).tolist(),
columns=['Keyword', 'Count'])
df3 = pd.concat([df1['Subject'], df2['Keyword'], df2['Count']],
axis=1)
df3['Count'] = df3['Count'].apply(lambda x: x.rstrip(")"))
base = os.path.basename(file)
filename = os.path.splitext(base)[0]
name = filename.split('_')
name2 = name[0]
name2 = name2.lower()
## converting str to dataframe
name3 = pd.read_csv(StringIO(name2), names=['Candidate Name'])
dataf = pd.concat([
name3['Candidate Name'], df3['Subject'], df3['Keyword'],
df3['Count']
],
axis=1)
dataf['Candidate Name'].fillna(dataf['Candidate Name'].iloc[0],
inplace=True)
return (dataf)
# function ends
# code to execute/call the above functions
final_database = pd.DataFrame()
i = 0
while i < len(onlyfiles):
file = onlyfiles[i]
dat = create_profile(file)
final_database = final_database.append(dat)
i += 1
print(final_database)
# code to count words under each category and visulaize it through Matplotlib
final_database2 = final_database['Keyword'].groupby(
[final_database['Candidate Name'],
final_database['Subject']]).count().unstack()
final_database2.reset_index(inplace=True)
final_database2.fillna(0, inplace=True)
new_data = final_database2.iloc[:, 1:]
new_data.index = final_database2['Candidate Name']
#json=new_data.to_json()
# execute the below line if you want to see the candidate profile in a csv format
sample2 = new_data.to_csv(
'C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/sample/sample.csv'
)
import matplotlib.pyplot as plt
plt.rcParams.update({'font.size': 5})
ax = new_data.plot.barh(title="Resume keywords by category",
legend=True,
figsize=(25, 7),
stacked=True)
labels = []
for j in new_data.columns:
for i in new_data.index:
label = str(int(new_data.loc[i][j]))
labels.append(label)
patches = ax.patches
for label, rect in zip(labels, patches):
width = rect.get_width()
if width > 0:
x = rect.get_x()
y = rect.get_y()
height = rect.get_height()
ax.text(x + width / 2.,
y + height / 2.,
label,
ha='center',
va='center')
plt.savefig(
"C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/graph/graph.png"
)
plt.savefig(
"C:/Users/manikotarajas.tumul/HCL Technologies Ltd/MYCO - check/graph/graph.pdf"
)
return 200
from win32com import client
import win32api
input_file = r'C:\Users\Apple\projects\pdfplus\pdfplus\xcl.xlsx'
#give your file name with valid path
output_file = r'C:\Users\Apple\projects\pdfplus\pdfplus\xcl.pdf'
#give valid output file name and path
app = client.DispatchEx("Excel.Application")
app.Interactive = False
app.Visible = False
Workbook = app.Workbooks.Open(input_file)
try:
Workbook.ActiveSheet.ExportAsFixedFormat(0, output_file)
except Exception as e:
print(
"Failed to convert in PDF format.Please confirm environment meets all the requirements and try again"
)
print(str(e))
finally:
Workbook.Close()
app.Exit()
if not os.path.exists(out_folder):
print('Creating output folder...')
os.makedirs(out_folder)
print(out_folder, 'created.')
else:
print(out_folder, 'already exists.\n')
for files in os.listdir("."):
if files.endswith(".docx"):
print(files)
print('\n\n')
try:
word = client.DispatchEx(
"Word.Application"
) # Using DispatchEx for an entirely new Word instance
word.Visible = True # Added this in here so you can see what I'm talking about with the movement of the dispatch and Quit lines.
for files in os.listdir("."):
if files.endswith(".docx"):
out_name = files.replace(file_type, r"pdf")
in_file = os.path.abspath(folder + "\\" + files)
out_file = os.path.abspath(out_folder + "\\" + out_name)
doc = word.Documents.Open(in_file)
print('Exporting', out_file)
doc.SaveAs(out_file, FileFormat=17)
doc.Close()
word.Quit()
except (Exception, e):
import os
import sys
from win32com import client as wc
w = wc.Dispatch('Word.Application')
w = wc.DispatchEx('Word.Application')
file = "C:\\Users\sadscv\PycharmProjects\gadgets\tableExtractor\docs\cs.doc"
doc=w.Documents.Open(file)
doc.SaveAs(file[:-3]+"docx",16)
import win32com.client as win32
import os
cwd = os.getcwd()
excel = win32.DispatchEx("Excel.Application")
excel.Visible = True
workbook = excel.Workbooks.Open(cwd + '/test_xls.xlsx')
worksheet1 = workbook.Worksheets[0]
worksheet1.Name = "My sheet1" # change its name
worksheet1.Cells(1, 1).Value = "Hello, Excel" # change the cell value
print(worksheet1.Cells(1, 1).Value)
for i in range(1, 5):
worksheet1.Cells(2, i).Value = i
worksheet1.Range(worksheet1.Cells(3, 1), worksheet1.Cells(3, 4)).Value = [5, 6, 7, 8]
worksheet1.Range('A4:D4').Value = [i for i in range(9, 13)]
worksheet1.Cells(5, 4).Formula = '=SUM(A2:A4)'
worksheet1.Cells(5, 4).Font.Size = 16
worksheet1.Cells(5, 4).Font.Bold = True
# FileFormat=6, csv format, chech others
print(cwd[0:2] + '/myexcel.xlsx')
workbook.SaveAs(cwd + '/myexcel.xlsx') # won't return until yes/no dialog is clicked if file exists already
workbook.Close(SaveChanges=0)
excel.Quit()
import win32com.client as win32
# There is only one instance
excel_a = win32.Dispatch("Excel.Application")
excel_b = win32.Dispatch("Excel.Application")
excel_a.Visible = True
excel_b.Visible = True
excel_a.Quit()
excel_b.Quit()
# This is the way to get two instances, use ...Ex version
excel_a = win32.DispatchEx("Excel.Application")
excel_b = win32.DispatchEx("Excel.Application")
# now check to see whether we get two windows
excel_a.Visible = True
excel_b.Visible = True
print(excel_a.__module__)
print(excel_b.__module__)
print("two excel windows")
input("Press ENTER to exit")
# use ftype command to check the association
# if you don't see two windows. Check this:
# Windows Explorer --> Tools -->Options --> File Types
# find xls/xlsx, select Advanced button, click Open in the action window,
# then click edit button, add "%1" (with quotes) to the end of the second
def report_card(request):
"""
Report Card for stduent by searching admission number
Issue: Formating of excell sheet get's overlapped
"""
if request.method == "POST":
class_room = request.POST.get("class_room")
pk = request.POST.get("add_number")
if pk and class_room:
class_room_student = ClassRoomStudent.objects.get(
student__admissionNumber=pk)
additional_sub = AdditionalSubjectMapping.objects.filter(
classroomStudent=class_room_student)[0].subject
class_room = class_room
marks = Marks.objects.filter(classroomStudent=class_room_student)
subjects = []
exam_mapping = ExamMapping.objects.filter(classroom=class_room)
# get subjects
for a in exam_mapping:
if a.subject not in subjects:
subjects.append(a.subject)
subjects.sort()
# get marks for unit test, note-book, half-yearly, SEA exam
ut_1_marks_list = []
exam_name = Exam.objects.get(examName='UT-1')
marks_ut_1 = marks.filter(examName=exam_name,
classRoomStudent=class_room_student)
for sub in subjects:
ut_1_marks_list.append(marks_ut_1.get(subject=sub).marks)
ut_1_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
ut_2_marks_list = []
exam_name = Exam.objects.get(examName='UT-2')
marks_ut_2 = marks.filter(examName=exam_name,
classRoomStudent=class_room_student)
for sub in subjects:
ut_2_marks_list.append(marks_ut_2.get(subject=sub).marks)
ut_2_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
annual_term_marks_list = []
exam_name = Exam.objects.get(examName='Annual')
marks_annual_term = marks.filter(
examName=exam_name, classRoomStudent=class_room_student)
for sub in subjects:
annual_term_marks_list.append(
marks_annual_term.get(subject=sub).marks)
annual_term_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
half_yearly_marks_list = []
exam_name = Exam.objects.get(examName='Half-Yearly')
marks_half_yearly = marks.filter(
examName=exam_name, classRoomStudent=class_room_student)
for sub in subjects:
half_yearly_marks_list.append(
marks_half_yearly.get(subject=sub).marks)
half_yearly_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
sea1_term_marks_list = []
exam_name = Exam.objects.get(examName='SEA-1')
marks_sea1_term = marks.filter(examName=exam_name,
classRoomStudent=class_room_student)
for sub in subjects:
sea1_term_marks_list.append(
marks_sea1_term.get(subject=sub).marks)
sea1_term_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
sea2_term_marks_list = []
exam_name = Exam.objects.get(examName='SEA-2')
marks_sea2_term = marks.filter(examName=exam_name,
classRoomStudent=class_room_student)
for sub in subjects:
sea2_term_marks_list.append(
marks_sea2_term.get(subject=sub).marks)
sea2_term_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
notebook_1_marks_list = []
exam_name = Exam.objects.get(examName='NoteBook-1')
marks_notebook_1 = marks.filter(
examName=exam_name, classRoomStudent=class_room_student)
for sub in subjects:
notebook_1_marks_list.append(
marks_notebook_1.get(subject=sub).marks)
notebook_1_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
notebook_2_marks_list = []
exam_name = Exam.objects.get(examName='NoteBook-2')
marks_notebook_2 = marks.filter(
examName=exam_name, classRoomStudent=class_room_student)
for sub in subjects:
notebook_2_marks_list.append(
marks_notebook_2.get(subject=sub).marks)
notebook_2_marks_list.append(
AdditionalSubjectMapping.objects.get(
subject=additional_sub,
classroomStudent=class_room_student,
examName=exam_name).marks)
subjects.append(additional_sub)
if "excel" in request.FILES:
excel_file = request.FILES["excel"]
wb = load_workbook(excel_file)
sheet = wb.get_sheet_by_name('ANNUAL')
# set name, parents name, dob, addmission number, class, attendence
sheet["B9"] = class_room_student.student.fullName
sheet["B11"] = class_room_student.student.admissionNumber
sheet["B10"] = class_room_student.student.parent.fatherName
sheet["L10"] = class_room_student.student.parent.motherName
sheet["K9"] = class_room_student.roll_number
sheet["B12"] = class_room_student.student.dob
sheet["G9"] = class_room_student.classRoom.classSection
# get total attendence data
total_days = StudentAttendence.objects.filter(
student=class_room_student).count()
present_days = StudentAttendence.objects.filter(
student=class_room_student, status="present").count()
sheet["L11"] = present_days
sheet["N11"] = total_days
for i in range(len(subjects)):
sheet[f'A{i+17}'].value = subjects[i]
sheet[f'B{i+17}'].value = ut_1_marks_list[i]
sheet[f'C{i+17}'].value = sea1_term_marks_list[i]
sheet[f'D{i+17}'].value = half_yearly_marks_list[i]
sheet[f'G{i+17}'].value = subjects[i]
sheet[f'H{i+17}'].value = ut_2_marks_list[i]
sheet[f'I{i+17}'].value = sea2_term_marks_list[i]
sheet[f'J{i+17}'].value = annual_term_marks_list[i]
sheet.delete_rows(16 + len(subjects), 9 - len(subjects))
# wb.save('report-card.xlsx')
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
report_card = ReportCard.objects.create(
class_room_student=class_room_student)
report_card.reportCard.save("reportcard", File(tmp), True)
report_card.save()
try:
from win32com import client
import win32api
excel = client.DispatchEx("Excel.Application")
excel.Visible = 0
wb = excel.Workbooks.Open(tmp)
ws = wb.Worksheets[1]
try:
wb.SaveAs('c:\\targetfolder\\feeslip.pdf',
FileFormat=57)
except:
print("Failed to convert")
finally:
wb.Close()
excel.Quit()
except:
pass
return render(request, 'marks/reportCard.html')
