How to get Excel to treat values like '$99' and '99%' as numbers instead of strings

economicpolicy

When I export a table containing numbers with symbols, such as 99% or $99, Excel treats those values as strings, displaying a green tab in the corner of the cell and (when clicking on the cell) displaying a yellow warning icon stating "Number stored as text."

This occurs when exporting as Excel, but not when exporting as CSV. When opening a DataTables CSV, the 99% and $99 values are interpreted as numbers (with the units intact), which is what I want.

This behavior can be seen on the export example here: https://datatables.net/extensions/buttons/examples/html5/simple.html

Compare the "Salary" column in the resulting Excel file vs. the CSV file. In the Excel, the number is left-aligned and treated as a string; in the CSV, the $ sign remains intact, but the number is right-aligned, indicating it has been interpreted as a number.

Is there any way to get the CSV-style behavior from the Excel export, so that numbers with units are treated as numbers instead of strings?

allan

Thanks for your question. At the moment The Excel export sees 99% and $99 as strings since they have non-numeric characters in them. I'm not entirely sure what would be involved in having Excel recognise them as numbers.

Let me do some research into that and I'll get back to you!

Regards,
Allan

allan

I've just committed support for percentage values. They will be automatically detected and the required styling applied, so all you should need to do is include the changes. The nightly version of Buttons will update in the next few minutes with the change.

Currency formatting appears to be different, it doesn't appear to use a number formatter. I'll need to look more into that.

Regards,
Allan

allan

It took a little longer than I expected, but this is a useful thing to have in Buttons I think (thanks for prompting and promoting this feature!), but I've just committed support for currency output in Excel.

Its currently limited to only dollar, pound and euro signs, I may add others in future, and the Excel formatting isn't particularly flexible (I don't wish to add a bonkers number of options to the files and it will just increase code size), but the basic idea seems to work nicely.

Regards,
Allan

economicpolicy

Thanks Allan, this is fantastic! Both the $ and % values now work great.

I'm looking to customize the number of decimal places included in the % values. Currently no decimal places are displayed (the full number is captured by Excel and is displayed if you double-click a cell, but only the rounded integer is displayed). Can you advise on how this could be accomplished?

In my case I am trying to display the same number of decimal places as are in the original table (i.e., to have the Excel export look exactly like the HTML table), but it would also work for my current purposes just to hard-code things so that percent values display with one decimal place.

allan

Sorry I didn't manage to get back to you about this yesterday (unfortunately unwell, but more or less recovered now!). I'll look into what options there are for this and will reply back when done.

Regards,
Allan

allan

I've just committed a change that will automatically detect percentage values with 1 d.p. precision and show them in the Excel sheet formatted as such.

It doesn't automatically detect an arbitrary number of decimal places, that would take more code that I would like to add to Buttons (I'd like the library to remain small and portable - its Excel output is very much a secondary function, another library could be used in conjunction with Buttons if more control is required over the Excel file with an API that is not the raw XML).

Having said that, it is quite possible to change the decimal precision using the customize method. For example, let's say you want 2 d.p.:

customize: function ( xlsx ) {
  $( 'xf[numFmtId=9]', xlsx.xl['styles.xml'] ).attr( 10 );
}

9 and 10 are built in styles for OpenXML spreadsheets, meaning 0 and 2 d.p. respectively.

Other values are possible as well, but are a little more complicated. Let me know if you need 3 d.p. and I'll write an example for that.

Regards,
Allan