Trial balance report export to share point using x++

Trial balance report export to share point using x++.

using Microsoft.Dynamics.Platform.Integration.SharePoint;

[SRSReportParameterAttribute(classStr(TrialBalanceMainAccountReportContract))]
public class TrialBalanceMainAccountReportDP extends SrsReportDataProviderPreProcessTempDB
{
    #Define.BusinessUnitDim('BusinessUnit')
    #Define.DepartmentDim('Department')
    #Define.ExcelExtension('.xlsx')

    TrialBalanceMainAccountReportTmp        TrialBalanceMainAccountReportTmp;
    LedgerTrialBalanceTmp                   ledgerTrialBalanceTmp;
    TrialBalanceMainAccountReportContract   contract;
    Name                                    primaryDimensionFocus;
	
   
    public void setTrialBalanceTmpTable(TrialBalanceMainAccountReportTmp _TrialBalanceMainAccountReportTmp)
    {
        TrialBalanceMainAccountReportTmp.linkPhysicalTableInstance(_TrialBalanceMainAccountReportTmp);
    }

    [SRSReportDataSetAttribute(tablestr(TrialBalanceMainAccountReportTmp))]
    public TrialBalanceMainAccountReportTmp getDocAgreementHeader()
    {
        select TrialBalanceMainAccountReportTmp;
        return TrialBalanceMainAccountReportTmp;
    }

    public void processData()
    {
        FiscalCalendarPeriod                fiscalCalendarStartPeriod;
        GeneralJournalAccountEntry          generalJournalAccountEntryHeader, generalJournalAccountEntryLine;
        GeneralJournalEntry                 generalJournalEntryHeader, generalJournalEntryLine;
        DimensionAttributeValueCombination  dimensionAttributeValueCombination;
        LedgerJournalTrans                  ledgerJournalTrans;
        LedgerTrialBalanceTmp               ledgerTrialBalanceTmpReference;
        MainAccount                         mainAccount;
        AmountMST                           openingBalance, creditAmount, debitAmount, closingBalance;
        Name                                dimensionAttributeName;
        DimensionValue                      dimensionValue;
        boolean                             insertData = false;
        boolean                             displayOnlyBalances = true;
        CurrentOperationsTax                postingLayer;

        contract = this.parmDataContract() as TrialBalanceMainAccountReportContract;

        TransDate               FromDate            = contract.parmFromDate();
        TransDate               ToDate              = contract.parmToDate();
        TransDate               periodStartDate     = LedgerFiscalCalendar::findOpeningStartDateByDate(Ledger::fiscalCalendar(CompanyInfo::current()), FromDate);

        delete_from TrialBalanceMainAccountReportTmp;

		//  Based on date range looping records in 'generalJournalAccountEntryHeader' table.(Voucher transactions)
        while select MainAccount, LedgerDimension from  generalJournalAccountEntryHeader
            group by generalJournalAccountEntryHeader.LedgerDimension ,generalJournalAccountEntryHeader.MainAccount
        join generalJournalEntryHeader
            where generalJournalAccountEntryHeader.GeneralJournalEntry == generalJournalEntryHeader.RecId
                && generalJournalEntryHeader.AccountingDate >= fromDate
                && generalJournalEntryHeader.AccountingDate <= toDate
        {
            FiscalCalendarPeriod fiscalCalendarPeriod;
           
            //Debit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount  from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::No
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            debitAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Credit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::Yes
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            creditAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Opening balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                group by generalJournalAccountEntryLine.MainAccount, generalJournalAccountEntryLine.LedgerAccount
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
            join generalJournalEntryLine
            where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                && generalJournalEntryLine.Ledger == Ledger::current()
                && generalJournalEntryLine.AccountingDate >= fromDate
                && generalJournalEntryLine.AccountingDate <= ToDate
                 exists join fiscalCalendarPeriod
            where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                && fiscalCalendarPeriod.Type == FiscalPeriodType::Opening;

            openingBalance  = generalJournalAccountEntryLine.AccountingCurrencyAmount;
            closingBalance  = openingBalance + debitAmount + creditAmount;

            str  mainAccountId, MainAcountName;

            mainAccountId = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).MainAccountId;
            MainAcountName = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).Name;

