def populateCampaignRevenue10(wb): sh = wb.sheet_by_name('Rev10') for rownum in range(3,881): #sh.nrows): t = sh.cell(rownum,2).value product = get_or_create(db.session, Product, product = sh.cell(rownum,3).value) cp = sh.cell(rownum,4).value channel = get_or_create(db.session, Channel, channel = sh.cell(rownum,5).value) agency = sh.cell(rownum,16).value advertiser = get_or_create(db.session, Advertiser, advertiser = sh.cell(rownum,20).value) campaign = sh.cell(rownum,21).value industry = sh.cell(rownum,15).value if(industry == '(blank)'): industry = None repid = sh.cell(rownum,22).value if(repid == "VB"): repid = "VV" rep = get_or_create(db.session, Rep, repID = repid) try: start_date = xldate_as_tuple(sh.cell(rownum,24).value,0)[0:3] py_start = date(*start_date) end = xldate_as_tuple(sh.cell(rownum,25).value,0)[0:3] py_end = date(*end) except: pass contracted_deal = sh.cell(rownum,28).value if not isinstance(contracted_deal, float): contracted_deal = None revised_deal = sh.cell(rownum,28).value if not isinstance(revised_deal, float): revised_deal = None # For multiple reps: instance = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first() if instance: instance.rep.append(rep) db.session.commit() c = instance else: c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser, industry = industry, agency = agency, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, contracted_deal = contracted_deal, revised_deal = revised_deal) #print(campaign) db.session.add(c) db.session.commit() #campaignObj = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first() for colnum in range(37,61): rev = sh.cell(rownum,colnum).value if isinstance(rev,float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(2,colnum).value,0)[0:3] pyDate = date(*mydate) a = Actual(campaign=c, date=pyDate, actualRev=rev) db.session.add(a) db.session.commit() print("PopulateCampaignRevenue10 Finished")
def load_data(): # Create and fill Tables wb = xlrd.open_workbook( 'C:/Users/rthomas/Desktop/DatabaseProject/SalesMetricData.xls') wb.sheet_names() # Fill revenue tables sh = wb.sheet_by_name('Actual') for rownum in range(2, sh.nrows): camp_str = sh.cell(rownum, 0).value rep = Rep.objects.get(repID=sh.cell(rownum, 1).value) product = Product.objects.get(product=sh.cell(rownum, 2).value) channel = Channel.objects.get(channel=sh.cell(rownum, 3).value) advertiser = Advertiser.objects.get( advertiser=sh.cell(rownum, 4).value) start = xldate_as_tuple(sh.cell(rownum, 5).value, 0)[0:3] py_start = datetime.date(*start) end = xldate_as_tuple(sh.cell(rownum, 6).value, 0)[0:3] py_end = datetime.date(*end) try: campaign = Campaign.objects.get(campaign=camp_str) for colnum in range(7, sh.ncols): rev = sh.cell(rownum, colnum).value if isinstance(rev, float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(0, colnum).value, 0)[0:3] pyDate = datetime.date(*mydate) a = Actual(campaign=campaign, date=pyDate, actualRev=rev) a.save() except: try: campaign = Campaign.objects.get(campaign=camp_str, repId=rep, channel=channel, product=product, advertiser=advertiser, start_date=py_start, end_date=py_end) for colnum in range(7, sh.ncols): rev = sh.cell(rownum, colnum).value if isinstance(rev, float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(0, colnum).value, 0)[0:3] pyDate = datetime.date(*mydate) a = Actual(campaign=campaign, date=pyDate, actualRev=rev) a.save() except: pass
def readSFDCexcel(): s = db.session s.query(Sfdc).delete() wb = xlrd.open_workbook('C:/Users/rthomas/Desktop/DatabaseProject/SFDC OID Report 1-7-13.xls') sh = wb.sheet_by_index(0) for colnum in range(0, sh.ncols): colname = sh.cell(0, colnum).value if re.search('Opportunity ID', colname): oid_index = colnum if re.search('Channel',colname): channel_index = colnum if re.search('Pricing Model', colname): cp_index = colnum if re.search('Advertiser',colname): advertiser_index = colnum if re.search('Opportunity Owner',colname): rep_index = colnum if re.search('Start Date',colname): start_index = colnum if re.search('End Date',colname): end_index = colnum if re.search('Budget$',colname): budget_index = colnum if re.search('Insertion Order',colname): ioname_index = colnum if re.search('Currency',colname): currency_index = colnum for rownum in range(1,sh.nrows): oid_val = sh.cell(rownum, oid_index).value if oid_val is None or oid_val =='': break else: oid = int(oid_val) channel = sh.cell(rownum, channel_index).value cp = sh.cell(rownum, cp_index).value advertiser = sh.cell(rownum, advertiser_index).value rep_name_temp = sh.cell(rownum, rep_index).value last = re.search('[A-Z][a-z]*$', rep_name_temp) first = re.search('^[A-Z][a-z]*', rep_name_temp) rep_name = last.group() + ", " + first.group() start = xldate_as_tuple(sh.cell(rownum,start_index).value,0)[0:3] py_start = date(*start) + timedelta(days=1) end = xldate_as_tuple(sh.cell(rownum,end_index).value,0)[0:3] py_end = date(*end) ioname = sh.cell(rownum, ioname_index).value budget_val = sh.cell(rownum, budget_index).value currency = sh.cell(rownum, currency_index).value if budget_val == '': budget_val = None a = Sfdc(oid=oid, channel=channel, cp=cp, advertiser=advertiser, owner_name = rep_name, start_date = py_start, end_date = py_end, budget = budget_val, ioname=ioname, currency=currency, approved = False) s.add(a) s.commit()
def __load(self): sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id] sheetData = sheetDoc.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData") # @type sheetData Element rows = {} columns = {} for rowNode in sheetData: rowNum = int(rowNode.get("r")) for columnNode in rowNode: colType = columnNode.get("t") cellId = columnNode.get("r") cellS = columnNode.get("s") colNum = cellId[:len(cellId)-len(str(rowNum))] formula = None data = '' try: if columnNode.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v') is not None: if colType == "s": stringIndex = columnNode[0].text data = self.workbook.sharedStrings[int(stringIndex)] #Built in date-formatted fields elif cellS and int(self.workbook.cellStyles[int(cellS)].get('numFmtId')) in range(14, 22+1): data = xldate_as_tuple( float(columnNode[0].text), datemode=0) elif cellS and (self.workbook.cellStyles[int(cellS)].get('numFmtId') in self.workbook.numFmts) \ and is_date_format_string(self.workbook.numFmts[self.workbook.cellStyles[int(cellS)].get('numFmtId')]): data = xldate_as_tuple( float(columnNode[0].text), datemode=0) elif len(columnNode)>0 and columnNode[0] is not None: data = columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v").text if columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f") is not None: formula = columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f").text except Exception: raise #pass if not rowNum in rows: rows[rowNum] = [] if not colNum in columns: columns[colNum] = [] cell = Cell(rowNum, colNum, data, formula=formula) rows[rowNum].append(cell) columns[colNum].append(cell) self.__cells[cellId] = cell self.__rows = rows self.__cols = columns self.loaded=True
def get_date_or_none(entry): my_date = None if isinstance(entry, float): try: date_tuple = xldate_as_tuple(entry, 0)[0:3] my_date = date(*date_tuple) except: my_date = None return my_date
def load_data(): # Create and fill Tables wb = xlrd.open_workbook('C:/Users/rthomas/Desktop/DatabaseProject/SalesMetricData.xls') wb.sheet_names() # Fill revenue tables sh = wb.sheet_by_name('Actual') for rownum in range(2,sh.nrows): camp_str = sh.cell(rownum,0).value rep = Rep.objects.get(repID = sh.cell(rownum,1).value) product = Product.objects.get(product = sh.cell(rownum,2).value) channel = Channel.objects.get(channel = sh.cell(rownum,3).value) advertiser = Advertiser.objects.get(advertiser = sh.cell(rownum,4).value) start = xldate_as_tuple(sh.cell(rownum,5).value,0)[0:3] py_start = datetime.date(*start) end = xldate_as_tuple(sh.cell(rownum,6).value,0)[0:3] py_end = datetime.date(*end) try: campaign = Campaign.objects.get(campaign = camp_str) for colnum in range(7,sh.ncols): rev = sh.cell(rownum,colnum).value if isinstance(rev,float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(0,colnum).value,0)[0:3] pyDate = datetime.date(*mydate) a = Actual(campaign=campaign, date=pyDate, actualRev=rev) a.save() except: try: campaign = Campaign.objects.get(campaign = camp_str, repId = rep, channel=channel, product = product, advertiser = advertiser, start_date = py_start, end_date = py_end) for colnum in range(7,sh.ncols): rev = sh.cell(rownum,colnum).value if isinstance(rev,float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(0,colnum).value,0)[0:3] pyDate = datetime.date(*mydate) a = Actual(campaign=campaign, date=pyDate, actualRev=rev) a.save() except: pass
def _end_element(self, name): #print "end element:", name if name == 'sheetData': self.is_sheetdata = False elif self.is_sheetdata and name == 'row': self.row_count += 1 self.document.row_event(self.current_row) self.current_row = None elif name == 'c': c = self.cell self.parse_rel(c) if c[self.TYPE] == self.TYPE_SHARED_STRING: idx = int(self.data, 10) c[self.VALUE] = self.shared_strings[idx] else: c[self.VALUE] = self.data #fmt = self.styles.numberFormat(c[self.STYLE_IDX]) #print "cell format is:", str(fmt['numFmt']), c[self.COLUMN], c[self.VALUE] c[self.STYLE] = self.styles.cell_style(c[self.STYLE_IDX]) c[self.FMT] = cellType = self.styles.cell_type_from_style( c[self.STYLE]) v = c[self.VALUE] if (v is not None) and (c[self.FMT] in (datetime.datetime, datetime.date, datetime.time)): try: d = xldate.xldate_as_tuple(float(v), 0) c[self.VALUE] = cellType(*d) except (xldate.XLDateAmbiguous, ValueError), e: if v == 1.0: print "value 1.0 for date:", c c[self.VALUE] = '' else: print "Invalid date, assume text or number content:", c[ self.VALUE] if re.match(r'^\d+$', v): c[self.TYPE] = cellType = int elif re.match(r'^\d+\.\d+$', v): c[self.TYPE] = cellType = float else: c[self.TYPE] = cellType = unicode c[self.VALUE] = cellType(v) else: if v is None: c[self.VALUE] = '' elif not cellType is unicode: try: c[self.VALUE] = cellType(v) except TypeError, e: print repr(c) print str(e), "value:", repr(v) raise e
def _end_element(self, name): #print "end element:", name if name == 'sheetData': self.is_sheetdata = False elif self.is_sheetdata and name == 'row': self.row_count += 1 self.document.row_event(self.current_row) self.current_row = None elif name == 'c': c = self.cell self.parse_rel(c) if c[self.TYPE] == self.TYPE_SHARED_STRING: idx = int(self.data, 10) c[self.VALUE] = self.shared_strings[idx] else: c[self.VALUE] = self.data #fmt = self.styles.numberFormat(c[self.STYLE_IDX]) #print "cell format is:", str(fmt['numFmt']), c[self.COLUMN], c[self.VALUE] c[self.STYLE] = self.styles.cell_style(c[self.STYLE_IDX]) c[self.FMT] = cellType = self.styles.cell_type_from_style(c[self.STYLE]) v = c[self.VALUE] if (v is not None) and (c[self.FMT] in (datetime.datetime, datetime.date, datetime.time)): try: d = xldate.xldate_as_tuple(float(v), 0) c[self.VALUE] = cellType(*d) except (xldate.XLDateAmbiguous, ValueError), e: if v == 1.0: print "value 1.0 for date:", c c[self.VALUE] = '' else: print "Invalid date, assume text or number content:", c[self.VALUE] if re.match(r'^\d+$', v): c[self.TYPE] = cellType = int elif re.match(r'^\d+\.\d+$', v): c[self.TYPE] = cellType = float else: c[self.TYPE] = cellType = unicode c[self.VALUE] = cellType(v) else: if v is None: c[self.VALUE] = '' elif not cellType is unicode: try: c[self.VALUE] = cellType(v) except TypeError, e: print repr(c) print str(e), "value:", repr(v) raise e
def __load(self): sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id] sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0] # @type sheetData Element rows = {} columns = {} for rowNode in sheetData.childNodes: rowNum = int(rowNode.getAttribute("r")) for columnNode in rowNode.childNodes: colType = columnNode.getAttribute("t") cellId = columnNode.getAttribute("r") cellS = columnNode.getAttribute("s") colName = cellId[:len(cellId)-len(str(rowNum))] colOffset = columnOffset(colName) formula = None data = '' try: if colType == "s": stringIndex = columnNode.firstChild.firstChild.nodeValue data = self.workbook.sharedStrings[int(stringIndex)] #Date field elif cellS in ('1', '2', '3', '4') and colType == "n": data = xldate_as_tuple( int(columnNode.firstChild.firstChild.nodeValue), datemode=0) elif columnNode.firstChild: data = getattr( columnNode.getElementsByTagName("v")[0].firstChild, "nodeValue", None) if columnNode.getElementsByTagName("f"): formula = getattr( columnNode.getElementsByTagName("f")[0].firstChild, "nodeValue", None) except Exception: pass if not rowNum in rows: rows[rowNum] = [] if not colName in columns: columns[colName] = [] cell = Cell(rowNum, colName, data, formula=formula) row = rows[rowNum] while len(row) < colOffset - 1: row.append(Cell(rowNum, colName, None)) row.append(cell) columns[colName].append(cell) self.__cells[cellId] = cell self.__rows = rows self.__cols = columns self.loaded=True
def __load(self): sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id] sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0] # @type sheetData Element rows = {} columns = {} for rowNode in sheetData.childNodes: rowNum = int(rowNode.getAttribute("r")) for columnNode in rowNode.childNodes: colType = columnNode.getAttribute("t") cellId = columnNode.getAttribute("r") cellS = columnNode.getAttribute("s") colNum = cellId[:len(cellId) - len(str(rowNum))] formula = None data = '' try: if colType == "s": stringIndex = columnNode.firstChild.firstChild.nodeValue data = self.workbook.sharedStrings[int(stringIndex)] #Date field elif cellS in ('1', '2', '3', '4') and colType == "n": data = xldate_as_tuple(int( columnNode.firstChild.firstChild.nodeValue), datemode=0) elif columnNode.firstChild: data = getattr( columnNode.getElementsByTagName("v")[0].firstChild, "nodeValue", None) if columnNode.getElementsByTagName("f"): formula = getattr( columnNode.getElementsByTagName("f")[0].firstChild, "nodeValue", None) except Exception: pass if not rowNum in rows: rows[rowNum] = [] if not colNum in columns: columns[colNum] = [] cell = Cell(rowNum, colNum, data, formula=formula) rows[rowNum].append(cell) columns[colNum].append(cell) self.__cells[cellId] = cell self.__rows = rows self.__cols = columns self.loaded = True
def populateCampaignRevenue(wb): sh = wb.sheet_by_name("Rev041813_585") for rownum in range(2, 6198): # sh.nrows): campaign = sh.cell(rownum, 13).value print(campaign) date_created = D.now() t = sh.cell(rownum, 3).value product = get_or_create(db.session, Product, product=sh.cell(rownum, 4).value) chan = sh.cell(rownum, 5).value if chan == "MSLAL": chan = "Publisher" channel = get_or_create(db.session, Channel, channel=chan) try: advertiser = db.session.query(Advertiser).filter_by(advertiser=sh.cell(rownum, 6).value).first() except: advertiser = None print(sh.cell(rownum, 6).value + " not found") industry = sh.cell(rownum, 8).value agency = sh.cell(rownum, 9).value sfdc_oid = sh.cell(rownum, 10).value if not isinstance(sfdc_oid, float): sfdc_oid = None else: sfdc_oid = int(sfdc_oid) repid = sh.cell(rownum, 14).value if repid == "VB": repid = "VV" rep = get_or_create(db.session, Rep, repID=repid) cp = sh.cell(rownum, 15).value try: start_date = xldate_as_tuple(sh.cell(rownum, 16).value, 0)[0:3] py_start = date(*start_date) end = xldate_as_tuple(sh.cell(rownum, 17).value, 0)[0:3] py_end = date(*end) except: pass cpm_price = sh.cell(rownum, 19).value if not isinstance(cpm_price, float): cpm_price = None contracted_impr = sh.cell(rownum, 20).value if isinstance(contracted_impr, float): contracted_impr = int(contracted_impr) else: contracted_impr = None contracted_deal = sh.cell(rownum, 21).value if not isinstance(contracted_deal, float): contracted_deal = None revised_deal = sh.cell(rownum, 23).value if not isinstance(revised_deal, float): revised_deal = None # For multiple reps: camp_instance = ( db.session.query(Campaign).filter_by(campaign=campaign, start_date=py_start, end_date=py_end).first() ) if camp_instance: camp_instance.rep.append(rep) db.session.commit() c = camp_instance else: if sfdc_oid == 11919: camp_instance = db.session.query(Campaign).filter_by(sfdc_oid=11919).first() if camp_instance: c = camp_instance camp_instance.rep.append(rep) db.session.commit() else: c = Campaign( campaign=campaign, date_created=date_created, type=t, product=product, channel=channel, advertiser=advertiser, industry=industry, agency=agency, sfdc_oid=sfdc_oid, rep=[rep], cp=cp, start_date=py_start, end_date=py_end, cpm_price=cpm_price, contracted_impr=contracted_impr, contracted_deal=contracted_deal, revised_deal=revised_deal, ) db.session.add(c) db.session.commit() cc = Campaignchange( campaign=c, change_date=D.now(), start_date=py_start, end_date=py_end, cpm_price=cpm_price, revised_deal=revised_deal, ) db.session.add(cc) db.session.commit() else: c = Campaign( campaign=campaign, date_created=date_created, type=t, product=product, channel=channel, advertiser=advertiser, industry=industry, agency=agency, sfdc_oid=sfdc_oid, rep=[rep], cp=cp, start_date=py_start, end_date=py_end, cpm_price=cpm_price, contracted_impr=contracted_impr, contracted_deal=contracted_deal, revised_deal=revised_deal, ) db.session.add(c) db.session.commit() cc = Campaignchange( campaign=c, change_date=date_created, start_date=py_start, end_date=py_end, cpm_price=cpm_price, revised_deal=revised_deal, ) db.session.add(cc) db.session.commit() for colnum in chain(xrange(26, 38), xrange(56, 68), xrange(88, 100)): rev = sh.cell(rownum, colnum).value if isinstance(rev, float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(1, colnum).value, 0)[0:3] pyDate = date(*mydate) book_instance = db.session.query(Booked).filter_by(campaign=c, date=pyDate).first() if book_instance: rev = book_instance.bookedRev + rev book_instance.bookedRev = rev else: a = Booked(campaign=c, date_created=date_created, date=pyDate, bookedRev=rev) db.session.add(a) aa = Bookedchange(campaign=c, change_date=date_created, date=pyDate, bookedRev=rev) db.session.add(aa) db.session.commit() for colnum in chain(xrange(41, 53), xrange(73, 85), xrange(103, 115)): rev = sh.cell(rownum, colnum).value if isinstance(rev, float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(1, colnum).value, 0)[0:3] pyDate = date(*mydate) actual_instance = db.session.query(Actual).filter_by(campaign=c, date=pyDate).first() if actual_instance: rev = actual_instance.actualRev + rev actual_instance.actualRev = rev else: a = Actual(campaign=c, date_created=date_created, date=pyDate, actualRev=rev) db.session.add(a) # For CPM, we need to add if cp == "CPM" and pyDate.year == 2013: b = Booked(campaign=c, date_created=date_created, date=pyDate, bookedRev=rev) db.session.add(b) bb = Bookedchange(campaign=c, change_date=date_created, date=pyDate, bookedRev=rev) db.session.add(bb) aa = Actualchange(campaign=c, change_date=date_created, date=pyDate, actualRev=rev) db.session.add(aa) db.session.commit() print("PopulateRevenue Finished")
def populateCampaignRevenue10(wb): sh = wb.sheet_by_name("Rev10") for rownum in range(3, 881): # sh.nrows): date_created = D.now() t = sh.cell(rownum, 2).value product = get_or_create(db.session, Product, product=sh.cell(rownum, 3).value) cp = sh.cell(rownum, 4).value channel = get_or_create(db.session, Channel, channel=sh.cell(rownum, 5).value) agency = sh.cell(rownum, 16).value try: advertiser = db.session.query(Advertiser).filter_by(advertiser=sh.cell(rownum, 20).value).first() except: advertiser = None print(sh.cell(rownum, 6).value + " not found") campaign = sh.cell(rownum, 21).value industry = sh.cell(rownum, 15).value if industry == "(blank)": industry = None repid = sh.cell(rownum, 22).value if repid == "VB": repid = "VV" rep = get_or_create(db.session, Rep, repID=repid) try: start_date = xldate_as_tuple(sh.cell(rownum, 24).value, 0)[0:3] py_start = date(*start_date) end = xldate_as_tuple(sh.cell(rownum, 25).value, 0)[0:3] py_end = date(*end) except: pass contracted_deal = sh.cell(rownum, 28).value if not isinstance(contracted_deal, float): contracted_deal = None revised_deal = sh.cell(rownum, 28).value if not isinstance(revised_deal, float): revised_deal = None # For multiple reps: instance = db.session.query(Campaign).filter_by(campaign=campaign, start_date=py_start, end_date=py_end).first() if instance: instance.rep.append(rep) db.session.commit() c = instance else: c = Campaign( campaign=campaign, date_created=date_created, type=t, product=product, channel=channel, advertiser=advertiser, industry=industry, agency=agency, rep=[rep], cp=cp, start_date=py_start, end_date=py_end, contracted_deal=contracted_deal, revised_deal=revised_deal, ) db.session.add(c) db.session.commit() cc = Campaignchange( campaign=c, change_date=D.now(), start_date=py_start, end_date=py_end, revised_deal=revised_deal ) db.session.add(cc) db.session.commit() for colnum in range(37, 49): rev = sh.cell(rownum, colnum).value if isinstance(rev, float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(2, colnum).value, 0)[0:3] pyDate = date(*mydate) actual_instance = db.session.query(Actual).filter_by(campaign=c, date=pyDate).first() if actual_instance: rev = actual_instance.actualRev + rev actual_instance.actualRev = rev else: a = Actual(campaign=c, date=pyDate, actualRev=rev) db.session.add(a) aa = Actualchange(campaign=c, change_date=D.now(), date=pyDate, actualRev=rev) db.session.add(aa) db.session.commit() print("PopulateCampaignRevenue10 Finished")
def populateCampaignRevenue(wb): sh = wb.sheet_by_name('Rev020113') for rownum in range(5,5092): #sh.nrows): campaign = sh.cell(rownum,13).value t = sh.cell(rownum,3).value product = get_or_create(db.session, Product, product = sh.cell(rownum,4).value) chan = sh.cell(rownum,5).value if(chan == "MSLAL"): chan = "Publisher" channel = get_or_create(db.session, Channel, channel = chan) advertiser = get_or_create(db.session, Advertiser, advertiser = sh.cell(rownum,6).value) industry = sh.cell(rownum,8).value agency = sh.cell(rownum,9).value sfdc_oid = sh.cell(rownum,10).value if not isinstance(sfdc_oid, float): sfdc_oid = None else: sfdc_oid = int(sfdc_oid) repid = sh.cell(rownum,14).value if(repid == "VB"): repid = "VV" rep = get_or_create(db.session, Rep, repID = repid) cp = sh.cell(rownum,15).value try: start_date = xldate_as_tuple(sh.cell(rownum,16).value,0)[0:3] py_start = date(*start_date) end = xldate_as_tuple(sh.cell(rownum,17).value,0)[0:3] py_end = date(*end) except: pass cpm_price = sh.cell(rownum,19).value if not isinstance(cpm_price, float): cpm_price = None contracted_impr = sh.cell(rownum,20).value if isinstance(contracted_impr, float): contracted_impr = int(contracted_impr) else: contracted_impr = None contracted_deal = sh.cell(rownum,21).value if not isinstance(contracted_deal, float): contracted_deal = None revised_deal = sh.cell(rownum,23).value if not isinstance(revised_deal, float): revised_deal = None # For multiple reps: instance = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first() if instance: instance.rep.append(rep) db.session.commit() c = instance else: if sfdc_oid == 11919: instance = db.session.query(Campaign).filter_by(sfdc_oid = 11919).first() if instance: c = instance instance.rep.append(rep) db.session.commit() else: c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser, industry = industry, agency = agency, sfdc_oid = sfdc_oid, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, cpm_price = cpm_price, contracted_impr = contracted_impr, contracted_deal = contracted_deal, revised_deal =revised_deal) db.session.add(c) db.session.commit() else: c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser, industry = industry, agency = agency, sfdc_oid = sfdc_oid, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, cpm_price = cpm_price, contracted_impr = contracted_impr, contracted_deal = contracted_deal, revised_deal =revised_deal) print(campaign) db.session.add(c) db.session.commit() #campaignObj = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first() for colnum in range(26,62): rev = sh.cell(rownum,colnum).value if isinstance(rev,float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(4,colnum).value,0)[0:3] pyDate = date(*mydate) a = Booked(campaign=c, date=pyDate, bookedRev=rev) db.session.add(a) db.session.commit() for colnum in range(62,98): rev = sh.cell(rownum,colnum).value if isinstance(rev,float) and rev != 0.0: mydate = xldate_as_tuple(sh.cell(4,colnum).value,0)[0:3] pyDate = date(*mydate) a = Actual(campaign=c, date=pyDate, actualRev=rev) db.session.add(a) db.session.commit() print("PopulateRevenue Finished")
def __load(self): sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id] sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0] # @type sheetData Element rows = {} columns = {} for rowNode in sheetData.childNodes: rowNum = int(rowNode.getAttribute("r")) cell_ids = [] for columnNode in rowNode.childNodes: colType = columnNode.getAttribute("t") cellId = columnNode.getAttribute("r") cellS = columnNode.getAttribute("s") colNum = cellId[:len(cellId)-len(str(rowNum))] formula = None data = '' # malformed files with blank colNums # if this is the case, we just have to assume we're not # missing any cells. if not colNum: warnings.warn('Blank Column Found') if not cell_ids: colNum = u'A' else: colNum = num2xlcol(cell_ids[-1] + 1) cellId = u'{0}{1}'.format(colNum, rowNum) if not rowNum in rows: rows[rowNum] = [] if not colNum in columns: columns[colNum] = [] # fill in missing cells col_idx = xlcol2num(colNum) if not cell_ids and col_idx > 1: for i in range(1, col_idx): xlcol = num2xlcol(i) warnings.warn('Filling In Skipped Cell {0}'.format( xlcol )) cell = Cell(rowNum, xlcol, u'', formula=None) rows[rowNum].append(cell) columns[colNum].append(cell) self.__cells[u'{0}{1}'.format(xlcol, rowNum)] = cell cell_ids.append(col_idx) elif cell_ids and col_idx > cell_ids[-1] + 1: for i in range(cell_ids[-1] + 1, col_idx): xlcol = num2xlcol(i) warnings.warn('Filling In Skipped Cell {0}'.format( xlcol )) cell = Cell(rowNum, xlcol, u'', formula=None) rows[rowNum].append(cell) columns[colNum].append(cell) self.__cells[u'{0}{1}'.format(xlcol, rowNum)] = cell cell_ids.append(col_idx) cell_ids.append(col_idx) try: if colType == "s": stringIndex = columnNode.firstChild.firstChild.nodeValue data = self.workbook.sharedStrings[int(stringIndex)] #Date field elif cellS in ('1', '2', '3', '4') and colType == "n": data = xldate_as_tuple( int(columnNode.firstChild.firstChild.nodeValue), datemode=0) elif columnNode.firstChild: data = getattr( columnNode.getElementsByTagName("v")[0].firstChild, "nodeValue", None) if columnNode.getElementsByTagName("f"): formula = getattr( columnNode.getElementsByTagName("f")[0].firstChild, "nodeValue", None) except Exception: pass cell = Cell(rowNum, colNum, data, formula=formula) rows[rowNum].append(cell) columns[colNum].append(cell) self.__cells[cellId] = cell self.__rows = rows self.__cols = columns self.loaded=True
def rowsIter(self): sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id] sheetData = sheetDoc.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData" ) # @type sheetData Element for rowNode in sheetData: rowNum = int(rowNode.get("r")) rowCells = [] for columnNode in rowNode: colType = columnNode.get("t") cellId = columnNode.get("r") cellS = columnNode.get("s") colNum = cellId[:len(cellId) - len(str(rowNum))] formula = None data = '' try: if len( columnNode ) > 0 and columnNode[0] is not None and columnNode.find( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v' ) is not None: if colType == "s": stringIndex = columnNode[0].text data = self.workbook.sharedStrings[int( stringIndex)] #Built in date-formatted fields elif cellS and re.match("^[\d\.]+$", columnNode[0].text): if int(self.workbook.cellStyles[int(cellS)].get( 'numFmtId')) in range(14, 22 + 1): data = xldate_as_tuple(float( columnNode[0].text), datemode=0) elif (self.workbook.cellStyles[int(cellS)].get('numFmtId') in self.workbook.numFmts) \ and is_date_format_string(self.workbook.numFmts[self.workbook.cellStyles[int(cellS)].get('numFmtId')]): data = xldate_as_tuple(float( columnNode[0].text), datemode=0) else: data = columnNode.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v" ).text else: data = columnNode.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v" ).text elif columnNode.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}is" ) is not None: if colType == "inlineStr": data = columnNode[0][0].text if columnNode.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f" ) is not None: formula = columnNode.find( "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f" ).text except Exception: raise #pass cell = Cell(rowNum, colNum, data, formula=formula) rowCells.append(cell) yield rowNum, rowCells