H5以及Node讀取excel

zhouzhou發表於2019-02-16
H5Excel

支援讀寫Excel的node.js模組

支援讀寫excel檔案的模組有很多，但是都各有憂缺點，有些僅支援xls/xlsx的一種格式，有些僅支援讀取資料，有些僅支援匯出檔案，有些需要依賴python解析。常見的npm依賴模組如下：

js-xlsx: 目前 Github 上 star 數量最多的處理 Excel 的庫，支援解析多種格式表格XLSX / XLSM / XLSB / XLS / CSV，解析採用純js實現，寫入需要依賴nodejs或者FileSaver.js實現生成寫入Excel，可以生成子表Excel，功能強大，但上手難度稍大。不提供基礎設定Excel表格api例單元格寬度，文件有些亂，不適合快速上手；
node-xlsx: 基於Node.js解析excel檔案資料及生成excel檔案，僅支援xlsx格式檔案；
excel-parser: 基於Node.js解析excel檔案資料，支援xls及xlsx格式檔案，需要依賴python，太重不太實用；
excel-export : 基於Node.js將資料生成匯出excel檔案，生成檔案格式為xlsx，可以設定單元格寬度，API容易上手，無法生成worksheet字表，比較單一，基本功能可以基本滿足；
node-xlrd: 基於node.js從excel檔案中提取資料，僅支援xls格式檔案,不支援xlsx,有點過時，常用的都是XLSX 格式。

xlxs

對於Excel檔案的內容分析轉換，已經有了比較成熟的解決方案：https://github.com/SheetJS/js…

npm install xlsx
  • workbook 物件，指的是整份 Excel 文件。我們在使用 js-xlsx 讀取 Excel 文件之後就會獲得 workbook 物件。
  • worksheet 物件，指的是 Excel 文件中的表。我們知道一份 Excel 文件中可以包含很多張表，而每張表對應的就是 worksheet 物件。
  • cell 物件，指的就是 worksheet 中的單元格，一個單元格就是一個 cell 物件。
  • A1 標記，引用單元格時所使用的地址格式（如：A1、C7）
// workbook
{
 SheetNames: [`sheet1`, `sheet2`],
 Sheets: {
 // worksheet
 `sheet1`: {
  // cell
  `A1`: { ... },
  // cell
  `A2`: { ... },
  ...
 },
 // worksheet
 `sheet2`: {
  // cell
  `A1`: { ... },
  // cell
  `A2`: { ... },
  ...
 }
 }
}

基本用法

  1. 用 XLSX.read 讀取獲取到的 Excel 資料，返回 workbook
  2. 用 XLSX.readFile 開啟 Excel 檔案，返回 workbook
  3. 用 workbook.SheetNames 獲取表名
  4. 用 workbook.Sheets[xxx] 通過表名獲取表格
  5. 用 worksheet[address]操作單元格
  6. 用XLSX.utils.sheet_to_json針對單個表獲取表格資料轉換為json格式
  7. 用XLSX.writeFile(wb, `output.xlsx`)生成新的 Excel 檔案

讀取檔案

import XLSX from `xlsx`;

const wb = XLSX.readFile(`path/to/file.xlsx`); // 返回 workbook
workbook 物件結構如下：

{
    SheetNames: [`Sheet1`, `Sheet2`],
    Sheets: {
       `Sheet1`: { ... },
       `Sheet2`: { ... }
    },
    Props: { ... },
    ....
}

獲取工作表

根據表名獲取對應的工作表

const ws = wb.Sheets[workbook.SheetNames[0]]; // 返回 worksheet
worksheet 物件結構如下：

{
    `!ref`: `A1:C7`,
    A1: { ... },
    B1: { ... },
    ....
}
// 其中 worksheet[`!ref`] 是工作表的有效範圍（基於 A-1）。

獲取單元格

通過 worksheet[address_of_cell] 獲取單元格，也就是通過 A1 標記的鍵名來獲取單元格：

const cell = wb[`C7`];

或者通過地址物件 { r: R, c: C } 來獲取單元格，R 和 C 分別代表從 0 開始的行和列的索引。

