Excel 到 JSON javascript 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28782074/
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
Excel to JSON javascript code?
提问by Programmer
I want to convert excel sheet data to json. It has to be dynamic, so there is an upload button where user uploads the excel sheet and the data is then converted into json. Could you please provide me the javascript code? I tried SheetJS but couldn't figure out. I would prefer something straight forward :)
我想将excel表格数据转换为json。它必须是动态的，因此有一个上传按钮，用户可以在其中上传 Excel 表格，然后将数据转换为 json。你能提供我的javascript代码吗？我试过 SheetJS，但无法弄清楚。我更喜欢直接的东西:)
I really appreciate your help!
我真的很感谢你的帮助！
回答by Angry 84
NOTE: Not 100% Cross Browser
注意：不是 100% 跨浏览器
Check browser compatibility @http://caniuse.com/#search=FileReader
检查浏览器兼容性@ http://caniuse.com/#search=FileReader
as you will see people have had issues with the not so common browsers, But this could come down to the version of the browser.. I always recommend using something like caniuseto see what generation of browser is supported... This is only a working answer for the user, not a final copy and paste code for people to just use..
正如您将看到的，人们对不太常见的浏览器有问题，但这可能归结为浏览器的版本。我总是建议使用类似caniuse 的东西来查看支持哪一代浏览器...为用户提供工作答案，而不是供人们使用的最终复制和粘贴代码。
The Fiddle: http://jsfiddle.net/d2atnbrt/3/
小提琴：http: //jsfiddle.net/d2atnbrt/3/
THE HTML CODE:
HTML代码：
<input type="file" id="my_file_input" />
<div id='my_file_output'></div>
THE JS CODE:
JS代码：
var oFileIn;
$(function() {
oFileIn = document.getElementById('my_file_input');
if(oFileIn.addEventListener) {
oFileIn.addEventListener('change', filePicked, false);
}
});
function filePicked(oEvent) {
// Get The File From The Input
var oFile = oEvent.target.files[0];
var sFilename = oFile.name;
// Create A File Reader HTML5
var reader = new FileReader();
// Ready The Event For When A File Gets Selected
reader.onload = function(e) {
var data = e.target.result;
var cfb = XLS.CFB.read(data, {type: 'binary'});
var wb = XLS.parse_xlscfb(cfb);
// Loop Over Each Sheet
wb.SheetNames.forEach(function(sheetName) {
// Obtain The Current Row As CSV
var sCSV = XLS.utils.make_csv(wb.Sheets[sheetName]);
var oJS = XLS.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
$("#my_file_output").html(sCSV);
console.log(oJS)
});
};
// Tell JS To Start Reading The File.. You could delay this if desired
reader.readAsBinaryString(oFile);
}
This also requires https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.jsto convert to a readable format, i've also used jquery only for changing the div contents and for the dom ready event.. so jquery is not needed
这也需要https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js转换为可读格式，我还使用 jquery 仅用于更改 div 内容和dom 就绪事件.. 所以不需要 jquery
This is as basic as i could get it,
这是我能理解的最基本的，
EDIT - Generating A Table
编辑 - 生成表
The Fiddle: http://jsfiddle.net/d2atnbrt/5/
小提琴：http: //jsfiddle.net/d2atnbrt/5/
This second fiddle shows an example of generating your own table, the key here is using sheet_to_json to get the data in the correct format for JS use..
第二个小提琴显示了生成自己的表的示例，这里的关键是使用 sheet_to_json 以正确的格式获取数据以供 JS 使用。
One or two comments in the second fiddle might be incorrect as modified version of the first fiddle.. the CSV comment is at least
第二个小提琴中的一两个评论可能不正确，因为第一个小提琴的修改版本.. CSV 评论至少是
Test XLS File: http://www.whitehouse.gov/sites/default/files/omb/budget/fy2014/assets/receipts.xls
测试 XLS 文件：http: //www.whitehouse.gov/sites/default/files/omb/budget/fy2014/assets/receipts.xls
This does not cover XLSX files thought, it should be fairly easy to adjust for them using their examples.
这并不涵盖 XLSX 文件的想法，使用他们的示例对它们进行调整应该相当容易。
回答by user7456320
js-xlsx library makes it easy to convert Excel/CSV files into JSON objects.
js-xlsx 库可以轻松地将 Excel/CSV 文件转换为 JSON 对象。
Download the xlsx.full.min.js file from here. Write below code on your HTML page Edit the referenced js file link (xlsx.full.min.js) and link of Excel file
从这里下载 xlsx.full.min.js 文件。在您的 HTML 页面上编写以下代码编辑引用的 js 文件链接 (xlsx.full.min.js) 和 Excel 文件的链接
<!doctype html>
<html>
<head>
<title>Excel to JSON Demo</title>
<script src="xlsx.full.min.js"></script>
</head>
<body>
<script>
/* set up XMLHttpRequest */
var url = "http://myclassbook.org/wp-content/uploads/2017/12/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("");
/* Call XLSX */
var workbook = XLSX.read(bstr, {
type: "binary"
});
/* DO SOMETHING WITH workbook HERE */
var first_sheet_name = workbook.SheetNames[0];
/* Get worksheet */
var worksheet = workbook.Sheets[first_sheet_name];
console.log(XLSX.utils.sheet_to_json(worksheet, {
raw: true
}));
}
oReq.send();
</script>
</body>
</html>
Input:
输入：
Output:
输出：
回答by Kwang-Chun Kang
The answers are working fine with xls format but, in my case, it didn't work for xlsx format. Thus I added some code here. it works both xls and xlsx format.
答案适用于 xls 格式，但就我而言，它不适用于 xlsx 格式。因此我在这里添加了一些代码。它适用于 xls 和 xlsx 格式。
I took the sample from the official sample link.
Hope it may help !
希望它可以帮助！
function fileReader(oEvent) {
var oFile = oEvent.target.files[0];
var sFilename = oFile.name;
var reader = new FileReader();
var result = {};
reader.onload = function (e) {
var data = e.target.result;
data = new Uint8Array(data);
var workbook = XLSX.read(data, {type: 'array'});
console.log(workbook);
var result = {};
workbook.SheetNames.forEach(function (sheetName) {
var roa = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {header: 1});
if (roa.length) result[sheetName] = roa;
});
// see the result, caution: it works after reader event is done.
console.log(result);
};
reader.readAsArrayBuffer(oFile);
}
// Add your id of "File Input"
$('#fileUpload').change(function(ev) {
// Do something
fileReader(ev);
}
回答by Yuhang Zhou
@Kwang-Chun Kang Thanks Kang a lot! I found the solution is working and very helpful, it really save my day. For me I am trying to create a React.js component that convert *.xlsx to json object when user upload the excel file to a html input tag. First I need to install XLSX package with:
@Kwang-Chun Kang 非常感谢康！我发现该解决方案有效并且非常有帮助，它确实挽救了我的一天。对我来说，我正在尝试创建一个 React.js 组件，当用户将 excel 文件上传到 html 输入标签时，该组件将 *.xlsx 转换为 json 对象。首先，我需要安装 XLSX 包：
npm install xlsx --save
Then in my component code, import with:
然后在我的组件代码中，导入：
import XLSX from 'xlsx'
The component UI should look like this:
组件 UI 应如下所示：
<input
accept=".xlsx"
type="file"
onChange={this.fileReader}
/>
It calls a function fileReader(), which is exactly same as the solution provided. To learn more about fileReader API, I found this blog to be helpful: https://blog.teamtreehouse.com/reading-files-using-the-html5-filereader-api
它调用一个函数fileReader()，它与提供的解决方案完全相同。要了解有关 fileReader API 的更多信息，我发现此博客很有帮助：https: //blog.teamtreehouse.com/reading-files-using-the-html5-filereader-api
