def exportExcel(options, progressControl=None):
structure = options["structure"]
selection = options["selection"]
# if options["presetTime"] == "Include Newer Periods":
# worker = LoadDbWorker((options["source"], options["name"]), baseDialog = None)
# worker.startWork()
# metaData = worker.metaData
# if "timeColumn" in metaData:
# lastTime = max(selection[metaData["timeColumn"]])
# for t in metaData[metaData["timeColumn"]]:
# if t > lastTime:
# selection[metaData["timeColumn"]].append(t)
_sortingBeforeExport(selection, options["sorting"])
existingSheets = []
writer = Writer(options)
if progressControl is not None:
progressControl.setStep(0)
data = getData((options["source"], options["name"]), selection)
if progressControl is not None:
progressControl.setStep(2)
if len(structure["sheet"]) == 0:
writer.changeActiveSheet(options["sheetName"])
writer.writeHeader(options)
table = _prepareTable(data, options)
writer.writeTable(table, options)
else:
sheet = []
for i in structure["sheet"]:
sheet.append(selection[i])
sheetP = list(itertools.product(*sheet))
for t in sheetP:
sheetName = ""
for i in t:
sheetName += str(i)
fixed = {}
for i, j in enumerate(structure["sheet"]):
fixed[j] = {"value": t[i], "label": data["long"][j][t[i]]}
table = _prepareTable(data, options, fixed=fixed)
writer.changeActiveSheet(sheetName)
writer.writeHeader(options)
writer.writeTable(table, options)
if progressControl is not None:
progressControl.setStep(3)
writer.save()
def exportStata(options, progressControl=None):
structure = options["structure"]
selection = options["selection"]
if progressControl is not None:
progressControl.setStep(0)
data = getData((options["source"], options["name"]), selection)
if progressControl is not None:
progressControl.setStep(1)
rDim = []
cols = []
cDim = []
rows = []
for name in structure:
format = structure[name]["format"]
if format == "wide":
cDim.append(name)
elif format == "long":
rDim.append(name)
code = structure[name]["code"]
if code == "short":
cols.append(name)
elif code == "long":
cols.append(name)
elif code == "both":
cols.append(name)
cols.append(name + "Label")
rIterList = []
cIterList = []
for i in rDim:
rIterList.append(selection[i])
for i in cDim:
cIterList.append(selection[i])
# print "cols", len(cols)
colValueNames = []
if len(cDim) == 0:
colValueNames += ["value"]
if options["exportFlags"]:
colValueNames += ["flag"]
else:
for c in itertools.product(*cIterList):
colValueNames += ["value_" + "_".join(c)]
if options["exportFlags"]:
colValueNames += ["flag_" + "_".join(c)]
lines = []
for r in itertools.product(*rIterList):
values = []
for i, c in enumerate(rDim):
code = structure[c]["code"]
if code == "short":
values.append(r[i])
elif code == "long":
values.append(data["long"][c][r[i]])
elif code == "both":
values.append(r[i])
values.append(data["long"][c][r[i]])
entry = False
for c in itertools.product(*cIterList):
keyList = []
for bc in data["cols"]:
if bc in rDim:
keyEntry = r[rDim.index(bc)]
elif bc in cDim:
keyEntry = c[cDim.index(bc)]
else:
raise Error("Wow Wow Wow, thats not good, keylist and dict differ, what have you done?")
keyList.append(keyEntry)
key = tuple(keyList)
if key in data["data"]:
values.append(data["data"][key]["value"])
if options["exportFlags"]:
values.append(data["data"][key]["flag"])
entry = True
else:
values.append(None)
if options["exportFlags"]:
values.append(None)
if entry:
lines.append(values)
df = pd.DataFrame(lines, columns=cols + colValueNames)
convert_dates = None
for colName in cols:
if colName in structure and "encode" in structure[colName] and structure[colName]["encode"]:
if colName == "time":
convert_dates = {"time": "ty"}
if "Q" in df.loc[0, colName]:
convert_dates = {"time": "tq"}
df[colName] = pd.DatetimeIndex(df[colName])
# df['timeTest2'] = pd.DatetimeIndex(df[colName])
# df[colName] = pd.to_datetime(df[colName])
# df[colName] = df[colName].to_period()
else:
df[colName] = df[colName].astype("category")
for colName in colValueNames:
if colName.startswith("value"):
# log("convert col to float " + colName)
df[colName] = df[colName].astype("float64")
if progressControl is not None:
progressControl.setStep(2)
# info = getFileInfo(options["name"])
# dataset_label=options["name"] + " LU: " + str(info["updatedDate"])
df.to_stata(options["fileName"], write_index=False, convert_dates=convert_dates)