How to change document author of an excel file when exporting using jquery datatable

Datatable is a library that allows us to format, search, sort HTML tables. It also allows you to download various format of documents. I have used some other excel libraries in the past which allows you to update excel metadata or some other properties. In my case I am just trying to see if there is a way to change the author of the excel document before downloading it:

I did read their button export documentation and it does not show an option to change the author anywhere. I was just wondering if there is a way to customize this export to add that information there.

To add "Author" (and other related information) to your exported Excel file, you need to perform some low-level edits on the underlying XML files contained in your Excel (.xlsx) document.

To access the underlying .xlsx document structure, you use this DataTables customize option:

buttons: [
  {
    extend: 'excel',
    title: '',
    customize: function ( xlsx ) {
        ...
    }

You can see for yourself what the structure of a .xlsx document is by unzipping it.

The Author information is stored in a file named core.xml in a directory named docProps. By default, a DataTables Excel file does not include this file (or its containing directory), so we need to create them:

const coreXmlStr = 
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + 
        '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' +
            '<dc:creator>John Smith;Jane Jones</dc:creator>' + 
            '<cp:lastModifiedBy>John Smith</cp:lastModifiedBy>' + 
            '<dcterms:created xsi:type="dcterms:W3CDTF">2023-06-14T17:51:33Z</dcterms:created>' + 
            '<dcterms:modified xsi:type="dcterms:W3CDTF">2023-06-14T17:53:15Z</dcterms:modified>' + 
        '</cp:coreProperties>';

const parser = new window.DOMParser();
const coreXmlDoc = parser.parseFromString( coreXmlStr, 'text/xml' );

xlsx.docProps = {};
xlsx.docProps['core.xml'] = coreXmlDoc;

The coreXmlStr is a string of XML containing various information fields. You can put whatever values you want in these. My example has two authors:

John Smith;Jane Jones

The JavaScript parses this string of XML into an actual XML document, and then adds it to a new core.xml file in a docProps directory. The xlsx object represents the containing Excel file.

We also need to set up some additional relationship metadata so Excel actually uses this information:

var contentTypes = xlsx['[Content_Types].xml'];
$( 'Types', contentTypes ).append( '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>' );
var rels = xlsx._rels['.rels'];
$( 'Relationships', rels ).append( '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>' );

The overall code:

$(document).ready(function() {
$('#myTable').DataTable( {
dom: 'Bfrtip',
buttons: [
{
extend: 'excel',
title: '',
customize: function ( xlsx ) {
const coreXmlStr = 
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + 
'<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' +
'<dc:creator>John Smith;Jane Jones</dc:creator>' + 
'<cp:lastModifiedBy>John Smith</cp:lastModifiedBy>' + 
'<dcterms:created xsi:type="dcterms:W3CDTF">2023-06-14T17:51:33Z</dcterms:created>' + 
'<dcterms:modified xsi:type="dcterms:W3CDTF">2023-06-14T17:53:15Z</dcterms:modified>' + 
'</cp:coreProperties>';
const parser = new window.DOMParser();
const coreXmlDoc = parser.parseFromString( coreXmlStr, 'text/xml' );
xlsx.docProps = {};
xlsx.docProps['core.xml'] = coreXmlDoc;
var contentTypes = xlsx['[Content_Types].xml'];
$( 'Types', contentTypes ).append( '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>' );
var rels = xlsx._rels['.rels'];
$( 'Relationships', rels ).append( '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>' );
}
}
]
} );
});

In the exported Excel file, we see this: