当我从数据表中导出EXCEL时,我无法合并列的值.因为第B列的值被命名为Category.需要合并重复的值.我是编程新手,感谢您的帮助

这是我的jsfiddle

这是我的代码

<table id="example1" class="table table-sm cell-border compact stripe table-bordered table-hover dataTable dtr-inline"
    cellspacing="0" width="100%" aria-describedby="example1_info">


    <thead>
        <tr>
            <th class="d-none sorting_disabled" rowspan="1" colspan="1">Date</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Category</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Item Name</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Price</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Opening</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Recived</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Closing</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Sales</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
        </tr>
    </thead>

    <tbody>

        <tr data-row-id="1761" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 180ml">Golden Touch Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="9">9</td>
            <td class="editable-col" col-index="6" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1763" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 750ml">Golden Touch Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="3">3</td>
            <td class="editable-col" col-index="6" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
    
        <tr data-row-id="1948" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 180ml">Whytehall Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="18">18</td>
            <td class="editable-col" col-index="6" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1949" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 750ml">Whytehall Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="4">4</td>
            <td class="editable-col" col-index="6" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1992" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Roulette Brandy 750ml">Roulette Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="2230.00">2230.00</td>
            <td class="editable-col" col-index="3" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="1">1</td>
            <td class="editable-col" col-index="6" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="2048" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Bejois VSOP 375ML">Bejois VSOP 375ML</td>
            <td class="editable-col" col-index="2" oldval="200.00">200.00</td>
            <td class="editable-col" col-index="3" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="20">20</td>
            <td class="editable-col" col-index="6" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
  
    </tbody>


    <tfoot>

        <tr>
            <th class="d-none" rowspan="1" colspan="1">0</th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"> 1900</th>
            <th rowspan="1" colspan="1"> 9</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 0</th>
            <th rowspan="1" colspan="1"> 0</th>
        </tr>
    </tfoot>


 
</table>

我的Java脚本

 $(document).ready(function () {

        document.title = 'shop';
        $('#example1').DataTable({
            "processing": true,
            "dom": 'Bfrtip',
            "lengthChange": false,
            "searching": false,
            "info": true,
            "autoWidth": false,
            "responsive": true,
            "retrieve": true,
            "paging": false,
            "lengthMenu": [
                [-1],
                ["All"]
            ],
            "bSort": false,
            "bLengthChange": false,

            "buttons": ["copy", {
                    extend: 'excelHtml5',
                    footer: true,
                    text: 'Save as Excel',
                    pageSize: 'A4',
                      customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('c[r=C1] t', sheet).text('Sudha Wines');
                        $('row:first c', sheet).attr('s', '32');

                        var col = $('col', sheet);
                        $(col[0]).attr('width', 8);
                        $(col[1]).attr('width', 24);
                        $(col[2]).attr('width', 9);
                        $(col[3]).attr('width', 8);
                        $(col[4]).attr('width', 7);
                        $(col[5]).attr('width', 8);
                        $('row* ', sheet).each(function (index) {
                            if (index > 0) {
                                $(this).attr('ht', 26);
                                $(this).attr('customHeight', 1);
                            }
                        });






                    },
                    
                  
                }
            ],


        });
    });

output i'am getting in MS excel enter image description here

我想要的输出是

enter image description here

推荐答案

Excel使用的OpenXMLElectron 表格格式按如下方式指定合并范围:

<mergeCells  count="2">
    <mergeCell ref="B2:B5"/>
    <mergeCell ref="B6:B7"/>
</mergeCells>

上图表示工作表中的两个合并区域.

因此,我们需要添加逻辑来扫描"Category"列中的数据,以确定需要创建哪些范围.一旦有了这些范围,我们就可以构建上面的Excel XML片段,并在导出过程中将其插入到工作表中.

你已经有了你的customize: function (xlsx) { ... },所以我们可以在那里添加我们的逻辑:

var ranges = buildRanges(sheet);
                        
// build the HTML string:
var mergeCellsHtml = '<mergeCells count="' + ranges.length + '">';
ranges.forEach(function(range) {
    mergeCellsHtml = mergeCellsHtml + '<mergeCell ref="' + range + '"/>';
})
mergeCellsHtml = mergeCellsHtml + '</mergeCells>';

