如何在使用jQuery DataTable导出时更改Excel文件的文档作者。

huangapple
go评论136阅读模式
英文:

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

问题

Datatable 是一个允许我们格式化、搜索和排序 HTML 表格的库。它还允许您下载各种格式的文档。我以前使用过一些其他的 Excel 库，它们允许您更新 Excel 的元数据或一些其他属性。在我的情况下，我只是想看看在下载之前是否有一种方法可以更改 Excel 文档的作者：

如何在使用jQuery DataTable导出时更改Excel文件的文档作者。

我确实阅读了它们的按钮导出文档，但它并没有显示在任何地方更改作者的选项。我只是想知道是否有一种自定义此导出以添加该信息的方法。

英文:

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:

如何在使用jQuery DataTable导出时更改Excel文件的文档作者。

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.

答案1

得分: 2

要向导出的Excel文件中添加“作者”（以及其他相关信息），您需要对包含在Excel（.xlsx）文档中的底层XML文件进行一些低级编辑。

要访问底层.xlsx文档结构，您可以使用DataTables的customize选项：

  1. buttons: [
  2.   {
  3.     extend: 'excel',
  4.     title: '',
  5.     customize: function ( xlsx ) {
  6.         // 在这里进行编辑
  7.     }
  8.   }
  9. ]

您可以通过解压缩.xlsx文件自行查看.xlsx文档的结构。

“作者”信息存储在名为“core.xml”的文件中，位于名为“docProps”的目录中。默认情况下，DataTables生成的Excel文件不包括这个文件（或其所在的目录），因此我们需要创建它们：

  1. const coreXmlStr = 
  2.     '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + 
  3.     '<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">' +
  4.         '<dc:creator>John Smith;Jane Jones</dc:creator>' + 
  5.         '<cp:lastModifiedBy>John Smith</cp:lastModifiedBy>' + 
  6.         '<dcterms:created xsi:type="dcterms:W3CDTF">2023-06-14T17:51:33Z</dcterms:created>' + 
  7.         '<dcterms:modified xsi:type="dcterms:W3CDTF">2023-06-14T17:53:15Z</dcterms:modified>' + 
  8.     '</cp:coreProperties>';
  10. const parser = new window.DOMParser();
  11. const coreXmlDoc = parser.parseFromString( coreXmlStr, 'text/xml' );
  13. xlsx.docProps = {};
  14. xlsx.docProps['core.xml'] = coreXmlDoc;

coreXmlStr 是一个包含各种信息字段的XML字符串。您可以在其中放入您想要的任何值。我的示例中有两个作者：

  1. John Smith;Jane Jones

JavaScript将此XML字符串解析为实际的XML文档，然后将其添加到docProps目录中的新core.xml文件中。xlsx对象表示包含的Excel文件。

我们还需要设置一些额外的关系元数据，以便Excel实际使用这些信息：

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

总体的代码如下：

  1. $(document).ready(function() {
  3.   $('#myTable').DataTable( {
  4.     dom: 'Bfrtip',
  5.     buttons: [
  6.         {
  7.           extend: 'excel',
  8.           title: '',
  9.           customize: function ( xlsx ) {
  10.  
  11.             const coreXmlStr = 
  12.                    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + 
  13.                    '<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">' +
  14.                        '<dc:creator>John Smith;Jane Jones</dc:creator>' + 
  15.                        '<cp:lastModifiedBy>John Smith</cp:lastModifiedBy>' + 
  16.                        '<dcterms:created xsi:type="dcterms:W3CDTF">2023-06-14T17:51:33Z</dcterms:created>' + 
  17.                        '<dcterms:modified xsi:type="dcterms:W3CDTF">2023-06-14T17:53:15Z</dcterms:modified>' + 
  18.                    '</cp:coreProperties>';
  20.             const parser = new window.DOMParser();
  21.             const coreXmlDoc = parser.parseFromString( coreXmlStr, 'text/xml' );
  23.             xlsx.docProps = {};
  24.             xlsx.docProps['core.xml'] = coreXmlDoc;
  26.             var contentTypes = xlsx['[Content_Types].xml'];
  27.             $('Types', contentTypes).append('<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>');
  29.             var rels = xlsx._rels['.rels'];
  30.             $('Relationships', rels).append('<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>');
  31.           }
  32.         }
  33.     ]
  34.   } );
  36. });

在导出的Excel文件中，您将看到如下内容：

如何在使用jQuery DataTable导出时更改Excel文件的文档作者。

