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
