如何在 Javascript/HTML5 中解析 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8238407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to parse Excel file in Javascript/HTML5
提问by ducktyped
I am able to read Excel file via FileReaderbut it outputs text as well as weird characters with it. I need to read xlsfile row-wise, read data in every column and convert it to JSON.
我可以通过FileReader它读取 Excel 文件，但它会输出文本和奇怪的字符。我需要xls逐行读取文件，读取每一列中的数据并将其转换为 JSON。
How to read xls file row by row?
如何逐行读取xls文件？
采纳答案by Peru
Below Function converts the Excel sheet (XLSX format) data to JSON. you can add promise to the function.
下面的函数将 Excel 工作表（XLSX 格式）数据转换为 JSON。您可以向函数添加承诺。
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
var ExcelToJSON = function() {
this.parseExcel = function(file) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function(sheetName) {
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
var json_object = JSON.stringify(XL_row_object);
console.log(json_object);
})
};
reader.onerror = function(ex) {
console.log(ex);
};
reader.readAsBinaryString(file);
};
};
</script>
Below post has the code for XLS format Excel to JSON javascript code?
下面的帖子有 XLS 格式Excel 到 JSON javascript 代码的代码？
回答by SheetJS
Old question, but I should note that the general task of parsing XLS files from javascript is tedious and difficult but not impossible.
老问题，但我应该注意到，从 javascript 解析 XLS 文件的一般任务是乏味和困难的，但并非不可能。
I have basic parsers implemented in pure JS:
我有用纯 JS 实现的基本解析器：
- http://oss.sheetjs.com/js-xls/(XLS files, what you wanted)
- http://oss.sheetjs.com/js-xlsx/(XLSX/XLSM/XLSB files)
Both pages are HTML5 File API-driven XLS/XLSX parsers (you can drag-drop your file and it will print out the data in the cells in a comma-separated list). You can also generate JSON objects (assuming the first row is a header row).
两个页面都是 HTML5 文件 API 驱动的 XLS/XLSX 解析器（您可以拖放您的文件，它将打印出逗号分隔列表中单元格中的数据）。您还可以生成 JSON 对象（假设第一行是标题行）。
The test suite http://oss.sheetjs.com/shows a version that uses XHR to get and parse files.
测试套件http://oss.sheetjs.com/显示了一个使用 XHR 获取和解析文件的版本。
回答by Akash
Upload an excel filehere and you can get the data in JSONformat in console:
在excel file此处上传，您可以获取以下JSON格式的数据console：
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
var ExcelToJSON = function() {
this.parseExcel = function(file) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function(sheetName) {
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
var json_object = JSON.stringify(XL_row_object);
console.log(JSON.parse(json_object));
jQuery( '#xlx_json' ).val( json_object );
})
};
reader.onerror = function(ex) {
console.log(ex);
};
reader.readAsBinaryString(file);
};
};
function handleFileSelect(evt) {
var files = evt.target.files; // FileList object
var xl2json = new ExcelToJSON();
xl2json.parseExcel(files[0]);
}
</script>
<form enctype="multipart/form-data">
<input id="upload" type=file name="files[]">
</form>
<textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea>
<script>
document.getElementById('upload').addEventListener('change', handleFileSelect, false);
</script>This is a combination of the following Stackoverflowposts:
这是以下Stackoverflow帖子的组合：
Good Luck...
祝你好运...
回答by afzalriz304
This code can help you
Most of the time jszip.js is not working so include xlsx.full.min.js in your js code.
这段代码可以帮助你
大部分时间 jszip.js 不起作用，所以在你的 js 代码中包含 xlsx.full.min.js。
Html Code
html代码
<input type="file" id="file" ng-model="csvFile"
onchange="angular.element(this).scope().ExcelExport(event)"/>
Javascript
Javascript
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js">
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js">
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.8/xlsx.full.min.js">
</script>
$scope.ExcelExport= function (event) {
var input = event.target;
var reader = new FileReader();
reader.onload = function(){
var fileData = reader.result;
var wb = XLSX.read(fileData, {type : 'binary'});
wb.SheetNames.forEach(function(sheetName){
var rowObj =XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
var jsonObj = JSON.stringify(rowObj);
console.log(jsonObj)
})
};
reader.readAsBinaryString(input.files[0]);
};
回答by catamphetamine
If you want the simplest and tiniest way of reading an *.xlsx file in a browser then this library might do:
如果您想要在浏览器中以最简单和最小的方式读取 *.xlsx 文件，那么这个库可能会这样做：
https://catamphetamine.github.io/read-excel-file/
https://catamphetamine.github.io/read-excel-file/
<input type="file" id="input" />
import readXlsxFile from 'read-excel-file'
const input = document.getElementById('input')
input.addEventListener('change', () => {
readXlsxFile(input.files[0]).then((data) => {
// `data` is an array of rows
// each row being an array of cells.
})
})
In the example above datais raw string data.
It can be parsed to JSON with a strict schema by passing schemaargument. See API docs for an example of that.
在上面的例子中data是原始字符串数据。可以通过传递schema参数将其解析为具有严格模式的 JSON 。有关示例，请参阅 API 文档。
API docs: http://npmjs.com/package/read-excel-file
回答by The gates of Zion
Thank you for the answer above, I think the scope (of answers) is completed but I would like to add a "react way" for whoever using react.
感谢您提供上述答案，我认为（答案）范围已完成，但我想为使用 react 的任何人添加“反应方式”。
Create a file called importData.js:
创建一个名为 importData.js 的文件：
import React, {Component} from 'react';
import XLSX from 'xlsx';
export default class ImportData extends Component{
constructor(props){
super(props);
this.state={
excelData:{}
}
}
excelToJson(reader){
var fileData = reader.result;
var wb = XLSX.read(fileData, {type : 'binary'});
var data = {};
wb.SheetNames.forEach(function(sheetName){
var rowObj =XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
var rowString = JSON.stringify(rowObj);
data[sheetName] = rowString;
});
this.setState({excelData: data});
}
loadFileXLSX(event){
var input = event.target;
var reader = new FileReader();
reader.onload = this.excelToJson.bind(this,reader);
reader.readAsBinaryString(input.files[0]);
}
render(){
return (
<input type="file" onChange={this.loadFileXLSX.bind(this)}/>
);
}
}
Then you can use the component in the render method like:
然后您可以在渲染方法中使用该组件，例如：
import ImportData from './importData.js';
import React, {Component} from 'react';
class ParentComponent extends Component{
render(){
return (<importData/>);
}
}
<ImportData/>would set the data to its own state, you can access Excel data in the "parent component" by following this:
<ImportData/>将数据设置为自己的状态，就可以通过Excel中的数据“父组件”按照这个：
回答by amin
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script>
var ExcelToJSON = function() {
this.parseExcel = function(file) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function(sheetName) {
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
var json_object = JSON.stringify(XL_row_object);
console.log(JSON.parse(json_object));
jQuery( '#xlx_json' ).val( json_object );
})
};
reader.onerror = function(ex) {
console.log(ex);
};
reader.readAsBinaryString(file);
};
};
function handleFileSelect(evt) {
var files = evt.target.files; // FileList object
var xl2json = new ExcelToJSON();
xl2json.parseExcel(files[0]);
}
</script>
<form enctype="multipart/form-data">
<input id="upload" type=file name="files[]">
</form>
<textarea class="form-control" rows=35 cols=120 id="xlx_json"></textarea>
<script>
document.getElementById('upload').addEventListener('change', handleFileSelect, false);
</script>回答by vsnahar
include the xslx.js , xlsx.full.min.js , jszip.js
包括 xslx.js ， xlsx.full.min.js ， jszip.js
add a onchange event handler to the file input
将 onchange 事件处理程序添加到文件输入
function showDataExcel(event)
{
var file = event.target.files[0];
var reader = new FileReader();
var excelData = [];
reader.onload = function (event) {
var data = event.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function (sheetName) {
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
for (var i = 0; i < XL_row_object.length; i++)
{
excelData.push(XL_row_object[i]["your column name"]);
}
var json_object = JSON.stringify(XL_row_object);
console.log(json_object);
alert(excelData);
})
};
reader.onerror = function (ex) {
console.log(ex);
};
reader.readAsBinaryString(file);
}
回答by Vijay Reddy
If you are ever wondering how to read a file from server this code might be helpful.
如果您想知道如何从服务器读取文件，此代码可能会有所帮助。
Restrictions :
限制 ：
- File should be in the server (Local/Remote).
- You will have to setup headers or have CORS google plugin.
- 文件应该在服务器中（本地/远程）。
- 您必须设置标题或拥有 CORS 谷歌插件。
<Head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script lang="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.12.4/xlsx.core.min.js"></script>
</head>
<body>
<script>
/* set up XMLHttpRequest */
// replace it with your file path in local server
var url = "http://localhost/test.xlsx";
var oReq = new XMLHttpRequest();
oReq.open("GET", url, true);
oReq.responseType = "arraybuffer";
oReq.onload = function(e) {
var arraybuffer = oReq.response;
/* convert data to binary string */
var data = new Uint8Array(arraybuffer);
var arr = new Array();
for (var i = 0; i != data.length; ++i) {
arr[i] = String.fromCharCode(data[i]);
}
var bstr = arr.join("");
var cfb = XLSX.read(bstr, { type: 'binary' });
cfb.SheetNames.forEach(function(sheetName, index) {
// Obtain The Current Row As CSV
var fieldsObjs = XLS.utils.sheet_to_json(cfb.Sheets[sheetName]);
fieldsObjs.map(function(field) {
$("#my_file_output").append('<input type="checkbox" value="' + field.Fields + '">' + field.Fields + '<br>');
});
});
}
oReq.send();
</script>
</body>
<div id="my_file_output">
</div>
</html>
回答by Darin Dimitrov
XLS is a binary proprietary format used by Microsoft. Parsing XLS with server side languages is very difficult without using some specific library or Office Interop. Doing this with javascript is mission impossible. Thanks to the HTML5 File API you can read its binary contents but in order to parse and interpret it you will need to dive into the specifications of the XLS format. Starting from Office 2007, Microsoft embraced the Open XMLfile formats (xslxfor Excel) which is a standard.
XLS 是 Microsoft 使用的二进制专有格式。如果不使用某些特定的库或 Office Interop，用服务器端语言解析 XLS 是非常困难的。用 javascript 做到这一点是不可能完成的任务。多亏了 HTML5 文件 API，您可以读取其二进制内容，但为了解析和解释它，您需要深入了解 XLS 格式的规范。从 Office 2007 开始，Microsoft 采用标准的Open XML文件格式（xslx用于 Excel）。
