LANSA Integrator: ExcelReadService how to change date format and locale of Excel cell.

[Rieko Saitoh]

Hi all,

I would like to set the dateformat in ExcelReadService as below.
March 14, 2012
Such a format is not common in Japanese. But in Excel we can change the cell formatting to set the date format.

Cell Formatting - Display Format - Date
Setting the locale to English (US) and the type "March 14, 2012".

cell setting.png (78.46 KiB) Viewed 7156 times

Is it possible to change the dateformat like this with ExcelReadService?
I read the manual and found that the date format can be changed with the DEFINE command. Is it possible to change the locale as well?

Thank you.

Best regards,
Rieko Saitoh
LANSA japan

[Dino]

Hi

I saw this example in the documentation, it shows how to change the date format, not the locale on it. Maybe an enhancement will be need to be able to change the locale.

Code: Select all

#jsmxcmd := 'ADD OBJECT(*CELLSTYLE) COLUMN(5) RANGE(5,10) TYPE(*DATE) FORMAT(*FORMAT14)'

https://docs.lansa.com/15/en/lansa093/c ... j_0485.htm

[Dino]

Also, per the reusable part code in the XJSMEXCL ExcelService example provided in V15, looks like while format is a valid parameter for CellStyle, Locale is not there.

Code: Select all

#xjsmcmd := 'add Object(*CellStyle) Sheet(' + #Sheet + ') Column(' + #Column + ') Range(' + #Range + ') Type(' + #type + ') Font(' + #Font + ') FontSize(' + #FontSize + ') FontColor(' + #FontColor + ') Bold(' + #Bold + ') Italic(' + #Italic + ') Border(' + #Border + ') BorderStyle(' + #BorderStyle + ') BorderColor(' + #BorderColor + ') BackGround(' + #BackGround + ') Wrap(' + #Wrap + ') Locked(' + #Locked + ') Indent(' + #Indent + ') HAlign(' + #HAlign + ') VAlign(' + #VAlign + ')'

* Only add the format is nominated to ensure correct default is applied
If (#format <> *blank)

#xjsmcmd := #xjsmcmd + ' Format(' + #Format + ')'

Endif