def load(filename): dir = os.path.dirname(filename) wb = xlrd.open_workbook(filename) sheet = wb.sheets()[0] i = 1 for i in xrange(1, sheet.nrows): fields = sheet.row_values(i) types = sheet.row_types(i) name = get_str(fields[0], types[0]) version = fields[1] category = fields[2] grade = get_int(fields[3], types[3]) clss = fields[4] course = fields[5] user = get_str(fields[6], types[6]) path = os.path.join(dir, fields[7]) testfile = os.path.join(dir, fields[8]) try: cwid = upload(name, version, category, grade, clss, course, user, path, testfile) except Exception: _, e, tb = sys.exc_info() e = Exception('第 %d 行:%s' % ((i + 1), e)) raise e.__class__, e, tb else: xlsloader.load(testfile, cwid)
def convert(xlsfile, csvfile, fields, validate = True, badRecs = None): logger = createLogger("xls2csv") logger.debug("Entered convert") xls = load(xlsfile, fields) if validate: validateAllRFieldTypes(xls) writer = csv.writer(open(csvfile, 'w'), delimiter=',',\ quotechar='"', quoting=csv.QUOTE_MINIMAL) names = xls.fields.names() writer.writerow(names) ndx = 0 for record in xls.records(): lr = [] for m in names: lr.append(csvFix(record[m])) ndx = ndx + 1 if badRecs is None or (ndx+1 not in badRecs): try: writer.writerow(lr) except UnicodeEncodeError: print lr print ndx raise else: logger.debug("Discarding %d"%ndx)
def loadChangedIDs(self): fn = os.path.join(self.dir, self.changeIDFile) self.changedIDs = {} if not os.path.isfile(fn): return 0 self.logger.debug("Loading changed ids") idxls = xlsloader.load(fn, self.changeIDfields) ndx = 0 dups = {} for r in idxls.records(): ndx = ndx + 1 msg = xlsloader.validateRecordFieldTypes(r, idxls, ndx) if msg != "": self.iderrs.append((ndx+1, msg)) else: old = r["Old ID"] new = r["New ID"] try: rs = dups[old] except KeyError: rs = [] dups[old] = rs rs.append(ndx+1) # if len(rs)> 1: print "BUG" self.changedIDs[old] = new for k in dups.keys(): rs = dups[k] if len(rs) > 1: msg = "" for r in rs: if msg == "": msg = "Change ID from: %s defined more than once in records: %d"%(k, r) else: msg = msg + ", %d"%r self.iderrs.append((0, msg)) self.logger.debug("AFTER loadChangeIDs len=%d"%len(self.changedIDs)) if self.changedIDs: for k in self.changedIDs.keys(): if k not in self.mloaded: self.logger.debug("CHANGE ID: %s not in map"%str(k)) self.iderrs.append((0, "CHANGE PROPERTY ID: %s not in properties"%str(k))) newkey = self.changedIDs[k] if newkey in self.mloaded: self.logger.debug("CHANGE ID: %s to %s already in map"%(str(k), str(newkey))) self.iderrs.append((0, "CHANGE PROPERTY ID: %s - NEW PROPERTY ENTITY ID %s is already in used properties"%(str(k), str(newkey)))) return len(self.iderrs)
def createLoads(self, loadDir): self.logger.debug("entered createLoads") super(Properties, self).createLoads(loadDir) self.logger.debug("Calling base class") fn = os.path.join(self.dir, self.changeIDFile) if os.path.isfile(fn): idxls = xlsloader.load(fn, self.changeIDfields) sqlfile = open(os.path.join(loadDir, "change_ids.sql"), "w") for r in idxls.records(): old = r["Old ID"] new = r["New ID"] sqlfile.write("update mod_property set property_entity_id = '%s' where property_entity_id = '%s';\n"%(new, old)) sqlfile.write("quit\n")
def loadXLS(fpath): if os.path.isfile(fpath): xls = xlsloader.load(fpath, self.fields) for record in xls.records(): val = record[self.primaryKey] ls = {} for f1 in self.fields: f = f1[0] if f != self.primaryKey: try: v = record[f] ls[f.upper()] = v except KeyError: pass self.mloaded[val] = ls
def createLoads(self, loadDir): self.logger.debug("entered createLoads") if self.newFile is not None: badRecs = {} for r in self.addErrs: badRecs[r[0]] = 1 fpath = os.path.join(self.dir, self.newFile) if os.path.isfile(fpath): xls2csv.convert(fpath, os.path.join(loadDir, self.newCSVLoad), self.fields, True, badRecs) if self.updFile is not None: badRecs = {} # print self.updErrs for r in self.updErrs: badRecs[r[0]] = 1 # print badRecs fpath = os.path.join(self.dir, self.updFile) if os.path.isfile(fpath): fo = open(os.path.join(loadDir, self.updSQLLoad), "w") self.createUpdSql(fo, xlsloader.load(fpath, self.fields), badRecs)
def _test_import_xls(self): xlsloader.load('testing/data/unit1_tests.xlsx', 1)
def __validateXLS(self, fn, adding = False): self.onPreValidate(adding) xls = xlsloader.load(fn, self.fields) ndx = 0 errs = [] uniqueMaps = {} for record in xls.records(): ndx = ndx + 1 msg = xlsloader.validateRecordFieldTypes(record, xls, ndx) if msg != "": errs.append((ndx+1,msg)) for f in self.notNulls: rv = record[f] if cleanString(rv) == "": errs.append((ndx+1, "%s Cannot be empty"%f)) if self.primaryKey is not None: if adding: val = record[self.primaryKey] if type(val) in types.StringTypes: val = val.strip() # assumed string try: old = self.baseKeys[val] errs.append((ndx+1, "Adding a record on already existing key. This account entity id: %s already used on rec: %d"%(val,old))) except KeyError: pass else: # not adding val = record[self.primaryKey] if type(val) in types.StringTypes: val = val.strip() try: old = self.baseKeys[val] except KeyError: errs.append((ndx+1, "%s : %s for update not found"%(self.primaryKey, val))) mfs = None pfs = None if self.parentFSField is not None: pfs = record[self.parentFSField] try: mfs = parent_management[pfs] except KeyError: errs.append((ndx+1, "%s is an invalid %s"%(pfs, self.parentFSField))) if self.managingFSField is not None and mfs is not None: rv = record[self.managingFSField] if rv not in mfs: errs.append((ndx+1, "%s is not a valid %s for parent %s"%(rv, \ self.managingFSField, pfs))) for f in uniqueMaps.keys(): m = uniqueMaps[f] rv = record[f] if cleanString(rv) != "": if type(rv) in types.StringTypes: rv = rv.strip() try: val = m[rv] except KeyError: val = [] val.append(ndx + 1) m[rv] = val self.validateMore(adding, record, ndx, errs) self.logger.debug("*** %d records in XLS"%ndx) ## dump the collected unique violations for f in uniqueMaps.keys(): m = uniqueMaps[f] for f2 in m.keys(): m2 = m[f2] if len(m2) > 1: msg = "%s has duplicate values %s in records "%(f, f2) first = True for rx in m2: if first: first = False else: msg = msg + "," msg = msg + " %d"%rx errs.append((0,msg)) return errs