Excel export - change negative currency to number?

emf411

Hello,

My datatable has positive and negative dollar amounts, for example:

($18,929.00)

I've been able to implement the built-in styles during the Excel button export to remove the $ from the positive numbers, but can someone help me with how to remove the $ from the negative numbers? Ideally, I'd like Excel to display this:

(18,929)

Thank you!!

emf411

I've tried this code but it's still showing in Excel as ($18,929.00) for some reason:

                        customize: function (xlsx) {
                                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            $('row c[r^="I"]', sheet).each(function () {
                                //alert($(this).text() + '\r\n' + $(this).text().replace(/[$,]/g, '').replace(/[(,]/g, '-').replace(/[),]/g, ''));
                                $(this).text().replace(/[$,]/g, '');
                                $(this).text().replace(/[(,]/g, '-');
                                $(this).text().replace(/[),]/g, '');
                                $(this).attr('s', '64');
                            });
                            $('row:first c', sheet).attr('s', '32');
                        }

allan

I don't think you are saving the modified values at all.

$(this).text().replace(/[$,]/g, '');

Will remove $ and , characters, but it doesn't then save it anywhere. You probably want to save it into a variable and then once all your replacements are done write it back into a cell.

Allan

emf411

Thank you Allan, I ended up going a different route by using the exportOptions to accomplish what I needed, this worked perfectly:

                format: {
                    body: function (data, row, column, node) {
                        //Add additional <p> HTML tag to data to avoid strip errors and then strip all the HTML tags
                        data = $('<p>' + data + '</p>').text();

                        //Strip $ from currency column to make it numeric
                        return column === 8 ?
                            data.replace(/[\$\),]/g, '').replace(/[\(]/g, '-') :
                            data;
                    }
                }

--Eric

allan

Nice one - thanks for posting back with your solution.

Allan