Support data types in xlsx

[wonderiuy]

Exporting numbers and date like 1.234.567,89 ("." as thousand separator and "," as decimal separator) and dates like 28/09/2019 (dd/mm/yyyy format) are correctly exported with number and date type cell if exporting in "xls" format but are bare text type cell if exported in "xlsx" format. Using the "data-tableexport-value" attribute and passing US formatted value does NOT solve the problem with "xlsx" format. Any help?

[hhurz]

As stated in the readme.md: The implementation of XLSX format (SheetJS/js-xlsx) only lets you export table data, but not any styling information of the html table. Thus for now there is no better support for it.

[wonderiuy]

SheetJS/js-xlsx actually supports data types:

The raw value is stored in the v value property, interpreted based on the t type property. This separation allows for representation of numbers as well as numeric text. There are 6 valid cell types: Type | Description b | Boolean: value interpreted as JS boolean e | Error: value is a numeric code and w property stores common name n | Number: value is a JS number d | Date: value is a JS Date object or string to be parsed as Date s | Text: value interpreted as JS string and written as text z | Stub: blank stub cell that is ignored by data processing utilities **

Is there any way to use these data types with tableExport or to specify them?

[hhurz]

The types b, n, d, s and z are already used internally and will be choosen depending on the table cells JS type. They can't be set externally. Currently I'm reworking the XLSX export process, thus at some time (!) the data attributes and callback functions as known from the other formats should also be available for the XLSX format...

[wonderiuy]

Great news then, thank you. Please recognize correcty also number like 123.456,78 ("," as decimal separator) and date in dd/mm/yyyy format. Thank you again

[hhurz]

FYI: Added support for data attributes and callback functions as known from the other formats in release v1.10.7

[wonderiuy]

WOW, so fast. Thank you, will test today :)

[wonderiuy]

Have done some testing: numbers like 1.234.567,89 (comma as decima separator) are not recognized, have to use data-tableexport-value with 1234567.89 (point as decimal separator) No problem with this Dates are still a problem: date like "31/01/2018" are considered as text. Using data-tableexport-value passing "01/31/2018" became "20180131" in xlsx (dates but personalized format) How should i pass a date to have the correct result dd/mm/yyyy?

[wonderiuy]

Thank you hhurz for your quick development, which I appreciate very very much. The date: {html: 'dd/mm/yyyy'} option works well as input, but the output field is General type and not date type (then sorting it will prompt the user to handle contents as number) Then I've started using data-tableexport-xlsxformatid="14" which partially solve the problem of the output field type (finally date type) but one row the date is correct and the next row is 01/01/1970: ie: 31/01/2018 <-correct 01/01/1970 <-wrong 28/02/2018 <-correct 01/01/1970 <-wrong 31/03/2018 <-correct 01/01/1970 <-wrong and so on. Sorry to bother you again with this issue. Let me say that your plugin now is the only one that handle numbers and dates (nearly :P ) perfectly

[hhurz]

Thank you for testing again! To get rid of this issue :stuck_out_tongue_winking_eye:, it would be very helpful if you could provide sample code for the html table you are testing with. This would make it easier for me to understand what you are doing with the plugin exactly.

[wonderiuy]

OK, this is the table:

test.txt

This is the output:

test.xlsx

And this is the command i launch:

$('#tab_isee').tableExport({type:'xlsx',date: {html: 'dd/mm/yyyy'}, numbers: {html: {decimalMark: ',',thousandsSeparator: '.'}, output: {decimalMark: '.',thousandsSeparator: ''}} });

[wonderiuy]

WIll try tomorrow at office: thank you hhurz

[hhurz]

Thanks for providing the sample data. It helped to detect the reason for the date parsing problem. FYI: Regarding to your sample table, you don't need to use data-tableexport-xlsxformatid="14". "14" is the default format id. Here is the command I used to export your table:

    $('#tab_isee').tableExport({
      type: 'excel',
      date: {html: 'dd/mm/yyyy'},
      mso: {fileFormat: 'xlsx'},
            xslx: {formatId: {date: 14, numbers: 2}},
      numbers: {html: {decimalMark: ',', thousandsSeparator: '.'},
                output: {decimalMark: '.', thousandsSeparator: ''}}
    });

[wonderiuy]

It work flawlessly! Thank you again, also for the code. Where is the donate button? Your code has little typo:

$('#tab_isee').tableExport({
        type: 'excel',
        date: {html: 'dd/mm/yyyy'},
        mso: {fileFormat: 'xlsx',
            xlsx: {formatId: {date: 14, numbers: 2}}
        },
        numbers:{
            html: {decimalMark: ',', thousandsSeparator: '.'},
            output: {decimalMark: '.', thousandsSeparator: ''}
        }
    });

xlsx: has to be under mso: and is xlsx and not xslx. Btw, i dont'know if it's my Excel but I still have to use data-tableexport-xlsxformatid="14" to have the field of the correct type (date) And if i pass an empty cell, the output in xlsx is 0,00 (I have to use data-tableexport-cellformat="" to have an empty cell in the output)

[wonderiuy]

Thank you hhurz for your splendid work