Export data from JSON to excel by preserving data type (from StackOverflow)

agershun commented 9 years ago

I am using alasql.js file to export JSON data into excel file. The problem I am facing is that when I export data it is exporting without any problem but the columns which having the type as number are exported as text in excel.Any help is greatly appreciated.

Source

mpanoratra commented 9 years ago

Is there any update on this? I am running across the same issue.

agershun commented 9 years ago

Sorry, not yet. I increase the priority of this issue.

mathiasrw commented 9 years ago

Any news on this one?

elifio commented 7 years ago

Hi:

maybe this can help you the "number" cell type is not assigned... this is the solution for simple NO Decimals but you can implement others...

if(typeid == 'money') {
                        typestyle = 'mso-number-format:\"\\#\\,\\#\\#0\\\\ _р_\\.\";white-space:normal;';
                    } else if(typeid == 'number') {
                        typestyle = "mso-number-format:'0' "; // <-- this line!
                    } else if (typeid == 'date') {
                        typestyle = 'mso-number-format:\"Short Date\";'; 
                    } else {
                        // FOr other types is saved
                        if( opts.types && opts.types[typeid] && opts.types[typeid].typestyle) {
                            typestyle = opts.types[typeid].typestyle;
                        } 
                    }

mso-number-format:"0" NO Decimals mso-number-format:"0.000" 3 Decimals mso-number-format:"#\,##0.000" Comma with 3 dec mso-number-format:"mm\/dd\/yy" Date7 mso-number-format:"mmmm\ d\,\ yyyy" Date9 mso-number-format:"m\/d\/yy\ h:mm\ AM\/PM" D -T AMPM mso-number-format:"Short Date" 01/03/1998 mso-number-format:"Medium Date" 01-mar-98 mso-number-format:"d-mmm-yyyy" 01-mar-1998 mso-number-format:"Short Time" 5:16 mso-number-format:"Medium Time" 5:16 am mso-number-format:"Long Time" 5:16:21:00 mso-number-format:"Percent" Percent - two decimals mso-number-format:"0%" Percent - no decimals mso-number-format:"0.E+00" Scientific Notation mso-number-format:"\@" Text mso-number-format:"#\ ???\/???" Fractions - up to 3 digits (312/943) mso-number-format:"\0022£\0022#\,##0.00" £12.76 mso-number-format:"#\,##0.00_ \;[Red]-#\,##0.00\ "

mathiasrw commented 7 years ago

Awesome - thanks :)

I imagine we could impelment the most common and let people be able to set their own string...

elifio commented 7 years ago

I think this a better solution... declare your the types in the opts

opts= {
sheetid: 'sheetitle',
headers: true,
caption: {
title: 'Title'
},
column: {style: "font-size:10pt;background-color:#D9D9D9;mso-number-format:'\@';"}, types: {'numberNoDecimal': {'typestyle': 'mso-number-format:"0"'},'number3Decimal': {'typestyle': 'mso-number-format:"0.000"'}},
columns: columnstyle
},

then you must declare colums style

columnstyle.push({columnid: columna.field, title: columna.name, width: 50, cell: { typeid: 'numberNoDecimal'}});

sundarsambandam commented 7 years ago

I am trying to implement but didnt work.can some show me how i can implement this? jsfiddle example will be useful

AlaSQL / alasql

Export data from JSON to excel by preserving data type (from StackOverflow) #305