$( 'sheetData', sheet ).after( mergeCellsHtml );
// don't know why, but Excel auto-adds an extra mergeCells tag, so remove it:
$( 'mergeCells', sheet ).last().remove();

建立靶场的工作在buildRanges(sheet)函数中.我希望这段代码可以得到简化/改进,但它显示了总体方法:

function buildRanges(sheet) {

  let prevCat = ''; // previous category
  let currCat = ''; // current category
  let currCellRef = ''; // current cell reference
  let rows = $('row', sheet);
  let startRange = '';
  let endRange = '';
  let ranges = [];

  rows.each(function (i) {
    if (i > 0 && i < rows.length) { // skip first (headings) row
      let cols = $('c', $(this));
      cols.each(function (j) {
        if (j == 1) { // the "Category" column
          currCat = $(this).text(); // current row's category
          currCellRef = $(this).attr('r'); // e.g. "B3"
          if (currCat !== prevCat) {
            if (i == 1) {
              // start of first range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            } else {
              // end of previous range
              if (endRange !== startRange) {
                // capture the range:
                ranges.push( startRange + ':' + endRange );
              }
               // start of a new range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            }
          } else {
            // extend the current range end:
            endRange = currCellRef;
          }
          //console.log( $(this).attr('r') );
        }
      });
      if (i == rows.length -1 && endRange !== startRange) {
        // capture the final range:
        ranges.push( startRange + ':' + endRange );
      }
    }
  });
  return ranges;
}

此函数扫描类别列,查找该列中的值发生更改时的情况.

它假定值已经排序/分组,以支持这一点.


One Important Note

我在您的按钮逻辑中添加了以下行:

title: '', // no title row in excel sheet

这可确保Electron 表格尚未创建任何合并单元格区域.如果您需要此功能,则需要调整我的逻辑,因为Electron 表格中已经有一个<mergeCells>标记(在第一行,包含标题).


最终结果如下所示:

enter image description here

以下是一个演示中的所有部分(不确定您是否可以从Stack代码片段中实际运行Excel下载):

  $(document).ready(function () {

        document.title = 'shop';
        $('#example1').DataTable({
            "processing": true,
            "dom": 'Bfrtip',
            "lengthChange": false,
            "searching": false,
            "info": true,
            "autoWidth": false,
            "responsive": true,
            "retrieve": true,
            "paging": false,
            "lengthMenu": [
                [-1],
                ["All"]
            ],
            "bSort": false,
            "bLengthChange": false,

            "buttons": ["copy", {
                    extend: 'excelHtml5',
                    footer: true,
                    text: 'Save as Excel',
                    title: '', // no title row in excel sheet
                    pageSize: 'A4',
                      customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        
                        $('row:first c', sheet).attr('s', '32');

                        var col = $('col', sheet);
                        $(col[0]).attr('width', 8);
                        $(col[1]).attr('width', 24);
                        $(col[2]).attr('width', 9);
                        $(col[3]).attr('width', 8);
                        $(col[4]).attr('width', 7);
                        $(col[5]).attr('width', 8);
                        $('row* ', sheet).each(function (index) {
                            if (index > 0) {
                                $(this).attr('ht', 26);
                                $(this).attr('customHeight', 1);
                            }
                        });

                        var ranges = buildRanges(sheet);
                        
                        // build the HTML string:
                        var mergeCellsHtml = '<mergeCells count="' + ranges.length + '">';
                        ranges.forEach(function(range) {
                            mergeCellsHtml = mergeCellsHtml + '<mergeCell ref="' + range + '"/>';
                        })
                        mergeCellsHtml = mergeCellsHtml + '</mergeCells>';

                        $( 'sheetData', sheet ).after( mergeCellsHtml );
                        // don't know why, but Excel auto-adds an extra mergeCells tag, so remove it:
                        $( 'mergeCells', sheet ).last().remove(); 



                    }
                    
                  
                }
            ]


        });
    });
    