英文:

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:

  1. buttons: [
  2.   {
  3.     extend: &#39;excel&#39;,
  4.     title: &#39;&#39;,
  5.     customize: function ( xlsx ) {
  6.         ...
  7.     }

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:

  1. const coreXmlStr = 
  2.         &#39;&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;yes&quot;?&gt;&#39; + 
  3.         &#39;&lt;cp:coreProperties xmlns:cp=&quot;http://schemas.openxmlformats.org/package/2006/metadata/core-properties&quot; xmlns:dc=&quot;http://purl.org/dc/elements/1.1/&quot; xmlns:dcterms=&quot;http://purl.org/dc/terms/&quot; xmlns:dcmitype=&quot;http://purl.org/dc/dcmitype/&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;&#39; +
  4.             &#39;&lt;dc:creator&gt;John Smith;Jane Jones&lt;/dc:creator&gt;&#39; + 
  5.             &#39;&lt;cp:lastModifiedBy&gt;John Smith&lt;/cp:lastModifiedBy&gt;&#39; + 
  6.             &#39;&lt;dcterms:created xsi:type=&quot;dcterms:W3CDTF&quot;&gt;2023-06-14T17:51:33Z&lt;/dcterms:created&gt;&#39; + 
  7.             &#39;&lt;dcterms:modified xsi:type=&quot;dcterms:W3CDTF&quot;&gt;2023-06-14T17:53:15Z&lt;/dcterms:modified&gt;&#39; + 
  8.         &#39;&lt;/cp:coreProperties&gt;&#39;;
  10. const parser = new window.DOMParser();
  11. const coreXmlDoc = parser.parseFromString( coreXmlStr, &#39;text/xml&#39; );
  13. xlsx.docProps = {};
  14. xlsx.docProps[&#39;core.xml&#39;] = 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:

  1. 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:

  1. var contentTypes = xlsx[&#39;[Content_Types].xml&#39;];
  2. $( &#39;Types&#39;, contentTypes ).append( &#39;&lt;Override PartName=&quot;/docProps/core.xml&quot; ContentType=&quot;application/vnd.openxmlformats-package.core-properties+xml&quot;/&gt;&#39; );
  3. var rels = xlsx._rels[&#39;.rels&#39;];
  4. $( &#39;Relationships&#39;, rels ).append( &#39;&lt;Relationship Id=&quot;rId2&quot; Type=&quot;http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties&quot; Target=&quot;docProps/core.xml&quot;/&gt;&#39; );

The overall code:

  1. $(document).ready(function() {
  2. $(&#39;#myTable&#39;).DataTable( {
  3. dom: &#39;Bfrtip&#39;,
  4. buttons: [
  5. {
  6. extend: &#39;excel&#39;,
  7. title: &#39;&#39;,
  8. customize: function ( xlsx ) {
  9. const coreXmlStr = 
  10. &#39;&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;yes&quot;?&gt;&#39; + 
  11. &#39;&lt;cp:coreProperties xmlns:cp=&quot;http://schemas.openxmlformats.org/package/2006/metadata/core-properties&quot; xmlns:dc=&quot;http://purl.org/dc/elements/1.1/&quot; xmlns:dcterms=&quot;http://purl.org/dc/terms/&quot; xmlns:dcmitype=&quot;http://purl.org/dc/dcmitype/&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;&#39; +
  12. &#39;&lt;dc:creator&gt;John Smith;Jane Jones&lt;/dc:creator&gt;&#39; + 
  13. &#39;&lt;cp:lastModifiedBy&gt;John Smith&lt;/cp:lastModifiedBy&gt;&#39; + 
  14. &#39;&lt;dcterms:created xsi:type=&quot;dcterms:W3CDTF&quot;&gt;2023-06-14T17:51:33Z&lt;/dcterms:created&gt;&#39; + 
  15. &#39;&lt;dcterms:modified xsi:type=&quot;dcterms:W3CDTF&quot;&gt;2023-06-14T17:53:15Z&lt;/dcterms:modified&gt;&#39; + 
  16. &#39;&lt;/cp:coreProperties&gt;&#39;;
  17. const parser = new window.DOMParser();
  18. const coreXmlDoc = parser.parseFromString( coreXmlStr, &#39;text/xml&#39; );
  19. xlsx.docProps = {};
  20. xlsx.docProps[&#39;core.xml&#39;] = coreXmlDoc;
  21. var contentTypes = xlsx[&#39;[Content_Types].xml&#39;];
  22. $( &#39;Types&#39;, contentTypes ).append( &#39;&lt;Override PartName=&quot;/docProps/core.xml&quot; ContentType=&quot;application/vnd.openxmlformats-package.core-properties+xml&quot;/&gt;&#39; );
  23. var rels = xlsx._rels[&#39;.rels&#39;];
  24. $( &#39;Relationships&#39;, rels ).append( &#39;&lt;Relationship Id=&quot;rId2&quot; Type=&quot;http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties&quot; Target=&quot;docProps/core.xml&quot;/&gt;&#39; );
  25. }
  26. }
  27. ]
  28. } );
  29. });

In the exported Excel file, we see this:

如何在使用jQuery DataTable导出时更改Excel文件的文档作者。

huangapple
  • 本文由 发表于 2023年6月15日 01:33:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76476188.html
  • datatables
  • excel
  • javascript
  • jquery
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定