Add metadata excel style

Add sort feature on metadata table Add last modified date and last modified by name columns
xgeek-net · xgeek-net · Nov 2, 2018 · Nov 2, 2018 · Nov 2, 2018 · Nov 2, 2018
commit 1935b3a59def6331127733775e25b8d1d96650eb
diff --git a/bower.json b/bower.json
@@ -16,6 +16,7 @@
     "vue": "^2.5.16",
     "font-awesome": "^5.0.13",
     "moment": "^2.22.2",
-    "js-xlsx": "^0.14.0"
+    "js-xlsx": "^0.14.0",
+    "xlsx-populate": "^1.17.0"
   }
 }
diff --git a/builder.json b/builder.json
@@ -21,6 +21,7 @@
     "!src/view/components/font-awesome/advanced-options/*",
     "!src/view/components/font-awesome/svg-with-js/*",
     "!src/view/components/font-awesome/use-on-desktop/*",
+    "!src/view/components/xlsx-populate/examples/*",
     "!src/view/components/js-xlsx/demos/*",
     "!src/view/components/js-xlsx/docbits/*",
     "!src/view/components/js-xlsx/test_files/*",

diff --git a/src/view/css/style.css b/src/view/css/style.css
@@ -349,6 +349,7 @@
   width: 1.8rem;
   height: 1.8rem;
 }
+
 .slds-dropdown--actions a.slds-color-text-error, 
 .slds-dropdown_actions a.slds-color-text-error,
 .slds-dropdown--actions a.slds-color-text-error:hover, 
@@ -386,8 +387,25 @@
 .new-pipeline-detail-sfdc .slds-form_horizontal .slds-form-element>.slds-form-element__label.slds-text-align_left{
   text-align: left;
 }
-
-
+.new-pipeline-detail-sfdc .slds-th__action{
+  height: auto;
+  padding: 0;
+}
+.new-pipeline-detail-sfdc .slds-is-sortable__icon{
+  opacity: 0;
+  display: inline-block;
+}
+.new-pipeline-detail-sfdc .slds-is-sortable .slds-th__action:hover,
+.new-pipeline-detail-sfdc .slds-is-sortable .slds-th__action:focus {
+  background-color: transparent;
+}
+.new-pipeline-detail-sfdc .slds-is-sortable .slds-th__action:hover .slds-icon,
+.new-pipeline-detail-sfdc .slds-is-sortable .slds-th__action:focus .slds-icon {
+  opacity: 1.0;
+}
+.new-pipeline-detail-sfdc .sort-active .slds-th__action .slds-icon{
+  opacity: 1.0;
+}
 /**
  * pipeline-detail
  */

diff --git a/src/view/index.html b/src/view/index.html
@@ -44,6 +44,7 @@ <h1 class="">{{ page.title }}</h1>
 
   <script type="text/javascript" src="components/vue/dist/vue.min.js"></script>
   <script type="text/javascript" src="components/moment/min/moment-with-locales.min.js"></script>
+  <script type="text/javascript" src="components/xlsx-populate/browser/xlsx-populate.min.js"></script>
   <script type="text/javascript" src="components/js-xlsx/dist/xlsx.full.min.js"></script>
   <script type="text/javascript" src="js/include/ExcelHandler.js"></script>
   <script type="text/javascript" src="js/include/master.js"></script>

