title: Add sheet to excel output of Jquery datatables
body: <h3>Edit - Solution:</h3>
  <p>Okay so the solution to my issue was very simple. If you look in my <code>workbook.xml.rels</code> output you'll notice that rId2 is already in use by the workbook's styles. The solution was very simple: <strong>Don't use rId2 for any new sheet</strong>. After that simple change my excel output loaded just fine.</p>
  <p>I've added some comments to my function to reflect this.</p>
  <p>Big thanks to @andrewjames as his solution helped me find this bug.</p>
  <h1>Problem:</h1>
  <p>I am using jquery datatables to render a report. When a user outputs the report to excel, I'd like append N sheets to the output using a custom function (generate_excel_sheet).</p>
  <h1>Current Solution:</h1>
  <p>I have a function that should allow me to dynamically add new sheets to my excel output:</p>
  <pre><code>/**
   * 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) =&gt; {
  
    // 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 = &quot;&quot;;
      while(n &gt;= 0) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
      }
      return s.toUpperCase();
    }
  
    // Add sheet to [Content_Types].xml =&gt; &lt;Types&gt;
    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 =&gt; 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 =&gt; &lt;workbook&gt;&lt;sheets&gt;
    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:
    // * &lt;row&gt; for each row
    // * &lt;c&gt;   for each item in each row 
    var body = '';
    for (i = 0; i &lt; data.length; i++) {
      body += `&lt;row  r=&quot;${i+1}&quot;&gt;`;
      for (j = 0; j &lt; data[i].length; j++) {
        body += 
          `&lt;c r=&quot;${colName(j)}${i+1}&quot; t=&quot;inlineStr&quot;&gt;` +
            `&lt;is&gt;` +
              `&lt;t&gt;${data[i][j]}&lt;/t&gt;` +
            `&lt;/is&gt;` +
          `&lt;/c&gt;`
      }
      body += `&lt;/row&gt;`;
    }
  
    // build &lt;col&gt; elements for each column in data
    var columns = '';
    for (i = 0; i &lt; data[0].length; i++) {
      columns += `&lt;col customWidth=&quot;1&quot; width=&quot;14.850000000000001&quot; min=&quot;${i+1}&quot; max=&quot;${i+1}&quot; /&gt;`;
    }
  
    // build the sheet to be appended to the workbook
    var newSheet = 
      '&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;yes&quot;?&gt;'+
      '&lt;worksheet xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot; xmlns:r=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot; xmlns:mc=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot; xmlns:x14ac=&quot;http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac&quot; mc:Ignorable=&quot;x14ac&quot;&gt;'+
        '&lt;cols&gt;' +
          columns +
        '&lt;/cols&gt;' +
        '&lt;sheetData&gt;' +
          body +
        '&lt;/sheetData&gt;' +
      '&lt;/worksheet&gt;';
  
    // Add sheet to xl/worksheets
    xlsx.xl.worksheets[`${name}.xml`] = $.parseXML(newSheet);
  }
  </code></pre>
  <p>Then when I build my datatable I am using the following code to build my excel button:</p>
  <pre><code>        buttons: [
            'copy',
            {
              extend: 'excelHtml5',
              title: rpt_title, 
              messageTop: rpt_message,
              customize: function(xlsx) {
                generate_excel_sheet(xlsx, 2, &quot;test&quot;, [
                  [&quot;test1&quot;, 123],
                  [&quot;test2&quot;, 456],
                  [&quot;test3&quot;, 789],
                ]);
              }
            }
          ],
  </code></pre>
  <h1>Output/Errors:</h1>
  <p>Here is the output of various xml files within the xlsx file:</p>
  <p>[Content_Types].xml</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;true&quot;?&gt;
  &lt;Types xmlns=&quot;http://schemas.openxmlformats.org/package/2006/content-types&quot;&gt;
    &lt;Default ContentType=&quot;application/xml&quot; Extension=&quot;xml&quot;/&gt;
    &lt;Default ContentType=&quot;application/vnd.openxmlformats-package.relationships+xml&quot; Extension=&quot;rels&quot;/&gt;
    &lt;Default ContentType=&quot;image/jpeg&quot; Extension=&quot;jpeg&quot;/&gt;
    &lt;Override ContentType=&quot;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml&quot; PartName=&quot;/xl/workbook.xml&quot;/&gt;
    &lt;Override ContentType=&quot;application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml&quot; PartName=&quot;/xl/worksheets/sheet1.xml&quot;/&gt;
    &lt;Override ContentType=&quot;application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml&quot; PartName=&quot;/xl/styles.xml&quot;/&gt;
    &lt;Override ContentType=&quot;application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml&quot; PartName=&quot;/xl/worksheets/test.xml&quot;/&gt;
  &lt;/Types&gt;
  </code></pre>
  <p>workbook.xml.rels</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;true&quot;?&gt;
  &lt;Relationships xmlns=&quot;http://schemas.openxmlformats.org/package/2006/relationships&quot;&gt;
    &lt;Relationship Target=&quot;worksheets/sheet1.xml&quot; Type=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet&quot; Id=&quot;rId1&quot;/&gt;
    &lt;Relationship Target=&quot;styles.xml&quot; Type=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles&quot; Id=&quot;rId2&quot;/&gt;
    &lt;Relationship Target=&quot;worksheets/test.xml&quot; Type=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet&quot; Id=&quot;rId2&quot;/&gt;
  &lt;/Relationships&gt;
  </code></pre>
  <p>workbook.xml</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;true&quot;?&gt;
  &lt;workbook xmlns:r=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot; xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot;&gt;
    &lt;fileVersion rupBuild=&quot;24816&quot; lowestEdited=&quot;5&quot; lastEdited=&quot;5&quot; appName=&quot;xl&quot;/&gt;
    &lt;workbookPr autoCompressPictures=&quot;0&quot; showInkAnnotation=&quot;0&quot;/&gt;
    &lt;bookViews&gt;
      &lt;workbookView tabRatio=&quot;500&quot; windowHeight=&quot;19020&quot; windowWidth=&quot;25600&quot; yWindow=&quot;0&quot; xWindow=&quot;0&quot;/&gt;
    &lt;/bookViews&gt;
    &lt;sheets&gt;
      &lt;sheet r:id=&quot;rId1&quot; sheetId=&quot;1&quot; name=&quot;Sheet1&quot;/&gt;
      &lt;sheet r:id=&quot;rId2&quot; sheetId=&quot;2&quot; name=&quot;test&quot;/&gt;
    &lt;/sheets&gt;
    &lt;definedNames/&gt;
  &lt;/workbook&gt;
  </code></pre>
  <p>test.xml</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot;?&gt;
  &lt;worksheet mc:Ignorable=&quot;x14ac&quot; xmlns:x14ac=&quot;http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac&quot; xmlns:mc=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot; xmlns:r=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot; xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot;&gt;
    &lt;cols&gt;
      &lt;col max=&quot;1&quot; min=&quot;1&quot; width=&quot;14.850000000000001&quot; customWidth=&quot;1&quot;/&gt;
      &lt;col max=&quot;2&quot; min=&quot;2&quot; width=&quot;14.850000000000001&quot; customWidth=&quot;1&quot;/&gt;
    &lt;/cols&gt;
    &lt;sheetData&gt;
      &lt;row r=&quot;1&quot;&gt;
        &lt;c r=&quot;A1&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;test1&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
        &lt;c r=&quot;B1&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;123&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
      &lt;/row&gt;
      &lt;row r=&quot;2&quot;&gt;
        &lt;c r=&quot;A2&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;test2&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
        &lt;c r=&quot;B2&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;456&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
      &lt;/row&gt;
      &lt;row r=&quot;3&quot;&gt;
        &lt;c r=&quot;A3&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;test3&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
        &lt;c r=&quot;B3&quot; t=&quot;inlineStr&quot;&gt;
          &lt;is&gt;
            &lt;t&gt;789&lt;/t&gt;
          &lt;/is&gt;
        &lt;/c&gt;
      &lt;/row&gt;
    &lt;/sheetData&gt;
  &lt;/worksheet&gt;
  </code></pre>
  <p>From here excel gives and error saying that the workbook needs to be repaired. When the repairs are finished sheet test is blank (no data) and the following message and log file is generated:</p>
  <p>&quot;Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
  Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)&quot;</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;true&quot;?&gt;
  &lt;recoveryLog xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot;&gt;
    &lt;logFileName&gt;error087400_01.xml&lt;/logFileName&gt;
    &lt;summary&gt;Errors were detected in file 'C:\Users\cmaxie\Downloads\Transaction Detail Drill Report (25).xlsx'&lt;/summary&gt;
    &lt;additionalInfo&gt;
      &lt;info&gt;Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.&lt;/info&gt;
    &lt;/additionalInfo&gt;
    &lt;removedRecords&gt;
    &lt;removedRecord&gt;Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)&lt;/removedRecord&gt;
    &lt;/removedRecords&gt;
  &lt;/recoveryLog&gt;
  </code></pre>