function buildRanges(sheet) {

  let prevCat = ''; // previous category
  let currCat = ''; // current category
  let currCellRef = ''; // current cell reference
  let rows = $('row', sheet);
  let startRange = '';
  let endRange = '';
  let ranges = [];

  rows.each(function (i) {
    if (i > 0 && i < rows.length) { // skip first (headings) row
      let cols = $('c', $(this));
      cols.each(function (j) {
        if (j == 1) { // the "Category" column
          currCat = $(this).text(); // current row's category
          currCellRef = $(this).attr('r'); // e.g. "B3"
          if (currCat !== prevCat) {
            if (i == 1) {
              // start of first range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            } else {
              // end of previous range
              if (endRange !== startRange) {
                // capture the range:
                ranges.push( startRange + ':' + endRange );
              }
               // start of a new range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            }
          } else {
            // extend the current range end:
            endRange = currCellRef;
          }
          //console.log( $(this).attr('r') );
        }
      });
      if (i == rows.length -1 && endRange !== startRange) {
        // capture the final range:
        ranges.push( startRange + ':' + endRange );
      }
    }
  });
  return ranges;
}
<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>

  <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css"/>

  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css"/> 
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.colVis.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.flash.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.print.min.js"></script>

</head>

<body>

<div style="margin: 20px;">

    <table id="example1" class="table table-sm cell-border compact stripe table-bordered table-hover dataTable dtr-inline"
    cellspacing="0" width="100%" aria-describedby="example1_info">


    <thead>
        <tr>
            <th class="d-none sorting_disabled" rowspan="1" colspan="1">Date</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Category</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Item Name</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Price</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Opening</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Recived</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Closing</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Sales</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
        </tr>
    </thead>

    <tbody>

        <tr data-row-id="1761" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 180ml">Golden Touch Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="9">9</td>
            <td class="editable-col" col-index="6" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1763" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 750ml">Golden Touch Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="3">3</td>
            <td class="editable-col" col-index="6" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
    
        <tr data-row-id="1948" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 180ml">Whytehall Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="18">18</td>
            <td class="editable-col" col-index="6" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1949" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 750ml">Whytehall Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="4">4</td>
            <td class="editable-col" col-index="6" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1992" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Port</th>
            <td class="editable-col" col-index="2" oldval="Roulette Brandy 750ml">Roulette Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="2230.00">2230.00</td>
            <td class="editable-col" col-index="3" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="1">1</td>
            <td class="editable-col" col-index="6" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="2048" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Port</th>
            <td class="editable-col" col-index="2" oldval="Bejois VSOP 375ML">Bejois VSOP 375ML</td>
            <td class="editable-col" col-index="2" oldval="200.00">200.00</td>
            <td class="editable-col" col-index="3" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="20">20</td>
            <td class="editable-col" col-index="6" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
  
    </tbody>


    <tfoot>

        <tr>
            <th class="d-none" rowspan="1" colspan="1">0</th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"> 1900</th>
            <th rowspan="1" colspan="1"> 9</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 0</th>
            <th rowspan="1" colspan="1"> 0</th>
        </tr>
    </tfoot>


 
</table>

</div>



</body>
</html>

Javascript相关问答推荐

有条件的悲剧

使用axios.获取实时服务器时的404响应

单击子元素时关闭父元素(JS)

类型脚本中只有字符串或数字键而不是符号键的对象

成功完成Reducers后不更新状态

如何将react—flanet map添加到remixjs应用程序

如何在ASP.NET中使用Google Charts API JavaScript将条形图标签显示为绝对值而不是负值

禁用.js文件扩展名并从目录导入隐式根index.js时,找不到NodeJS导入模块

如何发送从REST Api收到的PNG数据响应

如何在.NET Core中将chtml文件链接到Java脚本文件?

面对代码中的错误作为前端与后端的集成

如何确保预订系统跨不同时区的日期时间处理一致?

Reaction-SWR-无更新组件

如何在Java脚本中对列表中的特定元素进行排序?

令牌JWT未过期

未捕获语法错误:Hello World中的令牌无效或意外

如何在Java脚本中对数据进行签名,并在PHP中验证签名?

在将元素追加到DOM之前,createElement()是否会触发回流?混淆abt DocumentFragment行为

相对于具有选定类的不同SVG组放置自定义工具提示

如何在css中裁剪成一定Angular 的圆的一部分,而不需要复杂的多边形