编辑-解决方案：
好吧，所以我的问题的解决方案非常简单。如果您查看我的workbook.xml.rels输出，您会注意到rId2已被工作簿的样式使用。解决方案非常简单：不要对任何新工作表使用rId2。在那次简单的更改之后，我的excel输出加载得很好。
我在我的函数中添加了一些注释来反映这一点。
非常感谢@andrewjames，因为他的解决方案帮助我找到了这个bug。
问题：
我正在使用jquery数据表来呈现报告。当用户将报告输出到excel时，我希望使用自定义函数(generate_excel_sheet(将N张表附加到输出中。
当前解决方案：
我有一个功能，应该允许我动态添加新的工作表到我的excel输出：
/**
* Builds a new excel sheet and attaches it to the current workbook
* @param {Object} xlsx - The excel workbook
* @param {Number} id - The id to be associated with the new sheet
* @param {string} name - The name to be associated with the new sheet
* @param {Array} data - The data to be inserted into the new sheet
*/
let generate_excel_sheet = (xlsx, id, name, data) => {
// helper function for generating column identifier letters (A, AA, AB, etc)
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.toUpperCase();
}
// Add sheet to [Content_Types].xml => <Types>
var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];
var clone = source.cloneNode(true);
clone.setAttribute('PartName',`/xl/worksheets/${name}.xml`);
xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);
// Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
var clone = source.cloneNode(true);
clone.setAttribute('Id',`rId${id}`); // CANNOT USE rId2, see solution
clone.setAttribute('Target',`worksheets/${name}.xml`);
xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);
// Add new sheet to xl/workbook.xml => <workbook><sheets>
var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
var clone = source.cloneNode(true);
clone.setAttribute('name',name);
clone.setAttribute('sheetId',`${id}`);
clone.setAttribute('r:id',`rId${id}`); // CANNOT USE rId2, see solution
xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);
// build out the following from data:
// * <row> for each row
// * <c> for each item in each row
var body = '';
for (i = 0; i < data.length; i++) {
body += `<row r="${i+1}">`;
for (j = 0; j < data[i].length; j++) {
body +=
`<c r="${colName(j)}${i+1}" t="inlineStr">` +
`<is>` +
`<t>${data[i][j]}</t>` +
`</is>` +
`</c>`
}
body += `</row>`;
}
// build <col> elements for each column in data
var columns = '';
for (i = 0; i < data[0].length; i++) {
columns += `<col customWidth="1" width="14.850000000000001" min="${i+1}" max="${i+1}" />`;
}
// build the sheet to be appended to the workbook
var newSheet =
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+
'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+
'<cols>' +
columns +
'</cols>' +
'<sheetData>' +
body +
'</sheetData>' +
'</worksheet>';
// Add sheet to xl/worksheets
xlsx.xl.worksheets[`${name}.xml`] = $.parseXML(newSheet);
}
然后，当我构建数据表时，我使用以下代码来构建excel按钮：
buttons: [
'copy',
{
extend: 'excelHtml5',
title: rpt_title,
messageTop: rpt_message,
customize: function(xlsx) {
generate_excel_sheet(xlsx, 2, "test", [
["test1", 123],
["test2", 456],
["test3", 789],
]);
}
}
],
输出/错误：
以下是xlsx文件中各种xml文件的输出：
[Content_Types].xml
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default ContentType="application/xml" Extension="xml"/>
<Default ContentType="application/vnd.openxmlformats-package.relationships+xml" Extension="rels"/>
<Default ContentType="image/jpeg" Extension="jpeg"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" PartName="/xl/workbook.xml"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet1.xml"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" PartName="/xl/styles.xml"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/test.xml"/>
</Types>
工作簿.xml.rels
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId1"/>
<Relationship Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Id="rId2"/>
<Relationship Target="worksheets/test.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId2"/>
</Relationships>
工作簿.xml
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fileVersion rupBuild="24816" lowestEdited="5" lastEdited="5" appName="xl"/>
<workbookPr autoCompressPictures="0" showInkAnnotation="0"/>
<bookViews>
<workbookView tabRatio="500" windowHeight="19020" windowWidth="25600" yWindow="0" xWindow="0"/>
</bookViews>
<sheets>
<sheet r:id="rId1" sheetId="1" name="Sheet1"/>
<sheet r:id="rId2" sheetId="2" name="test"/>
</sheets>
<definedNames/>
</workbook>
test.xml
<?xml version="1.0"?>
<worksheet mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cols>
<col max="1" min="1" width="14.850000000000001" customWidth="1"/>
<col max="2" min="2" width="14.850000000000001" customWidth="1"/>
</cols>
<sheetData>
<row r="1">
<c r="A1" t="inlineStr">
<is>
<t>test1</t>
</is>
</c>
<c r="B1" t="inlineStr">
<is>
<t>123</t>
</is>
</c>
</row>
<row r="2">
<c r="A2" t="inlineStr">
<is>
<t>test2</t>
</is>
</c>
<c r="B2" t="inlineStr">
<is>
<t>456</t>
</is>
</c>
</row>
<row r="3">
<c r="A3" t="inlineStr">
<is>
<t>test3</t>
</is>
</c>
<c r="B3" t="inlineStr">
<is>
<t>789</t>
</is>
</c>
</row>
</sheetData>
</worksheet>
excel在这里给出了一个错误，说明工作簿需要修复。维修完成后，板材测试为空白(无数据(，并生成以下消息和日志文件：
"Excel已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。从/xl/workbook.xml部分(工作簿(中删除记录：工作表属性"；
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error087400_01.xml</logFileName>
<summary>Errors were detected in file 'C:UserscmaxieDownloadsTransaction Detail Drill Report (25).xlsx'</summary>
<additionalInfo>
<info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info>
</additionalInfo>
<removedRecords>
<removedRecord>Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)</removedRecord>
</removedRecords>
</recoveryLog>
看起来您的方法可能基于此示例。
我获取了该代码，并对其进行了一组最小的更改，以处理您的测试数据。因此，尽管最初的演示处理了来自多个不同DataTables的数据(并将每个表加载到自己的表中(，但现在我的方法采用了"；额外的"；来自简单阵列阵列的数据，例如：
var data = [ ["test1", 123], ["test2", 456], ["test3", 789] ];
其他注意事项：
- 我重新安排了对
addSheet()的主调用中的参数 - 我以为你从来没有；标题"；行，因此参数现在为
null - 因为您没有从其他表中读取数据，所以没有要处理的列标题
这是完整的页面，您可以将其复制/粘贴到一个独立的网页中，并自行运行，以进行测试/验证。
它可能在标题中有几个额外的按钮相关资源，您不需要这些资源(例如PDF处理(，因为它取自我的另一个测试模板。
很明显，你可以删除这些，并删除任何你不再需要的额外自定义Excel代码(但我保留了这些代码，或者只是注释掉了(。
最后一点：这个代码只处理一个额外的工作表。但现在这已经起作用了，添加所需的数量就很简单了。
<!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="example" class="display dataTable cell-border" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>61</td>
<td>2011/04/25</td>
<td>$320,800</td>
</tr>
<tr>
<td>Garrett Winters</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>63</td>
<td>2011/07/25</td>
<td>$170,750</td>
</tr>
<tr>
<td>Ashton Cox</td>
<td>Junior Technical Author</td>
<td>San Francisco</td>
<td>66</td>
<td>2009/01/12</td>
<td>$86,000</td>
</tr>
</tbody>
</table>
</div>
<script>
$(document).ready(function() {
function buildCols(data) {
// Builds cols XML.
//To do: deifne widths for each column.
//Params:
// data: row data.
//Returns:
// String of XML formatted column widths.
var cols = '<cols>';
for (i=0; i<data.length; i++) {
colNum = i + 1;
cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';
}
cols += '</cols>';
return cols;
}
function buildRow(data, rowNum, styleNum) {
// Builds row XML.
//Params:
// data: Row data.
// rowNum: Excel row number.
// styleNum: style number or empty string for no style.
//Returns:
// String of XML formatted row.
var style = styleNum ? ' s="' + styleNum + '"' : '';
var row = '<row r="' + rowNum + '">';
for (i=0; i<data.length; i++) {
colNum = (i + 10).toString(36).toUpperCase(); // Convert to alpha
var cr = colNum + rowNum;
row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +
'<is>' +
'<t>' + data[i] + '</t>' +
'</is>' +
'</c>';
}
row += '</row>';
return row;
}
function getTableData(data, title) {
// Processes Datatable row data to build sheet.
//Params:
// data: data for new sheet.
// title: Title displayed at top of SS or empty str for no title.
//Returns:
// String of XML formatted worksheet.
//var header = getHeaderNames(table);
//var table = $(table).DataTable();
var rowNum = 1;
var mergeCells = '';
var ws = '';
//ws += buildCols(header);
ws += '<sheetData>';
//if (title.length > 0) {
// ws += buildRow([title], rowNum, 51);
// rowNum++;
//
// mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();
//
// mergeCells = '<mergeCells count="1">'+
// '<mergeCell ref="A1:' + mergeCol + '1"/>' +
// '</mergeCells>';
//}
//ws += buildRow(header, rowNum, 2);
//rowNum++;
// Loop through each row to append to sheet.
//table.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
data.forEach(function (item, index) {
var rowData = item;
// If data is object based then it needs to be converted
// to an array before sending to buildRow()
ws += buildRow(rowData, rowNum, '');
rowNum++;
} );
ws += '</sheetData>' + mergeCells;
return ws;
}
function setSheetName(xlsx, name) {
// Changes tab title for sheet.
//Params:
// xlsx: xlxs worksheet object.
// name: name for sheet.
if (name.length > 0) {
var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
source.setAttribute('name', name);
}
}
function addSheet(xlsx, data, title, name, sheetId) {
//Clones sheet from Sheet1 to build new sheet.
//Params:
// xlsx: xlsx object.
// data: data for new shet.
// title: Title for top row or blank if no title.
// name: Name of new sheet.
// sheetId: string containing sheetId for new sheet.
//Returns:
// Updated sheet object.
//Add sheet2 to [Content_Types].xml => <Types>
//============================================
var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];
var clone = source.cloneNode(true);
clone.setAttribute('PartName','/xl/worksheets/sheet' + sheetId + '.xml');
xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);
//Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
//=====================================================================
var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
var clone = source.cloneNode(true);
clone.setAttribute('Id','rId'+sheetId+1);
clone.setAttribute('Target','worksheets/sheet' + sheetId + '.xml');
xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);
//Add second sheet to xl/workbook.xml => <workbook><sheets>
//=========================================================
var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
var clone = source.cloneNode(true);
clone.setAttribute('name', name);
clone.setAttribute('sheetId', sheetId);
clone.setAttribute('r:id','rId'+sheetId+1);
xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);
//Add sheet2.xml to xl/worksheets
//===============================
var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+
'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+
getTableData(data, title) +
'</worksheet>';
xlsx.xl.worksheets['sheet' + sheetId + '.xml'] = $.parseXML(newSheet);
}
var table = $('#example').DataTable( {
dom: 'Brftip',
buttons: [
{
extend: 'excelHtml5',
text: 'Excel',
customize: function( xlsx ) {
//setSheetName(xlsx, 'Calls');
// Add more of these 2 lines, to add more sheets, as needed:
var data = [ ["test1", 123], ["test2", 456], ["test3", 789] ];
addSheet(xlsx, data, null, 'TabName2', '2');
}
}
]
} );
} );
</script>
</body>
</html>