Export to Excel: specific data is missing

elstupid

Link to test case: https://live.datatables.net/ramapozi/1/edit
Description of problem:
I came across this very specific problem with data, see example. It's part of a live table I have with items, which have a brand name and a serial number.

If I try to export this table to CSV or any other option, data is exactly exported as is. If it's exported to Excel however, the value for "18E03778" is converted to #NULL!
If I change it to "18F03778" it exports just fine. It seems this is data specific, where the export to Excel interprets data and sees some as text, others as numbers and this one possibly as hex or something?
Because this is textual data in the JSON it should render as just text in Excel.

Is there a workaround to force this to render as text (even though I feel this shouldn't happen)?

allan

Ooo - that's a nasty one. Thanks for letting me know about it. It is due to this line of code which attempts to be smart about the export, but for your specific data it is not what is wanted.

I might need to rewrite how the export works so that line only runs on number type columns... Let me ponder it a bit

Allan

elstupid

Glad to help Might be a challenge to fix this, good luck

elstupid

Hi Allan. I know you are spending most of your time to DT3, but can you give me a quick update on this issue? Will this be solved in DT2 or are you moving this to DT3?

allan

I haven't progressed it I'm afraid, as I'm not actually sure what the correct fix is going to be just yet! Perhaps the simpilest option is going to be a static parameter Buttons.exponentialNumbers or something, and that will convert only when enabled.

I had been wondering about having it convert only when the whole column is exponential notation - that might be an option?

Would be interested on your thoughts on an API for this.

The most direct option, if you want a quick fix, is to comment that line out!

Allan

elstupid

Thanks Allan. I can't comment that line out as I'm using the CDN version.

I think the best option is a parameter, this gives a level of control.
The convert-when-all-exponential should work in most cases, but the problem will appear again when there's only one item with such a serial number (or a filtered set for that matter).

Don't know how many users are relying on the automatic conversion now, a new parameter would make this a breaking change.

choc

@elstupid

Update: Oops! Sorry, I oversaw that. It doesn't work with the Excel export...

A quick fix on our side would be to assign a type to that column, for example,

{ data: 'nummer', type: 'string' }

I always do it this way so that DT runs faster without having to guess the type.

As an optimisation, if you know the column type in advance, you can set the value using this option, saving DataTables from running its auto detection routine.

Ref: columns.type

choc

@allan

How about just fixing it like this?

add:

var columnTypes = dt.columns(config.exportOptions.columns)
  .indexes()
  .map(function (idx) {
    return dt.column(idx).type()
  })
  .toArray()

and then replace this part with :

            if (
                columnTypes[i] !== "string" &&
                (
                    typeof row[i] === "number" ||
                    (row[i].match &&
                        row[i].match(/^-?\d+(\.\d+)?([eE]\-?\d+)?$/) && // Includes exponential format
                        !row[i].match(/^0\d+/))
                )
            ) {

just a rough attempt to fix the issue by excluding string type columns. I tested it on my side and it works. However, I haven’t given much thought to whether this might affect others.

allan

Yes, checking the column type is probably going to be the correct approach. Its not perfect, but then neither is the current solution!

Many thanks for investigating.

Allan