import requests
import xlsxwriter
import xlsxwriter
import xlrd
import os
ticket = xlsxwriter.Workbook("output.xlsx")
worksheet1=ticket.add_worksheet("Sheet 1")
wb = xlrd.open_workbook("Siebel Ticket List new.xlsx")#input file
sh = wb.sheet_by_index(0)
wb1 = xlrd.open_workbook("lib.xlsx")#lib file
sh1 = wb1.sheet_by_index(0)

worksheet1.write(0,11,"Keyword1")
worksheet1.write(0,12,"Keyword2")
worksheet1.write(0,13,"Outcome")
worksheet1.write(0,14,"Business Impact")
worksheet1.write(0,15,"Urgency")
rownumber=3
rownumber1=0
try:
    while sh.cell(rownumber,1).value != 0:
        AA=sh.cell(rownumber,1).value
        BB=sh.cell(rownumber,2).value
        CC=sh.cell(rownumber,3).value
        DD=sh.cell(rownumber,4).value
        EE=sh.cell(rownumber,5).value
        FF=sh.cell(rownumber,6).value
        GG=sh.cell(rownumber,7).value
        HH=sh.cell(rownumber,8).value
        II=sh.cell(rownumber,9).value
        JJ=sh.cell(rownumber,10).value
        KK=sh.cell(rownumber,11).value
        worksheet1.write(rownumber1,0,AA)
        worksheet1.write(rownumber1,1,BB)
        worksheet1.write(rownumber1,2,CC)
        worksheet1.write(rownumber1,3,DD)
        worksheet1.write(rownumber1,4,EE)
        worksheet1.write(rownumber1,5,FF)
        worksheet1.write(rownumber1,6,GG)
        worksheet1.write(rownumber1,7,HH)
        worksheet1.write(rownumber1,8,II)
        worksheet1.write(rownumber1,9,JJ)
        worksheet1.write(rownumber1,10,KK)
        rownumber=rownumber+1
        rownumber1=rownumber1+1
except:
    pass
n=sh1.nrows#lib
m=sh.nrows#data
for n1 in range(n):
    a1= sh1.cell(n1,0).value
    a1=str(a1).encode("utf-8").lower()
    a1a1= sh1.cell(n1,1).value
    a1a1=str(a1a1).encode("utf-8").lower()
    b1= sh1.cell(n1,2).value
    b1=str(b1).lower()
    d1= sh1.cell(n1,3).value
    d1=str(d1).lower()
    e1= sh1.cell(n1,4).value
    e1=str(e1).lower()
    for m1 in range(m):
        c1= str(sh.cell(m1,6)).encode("utf-8").lower()
        c1c1= str(sh.cell(m1,10)).encode("utf-8").lower()
        if (a1 or a1a1) in (c1 or c1c1):
            m2=m1-3
            worksheet1.write(m2,11,a1)
            worksheet1.write(m2,12,a1a1)
            worksheet1.write(m2,13,b1)
            worksheet1.write(m2,14,d1)
            worksheet1.write(m2,15,e1)
ticket.close()