Export excel numbers

dosuna7

Hello,
I have a problem when export to Excel a number.

I use thymeleaf to create table:

<tbody>
<tr th:each="movement : ${movements}">
<td th:text="${#dates.format(movement.getDate(), 'dd-MM-yyyy')}"></td>
<td th:text="${movement.getOperationType().getName()}"> </td>
<td th:text="${#numbers.formatDecimal(movement.getAmount(), 0, 'POINT', 2, 'COMMA')}"></td>
</tr>
</tbody>

When export to PDF o CSV, the export is correct. But when export to Excel the numbers are incorrect.

Mi table is:
Date | operation | amount
30-07-2020 | Contribution | 2.000,00

I search in forums and I see this solution:
<tbody>
<tr th:each="movement : ${movements}">
<td th:text="${#dates.format(movement.getDate(), 'dd-MM-yyyy')}"></td>
<td th:text="${movement.getOperationType().getName()}"> </td>
<td th:text="${movement.getAmount()}"></td>
</tr>
</tbody>

$(document).ready(function() {
$('.table-winvest').DataTable(
{
"order":[],
"dom": 'lBfrtip',
buttons: [
'excel', 'pdf', 'csv'
],
columns: [
{ data: "1", render: $.fn.dataTable.render.text() },
{ data: "2", render: $.fn.dataTable.render.text() },
{ data: "3", render: $.fn.dataTable.render.number('.', ',', 2, '') }
],
initComplete: function() {
var $buttons = $('.dt-buttons').hide();
$('#exporDatatable').on('click', function(e) {
var btnClass = e.target.id ? '.buttons-' + e.target.id : null;
if (btnClass) $buttons.find(btnClass).click();
});
}
}
);
});

But also don't work, work in a view and PDF and CSV, but Excel I have the same problem.

Thanks.

dosuna7

P.S. It only happens when the numbers are less than a thousand

kthorngren

Please describe the problem with the numbers. What are you expecting and what do you see?

Please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

dosuna7

The datatable is this:

PDF export is this (this case is correct):

Excel export is this (this case is incorrect when numbers are less than a thousand):

colin

As Kevin said, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

dosuna7

The datatable is this:

PDF export is this (this case is correct):

Excel export is this (this case is incorrect when numbers are less than a thousand):

tangerine

That is not a test case. Please re-read Colin's last post.

dosuna7

Good Morning.
Sorry is my first time.
I create a Test CAse in JSFiddle: https://jsfiddle.net/arc97fbk/2/
In the test-case not include the CSS but you can see the example. The numbers in the table it's correct, but the numbers in excel not.
When download the excel you need rename the file.

Thanks, and sorry

colin

Thanks for the test case, but it isn't running and demonstrating the problem. To progress this, we need to see the issue, with reproducible steps.

Colin

dosuna7

Hello.
By reproducing the case you need:
1. Click in Excel button.
2. Download the excel
3. Rename Excel to open file.
4. Open file and you can see the column C the numbers higger thousand it's correct. But the numbers lower Sheet C4, and C6 not is correct.
The datatable in front end is this:
Fecha Operación Cantidad (USDT)
30/06/2020 Contribution 17.978,00
28/07/2020 Contribution 853,39
02/10/2020 Compounding 6.630,61
31/12/2020 Compounding 565,16

The datatable in excel is
Fecha Operación Cantidad (USDT)
30/06/2020 Contribution 17.978,00
28/07/2020 Contribution 85.339
02/10/2020 Compounding 6.630,61
31/12/2020 Compounding 56.516

The value 853,39, when export PDF it's correct, but when export to excel, this value is converted to 85.339.

Thanks

colin

As I said, the test case isn't runnable - it's giving errors. Please can you look at that and provide a test case that executes,

Colin

dosuna7

Sorry but I don't undersand. I run the cas and work.

colin

Sorry, you are right, I was seeing the errors and the lack of styling and assumed it hadn't worked - apologies for that.

Looking at your issue, this thread should help, it's asking the same thing.

Cheers,

Colin

dosuna7

Hello,
I see the link that you send me. I test and modify my webpage and simplify for if you can test. I separate the JS datatable. https://jsfiddle.net/soa6d2n8/

You can see that implement the solution but don't work.

colin

The exportOptions were in the wrong place - see updated here. You would need a similar conversion for the numbers > 1k.

It would probably be worth looking at orthogonal data instead - take a look at the checkbox field here. That way you can modify the data in the columns.render function,

Colin

dosuna7

Thanks Colin!