It sounds like you've found one of the changes between v9 and v10, thedefault excel format is now Text.
To work around this you can wrap your numeric expressions/fields in the num(), they will then export to excel as numeric values.
From the V10 SR3 release notes:
The default export format when exporting to Excel is now always text. Ifyou
experience that exports from your application has changed, then you need tochange
the format of those columns to some appropriate numeric format.
Wrapping the numeric expressions/fields in num() does not work when you have mixed expression number formats and use the "Send to Excel" option. However, it does work when you use the "Copy to Clipboard", ... "Full Table". Then paste it in Excel.
I have created buttons with macros for my end users that do the copy/paste and adjust column/row widths of the cells in Excel where I am using tables with mixed formats. It is one click for the end user and they really like that option.
I was hoping that this was a bug and QlikTech would correct this issue going forward. I am trying to eliminate as many macros as possible, but with this issue, I don't see how to avoid it.
Working in 9SR6 and 7 I have found that exporting to Excel depends very much on the version of not only Excel but also of windows. E.g. Exporting "2011/10" (calendar week 10 of year 2011) it is a string not a date function I get differing results:
- W7 Ultimate to Office 2010 - the entire column comes out clean "as is seen in QV"
- XP to Office 2003 - the column has a mixed status of "as seen in QV" and the number in time format 40nnn. Have not found a way of controlling that.
- W7 professional to Office 2003 or 2010 often also a mixed salad.
No amount of formatting in Excel sets this correctly.
Maybe these things are related?