export to xlsx formatting for currency is blank and date format is odd

Permanently deleted user

23 Juli 2019 15:28

Greetings: 

 

Recently updated to GE 4.11 and HTML 2.12.1. When a user exports parcel tabular data to xlsx, the field results set with currency formatting are blank. Resolved the issue by setting the formatting to default in GE Mgr.

 

The date fields do not export in a date format (i.e. 5/25/2005). The results are in an odd format, perhaps in UNIX? (i.e. 38497.29). Attempting to find a work around similar to what I did for the currency formatted fields, but no joy.

 

Please advise.

 

Thank you,

 

-SJE

 

Mohave County Public Viewer (https://mcgis2.mohavecounty.us/html5/?viewer=moh)

0

• 

  Scott Daniel

  23 Juli 2019 16:05

  That Date value looks like a Julian value. In Excel, just change the format to date and that should fix that part. Not sure about currency.

   

  Scott

  0
• 

  Steffen Helgerod

  24 Juli 2019 00:06

  I too have had this issue since the upgrade to 2.12.1. Date Time and Currency all cause issues for the export to xlsx funtion. I had to convert dates to varchar to enable export to xlsx tables to display properly. Eg: CONVERT(varchar(12), Database.Table.Date, 107) AS [Date] and this resolved that issue.

   

  I have had further issues with anymore than 2 or 3 datalinks those next sheets in the exported xlsx seem to have what I would say are hidden commas in a csv type issue. Randomly there are fields missing or only parts of fields displaying. These datalinks display perfectly in SQL and in Geocortex, but not when run through the export to xlsx tool in the action menu. I have created my own xlsx tool test workflow to export the datalinks I have issues with and these work fine - so its definately the export to xlsx action menu that is causing the issue.

  0
• 

  Permanently deleted user

  29 Juli 2019 15:32

  Same problem here.  I've told users to make the date field readable in excel by using this formula:  =(A1/(60*60*24*1000))+DATE(1970,1,1)-(4/24) where A1 is the cell to reference.

   

  The date number exported from GCX is the number of milliseconds since the Unix epoch date of 1/1/1970. Excel dates are number of days with the fraction being the time which is why you subtract the 4/24 to adjust for UTC.

   

  This bug is independant if your using Oracle or SQL for your Enterprise geodatabase. 

  0

Du måste logga in om du vill lämna en kommentar.