            //info(strFmt("Id %1, Name %2, Debit: %3, Credit%4, LedgerDim:%5, opnBl%6, CloseBl%7", mainAccountId, 
                    MainAcountName, drAmt, CrAmt, generalJournalAccountEntry.LedgerAccount,opnBl, CloseBl ));

            TrialBalanceMainAccountReportTmp.FromDate              = FromDate;
            TrialBalanceMainAccountReportTmp.ToDate                = ToDate;
            TrialBalanceMainAccountReportTmp.MainAccount           = str2Int64(mainAccountId);
            TrialBalanceMainAccountReportTmp.Name                  = MainAcountName;
            TrialBalanceMainAccountReportTmp.OpeningBalance        = openingBalance;
            TrialBalanceMainAccountReportTmp.ClosingTransactions   = closingBalance;
            TrialBalanceMainAccountReportTmp.AmountCredit          = creditAmount;
            TrialBalanceMainAccountReportTmp.AmountDebit           = debitAmount;

            // Fetching Dimension Values
            if (generalJournalAccountEntryHeader.LedgerDimension)
            {
                TrialBalanceMainAccountReportTmp.BusinessUnit       = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #BusinessUnitDim);
                TrialBalanceMainAccountReportTmp.Department         = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #DepartmentDim);
            }
            TrialBalanceMainAccountReportTmp.insert();
        }
        this.uploadOnSharePoint();
    }

    public DimensionValue getDefaultDimensionValue(DimensionDefault   _defaultDimension, Name  _dimensionName)
    {
        DimensionAttributeValueSetStorage   dimStorage;

        dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

        return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(_dimensionName).RecId);
    }

    public void uploadOnSharePoint()
    {
        System.IO.MemoryStream      memoryStream;
        int                         row;
        int                         col;
        Filename                    fileNameExt;
        System.Exception            ex;
        str                         errorMessage = '';

        try
        {
            ttsbegin;
            memoryStream = new System.IO.MemoryStream();

            row =   1;
        
            using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
            {
                var sheet = package.get_Workbook().get_Worksheets();
                var worksheet = sheet.Add("Hyperion trail balance"); // adding new work sheet.
                var cells = worksheet.get_Cells();
                OfficeOpenXml.Style.ExcelStyle style;
                OfficeOpenXml.Style.ExcelFont font;
				
				//adding colum names
                Col = 1;
                var cell = Cells.get_item(row,Col);
                cell.set_Value("Main Account");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Business Unit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Department");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Name");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Opening Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Debit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Credit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Closing Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                while select TrialBalanceMainAccountReportTmp
                {
                    row++;
                    Col = 1;
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.MainAccount);
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value((TrialBalanceMainAccountReportTmp.BusinessUnit));
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Department);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Name);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.OpeningBalance);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountDebit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountCredit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.ClosingTransactions);
                    col++;
                }
                package.save();
            }

            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
			
			// File name
            fileNameExt = 'Hyperiod Trail balance';
            fileNameExt =  subStr(fileNameExt, 1, 30) + #ExcelExtension;
			
			str fileContentType = System.Web.MimeMapping::GetMimeMapping(fileNameExt);
			
			//path
            str folderPath = "/Shared Documents/Integration/Input";

            ISharePointProxy    proxy = null;
            str                 src = '';
            str                 hostName = '';

			src = ' https://'domain name'.sharepoint.com';// 
				
			System.UriBuilder builder = new System.UriBuilder(src);
			hostName = builder.Host;
			str externalId = xUserInfo::getCurrentUserExternalId();

			try
			{
				proxy = SharePointHelper::CreateProxy(hostName, '/', externalId);
			}
			catch(Exception::CLRError)
			{
				proxy = null;
			}

            if(proxy)
            {
                Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider prov =
                    new Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(proxy, folderPath);
                    
                prov.SaveFileWithOverwrite(newguid(), fileNameExt, fileContentType, memoryStream);
            }
            else
            {
                throw Error("Sharepoint Connection Error");
            }
            ttscommit;
        }
        catch (ex)
        {
            System.Exception e = ex;
            while (e != null)
            {
                errorMessage += e.Message;
                e = e.InnerException;
            }

            if (appl.ttsLevel() > 0)
            {
                ttsabort;
            }

            checkFailed("Process failed");
            error(errorMessage);
        }
    }

}

Trial balance using Standard class:

    LedgerTrialBalanceListPageBalanceParms 	balanceParameters;
    DimensionHierarchy 			        dimHier;
    LedgerTrialBalanceTmp   			LedgerTrialBalanceTmp;
    TransDate                                   fromDate,toDate;
    List 					selected = new List(Types::Enum);
 
    selected.addEnd(CurrentOperationsTax::Current);

    dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, 'Main account set');
	
    balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
	
    balanceParameters.parmDateCode('');
    balanceParameters.parmDimensionSetHierarchy(dimHier);
    balanceParameters.parmStartDate(fromDate);
    balanceParameters.parmEndDate(toDate);
    balanceParameters.parmPostingLayers(selected);

    select firstOnly RecId from LedgerTrialBalanceTmp;

    LedgerTrialBalanceTmp::calculateBalances_V2(
						LedgerTrialBalanceTmp,
						dimHier.Name,
						balanceParameters.getStartDate(),
						balanceParameters.getEndDate(),
						balanceParameters.getIncludeOpeningPeriods(),
						balanceParameters.getIncludeClosingAdjustments(),
						balanceParameters.getIncludeClosingTransactions(),
						balanceParameters.getPostingLayers(),
						true,//ShowErrors,
						true,// runUpdateBalancesInNewSession,
						Ledger::primaryLedger(CompanyInfo::findDataArea('other company').RecId),//Ledger::current(),// current comapny.
						balanceParameters.getDisplayMainAccountCategory());


	while select LedgerTrialBalanceTmp
	    where LedgerTrialBalanceTmp.EndingBalance != 0
	{
	    info(strfmt('%1:%2', LedgerTrialBalanceTmp.DimensionValues[1],LedgerTrialBalanceTmp.EndingBalance))
	}

Keep Daxing!!