Export Template into D365 through x++ code

 using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;


/// <summary>

/// Contains buisness logic used in customer invoice journal template Export

/// </summary>

class CustInvoiceJourExportTemplate

{    

    private const int InvoiceDate           = 1;

    private const int BillingPeriodDate     = 2;

    private const int Description           = 3;

    private const int TCAccountId           = 4;

    private const int BillingClassification = 5;

    private const int BillingCode           = 6;

    private const int AmountCurDebit        = 7;    

    private const int TotalAmount           = 8;

    private const int ItemGSTGroup          = 9;

    private const int Fund                  = 10;

    private const int Division              = 11;    

    private const int Precinct              = 12;

    private const int Property              = 13;

    private const int Activity              = 14;

    private const int Project               = 15;


    COM                 comObj, intObj;    


    protected static const str DateFormatString    = 'dd/mm/yyyy'; // OXML NumFmtID = 14

    protected static const str GenericFormatString = '@'; // OXML NumFmtID = 49

    protected static const str AmountFormatReal    = '0.00';


    protected static readonly System.Drawing.Color HeaderColor = System.Drawing.Color::FromArgb(184, 204, 228);

    protected static readonly System.Drawing.Color BodyColor = System.Drawing.Color::FromArgb(219, 229, 241);


    /// <summary>

    /// Main method of class

    /// </summary>

    public static void main (Args args)

    {

       CustInvoiceJourExportTemplate custInvoiceJourExportTemplate;

        custInvoiceJourExportTemplate = new CustInvoiceJourExportTemplate ();

                                      

        custInvoiceJourExportTemplate.generateData();

    }


    protected void setCellLabel(OfficeOpenXml.ExcelRange _cell, str _label, str _cellFormat, boolean _includeTrailingColon = true,

                                OfficeOpenXml.Style.ExcelHorizontalAlignment _horizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment::Left)

    {

        var cellStyle = _cell.get_Style();

        cellStyle.set_HorizontalAlignment(_horizontalAlignment);

               

        var column = _cell.get_Worksheet().Column(_cell.get_Start().get_Column());

        var columnStyle = column.get_Style();

        var columnNumberFormat = columnStyle.get_Numberformat();

 

        columnNumberFormat.set_Format(_cellFormat);


        cellStyle.get_Font().set_Bold(true);

        _cell.set_Value(_label);

    }


    /// <summary>

    /// Auto fit template columns

    /// </summary>

    /// <param name = "_worksheet">

    /// ExcelWorksheet

    /// </param>

    protected void autoFitWorksheet(OfficeOpenXml.ExcelWorksheet _worksheet)

    {

        var lastColumn = _worksheet.get_Dimension().get_End().get_Column();

        for (var i = 1; i <= lastColumn; i++)

        {

            _worksheet.Column(i).AutoFit();

        }

    }


    /// <summary>

    /// Method used for template

    /// </summary>

    void generateData()

    {

        DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation("@SYS27201", "xlsx", null);


        if (saveResult && saveResult.parmAction() != DocuFileSaveAction::Cancel)

        {

            saveResult.parmOpenParameters('web=1');

            saveResult.parmOpenInNewWindow(false);

            //System.IO.Stream workbookStream     = new System.IO.MemoryStream();

            System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();


            using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))

            {

                var currentRow  = InvoiceDate;

                var worksheets  = package.get_Workbook().get_Worksheets();

                var worksheet   = worksheets.Add("@SYS27201");


                var cells       = worksheet.get_Cells();


                var cell=cells.get_Item(currentRow, InvoiceDate);

                this.setCellLabel(cell, "@InvoiceDateImportTemplate", DateFormatString);

                

                /*comObj = cells.get_Item(currentRow, InvoiceDate).comObject();

                intObj = comObj.Interior();

                intObj.color(16711680);*/


                cell = null;

                


                cell=cells.get_Item(currentRow, BillingPeriodDate);

                this.setCellLabel(cell, "@S:BillingPeriodImportTemplate", DateFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Description);

                this.setCellLabel(cell, "@S:DescriptionImportTemplate", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, TCAccountId);

                this.setCellLabel(cell, "@S:TCAccountImportTemplate", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, BillingClassification);

                this.setCellLabel(cell, "@S:BillingClassificationImportTemplate", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, BillingCode);

                this.setCellLabel(cell, "@S:BillingCodeImportTemplate", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, AmountCurDebit);

                this.setCellLabel(cell, "@S:AmountBeforeGSTTemplate", AmountFormatReal);

                cell = null;


                cell=cells.get_Item(currentRow, TotalAmount);

                this.setCellLabel(cell, "@S:TotalIncludeGST", AmountFormatReal);

                cell = null;              

                

                cell=cells.get_Item(currentRow, ItemGSTGroup);

                this.setCellLabel(cell, "@SYS21932", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Fund);

                this.setCellLabel(cell, "@S:Fund", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Division);

                this.setCellLabel(cell, "@S:Division", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Precinct);

                this.setCellLabel(cell, "@S:Precinct", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Property);

                this.setCellLabel(cell, "@S:Property", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Activity);

                this.setCellLabel(cell, "@S:Activity", GenericFormatString);

                cell = null;


                cell=cells.get_Item(currentRow, Project);

                this.setCellLabel(cell, "@S:Project", GenericFormatString);

                cell = null;


                this.autoFitWorksheet(worksheet);

                package.Save();

            }


            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

            DocuFileSave::processSaveResult(memoryStream, saveResult);

        }

    }


}

Comments

Post a Comment

Popular posts from this blog

Create ledger journals in D365FO using X++ + LedgerDimensionACType

 internal final class HCL_LedgerJournalImportCSV extends RunBaseBatch {     private str         availableTypes = ".csv";     //In the class Declaration control names for OK and upload control     private const str   OkButtonName = 'OkButton';     private const str   FileUploadName = 'FileUpload';     Dialog              dialog;     str                 textFile;     QueryRun            gQueryRun;     HCL_tempLedgerJournalTransForImport tempLedgerJournalTransForImport;     #define.CurrentVersion(3)     public Object dialog()     {         DialogGroup      dialogGroup;         FormBuildControl formBuildControl;         FileUploadBuild  dialogFile...
Read more

Excel Import into D365 using x++ code

 using System.IO; using OfficeOpenXml; using OfficeOpenXml.Style; using OfficeOpenXml.Table; using OfficeOpenXml.ExcelRange; using OfficeOpenXml.ExcelPackage; /// <summary> /// Customer invoice journal excel import /// </summary> class CustInvoiceJournalExcelImport  {     int currentRow              = 1;     int coloumn                 = 1;     ExcelWorksheets             worksheets;     ExcelWorksheet              worksheet;     OfficeOpenXml.ExcelRange    cells;     OfficeOpenXml.ExcelRange    cell;     #OCCRetryCount     CustInvoiceTable         CustInvoiceTable;     CustInvoiceLineImport    CustInvoiceLineImport;                 ...
Read more

Sales invoice report

 /// <summary> /// Event handler class for adding document based on document types to print management. /// </summary> public class GOPPrintMgmtDocTypeEventHandler {     /// <summary>     /// To add custom invoice report document in print management for sales order invoice.     /// </summary>     /// <param name = "_docType"> <c>PrintMgmtDocumentType</c> </param>     /// <param name = "_result"> <c>EventHandlerResult</c> </param>     [SubscribesTo(classStr(PrintMgmtDocType), delegateStr(PrintMgmtDocType, getDefaultReportFormatDelegate))]     public static void PrintMgmtDocType_getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)     {         #ISOCountryRegionCodes         switch (_docType)         {           ...
Read more