js前端之导出数据至execl及csv文件

****************************导出execl文件*****************************

导出execl,包括涉及单元格操作;两步操作

1.<input class="down-btn" onclick="ExportToExecl()" type="button" value="导出" /> //该触发事件

function ExportToExecl(){
  jsono = [];
  cellMerges = [];

  //计算导出的数据总列数
  var headEntity1 = {"Column0":"","Column1":"","Column2":"","Column3":"","Column4":"","Column5":"","Column6":"","Column7":"","Column8":"","Column9":"","Column10":""};
  jsono.push(headEntity2);
  var cellEntity1 = {s:{c:0,r:0},e:{c:10,r:0}}; //s的c为开始合并的单元格起始列,e的c为开始合并单元格的最后一列
  cellMerges.push(cellEntity1);
  //空行
  jsono.push(headEntityEmpty);
  var cellEntityEmpty = {s:{c:0,r:jsono.length-1},e:{c:10,r:jsono.length-1}};
  cellMerges.push(cellEntityEmpty);
  //列
  var tempEndEntity11 = {"Column0":"Manufacturer/供应商","Column1":"","Column2":"","Column3":"","Column4":"","Column5":"","Column6":"待定","Column7":"","Column8":"","Column9":"","Column10":""};
  jsono.push(tempEndEntity11);
  var cellEntityEnd110 = {s:{c:0,r:jsono.length-1},e:{c:5,r:jsono.length-1}};
  var cellEntityEnd210 = {s:{c:6,r:jsono.length-1},e:{c:10,r:jsono.length-1}};
  cellMerges.push(cellEntityEnd110);cellMerges.push(cellEntityEnd210);
  downloadExl();
}

2.公用方法直接复制粘贴:

function saveAs(obj, fileName) {//当然可以自定义简单的下载文件实现方式
  var tmpa = document.createElement("a");
  tmpa.download = fileName || "下载";
  tmpa.href = URL.createObjectURL(obj); //绑定a标签
  tmpa.click(); //模拟点击实现下载
  setTimeout(function () { //延时释放
    URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
  }, 100);
}

var cellMerges = []; //单元格数组

const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary' };//这里的数据是用来定义导出的格式类型
function downloadExl(data, type) {
  data = jsono;
  var wb = { SheetNames: ['Sheet1'], Sheets: {}, Props: {} };
  // wb.Sheets['Sheet1'] = XLSX.utils.json_to_sheet(data);//通过json_to_sheet转成单页(Sheet)数据
  data = format2Sheet(data);
  data["C2"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } },
    fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
  data["!merges"] = [{//合并第一行数据[B1,C1,D1,E1]
    s: {//s为开始
      c: 1,//开始列
      r: 0//开始取值范围
    },
    e: {//e结束
      c: 4,//结束列
      r: 0//结束范围
    }
  }];
  data["!merges"] = cellMerges;
  //20220121
  wb=format2WB(data,'Sheet1');
  // data["!ref"]="A1:E7";
  // wb.Sheets['Sheet1'] = data;
  saveAs(new Blob([s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream" }), "execl数据导出" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
}
function s2ab(s) {
  if (typeof ArrayBuffer !== 'undefined') {
    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;
  } else {
    var buf = new Array(s.length);
    for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }
}

function format2Sheet(json, n, r, keyMap, t) {
  keyMap = keyMap || Object.keys(json[0]);
  var types = (t == undefined ? ((v) => (({ "number": "n", undefined: "s", "boolean": "b","string":"s" })[typeof v])||"s") : t);
  n = n || 0;
  r = r || 0;
  var tmpdata = {};//用来保存转换好的json
  var t1 = json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
  v: v[k],
  position: ((j + n) > 25 ? xlsxUtils.getCharCol((j + n)) : String.fromCharCode(65 + (j + n))) + (i + 1 + r),
  }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
  v: v.v,
  t: types?types(v.v):"s"
  });
  return tmpdata;
}
function format2WB(sheetData, title, wb, ref) {
  title = title || "mySheet";
  var outputPos = Object.keys(sheetData);
  if (!wb) wb = { Sheets: {}, SheetNames: [] };
  wb.SheetNames.push(title);
  wb.Sheets[title] = Object.assign({}, sheetData, {
  '!ref': ref || (outputPos[0] + ':' + outputPos.reverse().find(_=>_.indexOf("!")==-1))//设置填充区域
  });
  return wb;
}

