Answers

• navinAce Posts: 10Questions: 1Answers: 0

  January 24

  body: function(data, row, column, node) {
  // Assuming the problematic column is at index 6 (seventh column)
  if (column === 6) {
  return data ? data : ''; // Force conversion to string to maintain the integrity of the 16-digit number
  }
  return data;
  }
• navinAce Posts: 10Questions: 1Answers: 0

  January 24

  on the column 6 there are 16 digit value number, due to that when i export, the value is rounded off or any special character are added in the beginning of the 16 digit number.
  what can be the solution
• allan Posts: 64,008Questions: 1Answers: 10,554 Site admin

  January 24

  The test case doesn't appear to show a table or export buttons?

  Forcing the column to be a string sounds like a good plan though, although return data ? data : ''; doesn't appear to do that? data.toString() perhaps?

  Allan
• navinAce Posts: 10Questions: 1Answers: 0

  January 24

  i almost tried this much thing non of them work
  return \${data}; output:2223703360068248
  return \u200B${data}; output: ​2223703360068248
  return data.toString(); output: 2223703360068250
  return "${data}"; output: "2223703360068248"
  return '${data}; output: '2223703360068248
• allan Posts: 64,008Questions: 1Answers: 10,554 Site admin

  January 24

  Can you update the test case to be running and show the issue so I can try to debug it?

  Allan
• navinAce Posts: 10Questions: 1Answers: 0

  January 24 edited January 24

  basically it is a huge file dates are adding dynamically

  var file_name = '<?php echo $file_name_excel ?>';
      $('#dynamic_table').DataTable({
          dom: 'Bifrtp',
          bPaginate: false,
          ordering: true,
          buttons: [{
              extend: 'excel',
              footer: true,
              title: file_name,
              className: 'excel-buttonNAD',
              exportOptions: {
                  columns: ':visible',
                  format: {
                      header: function(mDataProp, columnIdx) {
                          var htmlText = '<span>' + mDataProp + '</span>';
                          var jHtmlObject = jQuery(htmlText);
                          jHtmlObject.find('div').remove();
                          var newHtml = jHtmlObject.text();
                          return newHtml;
                      },
                      body: function(data, row, column, node) {
                          // Assuming the problematic column is at index 6 (seventh column)
                          if (column === 6) {
                              return `\`${data}`; // Force conversion to string to maintain the integrity of the 16-digit number
                          }
                          return data;
                      }
                  }
              }
          }, {
              className: 'colVisBAT',
              extend: 'colvis',
              text: 'Column Visibility',
              collectionLayout: 'fixed two-column'
          }]
      });
  

  i dont thing so i can do it in code pen
  i need output to be string of 16 digit without any special char , and quotes
• navinAce Posts: 10Questions: 1Answers: 0

  January 24

      var file_name = '<?php echo $file_name_excel ?>';
      $('#dynamic_table').DataTable({
          dom: 'Bifrtp',
          bPaginate: false,
          ordering: true,
          buttons: [{
              extend: 'excel',
              footer: true,
              title: file_name,
              className: 'excel-buttonNAD',
              exportOptions: {
                  columns: ':visible',
                  format: {
                      header: function(mDataProp, columnIdx) {
                          var htmlText = '<span>' + mDataProp + '</span>';
                          var jHtmlObject = jQuery(htmlText);
                          jHtmlObject.find('div').remove();
                          var newHtml = jHtmlObject.text();
                          return newHtml;
                      },
                      body: function(data, row, column, node) {
                          // Assuming the problematic column is at index 6 (seventh column)
                          if (column === 6) {
                              return `\`${data}`; // Force conversion to string to maintain the integrity of the 16-digit number
                          }
                          return data;
                      }
                  }
              }
          }, {
              className: 'colVisBAT',
              extend: 'colvis',
              text: 'Column Visibility',
              collectionLayout: 'fixed two-column'
          }]
      });
  

  the code is huge i cant make it running here.
  the problem is i have to upload the excel in a portial, which has 16 digit.
  we have to do something to make it to string
  because in number it is automatically rounding off.
