js中xlsx.utils怎么拿到所有的行

发布网友发布时间：2022-05-15 08:07

共2个回答

懂视网时间：2022-05-15 12:29

这次给大家带来js-xlsx的工具类库xlsxUtils的使用详解，使用js-xlsx的工具类库xlsxUtils的的注意事项有哪些，下面就是实战案例，一起来看一下。

函数说明:

var xlsxUtils = {
 Binary: {
 fixdata(data) { //文件流转BinaryString
  //......
 },
 s2ab(s) { //字符串转字符流
  //......
 }
 },
 _wb: null,//缓存导入时的Workbook 
 _rABS: false,//设置读取方式
 /**
 * @desc 导入根据文件
 * @param {File} f 文件
 * @param {Function} c 回调
 * @return {Object} 回调值
 */
 import(f, c) { //......
 }, /**
 * @desc 根据表Sheet名获取数据
 * @param {String} name 
 * @return {Object} 
 */
 getSheetByName(name) {//
 //......
 }, /**
 * @desc 根据表Sheet索引获取数据
 * @param {Number} index 
 * @return {Object}
 */
 getSheetByIndex(index = 0) { //......
 }, /**
 * @desc 导出
 * @param {Array} data 数据{title1:dataList,title2:dataList....} 
 * @param {String} type 
 * @return {Blob}
 */
 export(data, type) { //......
 }, /**
 * 从数据数组或对象中根据key生成相同key值的对象
 * @param {Object|Array} data 
 * @return {Object}
 */
 readDataHead(data) { //......
 }, /**
 * @desc 格式化数据为Sheet格式
 * @param {Array} json 数据
 */
 format2Sheet(json, n) { //......
 }, /**
 * @desc 格式化数据为Sheet格式
 * @param {Array} sheetData 
 * @param {String} title 
 * @param {Object} wb 
 */
 format2WB(sheetData, title = "mySheet", wb) { //......
 }, /**
 * @desc 将xlsx Workbook 转为blob
 * @param {Array} wb 
 * @param {String} type 类型
 */
 format2Blob(wb, type) { //......
 }, /**
 * @desc 匹配单元格对应的标识
 * @param {Number} n 
 */
 getCharCol(n) { //......
 },
};

示例:

导入示例(在线示例):

<!DOCTYPE html><html><head>
 <meta charset="UTF-8">
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
 <meta http-equiv="X-UA-Compatible" content="ie=edge">
 <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
 <script src="./xlsx.utils.js"></script></head><body>
 <input type="file" id="demo" />
 <div id="loadInfo"></div>
 <div>
 <div>
  <label>SheetNames</label>:  <span id="SheetNames"></span>
 </div>
 <div>
  <label id="demoName"></label>(示例):  <span id="demoData"></span>
 </div>
 </div>
 <script>
 var demo = document.getElementById("demo");
 demo.onchange = function () {  let f = this.files[0];  let loadInfo = document.getElementById("loadInfo");  let demoName = document.getElementById("demoName");  let demoData = document.getElementById("demoData");
  loadInfo.innerHTML = "正在读取请骚等";
  demoName.innerHTML = "";
  demoData.innerHTML = "";
  xlsxUtils.import(f, (w) => {  document.getElementById("SheetNames").innerHTML = JSON.stringify(w.SheetNames);
  demoName.innerHTML = w.SheetNames[0];
  demoData.innerHTML = JSON.stringify(xlsxUtils.getSheetByIndex(0));
  loadInfo.innerHTML = "读取完成";
  });
 }; </script></body></html>

导出示例(在线示例)

<!DOCTYPE html><html><head>
 <meta charset="UTF-8">
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
 <meta http-equiv="X-UA-Compatible" content="ie=edge">
 <title>demo</title>
 <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
 <script src="xlsx.utils.min.js"></script>
 <script src="saveAs.min.js"> </script></head><body>
 <script>
 var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }]; </script>
 <button id="down">导出</button>
 <script>
 var down = document.getElementById("down");
 down.onclick = function () {
  data.unshift(xlsxUtils.readDataHead(data));  var blob = xlsxUtils.export({ "Sheet1": data, "Sheet2": [{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }] });
  saveAs(URL.createObjectURL(blob), "aa.xlsx");
 }; </script></body></html>

自定义起始列导出示例(在线示例)

<!DOCTYPE html><html><head>
 <meta charset="UTF-8">
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
 <meta http-equiv="X-UA-Compatible" content="ie=edge">
 <title>demo</title>
 <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
 <script src="xlsx.utils.min.js"></script>
 <script src="saveAs.min.js"> </script></head><body>
 <script>
 var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }]; </script>
 <button id="down">导出</button>
 <script>
 var down = document.getElementById("down");
 down.onclick = function () {
  data.unshift(xlsxUtils.readDataHead(data)); 
  var tmpdata = xlsxUtils.format2Sheet(data, 2);  var tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet1");
  tmpdata = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }], 4);
  tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet2", tmpWB);  var blob = xlsxUtils.format2Blob(tmpWB);
  saveAs(URL.createObjectURL(blob), "aa.xlsx");
 }; </script></body></html>

多数据合并导出到单表示例(在线示例)

<!DOCTYPE html><html><head>
 <meta charset="UTF-8">
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
 <meta http-equiv="X-UA-Compatible" content="ie=edge">
 <title>demo</title>
 <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
 <script src="xlsx.utils.min.js"></script>
 <script src="saveAs.min.js"> </script></head><body>
 <script>
 var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }]; </script>
 <button id="down">导出</button>
 <script>
 var down = document.getElementById("down");
 down.onclick = function () {  var d1 = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }]);  var h=xlsxUtils.readDataHead(data);//读取data的列头数据
  data.unshift(h);//将列头追加到最data的前
  var em={};//用于换行
  Object.keys(h).forEach((v)=>{
  em[v]="";
  });  for(var i=0;i<5;i++){//换5行
  data.unshift(em);
  }  var d2 = xlsxUtils.format2Sheet(data, 3);//3代表从D列开始
  d1=Object.assign(d1,d2); 
  var tmpWB = xlsxUtils.format2WB(d1, "Sheet1"); 
  var blob = xlsxUtils.format2Blob(tmpWB);
  saveAs(URL.createObjectURL(blob), "aa.xlsx");
 }; </script></body></html>

相信看了本文案例你已经掌握了方法，更多精彩请关注Gxl网其它相关文章！