***********************************导出csv文件******************************

function DownCurveLoad(){
  var dataList = [];
  var title = [
    {title:"Date;AFO;ID no.;Status;Attribute;Unit;Data row",field:"Data"}
  ];
  var tempX = {};
  var tempY = {};
  tempX.Data = tempY.Data = new Date(parentData[0].OPERATEDATE +" "+parentData[0].OPERATETIME).format('yyyy-MM-dd hh:mm:ss')+";" ;//todo数据操作
  tempX.Data += parentData[0].PROGRAMNUM+";"; tempY.Data += parentData[0].PROGRAMNUM+";";
  tempX.Data += parentData[0].PARTSERIALNUM+";"; tempY.Data += parentData[0].PARTSERIALNUM+";";
  tempX.Data += parentData[0].TOTLERESULT+";"; tempY.Data += parentData[0].TOTLERESULT+";";
  tempX.Data += "X coordinate 1st conrner;"; tempY.Data += "Y coordinate 1st conrner;";
  tempX.Data += parentData[0].XUNIT+";"; tempY.Data += parentData[0].YUNIT+";";
  var rowX = "";var rowY = "";
  for(var i = 0;i<curveData.length;i++){
  rowX = rowX + curveData[i].X_VALUE + ";";
  rowY = rowY + curveData[i].Y_VALUE + ";";
  }
  tempX.Data += rowX; tempY.Data += rowY;
  dataList.push(tempX);
  dataList.push(tempY);
  var data = [];
  for(var m=0;m<dataList.length;m++){
    var tem = {};
    tem.Data = dataList[m].Data;
    data.push(tem);
  }
  for(var i = 0; i < title.length; i++){
  data.push(title[i]);
  }
  var fileName = "csv数据导出_"+GetData();

  JSONToExcelConvertor(title,data,fileName);
}

function JSONToExcelConvertor(title, data, fileName) {
  var CSV = '';
  var row = "";

  for (var i = 0; i < title.length; i++) {
  if(title[i].title){
  row += title[i].title + ',';
  }
  }
  row = row.slice(0, -1);
  CSV += row + '\r\n';

  for (var i = 0; i < data.length; i++) {
  var row = "";
  for (var j = 0; j < title.length; j++) {
    if(title[j].title){
    row += '"' + (data[i][title[j].field] ? data[i][title[j].field] : "") + '"\t,';
    }
  }
  row.slice(0, row.length - 1);
    CSV += row + '\r\n';
  }

  if (CSV == '') {
  alert("Invalid data");
  return;
  }

  var fileName = fileName;
  var uri = new Blob(['\ufeff' + CSV], {type:"text/csv"});
  var link = document.createElement("a");
  link.href = URL.createObjectURL(uri);
  link.style = "visibility:hidden";
  link.download = fileName + ".csv";
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
}

function GetData(){
  var day = new Date();
  var Year = 0;
  var Month = 0;
  var Day = 0;
  var CurrentDate = "";
  //初始化时间
  Year= day.getFullYear();
  Month= day.getMonth()+1;
  Day= day.getDate();
  CurrentDate += Year + "/";
  if (Month >= 10 )
  {
    CurrentDate += Month + "/";
  }
  else
  {
    CurrentDate += "0" + Month + "/";
  }
  if (Day >= 10 )
  {
    CurrentDate += Day ;
  }
  else
  {
    CurrentDate += "0" + Day ;
  }
  //时分秒
  var hour = day.getHours();
  var minute = day.getMinutes();
  var second = day.getSeconds();
  CurrentDate =CurrentDate+" "+ (hour<10?("0"+hour):hour)+":";
  CurrentDate = CurrentDate+ (minute<10?("0"+minute):minute)+":";
  CurrentDate = CurrentDate + (second<10?("0"+second):second);
  return CurrentDate ;
}

说明:如果要操作execl单元格样式,免费版的多种js组件都是收费,所以可以通过NPOI操作,接口调用返回url地址实现对execl样式操作下载。

Don't wanna be the other guy,no more...

 

上一篇:简单实现文件上传、下载、预览、拖曳


下一篇:微信公众平台企业号开发—开启回调模式