Microsoft Dynamics AX (Archived)

Export dates to excel file

(0) Share

Report

Posted on by Martina Bergamo

1,470

Hi to all!

I've created a file excel from Dynamics AX 2012.

In the file I have a date (DeliveryDate). I want to format the date depend on the language of the vendor, for which the excel is created.

At the moment I use the following code:

System.Globalization.DateTimeFormatInfo myDateTimeInfo = System.Globalization.DateTimeFormatInfo::get_CurrentInfo();

cells.range('M:O').numberFormat(myDateTimeInfo.get_ShortDatePattern());

But the result, in the excel file is: dd.07/yyyy

For info: the delivery date is 16.07.2015 and the language of the customer is German.

Any ideas?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

JL Aguilera 68 on at

Like (0)

Report

RE: Export dates to excel file

You can try format date before set value on the cell.

for example;

ExcelHelper.setValue(row,5,date2str(custtrans.TransDate,321, 2, 3, 2, 3, 4));

//return 11/10/2019

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Export dates to excel file

You will get a date value even when you pass the date as a string, because your column is already formatted as date by using the .numberformat() method.

I've tried that directly with SysExcelCells class (not through Helper) and it works - it sets normal date value, so all filtering/sorting/etc. works properly.

Was this reply helpful? Yes No
Verified answer

Martina Bergamo 1,470 on at

Like (0)

Report
RE: Export dates to excel file

Texcel.png"/api/data/v9.1/msdyn_richtextfiles(843A4930-6B6E-4E84-B375-1026ACEE2AA3)/msdyn_imageblob/$value?size=full">

static void exportExcelFile(Args _args) { SysExcelApplication xlsApplication; SysExcelWorkBooks xlsWorkBookCollection; SysExcelWorkBook xlsWorkBook; SysExcelWorkSheets xlsWorkSheetCollection; SysExcelWorkSheet xlsWorkSheet; SysExcelWorksheetHelper worksheetHelper; SysExcelHelper sysExcelHelper; VendRFQJour vendRFQJour; int row = 1; str fileName; str worksheetName; Date deliveryDate; fileName = "C:\\path\\Test.xlsx"; vendRFQJour = VendRFQJour::find("000103"); worksheetName = strfmt("%1", date2str(systemdateget(),321,DateDay::Digits2,DateSeparator::None,DateMonth::Digits2,DateSeparator::None,DateYear::Digits4, DateFlags::None)); sysExcelHelper = SysExcelHelper::construct(); sysExcelHelper.initialize(); xlsWorkSheet = sysExcelHelper.addWorksheet(worksheetName); worksheetHelper = SysExcelWorksheetHelper::construct(xlsWorkSheet); worksheetHelper.addColumn(1, 'RFQ Id', Types::String); worksheetHelper.addColumn(2, 'Delivery date', Types::Date); worksheetHelper.setCellValue(1, row, 'RFQId'); worksheetHelper.setCellValue(2, row, 'DeliveryDate'); row++; worksheetHelper.setCellValue(1, row, vendRFQJour.RFQId); worksheetHelper.setCellValue(2, row, vendRFQJour.DeliveryDate); if(WinApi::fileExists(fileName)) WinApi::deleteFile(fileName); sysExcelHelper.save(filename); sysExcelHelper.close(); info(strFmt("Creato file: %1", fileName)); }

The method SysExcelWorksheethelper.addColumn, add a column to the excel file, aligns and formats the column based on the type.

Martina

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: Export dates to excel file

When you are assigning a value to an Excel cell (I presume that you're using SysExcelCells.item(..).value(<myDate>)), don't pass is as the date value, but convert it to a string (a simple strFmt("%1", <myDate>) will do).

Was this reply helpful? Yes No
Martina Bergamo 1,470 on at

Like (0)

Report
RE: Export dates to excel file

Ok, I've resolved my problem...I've inserted the short date pattern in a string...

str format = myDateTimeInfo.get_ShortDatePattern(); cells.range('M:O').numberFormat(format);

But, now, when I open the excel file, I see this:

I want only the date, without the time...

Any ideas?

Was this reply helpful? Yes No