[D365FO] Excel, EPPlus, Download, Save on Azure Blob

GOA_ExcelEPPlus.xpp

using OfficeOpenXml;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;

class GOA_ExcelEPPlus
{
    ExcelPackage      excelPkg;
    System.IO.MemoryStream      memoryStream;

    BinData bin;

    public static GOA_ExcelEPPlus constructFromResource(resourceNode _resourceNode)
    {
        Binary bin = Binary::constructFromContainer(SysResource::getResourceNodeData(_resourceNode));
        System.IO.MemoryStream    tmpMemoryStream = bin.getMemoryStream() as System.IO.MemoryStream;
        
        GOA_ExcelEPPlus ret = new GOA_ExcelEPPlus();
        ret.parmMemoryStream(new System.IO.MemoryStream());
        ret.parmExcelPkg(new ExcelPackage(ret.parmMemoryStream(), tmpMemoryStream));
        return ret;
     }

    public ExcelPackage parmExcelPkg(ExcelPackage _excelPkg = excelPkg)
    {
        excelPkg = _excelPkg;
        return excelPkg;
    }

    public System.IO.MemoryStream parmMemoryStream(System.IO.MemoryStream _memoryStream = memoryStream)
    {
        memoryStream = _memoryStream;
        return memoryStream;
    }

    public System.IO.Stream Save()
    {
        excelPkg.Save();
        memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
        return memoryStream;
    }

    public void Download(Filename _Filename)
    {
        
        File::SendFileToUser(memoryStream, _Filename);
    }

    public void SaveToAzureBlob(Filename _Filename, str _storageConnectionString)
    {
        CloudStorageAccount storageAccount = CloudStorageAccount::Parse(_storageConnectionString);
        CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
        CloudBlobContainer container1 = blobClient.GetContainerReference("d365fofiles");
        CloudBlockBlob blockBlob = container1.GetBlockBlobReference(_Filename);
        System.Byte[] fileStream = memoryStream.ToArray();
        blockBlob.UploadFromByteArray(fileStream, 0, fileStream.length, null, null, null);
    }

    public ExcelWorksheet getWorksheet(str _Name)
    {
        ExcelWorkbook   excelWorkbook = excelPkg.get_Workbook();
        ExcelWorksheets  excelWorksheets = excelWorkbook.get_Worksheets();
        ExcelWorksheet ret;
        ret = excelWorksheets.get_Item(_Name);
        if(ret == null)
        {
            info(strFmt("The worksheet %1 not exists in the current workbook.",_Name));
        }

        return ret;
    }

    public void  setCellValue(ExcelRange _ExcelRange, int _iCol, int _iRow, Anytype _Value)
    {
        Str                    bookmark;
        ExcelRange    Cell;
        System.DateTime             sysDate;

        bookmark = GOA_ExcelEPPLus::numToNameCell(_iCol,_iRow);
        Cell = _ExcelRange.get_Item( bookmark);

        switch (typeOf(_Value))
        {
            case Types::Enum:
                Cell.set_Value(enum2str(_Value));
                break;
            case Types::Date:
                if(_Value == dateNull())
                {
                    break;
                }
                sysDate = System.Convert::ToDateTime(_Value);
                Cell.set_Value(sysDate);
                break;
            case Types::UtcDateTime:
                sysDate = System.Convert::ToDateTime(_Value);
                Cell.set_Value(sysDate);
                break;
            default :
                Cell.set_Value(_Value);
                break;
        }
    }

    public static str numToNameCell(int _iCol, int _iRow)
    {
        #define.maxCol(256)
        #define.A('A')
        #define.Z('Z')
        int iCharCodeA = char2num(#A, 1) - 1;
        int iBase = char2num(#Z, 1) - char2num(#A, 1) + 1;
        int iDiv, iMod;
        str szRet = "";
        ;
        if (_iCol > #maxCol)
        throw error("@GLS105135");

        iDiv = _iCol;
        iMod = iDiv mod iBase;
        iDiv = iDiv div iBase;
        if (iDiv - 1 > 0 || (iDiv == 1 && iMod))
        {
            if (iMod)
            {
                szRet = num2char(iDiv + iCharCodeA);
            }
            else
            {
                szRet = num2char(iDiv - 1 + iCharCodeA);
            }
        }
        if (iMod)
        {
            szRet += num2char(iMod + iCharCodeA);
        }
        else
        {
            szRet += num2char(iBase + iCharCodeA);
        }
        szRet += int2str(_iRow);

        return szRet;
    }

}

goa_test.xpp

class goa_tests
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    
    public static void main(Args _args)
    {      
        
        resourceNode res = SysResource::getResourceNode(resourceStr(GOA_TestExcel));
        GOA_ExcelEPPlus excelEPPlus = GOA_ExcelEPPlus::constructFromResource(res);
        OfficeOpenXml.ExcelWorksheet excelWorksheet = excelEPPlus.getWorksheet("Sheet1");
        OfficeOpenXml.ExcelRange excelRange = excelWorksheet.get_Cells();

        excelEPPlus.setCellValue(excelRange, 1, 2, "d");
        excelEPPlus.setCellValue(excelRange, 1, 3, "d");
        excelEPPlus.setCellValue(excelRange, 1, 4, "a");
        excelEPPlus.setCellValue(excelRange, 2, 2, 3);
        excelEPPlus.setCellValue(excelRange, 2, 3, 2);
        excelEPPlus.setCellValue(excelRange, 2, 4, 5);
        excelEPPlus.setCellValue(excelRange, 3, 2, 9);
        excelEPPlus.setCellValue(excelRange, 3, 3, 4);
        excelEPPlus.setCellValue(excelRange, 3, 4, 2);
        excelRange.get_Item('A1').AddComment("qqq","ag");
        excelEPPlus.Save();
        str storageConnectionString = "storageConnectionString";
        excelEPPlus.SaveToAzureBlob("tmp.xlsx", storageConnectionString);
        //excelEPPlus.Download("tmp.xlsx");

    }

}