Finance | Project Operations, Human Resources, ...

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

(0) Share

Report

Posted on by ax.tech

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.

I have the same question (0)

All responses (12)

Answers (0)

Suggested answer

Komi Siabi 13,107 Most Valuable Professional on at

Like (0)

Report

Hi Ax.Techn,

Take a look at this on how to import into Ax from excel. When you have any challenge let us know.

axpedia.blogspot.com/.../import-from-excel-file-using-x-in-ax.html

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report

Hi Komi Siabi,

Thanks your valuable and knowledge. I just want to know how can i create a button on the form(front end) so, that user can click on that button browse the desired path where excel is located and it can import data from excel.

Was this reply helpful? Yes No
Suggested answer

Komi Siabi 13,107 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Komi Siabi 13,107 Most Valuable Professional on at

Like (0)

Report

Hi,

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

Please, when pasting your codes,

Use rich formatting -> Insert code

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report
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(); }

Hi komi siabi,

I have attached the code and errors.

Regards

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (2)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Komi Siabi 13,107 Most Valuable Professional on at

Like (0)

Report
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();

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report
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(); }

Hi Komi Siabi,

I have done as you said but still getting this.

Regards

Was this reply helpful? Yes No
Suggested answer

Komi Siabi 13,107 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No