Search code examples
javascripthtmlexcelexport-to-excel

Stop table2excel from considering ratio value like 50:10 as time in exported excel

I have a html table with some column values as String as well as Ratio Numbers (50:10, 60:30)

My problem is when I export it using table2excel, some ratio values are considered as time.

See below Image:

Image

My Export Code:

$("#pat_inj_table").table2excel({
    name: "Report",
    filename: name,
});

I found this code in table2excel documentation which I think can be useful to solve my problem:

Table2Excel.extend((cell, cellText) => {
  // {HTMLTableCellElement} cell - The current cell.
  // {string} cellText - The inner text of the current cell.

  // cell should be described by this type handler
  if (selector) return {
    t: ...,
    v: ...,
  };

  // skip and run next handler
  return null;
});

But I dont know how to use this above code.

Solution

  • There are two ways you can identify cell in the handler.

    1. Add attribute on each cell (for example, <td type="string">10:20</td>) and identify data based on that

    Table2Excel.extend((cell, cellText) => {
  return $(cell).attr('type') == 'string' ? {
    t: 's',
    v: cellText
  } : null;
});
var table2excel = new Table2Excel({
  defaultFileName: "myFile"
});
table2excel.export($(".table"));
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://rusty1s.github.io/table2excel/dist/table2excel.js"></script>
<table class="table" excel-name="excel-name">
  <thead>
    <tr>
      <th>#</th>
      <th>Column heading</th>
      <th>Column heading</th>
      <th>Column heading</th>
    </tr>
  </thead>
  <tbody>
    <tr class="active">
      <td>1</td>
      <td type="string">10:20</td>
      <td>Column content</td>
      <td>Column content</td>
    </tr>
  </tbody>
</table>

    1. Use regex to parse known format of data and use that to identify cell

    Table2Excel.extend((cell, cellText) => {
  return cellText && /\d+:\d+/gi.test(cellText) ? { t: 's', v: cellText } : null;
});
var table2excel = new Table2Excel({
  defaultFileName: "myFile"
});
table2excel.export($(".table"));
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://rusty1s.github.io/table2excel/dist/table2excel.js"></script>
<table class="table" excel-name="excel-name">
  <thead>
    <tr>
      <th>#</th>
      <th>Column heading</th>
      <th>Column heading</th>
      <th>Column heading</th>
    </tr>
  </thead>
  <tbody>
    <tr class="active">
      <td>1</td>
      <td type="string">10:20</td>
      <td>Column content</td>
      <td>Column content</td>
    </tr>
  </tbody>
</table>

    Take a look at this jsFiddle demo.