Search code examples
javascriptjqueryexceldatatables

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:

enter image description here

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.

Solution

  • 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:

    enter image description here