"""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')