Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

Split values on export to Excel

Posted on by 487

Hi.

Greetings

I have  a form where the details are as follow.

The item details are retrieved from Movemet JOurnal (posted) in ax 2012

Hence diff movement journals can have one or many multiple items. The details are saved in that table in a field concatenate by + sign

I want to export the item details into one single column. How can i acheive that when i export to excel(by code lets say as standard export to excel will not work.

How do i split the item into different column each of excel .Can anyone assist.

Dynsmics_2D00_Community.png

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Split values on export to Excel

    Hi NDingankar22,

    Wouldn't it be easier to export the data as they are and then use an Excel macro to format the columns and rows as you need it?

    Best regards,

    Ludwig

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: Split values on export to Excel

    I haven't tested but something like this will fix your problem. Maybe minor changes may be required.

    static void TheaxaptaCreateExcel(Args _args)
    {
        SysExcelApplication  xlsApplication;
        SysExcelWorkBooks    xlsWorkBookCollection;
        SysExcelWorkBook     xlsWorkBook;
        SysExcelWorkSheets   xlsWorkSheetCollection;
        SysExcelWorkSheet    xlsWorkSheet;
        SysExcelRange        xlsRange;
        CustomTable          customTable;
        int                  row = 1;
        str                  fileName;
        container            paramAsCon;
        int i;
        
        //Filename
        fileName = "C:\\Test.xlsx";
        //Initialize Excel instance
        xlsApplication           = SysExcelApplication::construct();
        //Open Excel document
        //xlsApplication.visible(true);
        //Create Excel WorkBook and WorkSheet
        xlsWorkBookCollection    = xlsApplication.workbooks();
        xlsWorkBook              = xlsWorkBookCollection.add();
        xlsWorkSheetCollection   = xlsWorkBook.worksheets();
        xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
        //Excel columns captions
        xlsWorkSheet.cells().item(row,1).value("Journal Num");
        xlsWorkSheet.cells().item(row,2).value("Item1");
        xlsWorkSheet.cells().item(row,3).value("Item2");
        xlsWorkSheet.cells().item(row,4).value("Item3");
        xlsWorkSheet.cells().item(row,5).value("Item4");
        //just caption not that important
        row  ;
        //Fill Excel with customTable JournalNum and item fields (only 20 records)
        while select customTable
        {
            if(row == 20)
            break;
            xlsWorkSheet.cells().item(row,1).value(customTable.JournalNum);
            
    
            paramAsCon = str2con(paramAsStr, ",");
        
            for (i=1;i<=conLen(paramAsCon);i  )
            {
                //info(conPeek(paramAsCon, i));    
                xlsWorkSheet.cells().item(row,1 i).value(conPeek(paramAsCon, i));
            }
            row  ;
        }
        //Check whether the document already exists
        if(WinApi::fileExists(fileName))
            WinApi::deleteFile(fileName);
        //Save Excel document
        xlsWorkbook.saveAs(fileName);
        //Open Excel document
        xlsApplication.visible(true);
        //Close Excel
        //xlsApplication.quit();
        //xlsApplication.finalize();
    }
    

  • NDingankar22 Profile Picture
    NDingankar22 487 on at
    RE: Export to Excel-Ax 21012

    ok.but how to generate columns in excel .. I will split it in comtainer but excel column generation is i m stuck at

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: Export to Excel-Ax 21012

    You can use str2con or strSplit

    www.schweda.net/blog_ax.php

    then you can export to excel

    www.theaxapta.com/.../exporting-data-to-excel-from-axapta-x.html

  • NDingankar22 Profile Picture
    NDingankar22 487 on at
    RE: Export to Excel-Ax 21012

    Thnk you for reply. The result should be as follows in excel. Like every item in each excel column(separate excel column)

    Jounrlnum  item1                      item2                                      item3                                                  item4

    GJ1             itema(apple)           itemb(badmonton)                        

    GJ2             itemc(choloclate)    itemD(dettol)                          

    GJ2                                                                                      

    G3              itema(apple)           itemb(badminton)                 item (cholocalte)                                itemD(detto))

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: Export to Excel-Ax 21012

    Can you show us what u want as a result.  Or the table you showed us are  the result you are trying to achieve?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans