Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

How to create a batch class to import an excel sheet to this form

Posted on by 86

Hi Experts,

I want to know how to Create a batch class to import an excel sheet to the form

    1. Path: Path: AP-> Common -> Amortization Header upload
    2. New dialog will open for Browse
    3. Press OK
    4. This will upload excel data to the above form.

Thanks in advance for your valuable time.

  • Komi Siabi Profile Picture
    Komi Siabi 12,407 Super User on at
    RE: How to create a batch class to import an excel sheet to this form

    Please, let us know if there error is resolved and mark answer(s) that worked for you.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi,

    Thanks a lot Komi and Martin for your valuable time and support.

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,407 Super User on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hello,

    Just like Martin said, you need to have a method on your class.

    I suggest you use a job instead of the class to make it more easier. Create a Job and paste your code in there then run it.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    class ImportAmortizationHeader
    {
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    ImportAmortizationHeader importAmortizationHeader;
    int row;
    date _startDate;
    date _endDate;
    int _numberOfPeriods;
    str _vehicleNumber;
    str _invoiceNumber;
    str _documentNumber;
    str _account;
    real _debitAmount;
    real _creditAmount;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename = "Path\\filename.xlsx";
    try
    {
    workbooks.open(filename);
    }
    catch (Exception::Error)
    {
    throw error("File cannot be opened.");
    }
    
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number
    cells = worksheet.cells();
    do
    {
    row  ;
    _startDate = cells.item(row, 1).value().bStr();
    _endDate = cells.item(row, 2).value().bStr();
    _numberOfPeriods = cells.item(row, 3).value().bStr();
    _vehicleNumber = cells.item(row, 4).value().bStr();
    _invoiceNumber = cells.item(row, 5).value().bStr();
    _documentNumber = cells.item(row, 6).value().bStr();
    _account = cells.item(row, 7).value().bStr();
    _DebitAmount = cells.item(row, 8).value().bStr();
    _creditAmount = cells.item(row, 9).value().bStr();
    
    
    AmortizationHeader.StartDate = _startDate;
    AmortizationHeader.EndDate = _endDate;
    AmortizationHeader.NumberOfPeriods = _numberOfPeriods;
    AmortizationHeader.VehicleNumber = _vehicleNumber;
    AmortizationHeader.InvoiceNumber = _invoiceNumber;
    AmortizationHeader.DocumentNumber = _documentNumber;
    AmortizationHeader.Account = _account;
    AmortizationHeader.DebitAmount = _debitAmount;
    AmortizationHeader.CreditAmount = _creditAmount;
    productType.insert();
    
    type = cells.item(row 1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    }
    amortization.png

    Hi Komi Siabi,

    I have done as you said but still getting this.

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,407 Super User on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi ax.tech,

    So, let take a look at the errors.

    Line 1. The class already exists.

    This means there is already an existing class called. AmortizationHeader. Kindly rename this one to maybe ImportAmortizationHeader.

    Line 23. 

    You need separate your declaration from your logic by a semi-colon. so after real _creditAmount; you need at the next line.

    class ImportAmortizationHeader
    {
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    AmortizationHeader amortizationHeader;
    int row;
    date _startDate;
    date _endDate;
    int _numberOfPeriods;
    str _vehicleNumber;
    str _invoiceNumber;
    str _documentNumber;
    str _account;
    real _debitAmount;
    real _creditAmount;
    ;
    application = SysExcelApplication::construct();

  • Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    You forgot to create a method. You're trying to write code direcly in class header.

    Also, please start using line indentation - code is very difficult to read without it, which is bad for you and also for everyone else who wants to read your code. It's possible if you noticed the bug if you followed the right structure. Also, don't use underscore as the prefix of local variable names. This prefix should be used for method parameters.

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    class AmortizationHeader
    {
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    AmortizationHeader amortizationHeader;
    int row;
    date _startDate;
    date _endDate;
    int _numberOfPeriods;
    str _vehicleNumber;
    str _invoiceNumber;
    str _documentNumber;
    str _account;
    real _debitAmount;
    real _creditAmount;
    
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename = "Path\\filename.xlsx";
    try
    {
    workbooks.open(filename);
    }
    catch (Exception::Error)
    {
    throw error("File cannot be opened.");
    }
    
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number
    cells = worksheet.cells();
    do
    {
    row  ;
    _startDate = cells.item(row, 1).value().bStr();
    _endDate = cells.item(row, 2).value().bStr();
    _numberOfPeriods = cells.item(row, 3).value().bStr();
    _vehicleNumber = cells.item(row, 4).value().bStr();
    _invoiceNumber = cells.item(row, 5).value().bStr();
    _documentNumber = cells.item(row, 6).value().bStr();
    _account = cells.item(row, 7).value().bStr();
    _DebitAmount = cells.item(row, 8).value().bStr();
    _creditAmount = cells.item(row, 9).value().bStr();
    
    
    AmortizationHeader.StartDate = _startDate;
    AmortizationHeader.EndDate = _endDate;
    AmortizationHeader.NumberOfPeriods = _numberOfPeriods;
    AmortizationHeader.VehicleNumber = _vehicleNumber;
    AmortizationHeader.InvoiceNumber = _invoiceNumber;
    AmortizationHeader.DocumentNumber = _documentNumber;
    AmortizationHeader.Account = _account;
    AmortizationHeader.DebitAmount = _debitAmount;
    AmortizationHeader.CreditAmount = _creditAmount;
    productType.insert();
    
    type = cells.item(row 1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    }
    amort.png

    Hi komi siabi,

    I have attached the code and errors. 

    Regards

  • Komi Siabi Profile Picture
    Komi Siabi 12,407 Super User on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi,

    Could you please, show us the error you are getting ?.

    Please, when pasting your codes,

    Use rich formatting -> Insert code

  • ax.tech Profile Picture
    ax.tech 86 on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi Komi Siabi,

    I have created a class

    class AmortizationHeader

    {

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

    AmortizationHeader amortizationHeader;

    int row;

    date _startDate;

    date _endDate;

    int _numberOfPeriods;

    str _vehicleNumber;

    str _invoiceNumber;

    str _documentNumber;

    str _account;

    real _debitAmount;

    real _creditAmount;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specify the file path that you want to read

    filename = "Path\\filename.xlsx";

    try

    {

    workbooks.open(filename);

    }

    catch (Exception::Error)

    {

    throw error("File cannot be opened.");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(4); //Here 3 is the worksheet Number

    cells = worksheet.cells();

    do

    {

    row++;

    _startDate = cells.item(row, 1).value().bStr();

    _endDate = cells.item(row, 2).value().bStr();

    _numberOfPeriods = cells.item(row, 3).value().bStr();

    _vehicleNumber = cells.item(row, 4).value().bStr();

    _invoiceNumber = cells.item(row, 5).value().bStr();

    _documentNumber = cells.item(row, 6).value().bStr();

    _account = cells.item(row, 7).value().bStr();

    _DebitAmount = cells.item(row, 8).value().bStr();

    _creditAmount = cells.item(row, 9).value().bStr();

    AmortizationHeader.StartDate = _startDate;

    AmortizationHeader.EndDate = _endDate;

    AmortizationHeader.NumberOfPeriods = _numberOfPeriods;

    AmortizationHeader.VehicleNumber = _vehicleNumber;

    AmortizationHeader.InvoiceNumber = _invoiceNumber;

    AmortizationHeader.DocumentNumber = _documentNumber;

    AmortizationHeader.Account = _account;

    AmortizationHeader.DebitAmount = _debitAmount;

    AmortizationHeader.CreditAmount = _creditAmount;

    productType.insert();

    type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    }

    but getting syntax error please help and let me where I am wrong

    Regards

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,407 Super User on at
    RE: How to create a batch class to import an excel sheet to this form in ax2012

    Hi Ax.Tech,

    Create an action menu item and call up your class in the property, add it to the form action pane.

    When you click on it, you will see the window to upload your excel file.

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