Change of DATE format in EXCEL sheet

[Former Member]

Hi All,

I need to have the <b>dates in an excel sheet</b> in <b>MM/dd/yyyy format</b>. Let me give you a situation.

Suppose I have 2 users, say <b>user1</b> and <b>user2</b>. For <b>user1</b>, I need to give the date in <b>yyyy/MM/dd</b> format and for <b>user2</b> in <b>MM/dd/yyyy</b> format.

I have defined a <b>function in component controller</b> for generating the <b>excel f</b>ile. In that function also I have used<b> ISimpleModifiableType</b> to set the format of date as per the user. I am displaying the dates in the <b>View</b> and also setting them in the<b> Excel sheet</b>.

I am <b>calling this function from the V</b>iew where I need to <b>display the date</b> as well as <b>provide the Excel generating LinkToURL</b>.

When I call this function, <b>it is displaying the dates in the required format</b> for both the users correctly, <b>ie yyyy/MM/dd for user1 and MM/dd/yyyy for user2</b>. <b>But, in the excel sheet, the format is not coming as per the requirement</b>. For user1, the dates are coming correctly in yyyy/MM/dd format. But for user2 also, the format is coming in yyyy/MM/dd <b> instead of MM/dd/yyyy</b>. I have <b>used the same attribute</b> to display the date in the screen , which is giving date in proper format as per user.

Please suggest <b>how to put the date in MM/dd/yyyy format in the excel sheet</b>.

Thanks,

Reinuka

[Former Member]

Hi Reinuka,

The default settings of excel sheet is such that it will show date values in yyyy/MM/dd format only. Hence u r facing such problem.

Suppose u r using attribute 'aaa' of type date for storing the date. The place where u set the value for the label in the excel, try using wdContext.currentContextElement().toGMTString() ... maybe this will work.

Regards,

Sayan

[former_member182294]

Hi Reinuka,

What is the Data type of the attribute assigned to date field? If its date type then try changing the type to 'string' so that it will keep the source formating.

I guess there is no other setting needs to be done to show MM/dd/yyyy format excel sheet.

Regards

Abhilash

[Former Member]

Hi Abhilash,

I tried to change the type of my attribute "Production Date" to string in the excelsheet but still it is taking yyyy/mm/dd format.The format is coming fine on the screen but it is setting the format i.e.yyyy/mm/dd on the excelsheet.

With string datatype also it is taking yyyy/mm/dd and without string datatype also

it is taking yyyy/mm/dd.The format on the screen is coming as mm/dd/yyyy but on the excel it is not.

I couldn't change the type of my attribute to date as it is asigned in the model.But I have set it to string in the excelsheet method by using the code given below where m using a new attribute to save the value which is coming onto the screen..But still it is not setting it to yyyy/mm/dd.And now m not using any method for setting the format in the controller.

wdContext.currentContextElement().setCxt_PrdDate(wdContext.nodeCa_Date().getCa_DateElementAt(j).getCa_Datep().toString());

label = new Label(5,j+2,wdContext.currentContextElement().getCxt_PrdDate());

where Cxt_PrdDate is the new attribute.

Ca_Datep() is the attribute for Production Date.

I have tried the above method but it is not coming.

Can you please suggest some method by which i can get the correct format on the excel.

[Former Member]

Hello Reinuka,

In the JXl api try and search if you can add date .

if yes then you need to first taek the date from the dB, format it in the proper format and then add it to the excel sheet.

However you could still try and check the code for other applications for which it is working fine?

Thanks and Regards

Pradeep Bhojak

[former_member185029]

Hello Renuka,

U will have to create Style in XML format.

</Style>

<Style ss:ID="s22">

<NumberFormat ss:Format="dd/mm/yy;@"/>

</Style>

Let me know your email and I will send you source code.

Ashutosh