基本步骤
npm install xlsjs
import * as XLSX from 'xlsx'
<input type='file' accept='.xlsx, .xls' onChange={this.onImportExcel} />
- excel表格的专业名词是:
workbook
- excel里面每张工作表是:
sheet
- `aoa_to_sheet`
将一个二维数组转成sheet
- `table_to_sheet`
将一个table dom直接转成sheet,会自动识别colspan和rowspan并将其转成对应的单元格合并;
XLSX.utils.table_to_sheet($('table')[0])
- `json_to_sheet`
将一个由对象组成的数组转成sheet
- `sheet_add_aoa`
将一组二维数组添加到现有工作表中
- `sheet_add_json`
将一个由对象组成的数组添加到现有工作表中。
- `sheet_to_json`
将工作表对象转换为 JSON 对象数组
- `sheet_to_csv`
生成定界符分隔值输出
- `sheet_to_txt`
生成 UTF16 格式的文本
- `sheet_to_html`
生成 HTML 输出
- `sheet_to_formulae`
生成公式列表(带有值回退)
let workbook = XLSX.read(data, {type: type});
type主要取值如下:
- `base64`: 文件的 Base64 编码的字符串
- `binary`: 二进制字符串
- `string`: UTF8编码的字符串
- `buffer`: nodejs 缓冲区
- `array`: 8 位无符号整数数组
- `file`: 将被读取的文件路径的字符串(仅nodejs下支持)
- `format_cell`
生成单元格的文本值(使用数字格式)
- `encode_row / decode_row`
在0索引行和1索引行之间转换
- `encode_col / decode_col`
在0索引列和列名之间进行转换
- `encode_cell / decode_cell`
转换单元格地址
- `encode_range / decode_range`
转换细胞范围
导入Excel表格
1.readAsArrayBuffer(file):将文件读取为ArrayBuffer。
2.readAsBinaryString(file):将文件读取为二进制字符串
3.readAsDataURL(file):将文件读取为Data URL
4.readAsText(file, [encoding]):将文件读取为文本,encoding缺省值为'UTF-8'
function readWorkbookFromLocalFile(file, callback) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {type: 'binary'});
if(callback) callback(workbook);
};
reader.readAsBinaryString(file);
}
function readWorkbookFromRemoteFile(url, callback) {
var xhr = new XMLHttpRequest();
xhr.open('get', url, true);
xhr.responseType = 'arraybuffer';
xhr.onload = function (e) {
if (xhr.status == 200) {
var data = new Uint8Array(xhr.response)
var workbook = XLSX.read(data, { type: 'array' });
if (callback) callback(workbook);
}
};
xhr.send();
}
导出Excel表格
function exportData() {
let data = [
{name: "张三", id: "123"},
{name: "李四", id: "124"}
];
exportExcel(data, "数据导出表格")
}
function exportExcel(data, filename) {
if (typeof XLSX == 'undefined') XLSX = require('xlsx');
let sheet= XLSX.utils.json_to_sheet(data);
let wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, sheet, "sheetName");
XLSX.writeFile(wb, filename + ".xlsx");
let blob = workbook2blob(wb)
openDownloadDialog(blob, 'excel的标题.xlsx');
}
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
let workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
let wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
};
let wbout = XLSX.write(workbook, wopts);
let blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream"
});
function s2ab(s) {
let buf = new ArrayBuffer(s.length);
let view = new Uint8Array(buf);
for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
function openDownloadDialog(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url);
}
let aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || '';
let event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}