Excel export DateTime field convert to local timezone

[martin.k]

Hi!

when I generate an excel file via aspose all datetime fields are represented as a double value in excel => change format and now it’s a datetime field. all good.

PROBLEM:

The datetime field with the changed format in EXCEL has UTC timezone but I want to get the datetime field in local timezone => php server. respectively the local time of the user logged in the SUGAR CRM instance.

Is there any way to do this?

thanks in advance!!

[Amjad_Sahi]

Hi,

Thanks for providing us some details about your issue.

Well, I am afraid, we not very sure what you really want to get. For your information, MS Excel stores DateTime in numeric value/data. Do you mean the DateTime value by Cell.DateTimeValue or you need to change the saved double value in the generated Excel file? Please elaborate your requirements and give us complete details about your requirements or problem. We would also need you give us a sample Excel file and sample code to show what you want to get/ achieve.

Thank you.

[martin.k]

Hi,

thanks for your fast reply.

I need a possibility to use a specific timezone for any timedate that aspose export to an excel file. Yes I mean the Cell.DateTimeValue.

Source is a timedate from a mysql database which is stored in UTC timezone. Does the change of the JVM timezone has any effect to the export?

I use this method to import the cell data:

"$this->m_Cells->importTwoDimensionArray(ClassFactory::_t1($arrO2DObject0), $pInt1, $pInt2, $pBoolean3);"

The double value in excel is fine after I changed the format to time/date. BUT it’s still in UTC timezone. I added the excel file to the post. The doulbe value in the excel file is unfortunately saved in UTC timezone.

thanks you!

[Amjad_Sahi]

Hi,

We will get back to you soon and provide you further details or any workaround (if possible).

By the way, could you try to use Style.setCustom() method instead of Style.setNumber() method to specify your desired DateTime formatting when you change to DateTime.

Thank you.

[Amjad_Sahi]

Hi,

We have evaluated your issue/ requirement a bit. What’s the data and datatype of the elements in
your $arrO2DObject0? We think it should be com.aspose.cells.DateTime or
java.util.Date instances. We are afraid there is no options for you to import
and change the timezone of those datetime values. However, we think you can
rebuild those DateTime/Date instances according to your requirement (such as
plus or minus time offset) before the import call and then import them. <o:p></o:p>

Thank you.

[martin.k]

Hi!

thanks for your reply/support! I resolved my problem by rebuild the instances of type DateTime. I was afraid, that the performance will be decreased but it’s ok!

thanks!