// XLSX.utils 中的 encode_cell/decode_cell 方法可以轉換單元格地址
const cell = wb[XLSX.utils.encode_cell({ r: 7, c: 2 })]; // 等同於 wb[`C7`]
cell 物件結構如下：

{
    v: `C7SKY`,
    w: `C7SKY`,
    t: `s`,
    ....
}

編輯單元格

cell.v = `小影志`;
delete cell.w;

需要注意的是，內建的匯出工具會優先嚐試使用 w 的值，所以如果之後要使用匯出功能，在修改值時應該同時刪除 w 按鍵或設定為 undefined。

刪除行/刪除列

js-xlsx 並沒有提供刪除行/刪除列的功能，所以需要我們自行實現：

function encodeCell(r, c) {
    return XLSX.utils.encode_cell({ r, c });
}

function deleteRow(ws, index) {
    const range = XLSX.utils.decode_range(ws[`!ref`]);

    for (let row = index; row < range.e.r; row++) {
        for (let col = range.s.c; col <= range.e.c; col++) {
            ws[encodeCell(row, col)] = ws[encodeCell(row + 1, col)];
        }
    }

    range.e.r--;

    ws[`!ref`] = XLSX.utils.encode_range(range.s, range.e);
}

function deleteCol(ws, index) {
    const range = XLSX.utils.decode_range(ws[`!ref`]);

    for (let col = index; col < range.e.c; col++) {
        for (let row = range.s.r; row <= range.e.r; row++) {
            ws[encodeCell(row, col)] = ws[encodeCell(row, col + 1)];
        }
    }

    range.e.c--;

    ws[`!ref`] = XLSX.utils.encode_range(range.s, range.e);
}

XLSX.utils 中的 encode_range / decode_range 方法用來轉換單元格範圍（A2:C7 相當於 {s:{c:0, r:1}, e:{c:2, r:6}}）。

儲存/匯出

通過 XLSX.writeFile(wb, filename) 就可以生成新的表格檔案：

XLSX.writeFile(wb, `output.xlsx`);

而 XLSX.utils.sheet_to_* 則提供了多種匯出格式（csv/txt/html/json/formulae），以下是匯出 JSON 檔案的程式碼：

const data = XLSX.utils.sheet_to_json(ws);

fs.writeFileSync(`path/to/export.json`, JSON.stringify(data, null, 4));

H5讀取excel

使用HTML5的FileReader，開啟本地檔案。

<input type="file" id="excel-file">
var wb;//讀取完成的資料
var rABS = false; //是否將檔案讀取為二進位制字串

function importf(obj) {//匯入
                if(!obj.files) {
                    return;
                }
                var f = obj.files[0];
                var reader = new FileReader();
                reader.onload = function(e) {
                    var data = e.target.result;
                    if(rABS) {
                        wb = XLSX.read(btoa(fixdata(data)), {//手動轉化
                            type: `base64`
                        });
                    } else {
                        wb = XLSX.read(data, {
                            type: `binary`
                        });
                    }
                    //wb.SheetNames[0]是獲取Sheets中第一個Sheet的名字
                    //wb.Sheets[Sheet名]獲取第一個Sheet的資料
                    document.getElementById("demo").innerHTML= JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) );
                };
                if(rABS) {
                    reader.readAsArrayBuffer(f);
                } else {
                    reader.readAsBinaryString(f);
                }
}

function fixdata(data) { //檔案流轉BinaryString
       var o = "",l = 0,w = 10240;
       for(; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
       o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
       return o;
}

Node讀取excel

var fs = require(`fs`);
var xlsx = require(`node-xlsx`);
 
var list = xlsx.parse("./excel/" +"1.xlsx"); //讀取excel
 
 
var datas=[];
var data=[1,2,3];
var data1=[4,5,6];
datas.push(data);    //一行一行新增的 不是一列一列
datas.push(data1);    
writeXls(datas);
function writeXls(datas) {
    var buffer = xlsx.build([
        {
            name:`sheet1`,
            data:datas   
        }
    ]);
    fs.writeFileSync(`test1.xlsx`,buffer,{`flag`:`w`});   //生成excel
}

相關文章