Finance | Project Operations, Human Resources, ...

Answered

excel export through code in AX 2012 R3

(0) Share

Report

Posted on by @rp@n

Hi all,

Can you please let me know i did excel export in AX 2012 R3. It's working fine.

I need some modification in excel.

1. In header i wants to make columns BOLD and background YELLOW.

2. The date format customer given below format

BUT In excel it's coming like

code

orderDate = maxPurchaseOrderDate;

a = date2str(orderDate,123,2,DateSeparator::Hyphen,2,DateSeparator::Hyphen,4);

3. In the Unit price and net amount columns customer wants CURRENCY SYMBOL + AMOUNT.

net amount = PurchLine.netAmount;

Unit price = PurchLine.PurchPrice

@ JAPAN CURRENCY SYMBOL

4. In excel the amount is showing for Unit price and Net amount IN BELOW FORMAT. I WANTS TO SHOW SAME LIKE AX

4. I wants to FRREZE panes the HEADER PART ONLY

please give me more shed on this.

thanks!

I have the same question (0)

All responses (10)

Answers (1)

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

How do you do the export?

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Through sysexcelApplication...

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Can you please anyone

Was this reply helpful? Yes No
Suggested answer

Anup Shah MSFT on at

Like (0)

Report

I believe the excel date/time settings will come from the default excel template, and the default uses you local language settings

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report
Hi @rp@n,

You can set cell format using numberFormat method, but you need to determine current user settings. Maybe the easiest way is to set format as Text and use strFmt function to convert date and amount to string and put string value to the Excel file.

SysExcelCells cells = worksheet.cells(); cells.range('A:A').numberFormat('@'); // data as Text for column A

docs.microsoft.com/.../excel.range.numberformat

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Kindly provide me answers of all question please

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

hello Sergei,

if possible, give me answer of all questions please

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi @rp@n,

1. There is style method on SysExcelRange class, you can use it to set up a style for a range. Also, check SysExcelWorksheetHelper class, it contains some useful methods for you.

2. Check SysExcelWorksheetHelper.getMonetaryNumberFormat and try to design something similar for date. Or format date to a string in AX to the proper format and use text format in Excel.

3. It's similar to point 2, use SysExcelWorksheetHelper.getMonetaryNumberFormat as a base for own format, or convert to string in AX and use text format in Excel.

4. Here can be found some information about FreezePanel method community.dynamics.com/.../export-data-to-excel-through-x

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

thanks a lot Sergei,

do you have any sample code?

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi @rp@n,

I have only sample how to set cell format and posted it already above. Try to search by references in code by mention methods and classes and check usage.

Was this reply helpful? Yes No

Join the conversation

Community site session details

excel export through code in AX 2012 R3

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

excel export through code in AX 2012 R3

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates