基于单元格类的DataTables Excel样式 [英] DataTables Excel style based on cell class

问题描述

我正在使用解决方案和的事实>函数可以传递3个变量:

• Excel的XML文件
• 代表按钮对象的对象
• DataTable实例

我们使用这最后一个将HTML中的类映射到Excel中的单元格:

  var table = $('#example').dataTable({dom:"Bfrtip"，纽扣: [{扩展:'excelHtml5'，title:''，//没有标题行文字:"Excel"，自定义:函数(xlsx，btnObj，tableInst){var cellIndexes = tableInst.cells(.mycustom").indexes();for(var i = 0; i< cellIndexes.length; i ++){var cellIndex = cellIndexes [i];var tblRow = cellIndex ['row'] + 1;//从0索引调整var tblCol = cellIndex ['column'] + 1;//从0索引调整var xlCol = String.fromCharCode(64 + tblCol);//危险:Z后失败(26列)alert('表行'+ tblRow +'col'+ tblCol+'映射到excel单元格引用'+ xlCol + tblRow);}}}]}); 

这不应用任何样式，仅向您显示如何确定已为DataTable单元格指定了特定样式，并将这些单元格索引转换为Excel样式的单元格引用.

因此，对于以下示例数据...

 < table id ="example" class ="display nowrap dataTable cell-border" style ="width:100％">< thead>< tr><头1< th<头部2< th<头部3< th</tr></thead>< tbody>< tr>< td>第1列第1< td>< td>第1列第2< td>< td>第1列第3< td></tr>< tr>< td>第2列第1< td>< td>第2行第2< td>< td>第2列3< td></tr>< tr>< td>第3列第1< td>< td class ="mycustom">第3行第2列</td>< td>第3行第3< td></tr>< tr>< td>第4列第1< td>< td>第4列第2< td>< td>第4列3< td></tr>< tr>< td class ="mycustom">第5列第1</td>< td>第5列第2< td>< td>第5列3< td></tr></tbody></table> 

...上面的代码生成2条警报，如下所示:

 表第3行col 2映射到excel单元格引用B3表第5行col 1映射到excel单元格参考A5 

然后，您可以在所需的选择器中使用 B3 和 A5 值-例如:

  $('c [r = B3] t'，sheet).attr('s'，'25'); 

附加说明:

描述了DataTables cells().indexes()函数

如果您想在列(或行)级别而不是单元格级别工作，我还没有尝试过-但这应该是上述版本的简单版本.

I'm using DataTables plugin to export a monthly calendar view; I need to set a cell style inside the excel file based on the class of the corrisponding cell in the DataTables calendar view.
I know how to style the exported excel file using the customize: function( xlsx, index ) {} however I'm not able to find, in the examples I saw on the forum, a way to set the style of the excel cell based on the class of the corrispondig cell in the DataTables view.
I have created my own xml style like this:

customize: function( xlsx, index ) {
  var new_style = '<?xml version="1.0" encoding="UTF-8"?>'+
  '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '+
  'xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" '+
  'xmlns:x14ac="https://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+
    '<numFmts count="2">'+
      '<numFmt numFmtId="164" formatCode="0.0" />'+
      '<numFmt numFmtId="165" formatCode="\d\d\d" />'+
    '</numFmts>'+
    '<fonts count="4" x14ac:knownFonts="1">'+

    ...... a lot of stuff here ....

    '<extLst>'+
      '<ext xmlns:x14="https://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}">'+
        '<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1" />'+
      '</ext>'+
    '</extLst>'+
  '</styleSheet>';

This is a styles.xml as you can find iside an .xlsx file if you change the extension to .zip and than unzip it. To apply the styles to the excel cell I'm doing:

  xlsx.xl['styles.xml'] = $.parseXML(new_style);
  var sheet = xlsx.xl.worksheets['sheet1.xml'];

  $('row:first c', sheet).attr( 's', '1' );
  $('row:eq(1) c', sheet).attr( 's', '2' );
  $('row:eq(2) c', sheet).attr( 's', '3' );
}

What I need to do is something like:

     $('row:eq(3) c', sheet).hasClass('custom').attr( 's', '1' ); //not working

or:

    $('row c[r^="B"]', sheet).each( function () {
       if ( $(this).hasClass('custom') ) {
       $(this).attr( 's', '4' );
      }
    }); // not working

Basically I'm working on a row of cells (more than 30, so I have AA, AB, AC and so on) and i need a method to discriminate some of them to add a different style, as you can see the header has 31 cells with calendars day/name and i would like the colums with Saturday and Sunday to be with a gray background like they are in the datatable table.

This is the datatable:

And this is the excel file so far, i need the Sab and Dom columns to be gray

UPDATE * with @andrewjames solution and @georg solution for double letters posted here Convert numbers to letters beyond the 26 character alphabet

function colName(n) {
  var ordA = 'A'.charCodeAt(0);
  var ordZ = 'Z'.charCodeAt(0);
  var len = ordZ - ordA + 1;
  var s = "";
  while(n >= 0) {
    s = String.fromCharCode(n % len + ordA) + s;
    n = Math.floor(n / len) - 1;
  }
  return s;
}

var cellIndexes = tabellaOre.cells(".Sab, .Dom").indexes();

for (var i = 0; i < cellIndexes.length; i++) {
  var cellIndex = cellIndexes[i];
  var tblRow = cellIndex['row']+4; //changed to my needs
  var tblCol = cellIndex['column']; //removed +1
  // var xlCol = String.fromCharCode(64 + tblCol); changed with follow
  var xlCol = colName(tblCol);

  // .... previous stuff here, it was already in a for() loop, so still working

  $('row c[r='+xlCol+tblRow+']', sheet).attr('s','12');
}

and this is the result:

As @andrewjames correctly says in his anwer:

My naive implementation will fail for more than 26 columns:

The colName(n) function solved the problem.
One last step would be to style the cells with thick borders with their own style, but I can consider this as solved.

解决方案

Assumptions:

It sounds like you already have your embedded styles.xml customized the way you want it, and you therefore know what style index values to refer to, from its <cellXfs> section.

It sounds as if the missing piece is knowing which DataTables cells have been given which CSS style classes, so you can select the relevant <cellXfs> indexes for the equivalent Excel cells.

Proposed Approach:

This takes advantage of the fact that the customize function can be passed 3 variables:

• the XML files for the Excel
• an object representing the button object(s)
• the DataTable instance

We use this last one to map classes in the HTML to cells in Excel:

  var table = $('#example').dataTable({
    dom: 'Bfrtip',
    buttons: [
      {
        extend: 'excelHtml5',
        title: '', // no title row
        text: 'Excel',
        customize: function ( xlsx, btnObj, tableInst ){

          var cellIndexes = tableInst.cells(".mycustom").indexes();
          for (var i = 0; i < cellIndexes.length; i++) {
            var cellIndex = cellIndexes[i];
            var tblRow = cellIndex['row']+1; // adjusted from 0 indexed
            var tblCol = cellIndex['column']+1; // adjusted from 0 indexed
            var xlCol = String.fromCharCode(64 + tblCol); // DANGER: fails after Z (26 columns)
            alert('table row ' + tblRow + ' col ' + tblCol
                + ' maps to excel cell ref ' + xlCol + tblRow);
          }
        }
      }
    ]

  });

This doesn't apply any styles, it just shows you how to determine which DataTable cells have been given a specific style, and translates those cell indexes into Excel-style cell references.

So, for the following sample data...

<table id="example" class="display nowrap dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Head 1</th>
                <th>Head 2</th>
                <th>Head 3</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Row 1 column 1</td>
                <td>Row 1 column 2</td>
                <td>Row 1 column three</td>
            </tr>
            <tr>
                <td>Row 2 column 1</td>
                <td>Row 2 column 2</td>
                <td>Row 2 column 3</td>
            </tr>
            <tr>
                <td>Row 3 column 1</td>
                <td class="mycustom">Row 3 column 2</td>
                <td>Row 3 column 3</td>
            </tr>
            <tr>
                <td>Row 4 column 1</td>
                <td>Row 4 column 2</td>
                <td>Row 4 column 3</td>
            </tr>
            <tr>
                <td class="mycustom">Row 5 column 1</td>
                <td>Row 5 column 2</td>
                <td>Row 5 column 3</td>
            </tr>
        </tbody>
    </table>

...the above code generates 2 alerts as follows:

table row 3 col 2 maps to excel cell ref B3
table row 5 col 1 maps to excel cell ref A5

You can then use the B3 and A5 values in the selectors you need - for example:

$('c[r=B3] t', sheet).attr( 's', '25' );

Additional notes:

The DataTables cells().indexes() function is described here.

My naive implementation will fail for more than 26 columns:

var xlCol = String.fromCharCode(64 + tblCol);

But it shouldn't be too hard to extend that for Excel columns "AA", "AB", and so on, if needed.

If you want to work at the column (or row) level, instead of the cell level, I have not tried that - but it should be a slightly simpler version of the above.

这篇关于基于单元格类的DataTables Excel样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！