Export excel numbers

Export excel numbers

dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    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):

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    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):

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    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 :blush:

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    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

  • dosuna7dosuna7 Posts: 9Questions: 1Answers: 0

    Thanks Colin!

Sign In or Register to comment.