Grid Export to Excel problem

[isv485Member]

Hi!

Run jqxGrid demo for spreadsheet. Input in first 3 columns some simple data like 1, 2, 3; 1,2,3; 1,2,3.
Press Export to Excel.
Open generated excel file.
Saved data do not correspond to input data and presented in some date-time format. Something like 12/31/1969.
If input data was in the date-time format, saved data will be correct.

I am running jqxWidgets 2.5.5.

[DimitarParticipant]

Hello isv485,

Please make sure the type property of your source datafields is set correctly, e.g.:

datafields: [
        { name: 'ShippedDate', map: 'm\\:properties>d\\:ShippedDate', type: 'date' },
        { name: 'Freight', map: 'm\\:properties>d\\:Freight', type: 'float' },
        { name: 'ShipName', map: 'm\\:properties>d\\:ShipName' },
        { name: 'ShipAddress', map: 'm\\:properties>d\\:ShipAddress' },
        { name: 'ShipCity', map: 'm\\:properties>d\\:ShipCity' },
        { name: 'ShipCountry', map: 'm\\:properties>d\\:ShipCountry' }
    ]

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[isv485Member]

Hi Dimitar!

Do you mean demo with grid spreadsheet does not set datafields correctly?
I used demo on jqWidgets web site.

Thanks!

[UJankeMember]

I also have the same problem,

source:

…
{ name: ‘BookingUnit’},
{ name: ‘BookingUnitReversal’},
….

which can have 3 possible values : 5202, 5490 or 5I9T that also means they are strings.

Grid:

{
text: ‘BookingUnit’,
dataField: ‘BookingUnit’,
width: “auto”,
cellsalign: ‘center’,
filtertype: ‘checkedlist’},
{
text: ‘Reversal’,
dataField: ‘BookingUnitReversal’,
width: “auto”,
cellsalign: ‘center’,
filtertype: ‘checkedlist’},

BookingUnit mostly starts with 5490 in the first row, BookingUnitReversal with 5I9T

After exporting to excel we have (We also have that problem when we export to an other format (csv and so on)

BookingUnit: 1/1/1970
Reversal: 5I9T

So i guess there is a small bug some where in one of the export.js . This always happens if a datafield with string/varchar format contains numbers.
Greetings
Uwe

[Peter StoevKeymaster]

Hi guys,

If you set the “type” property of your datafields during the Grid’s source object initialization, the Grid will export the data using the appropriate type.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[UJankeMember]

Well in the documentation of the dataadapter you will find:

datafields: An array describing the fields in a particular record. Each datafield must define the following members:
name – A string containing the data field’s name.
type(optional) – A string containing the data field’s type. Possible values: ‘date’, ‘number’, ‘bool’
map(optional) – A mapping to the data field.

now i use

{
name: ‘BookingUnit’,
type: ‘string’
},

and it works fine. Well adding string as possible value to the documentation should help others in this senario.
I guessed string as default type…

Have a nic chrismans
Uwe

[Peter StoevKeymaster]

Hi Uwe,

As you are using the Grid widget, it would be good to take a look at the Grid’s help topics, too. The “Data Sources” help topic explains how to bind the Grid to various data sources and how to initialize the ‘datafields’ array: jquery-grid-datasources.htm.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[isv485Member]

Thanks, Peter!

Setting field type corrects the problem.

[adamParticipant]

Hi

I am having a similar problem. I am using the number type on the data field as mentioned above but MS Excel 2011 still complains that this is a text field.

...	
{ name: 'fieldone', type: 'number' }
...

I opened this file in text editor and the XML seems to indicate the same:

<Row ss:AutoFitHeight="0">
..
 <Cell ss:StyleID="s67"><Data ss:Type="String">0</Data></Cell>
...

When I go in a manually update this ss:Type field it Excel doesn’t prompt me to convert this cell. Is this working in other versions of Excel?

[adamParticipant]

I looked into this further and found that in jqxdataexport:

This is why everything is being set to String? Is this right?

[Peter StoevKeymaster]

Hi adam,

1. If you report an issue regarding our products, please include the following information in your post:

– jQuery version, jQWidgets version, device type(PC, Mobile), browser name and version.
– Please, provide step by step instructions on how to reproduce the reported issue and provide a small sample which demonstrates it.

2. In our sample the Data Type is exported correctly: http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/dataexport.htm?web. You can use it as a reference.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[Author]

Posts