Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

Creating Excel File and then appending data to it

Posted on by Microsoft Employee

Hi Everyone,

                        I am new to Dynamics Ax, i want to create an Excel file and then check if the file already exists, if it exists then append data to it. I tried writting data to Excel file and it worked but how to do "append" data to Excel file using Dynamics ax X++(through code).

Can anyone please  tell me how to do it. Thanks.

Best Regards

Shah

  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 3,985 on at
    RE: Creating Excel File and then appending data to it

    Keeping it as simple as possible, this Job will write to an existing Excel sheet.

    Filename in variable fileName
    Row to write data to in variable excelRow

    static void RSH003_AppendExcel(Args _args)
    {
        // Variables for Dialog
        FileName                fileName    = @'C:\Test.xlsx';
    
        // Variabler til Excel
        SysExcelApplication                 sysExcelApplication;
        SysExcelWorksheet                   sysExcelWorksheet;
        SysExcelWorksheets                  sysExcelWorksheets;
        SysExcelWorkbooks                   sysExcelWorkbooks;
        SysExcelWorkbook                    sysExcelWorkbook;
        SysExcelCell                        sysExcelCell;
        counter                             excelRow;
        
        
        // Open the Excel document
        sysExcelApplication = SysExcelApplication::construct();
        sysExcelWorkbooks   = sysExcelApplication.workbooks();
    
        //specify the file path that you want to read
        try
        {
            sysExcelWorkbooks.open(filename,0,false);
        }
        catch (Exception::Error)
        {
            throw error("File cannot be opened.");
        }
    
        sysExcelWorkbook    = sysExcelWorkbooks.item(1);
        sysExcelWorksheets  = sysExcelWorkbook.worksheets();
        sysExcelWorksheet   = sysExcelWorksheets.itemFromNum(1);
    
        excelRow                            = 2;
        
        SysExcelCell = SysExcelWorksheet.cells().item(excelRow,1);
        SysExcelCell.value("Col1");
    
        SysExcelCell = SysExcelWorksheet.cells().item(excelRow,2);
        SysExcelCell.value("Col2");
        
        SysExcelCell = SysExcelWorksheet.cells().item(excelRow,3);
        SysExcelCell.value("Col3");
        
        // Save the Excel file
        sysExcelApplication.displayAlerts(false);
        sysExcelWorkbook.saveAs(fileName);
        sysExcelWorkBook.saved(true);
        sysExcelApplication.quit();
    }


  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Creating Excel File and then appending data to it

    Hi Rudi,

                 Yes,  there was Problem with file opening but it was just a variable mistake. Can you plesae provide some Example code for appending data to Excel file. if you have it. or if you have tried it. Thanks

    Best Regards

    Shah

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,985 on at
    RE: Creating Excel File and then appending data to it

    I think your problem is with the exportDataToExcel method.

    First of all when i try this code it fails on opening the Excel document.

    But the main problem i think is in the line:

    xlsWorkBook = xlsWorkBookCollection.add();

    I think you are adding an new WorkBook here, and then you have a blank workbook you are writing your data in.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Creating Excel File and then appending data to it

    Here is the Code, Function "createExcelFile" creates the Excel file with headings , then "exportDataToExcel" reopens the Excel file and tries to append new record to the Excel file and saves it. " this.findLastRow();" fucntion return the last row from the existing Excel document to write the new record in row.

    Best Regards

    Shah

    public void createExcelFile()

    {

    SysExcelApplication xlsApplication;

    SysExcelWorkBooks xlsWorkBookCollection;

    SysExcelWorkBook xlsWorkBook;

    SysExcelWorkSheets xlsWorkSheetCollection;

    SysExcelWorkSheet xlsWorkSheet;

    SysExcelRange xlsRange;

    InventTable inventTable;

    int row = 1;

    str fileName;

    System.Exception  ex;

    try

    {

    // Name of the Excel document.

    fileName = "C:\\test.xslx";

    // Excel open and initialize.

    xlsApplication = SysExcelApplication:: construct ();

    // xlsApplication.visible (true);

    // Create an Excel Worksheet produce.

    xlsWorkBookCollection = xlsApplication.workbooks();

    xlsWorkBook = xlsWorkBookCollection.add();

    xlsWorkSheetCollection = xlsWorkBook.worksheets();

    xlsWorkSheet = xlsWorkSheetCollection.itemFromNum (1);

    // Write to the worksheet cells headings.

    xlsWorkSheet.cells (). item (row, 1). value ('Depot PostCode');

    xlsWorkSheet.cells (). item (row,2). value ('To PostCode');

    xlsWorkSheet.cells (). item (row,3). value ('Distance in Meters');

    row ++;

    // Save Excel document.

    xlsWorkbook.saveAs(fileName);

    // Close Excel.

    xlsApplication.quit ();

    xlsApplication.finalize ();

    }

       catch(Exception::Error)

       {

        info(strFmt("%1", Exception::Error));

       }

    }

    public void exportDataToExcel()

    {

    SysExcelApplication xlsApplication;

    SysExcelWorkBooks xlsWorkBookCollection;

    SysExcelWorkBook xlsWorkBook;

    SysExcelWorkSheets xlsWorkSheetCollection;

    SysExcelWorkSheet xlsWorkSheet;

    SysExcelRange xlsRange;

    InventTable inventTable;

    int row;

    str fileName;

    System.Exception ex;

    if (WINAPI:: fileExists ("C:\\test.xslx"))

    {

       row = this.findLastRow();

       try

       {

        // Name of the Excel document.

        xlsWorkBookCollection.open("C:\\test.xslx");

        // Excel open and initialize.

        xlsApplication = SysExcelApplication:: construct ();

       // xlsApplication.visible (true);

        // Create an Excel Worksheet produce.

        xlsWorkBookCollection = xlsApplication.workbooks();

        xlsWorkBook = xlsWorkBookCollection.add();

        xlsWorkSheetCollection = xlsWorkBook.worksheets();

        xlsWorkSheet = xlsWorkSheetCollection.itemFromNum (1);

        row ++;

        // Excel Worksheet with data fill / (Excel cells fill).

        xlsWorkSheet.cells (). item (row, 1). value ("Test Actual");

        xlsWorkSheet.cells (). item (row, 2). value ("Test VIrtual");

        xlsWorkSheet.cells (). item (row, 3). value ("Test VIrtual 3");

       // row ++;

        // Check whether the document already exists.

        // Save Excel document.

        xlsWorkbook.saveAs("C:\\test.xslx");

        // Close Excel.

        xlsApplication.quit ();

        xlsApplication.finalize ();

        }

       catch(Exception::Error)

       {

        info(strFmt("%1", Exception::Error));

       }

       }

    }

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,985 on at
    RE: Creating Excel File and then appending data to it

    Are you able to share the code you use for this?

    The bacis process for doing this would be to open the existing Excel sheet, then append the new data to it and save it again.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Creating Excel File and then appending data to it

    Hi Rudi,

                 Thaks for your reply, What i am doing is, i create a Excel file with heading and save it to disk. then i get a record and i want to insert it in to that Excel file, if i save data as using "SaveAs" method it replaces the current Excel file and the headings are removed , what i want is the data from file should not be removed and new data is inserted after the last record in the file.

    Best Regards

    Shah

  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 3,985 on at
    RE: Creating Excel File and then appending data to it

    You have already written data to an excel file, then you just have to open an existing file and write to it like before.

    There is no append to excel file function like with text files.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,252 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,089 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans