ConversionDynamic likes truncated Excel sheet names

[d00958]

I've recently run into an issue with using writeOptimizationOutputToExcel on a model including a dynamic conversion component. Due to the way the Excel sheet names are constructed including class name and the suffix for variable & dimensions, the sheet names exceed the Excel limit of 31 characters.

[d00958]

For now, I've solved this by using a wrapper function to truncate the sheet names in case they exceed the 31 character limit to the left of the suffix. E.g., ConversionDynamicOptSummary_1dim is shortened to ConversionDynamiOptSummary_1dim and the output works well. Maybe a similar function could be built into a future release.

I am doing this directly in my jupyter notebook script after initial imports:

import time

def writeOptimizationOutputToExcelWrapper(
    esM,
    outputFileName="scenarioOutput",
    optSumOutputLevel=2,
    optValOutputLevel=1,
):
    def truncate_sheet_name(name, suffix):
        max_length = 31 - len(suffix)
        return name[:max_length] + suffix

    used_names = set()
    def get_unique_name(name):
        original = name
        counter = 1
        while name in used_names:
            name = f"{original[:26]}_{counter}"
            counter += 1
        used_names.add(name)
        return name

    for ip in esM.investmentPeriodNames:
        if len(esM.investmentPeriodNames) > 1:
            _outputFileName = outputFileName + f"_{ip}"
        else:
            _outputFileName = outputFileName
        fn.utils.output("\nWriting output to Excel... ", esM.verbose, 0)
        _t = time.time()
        writer = pd.ExcelWriter(_outputFileName + ".xlsx")

        for name in esM.componentModelingDict.keys():
            fn.utils.output("\tProcessing " + name + " ...", esM.verbose, 0)
            oL = optSumOutputLevel
            oL_ = oL[name] if isinstance(oL, dict) else oL

            optSum = esM.getOptimizationSummary(name, ip=ip, outputLevel=oL_)
            if not optSum.empty:
                sheet_name = truncate_sheet_name(name[:-5], "OptSummary_" + esM.componentModelingDict[name].dimension)
                sheet_name = get_unique_name(sheet_name)
                optSum.to_excel(writer, sheet_name=sheet_name)

            data = esM.componentModelingDict[name].getOptimalValues(ip=ip)
            oL = optValOutputLevel
            oL_ = oL[name] if isinstance(oL, dict) else oL
            dataTD1dim, indexTD1dim, dataTD2dim, indexTD2dim = [], [], [], []
            dataTI, indexTI = [], []
            for key, d in data.items():
                if d["values"] is None:
                    continue
                if d["timeDependent"]:
                    if d["dimension"] == "1dim":
                        dataTD1dim.append(d["values"]), indexTD1dim.append(key)
                    elif d["dimension"] == "2dim":
                        dataTD2dim.append(d["values"]), indexTD2dim.append(key)
                else:
                    dataTI.append(d["values"]), indexTI.append(key)
            if dataTD1dim:
                names = ["Variable", "Component", "Location"]
                dfTD1dim = pd.concat(dataTD1dim, keys=indexTD1dim, names=names)
                if oL_ == 1:
                    dfTD1dim = dfTD1dim.loc[
                        ((dfTD1dim != 0) & (~dfTD1dim.isnull())).any(axis=1)
                    ]
                if not dfTD1dim.empty:
                    sheet_name = truncate_sheet_name(name[:-5], "_TDoptVar_1dim")
                    sheet_name = get_unique_name(sheet_name)
                    dfTD1dim.to_excel(writer, sheet_name=sheet_name)
            if dataTD2dim:
                names = ["Variable", "Component", "LocationIn", "LocationOut"]
                dfTD2dim = pd.concat(dataTD2dim, keys=indexTD2dim, names=names)
                if oL_ == 1:
                    dfTD2dim = dfTD2dim.loc[
                        ((dfTD2dim != 0) & (~dfTD2dim.isnull())).any(axis=1)
                    ]
                if not dfTD2dim.empty:
                    sheet_name = truncate_sheet_name(name[:-5], "_TDoptVar_2dim")
                    sheet_name = get_unique_name(sheet_name)
                    dfTD2dim.to_excel(writer, sheet_name=sheet_name)
            if dataTI:
                if esM.componentModelingDict[name].dimension == "1dim":
                    names = ["Variable type", "Component"]
                elif esM.componentModelingDict[name].dimension == "2dim":
                    names = ["Variable type", "Component", "Location"]
                dfTI = pd.concat(dataTI, keys=indexTI, names=names)
                if oL_ == 1:
                    dfTI = dfTI.loc[((dfTI != 0) & (~dfTI.isnull())).any(axis=1)]
                if not dfTI.empty:
                    sheet_name = truncate_sheet_name(name[:-5], "_TIoptVar_" + esM.componentModelingDict[name].dimension)
                    sheet_name = get_unique_name(sheet_name)
                    dfTI.to_excel(writer, sheet_name=sheet_name)
        # get internal name of investment period
        _ip = esM.investmentPeriodNames.index(ip)
        # save periods Order to excel output
        periodsOrder = pd.DataFrame(
            [esM.periodsOrder[_ip]], index=["periodsOrder"], columns=esM.periods
        )
        periodsOrder.to_excel(writer, sheet_name="Misc")
        if esM.segmentation:
            ls = []
            for i in esM.periodsOrder[_ip].tolist():
                ls.append(esM.timeStepsPerSegment[_ip][i])
            segmentDuration = pd.concat(ls, axis=1).rename(
                columns={"Segment Duration": "timeStepsPerSegment"}
            )
            segmentDuration.index.name = "segmentNumber"
            segmentDuration.to_excel(writer, sheet_name="Misc", startrow=3)
        fn.utils.output("\tSaving file...", esM.verbose, 0)
        writer.close()
        fn.utils.output("Done. (%.4f" % (time.time() - _t) + " sec)", esM.verbose, 0)

And then simply calling the wrapper instead of the usual function:

writeOptimizationOutputToExcelWrapper(esM, outputFileName='scenarioOutput_convDyn', optSumOutputLevel=2, optValOutputLevel=1)

Hope this helps!

[t-gross]

Thank you for raising this issue and I am glad to hear that you have found a workaround for your use case.
We will create an issue for this and try to implement a proper solution.