diff --git a/src/view/js/include/ExcelHandler.js b/src/view/js/include/ExcelHandler.js
@@ -27,30 +27,75 @@ ExcelHandler.prototype.read = function(file, callback) {
 
 // Export data to excel
 ExcelHandler.prototype.write = function(data, fileName) {
-  fileName = fileName || 'Pipeline.xlsx';
-  function s2ab(s) {
-    var buf = new ArrayBuffer(s.length);
-    var view = new Uint8Array(buf);
-    for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
-    return buf;
-  }
-  const wb = XLSX.utils.book_new();
-  for(let sheetName in data) {
-    const ws = XLSX.utils.aoa_to_sheet(data[sheetName]);
-    XLSX.utils.book_append_sheet(wb, ws, sheetName);
-  }
-  // const ws = XLSX.utils.aoa_to_sheet(data['Reference']);
-  // XLSX.utils.book_append_sheet(wb, ws, 'Reference');
-
-  const fileContent = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});
-  let link = document.createElement("A");
-  const blob = new Blob([s2ab(fileContent)],{type:""})
-  link.href = window.URL.createObjectURL(blob);
-  link.download = fileName;
-  link.target = '_blank';
-  document.body.appendChild(link);
-  link.click();
-  document.body.removeChild(link);
+  const self = this;
+  fileName = fileName || 'Pipeline';
+  XlsxPopulate
+  .fromBlankAsync()
+  .then(function(workbook) {
+    // Create sheets
+    workbook.sheet(0).name('Metadata');
+    workbook.addSheet('Reference');
+    for(let sheetName in data) {
+      const sheet = workbook.sheet(sheetName);
+      let columnCount = 0;
+      let lineCount = 0;
+      for(let l = 0; l < data[sheetName].length; l++) {
+        let line = data[sheetName][l];
+        let styles = {
+          'border' : {
+            top: { style: "thin" },
+            left: { style: "thin" },
+            bottom: { style: "thin" },
+            right: { style: "thin" }
+          },
+          'wrapText' : true};
+        if(l == 0) {
+          lineCount = data[sheetName].length;
+          columnCount = line.length;
+        }
+        for(let c = 0; c < line.length; c++) {
+          // l0-c0 to A1, l0-c1 to B1
+          let val = line[c];
+          let cellNo = String.fromCharCode(65 + c) + (l + 1);
+          sheet.cell(cellNo).value(val).style(styles);
+          if(l == 0) {
+            let fillStyle = (c < 3) ? {'fill' : '4285f4'} : {'fill' : 'a5a5a5'};
+            sheet.cell(cellNo).style(fillStyle);
+          } else {
+            if(c == 4 && val.length > 0) {
+              sheet.cell(cellNo).value(moment(val).format('YYYY/MM/DD HH:mm'))
+            }
+          }
+        }
+      }
+      // Rejust cell width to fit data
+      for(let c = 0; c < columnCount; c++) {
+        let cellChar = String.fromCharCode(65 + c);
+        // A1:A20
+        const maxLength = sheet.range(cellChar + '1:' + cellChar + lineCount)
+        .reduce(function(max, cell) {
+          const value = cell.value();
+          if (value === undefined) return max;
+          // set length to 2x if has japanese string
+          const vLen = self.getBytes(value.toString());
+          return Math.max(max, vLen);
+        }, 0);
+        sheet.column(cellChar).width(maxLength + 5);
+      }
+    }
+    return workbook.outputAsync();
+  })
+  .then(function(blob) {
+    let link = document.createElement('A');
+    //const blob = new Blob([s2ab(fileContent)],{type:""})
+    link.href = window.URL.createObjectURL(blob);
+    link.download = fileName + '.xlsx';;
+    link.target = '_blank';
+    document.body.appendChild(link);
+    link.click();
+    document.body.removeChild(link);
+  });
+
 }
 
 // Read and convert xlsx to json 
@@ -112,4 +157,17 @@ ExcelHandler.prototype.handleCodePoints = function(array) {
     index += CHUNK_SIZE;
   }
   return result;
+}
+// 'abcde' → 5, 'あいうえお' → 10
+ExcelHandler.prototype.getBytes = function(txt) {
+  var length = 0;
+  for (var i = 0; i < txt.length; i++) {
+    var c = txt.charCodeAt(i);
+    if ((c >= 0x0 && c < 0x81) || (c === 0xf8f0) || (c >= 0xff61 && c < 0xffa0) || (c >= 0xf8f1 && c < 0xf8f4)) {
+      length += 1;
+    } else {
+      length += 2;
+    }
+  }
+  return length;
 }