Electron中excel读取

发表于 | 分类于
Electron中excel读取

Excel导入导出

下载

1
npm install node-xlsx

代码中引入node-xlsx包

1
const nodeXlsx = require("node-xlsx"); //引入模块

导入Excel文件

1
2
3
4
5
6
7
8
9
10
11
12
let path = 'D://test.xlsx'
let sheets = nodeXlsx.parse(path)

// 解析所有sheet
sheets.forEach(sheet => {
    // sheet.data是所有行数据
    let rows = sheet.data
    for (var i = 0; i < rows.length; i++) {
        console.log(`第${i + 1}行第一列数据：${rows[i][0]}`)
        console.log(`第${i + 1}行第二列数据：${rows[i][1]}`)
    }
});

导出Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
const xlsx = require("node-xlsx"); //引入模块

const exportExcel = (data) => {
  // 設置表格列寬
  const options = {
    "!cols": [
      { wch: 25 },
      { wch: 45 },
      { wch: 90 },
    ],
  };
  // 要導出的數據數組
  let xlsxObj = [
    {
      name: "sheet",
      data: [
        [
          "id",
          "name",
          "age",
        ],
      ],
    },
  ];
  // 生成導出的數據
  let foodData = data;
  foodData.forEach((item) => {
    let typeArr = [];
    typeArr.push(item.type);
    item.foodsList.forEach((list) => {
      let listArr = [];
      for (const key in list) {
        listArr.push(list[key]);
      }
      xlsxObj[0].data.push(typeArr.concat(listArr));
    });
  });

  //xlsxObj最终的数据格式为:
  // 二维数组
  // [
  //   ["id","name","age"],//第一列为表头信息
  //   ['1','小明',16],
  //   ['2','小红',18],
  //   ......
  // ]

  return xlsx.build(xlsxObj, options)
};

module.exports = exportExcel;

导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
var exportExcel = require('./exportExcel');
var fs = require('fs');
let data = [{
    id:"1",
    name:"小明",
    age:16
},{
    id:"2",
    name:"小红",
    age:18
}];
var buffer = exportExcel(data);
fs.writeFile('./result.xls',buffer,function(err){
    if(err){
        console.info(err);
    }else{
        console.info("导出成功");
    }
})

前端导出Excel

工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
var download_excel = function (content, filename) {
    // 创建隐藏的可下载链接
    var eleLink = document.createElement("a");
    eleLink.download = filename;
    eleLink.style.display = "none";
    // 字符内容转变成blob地址
    var blob = new Blob([content]);
    eleLink.href = URL.createObjectURL(blob);
    // 触发点击
    document.body.appendChild(eleLink);
    eleLink.click();
    // 然后移除
    document.body.removeChild(eleLink);
};

//替换table数据和worksheet名字
var format = function (s, c) {
    return s.replace(/{(\w+)}/g, function (m, p) {
        return c[p];
    });
};

export const table2excel = function (tableid, sheetName) {
    var template =
        '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"' +
        'xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>' +
        "<x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>" +
        "</x:ExcelWorkbook></xml><![endif]-->" +
        ' <style type="text/css">' +
        "table th,td {" +
        "min-width: 200px;" +
        " text-align: center;" +
        "background-color: #ffffff;" +
        "color: #333333;" +
        " }" +
        "</style>" +
        '</head><body ><table class="excelTable">{table}</table></body></html>';
    if (!tableid.nodeType) tableid = document.getElementById(tableid);
    var ctx = {worksheet: sheetName || "Worksheet", table: tableid.innerHTML};
    download_excel(format(template, ctx), sheetName);
};

使用

1
2
import {table2excel} from "./export_table_excel.js";
table2excel("mytable", "评价统计.xls");

选择文件夹/文件

选择文件夹

添加引用

1
const { app, dialog } = window.require("electron").remote;

弹窗选择

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select_dir: function() {
    dialog
        .showOpenDialog({
        properties: ["openFile", "openDirectory"]
    })
        .then(result => {
        if (!result.canceled) {
            this.outpath = result.filePaths[0];
        }
    })
        .catch(err => {
        console.log(err);
    });
}

选择文件

1
2
3
4
5
6
7
8
9
10
11
12
13
dialog.showOpenDialog({
    title:'请选择文件',
    defaultPath:'',
    filters:[{
        name:'表格(xls/xlsx)',
        extensions:['xls','xlsx']
    }],
    buttonLabel:'选择文件'
}).then(result=>{
    console.info('path',result.filePaths[0]);
}).catch(err=>{
    console.log(err)
})

进程间通讯

渲染进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
ipcRenderer.on('read_xls_result', (event, arg) => {
    let all_data = arg;
    let table_header = [];
    let table_data = [];
    if(all_data&&all_data.length>0){
        table_header = all_data[0];
        for (let i = 1; i < all_data.length; i++) {
            let row = all_data[i];
            let data_row = {};
            //设置初始值
            for (let j = 0; j < table_header.length; j++) {
                data_row[table_header[j]+""] = "";
            }
            for (let j = 0; j < row.length; j++) {
                data_row[table_header[j]+""] = row[j]||"";
            }
            table_data.push(data_row);
        }
    }
    console.info("table_header",table_header);
    console.info("table_data",table_data);
    this.table_header = table_header;
    this.table_data = this.table_data.concat(table_data) ;
})

ipcRenderer.send('read_xls', this.outpath)

在主进程中

1
2
3
4
5
6
7
8
9
10
11
const { ipcMain } = require("electron")

ipcMain.on("read_xls", (event, arg) => {
    console.log(arg);
    let sheets = nodeXlsx.parse(arg)
    sheets.forEach(sheet => {
        // sheet.data是所有行数据
        let rows = sheet.data;
        event.reply("read_xls_result", rows);
    });
});

数字运算

math.js

镜像地址

https://cdnjs.cloudflare.com/ajax/libs/mathjs/3.16.0/math.min.js

1
2
3
let num1 = 0.1;
let num2 = 0.2;
let sum = math.format(math.add(num1, num2), {precision: 4});