View Issue Details

IDProjectCategoryView StatusLast Update
0019927mantisbtreportspublic2015-09-06 17:37
ReporterAlexis Vibet Assigned Tovboctor  
PrioritynormalSeverityminorReproducibilityalways
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
Description

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.

Activities

vboctor

vboctor

2015-07-14 01:16

manager   ~0051051

Pull Request:
https://github.com/mantisbt/mantisbt/pull/615

Please test and confirm the fix above.

atrol

atrol

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?

vboctor

vboctor

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.

fgalvis

fgalvis

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

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

@atrol
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".

atrol

atrol

2015-07-15 10:55

developer   ~0051072

Last edited: 2015-08-15 14:29

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.

vboctoradmin

vboctoradmin

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.

atrol

atrol

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.

vboctor

vboctor

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.

dregad

dregad

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.

atrol

atrol

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.

atrol

atrol

2015-08-17 16:34

developer   ~0051288

Tried Excel 2007, got no error.

Related Changesets

MantisBT: master 7f81652b

2015-07-13 21:15

vboctor


Details Diff
Fix Excel errors for numeric cells with spaces

Fixes 0019927
Affected Issues
0019927
mod - core/excel_api.php Diff File