How to add formula to excel column while data export from Dynamics AX.

This Post is not about how the Dynamics AX data imported or exported, as you will find lots of blogs from Google. This post is about how to place the formula on a excel column which is not much available through search engins.

for this post you should have knowledge of excel export through x++.  if you would like to know see below links.

http://axaptadeveloper.blogspot.com/2008/06/export-data-to-microsoft-excel-from.html

https://community.dynamics.com/product/ax/axtechnical/b/mafsarkhan/archive/2011/03/07/dynamics-ax-code-to-read-write-data-to-excel.aspx

Few days back i came accross a scenario where i have to place the formula in a column of excel, the scenario was that i have to create the Bidding price sheet where i have to export some item data along with quantity and two empty columns, ‘Unit bidding price‘ and ‘total bidding price‘, both of these columns were empty but i also have to place formula of product on total bidding price column so that we can get the product of Quantity and Unit Bidding price. below piece of line helps me in putting formula on a column.

sysExcelWorksheet.cells().item(row,11).value(strfmt(“=PRODUCT(H%1:J%1)”,row));

Thanks to Kent Carstens who helps me out in this.

Advertisements

6 Responses to How to add formula to excel column while data export from Dynamics AX.

  1. AML says:

    Hi Amir,

    Do you have any idea on how to insert/add new row in MS Excel using x++ code? Please help me… thanks.. more power….

    Regards,

    AML

  2. AML says:

    Hi Sir Amir,

    I am asking for your help abount x++.

    Do you have any idea on how to add/insert row/rows in MS Excel using x++ code?

    Regards,

    AML

    • Amir Nazim says:

      i think u are looking something like this ?

      Writing Data to Excel file
      How it works
      1. Use SysExcelApplication class to create excel file.
      2. Use SysExcelWorkbooks and SysExcelWorkbook to create a blank workbook(by default 3 worksheets will be available).
      3. Use SysExcelWorkSheets to select worksheet for writing data.
      4. SysExcelCells to select the cells in the excel for writing the data.
      5. SysExcelCell to write the data in the selected cells.
      6. Once you done with write operation use SysExcelApplication.visible to open
      file.

      static void Write2ExcelFile(Args _args)
      {
      InventTable inventTable;
      SysExcelApplication application;
      SysExcelWorkbooks workbooks;
      SysExcelWorkbook workbook;
      SysExcelWorksheets worksheets;
      SysExcelWorksheet worksheet;
      SysExcelCells cells;
      SysExcelCell cell;
      int row;
      ;
      application = SysExcelApplication::construct();
      workbooks = application.workbooks();
      workbook = workbooks.add();
      worksheets = workbook.worksheets();
      worksheet = worksheets.itemFromNum(1);
      cells = worksheet.cells();
      cells.range(‘A:A’).numberFormat(‘@’);
      cell = cells.item(1,1);
      cell.value(“Item”);
      cell = cells.item(1,2);
      cell.value(“Name”);
      row = 1;
      while select inventTable
      {
      row++;
      cell = cells.item(row, 1);
      cell.value(inventTable.ItemId);
      cell = cells.item(row, 2);
      cell.value(inventTable.ItemName);
      }
      application.visible(true);
      }

      Reading Data from Excel File

      static void ReadExcel(Args _args)
      {
      SysExcelApplication application;
      SysExcelWorkbooks workbooks;
      SysExcelWorkbook workbook;
      SysExcelWorksheets worksheets;
      SysExcelWorksheet worksheet;
      SysExcelCells cells;
      COMVariantType type;
      int row;
      ItemId itemid;
      Name name;
      FileName filename;
      ;
      application = SysExcelApplication::construct();
      workbooks = application.workbooks();
      //specify the file path that you want to read
      filename = “C:\\item.xls”;
      try
      {
      workbooks.open(filename);
      }
      catch (Exception::Error)
      {
      throw error(“File cannot be opened.”);
      }
      workbook = workbooks.item(1);
      worksheets = workbook.worksheets();
      worksheet = worksheets.itemFromNum(1);
      cells = worksheet.cells();
      do
      {
      row++;
      itemId = cells.item(row, 1).value().bStr();
      name = cells.item(row, 2).value().bStr();
      info(strfmt(‘%1 – %2’, itemId, name));
      type = cells.item(row+1, 1).value().variantType();
      }
      while (type != COMVariantType::VT_EMPTY);
      application.quit();
      }

      • AML says:

        Hi Amir,

        Thanks for replying 🙂

        For example, I already have like 10 records in excel then, I want to insert new row in the 5th row to insert new record, would it possible using x++?

        Regards,

        AML

  3. axTr says:

    hi amir,

    How to Export item picture in excel

  4. Srikanth says:

    HI,

    How to read the excel cell Formula through X++

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: