What should I do if the Excel table exported by js exceeds 26 English characters?

What should I do if the Excel table exported by js exceeds 26 English characters? It’s not that there is no solution. The editor below will bring you an ES6 solution to the problem that the Excel table exported by js exceeds 26 English characters. Has very good reference value. Let’s follow the editor to take a look, I hope it will be helpful to everyone.

This requires some understanding of the header encoding rules of Excel tables. Currently, the sample code only extends to 52 fields

/**
*json数据导入导出Excel表格示例代码
*
/

var array_utils = require('./utils-array')
var XLSX = require("xlsx");

module.exports = {
 writeExcel : function (headers,data,file,callback) {
  if(data.length ==0){
   var obj = {}
   for(var v of headers){
    obj[v] = ''
   }
   data.push(obj)
  }
  _writeExcel(headers,data,file,callback)
 },

 /**
  * 获取excel原始信息
  * @param path 文件路径
  */
 info : function(path){
  return _info(path)
 },
 /**
  * 格式化excel原始信息
  * @param path 文件路径
  */
 formate_info : function (path) {
  return info_formate_info(path).formate
 },
 info_formate_info : function (path) {
  return info_formate_info(path)
 }

}

var _info = function(path) {
 var k = XLSX.readFile(path, {type: 'base64'});
 var result = {}
 k.SheetNames.forEach(function(sheetName) {
  var worksheet = k.Sheets[sheetName];
  result[sheetName] = XLSX.utils.sheet_to_json(worksheet);
 });
 return result
}

var info_formate_info = function(path){
 var info = _info(path)
 var result = {}
 for(var value in info){
  result[value] = {}
 }
 for(var key_info in info ){
  var array = info[key_info]
  if(array_utils.isArray(array) || array.length>0){
   var keys_array = Object.keys(array[0])
   var obj = {}
   for(var value of keys_array){
    obj[value] = []
   }
   for( var key in obj ){
    var subject_clone = JSON.parse(JSON.stringify(array))
    subject_clone.filter( (v)=>{
     for(var k in v){
      if(k!=key){
       delete v[k]
      }
     }
     return v;
    })
    var subject_key_value = Array.from(array_utils.arrayQC(subject_clone),v => v[key] )
    var obA = []
    for(var v of subject_key_value){
     var obk = {
      id : null,
      v : v
     }
     for(var ke in keys_array){
      var thisIndex = keys_array.findIndex(x=>x==keys_array[ke])
      var currentIndex = keys_array.findIndex(x=>x==key)
      if( thisIndex < currentIndex){
       try {
        var thisObj = array.find(x=>x[ key ] == v )
        obk[keys_array[ke]] = thisObj[ keys_array[ke] ]
       }catch (e){
        console.error(e)
       }
      }
     }

     obA.push(obk)
    }
    obj[key] = obA
   }
   result[key_info]= obj
  }
 }
 return {
  info : info,
  formate : result
 }
}

var _writeExcel = function (headers,data,file,callback) {
 var _headers = headers
 var _data = data;
 var headers = _headers
 // 为 _headers 添加对应的单元格位置
  .map((v, i) => Object.assign({}, {
   v: v,
   position:num(i)+1
  }))
  // 转换成 worksheet 需要的结构
  .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});
 var data = _data
  .map((v, i) => _headers.map((k, j) => Object.assign({}, {
   v: v[k],
   position:num(j) + (i+2)
  })))
  // 对刚才的结果进行降维处理（二维数组变成一维数组）
  .reduce((prev, next) => prev.concat(next))
  // 转换成 worksheet 需要的结构
  .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});
// 合并 headers 和 data
 // console.log("测试data",data)
 var output = Object.assign({}, headers, data);
// 获取所有单元格的位置
 var outputPos = Object.keys(output);
// 计算出范围
 var ref = outputPos[0] + &#39;:&#39; + outputPos[outputPos.length - 1];
// 构建 workbook 对象
 var wb = {
  SheetNames: [&#39;Sheet1&#39;],
  Sheets: {
   &#39;Sheet1&#39;: Object.assign({}, output, { &#39;!ref&#39;: ref })
  }
 };

 // 导出 Excel
 XLSX.writeFileAsync( file , wb,function (err) {
  callback(err)
 });
}
//定位Excel位置
var num=function(i){
 var n=parseInt(i+65)
 if(n>90){
  n=String.fromCharCode(65)+String.fromCharCode(i+39)
  return n
 }else {
  n=String.fromCharCode(n)
  return n
 }

}

Everyone can learn Yet? I hope it can help you when you encounter such problems.

Related recommendations:

nodejs method of exporting excel_node.js

mysql database exports excel xml and other format files

PHP class library usage example for exporting excel data

The above is the detailed content of What should I do if the Excel table exported by js exceeds 26 English characters?. For more information, please follow other related articles on the PHP Chinese website!