• kthorngren Posts: 21,670Questions: 26Answers: 5,017

  January 24 Answer ✓

  the code is huge i cant make it running here.

  We don't want the full code for a test case. The test case should be reduced done to the code and data that replicate the issue.

  the problem is i have to upload the excel in a portial, which has 16 digit.
  we have to do something to make it to string

  There are a couple different ways to try resolving this presented in this thread. Let us know if one of those helps. If you still need help then please provide a simple test case showing the issue.
  https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

  Kevin
• navinAce Posts: 10Questions: 1Answers: 0

  January 24

  Ok, thanks i will check and let you know about the update
• navinAce Posts: 10Questions: 1Answers: 0

  January 27

   $('#dynamic_table').DataTable({
          dom: 'Bifrtp',
          bPaginate: false,
          ordering: true,
          buttons: [{
              extend: 'excelHtml5',
              footer: true,
              title: file_name,
              className: 'excel-button',
              exportOptions: {
                  columns: ':visible'
              },
              customize: function(xlsx) {
                  var sheet = xlsx.xl.worksheets['sheet1.xml'];
                  var columns = ['column1', 'column2'];
                  var columnRefs = {};
  
                  // Find the cell references of the specified columns
                  $('row:first c', sheet).each(function() {
                      var cell = $(this);
                      var cellText = cell.text();
                      if (columns.includes(cellText)) {
                          columnRefs[cellText] = cell.attr('r'); // Get the exact cell reference (e.g., G1)
                      }
                  });
  
                  // Apply formatting only to the identified columns
                  for (var column in columnRefs) {
                      var colIndex = columnRefs[column].replace(/\d+/, ''); // Get the column letter without the row number
  
                      // Apply formatting to cells in the identified column, excluding the header cell itself
                      $('row c', sheet).each(function() {
                          var cell = $(this);
                          var cellRef = cell.attr('r');
                          var columnLetter = cellRef.replace(/[0-9]/g, ''); // Extract the column letter
                          var rowIndex = parseInt(cellRef.replace(/\D/g, ''), 10); // Extract the row number
                          if (columnLetter === colIndex && rowIndex > 1) { // Ensure exact match and skip the header row
                              var cellValue = cell.text();
                              if (cellValue.includes('-')) { // Skip cells with hyphens
                                  //console.log('Skipping cell with hyphen:', cellRef);
                                  return; // Skip further processing for this cell
                              }
                              cell.attr('t', 'str'); // Apply formatting
                              //  console.log('Formatted Cell: ', cellRef, cell);
                          }
                      });
                  }
              }
          }, {
              className: 'colVisBAT',
              extend: 'colvis',
              text: 'Column Visibility',
              collectionLayout: 'fixed four-column'
          }]
      });
  

  Problem:
  If you have a 16 digit number it will automatically get rounded off or get converted to scientific value
  Solution:
  1) return \${data}; output:2223703360068248
  by doing this your output would be followed with '`', would be difficult to remove after generating excel if multiple column are present
  2) In the above code it solve the problem, but when you open the file you have to select the field(the 16 digit column) and change it to text then it would work.
• kthorngren Posts: 21,670Questions: 26Answers: 5,017

  January 27 edited January 27 Answer ✓

  I tried the example I posted in the thread I linked and it seems to work:

  

  I exported it to a CSV file and the "`" doesn't appear in the file. Does this solution not work for you? On the surface this seems to work but there might be an Excel workflow where this is an issue. Not sure as I have only used this in basic worksheets.

  1) return \${data}; output:2223703360068248

  I'm not clear on what this is doing.

  2) In the above code it solve the problem, but when you open the file you have to select the field(the 16 digit column) and change it to text then it would work.

  Possibly you need to set the cell style to text using the Built in styles. Maybe use cell.attr( 's', '0' );.

  If you still need help with this then please provide a simple test case with an example of your large integer and the solution you are trying to use.
  https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

  Kevin
• navinAce Posts: 10Questions: 1Answers: 0

  January 29 edited January 29

  

  kthorngren
  You have used "return '\u200C'"
  the problem is when you export the file, and open it there would be an invisible character added before the large integer, you can see it by converting it to csv and open it with notepad you can see the invisible character
• navinAce Posts: 10Questions: 1Answers: 0

  January 29

  https://live.datatables.net/nidugevi/2/edit
  Checkout this test case
  Steps to follow when you open the file
  1) Select the B column and make it to text field and save it, this would work fine and doesn't even has invisible value included