View Issue Details

IDProjectCategoryView StatusLast Update
0019927mantisbtreportspublic2015-09-06 17:37
ReporterAlexis VibetAssigned Tovboctor 
Status closedResolutionfixed 
PlatformLinuxOSDebianOS Version7
Product Version1.2.15 
Target Version1.3.0-beta.3Fixed in Version1.3.0-beta.3 
Summary0019927: Wrong datatype in excel XML export

When generating excel XML export, sometimes the excel datatype is not valid for some particular values.

In function excel_prepare_string, excel_api.php file, line 142 :

The excel datatype is either "Number" or "String" depending on the result of is_numeric.

However is_numeric(" 42") returns true so the "Number" datatype is chosen, but excel does not consider " 42" to be a number and refuse to open the file.

Steps To Reproduce

In one of the bugs, input " 42" (<space>42) in "OS version", then try to do an excel export : excel will complain the value " 42" is invalid and will refuse to open the file.

Additional Information

tested with PHP 5.4 on debian 7

XML file opened with Excel 2007 on Windows 7.

TagsNo tags attached.




2015-07-14 01:16

manager   ~0051051

Pull Request:

Please test and confirm the fix above.



2015-07-14 04:17

developer   ~0051058

Alexis Vibet, could you please test if a value like for example 0x539 (which is numeric in terms of PHP is_numeric function) works with Excel 2007?



2015-07-14 10:15

manager   ~0051061

It would also be interesting to know which field contained the space, since the approach we typically follow is to trim on save. So in addition to trimming on export, we can also fix the missing trim on save.



2015-07-14 12:55

reporter   ~0051066

I tought mantis made a trim on save on all fields.

Alexis Vibet

Alexis Vibet

2015-07-15 10:13

reporter   ~0051071

The fix does solve the problem with <space>42.

Putting 0x539 in the "Os Version" field does make excel unable to open the file (complaining that the value is invalid). I don't know mubch about the types of number Excel allows, but it seems clear the is_numeric function cannot be trusted in that case.

@vboctor and fgalvis
The field used is "OS Version".



2015-07-15 10:55

developer   ~0051072

Last edited: 2015-08-15 14:29

View 2 revisions

It does not make that much sense to have different types in one column based on the content of one row.
e.g. export
Issue 1, OS Version = 1, type numeric
Issue 2, OS Version = 1b, type string

Do we have any standard field that is always numeric?
Hardly any, e.g. the issue id.

What about always using "String"?
Would we loose a lot of functionality in Excel?
If so, we could use numeric types just for fields that are numeric in database and numeric customs field.



2015-08-15 14:06

administrator   ~0051263

I've updated the pull request to only use numeric for the numeric fields as per @atrol suggestion. My testing shows that it works fine. Please test and let me know.



2015-08-16 11:39

developer   ~0051269

My testing shows that it works fine
Have you been able to test Excel 2007?

I am asking as I was not able to reproduce the original issue with newer Excel versions.



2015-08-16 17:11

manager   ~0051271

@atrol, no I haven't tested on Excel 2007 and won't be able to. But now we are not dependent on is_numeric() and hence the cause mention here for the breakage is no longer relevant. I'm planning to merge the PR soon and if there is an issue we can follow up later.



2015-08-17 03:22

developer   ~0051277

I don't have access to Excel 2007, but I think I have a 2003 version somewhere at home if that can help.



2015-08-17 03:50

developer   ~0051280

hence the cause mention here for the breakage is no longer relevant
I do also not expect the issue any longer, thus go for the merge.
I think I have a VM somewhere with Office 2007 and will try.



2015-08-17 16:34

developer   ~0051288

Tried Excel 2007, got no error.

Related Changesets

MantisBT: master 7f81652b

2015-07-14 01:15:14


Details Diff
Fix Excel errors for numeric cells with spaces

Fixes 0019927
mod - core/excel_api.php Diff File

Issue History

Date Modified Username Field Change
2015-07-13 11:59 Alexis Vibet New Issue
2015-07-14 01:16 vboctor Assigned To => vboctor
2015-07-14 01:16 vboctor Status new => assigned
2015-07-14 01:16 vboctor Note Added: 0051051
2015-07-14 04:17 atrol Note Added: 0051058
2015-07-14 10:15 vboctor Note Added: 0051061
2015-07-14 12:55 fgalvis Note Added: 0051066
2015-07-15 10:13 Alexis Vibet Note Added: 0051071
2015-07-15 10:55 atrol Note Added: 0051072
2015-08-15 14:06 vboctoradmin Note Added: 0051263
2015-08-15 14:29 atrol Note Edited: 0051072 View Revisions
2015-08-16 11:39 atrol Note Added: 0051269
2015-08-16 17:11 vboctor Note Added: 0051271
2015-08-17 03:22 dregad Note Added: 0051277
2015-08-17 03:50 atrol Note Added: 0051280
2015-08-17 11:33 vboctor Changeset attached => MantisBT master 7f81652b
2015-08-17 11:33 vboctor Status assigned => resolved
2015-08-17 11:33 vboctor Resolution open => fixed
2015-08-17 11:33 vboctor Fixed in Version => 1.3.0-beta.3
2015-08-17 11:34 vboctor Target Version => 1.3.0-beta.3
2015-08-17 16:34 atrol Note Added: 0051288
2015-09-06 17:37 vboctoradmin Status resolved => closed