如何将本地 Excel 文件数据传递给 Javascript 数组（用于谷歌图表）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24526141/
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 can I pass local Excel file data to Javascript array (for google charts)?
提问by CyrilFind
I have a simple problem but I can't find a simple solution:
我有一个简单的问题，但找不到简单的解决方案：
I have an Excel file (always the same), with several WorkSheets that non-IT coworkers want to be able to update so that it also updates the Google Charts (or other chart API you can advise me) webpage.
我有一个 Excel 文件（始终相同），其中包含几个工作表，非 IT 同事希望能够更新这些工作表，以便它还更新 Google Charts（或您可以建议我的其他图表 API）网页。
(By the way, I only need a certain part of each worksheet so if you know a way to extract only what I need that would be great :) )
（顺便说一句，我只需要每个工作表的某个部分，所以如果你知道一种只提取我需要的东西的方法，那就太好了:)）
I would like to avoid having server-side operation but it's maybe not possible since I want to read a local file.
我想避免进行服务器端操作，但由于我想读取本地文件，这可能是不可能的。
I have looked at ways to convert to JSON, or to load as base64 string, using xlsx.js but I find nothing simple that my colleagues could easily use.
我已经研究了使用 xlsx.js 转换为 JSON 或加载为 base64 字符串的方法，但我发现没有什么简单的方法可以让我的同事轻松使用。
Thank you very much.
非常感谢你。
Edit
编辑
I found a way to do exactly what I wanted so if it can help anyone:
我找到了一种方法来做我想做的事情，所以如果它可以帮助任何人：
First, I put all the data I needed in one csv file.
首先，我将需要的所有数据放在一个 csv 文件中。
Then, I used HTML5 FileAPI to read the csv file that I loaded with a file input.
然后，我使用 HTML5 FileAPI 读取我用文件输入加载的 csv 文件。
Finally I used a cvstojson script to parse the file then passed it to th google graph dataTable the way I wanted.
最后，我使用 cvstojson 脚本来解析文件，然后以我想要的方式将其传递给 google graph dataTable。
However, this allowed to load the data only once so I used a button-styled label for the file input that triggered a hidden "reset" button (actually cloning more than resetting) after the file was loaded so now it looks like a single button that allows to load the file multiple times after I changed the data in it. I also used jQuery's localStorage to populate my json. I know this is pretty messy so here's a code extract:
但是，这仅允许加载数据一次，因此我对文件输入使用了按钮样式的标签，该标签在加载文件后触发了隐藏的“重置”按钮（实际上是克隆而不是重置），因此现在它看起来像一个按钮这允许在我更改其中的数据后多次加载文件。我还使用 jQuery 的 localStorage 来填充我的 json。我知道这很乱，所以这里有一个代码摘录：
<div id="load">
<button id="clear">Clear</button>
<label id="forcvs" for="cvs" > Load data </label>
<input type="file" id="cvs" onchange="handleFiles(this.files);" accept=".csv"><br/>
</div>
The "handleFiles" fonction leads to the cvstojson script:
“handleFiles”函数指向 cvstojson 脚本：
var json = JSON.parse(localStorage.getItem('json'))
function handleFiles(files) {
// Check for the various File API support.
if (window.FileReader) {
// FileReader are supported.
getAsText(files[0]);
} else {
alert('FileReader are not supported in this browser.');
}
}
function getAsText(fileToRead) {
var reader = new FileReader();
// Read file into memory as UTF-8
reader.readAsText(fileToRead);
// Handle errors load
reader.onload = loadHandler;
reader.onerror = errorHandler;
}
function loadHandler(event) {
var csv = event.target.result;
processData(csv);
}
function processData(csv) {
object = $.csv.toObjects(csv, {separator:";"})
drawOutput(object);
}
function errorHandler(evt) {
if(evt.target.error.name == "NotReadableError") {
alert("Canno't read file !");
}
}
function drawOutput(object){
json = object
localStorage.setItem('json', JSON.stringify(json))
drawChart()
$( "#clear" ).trigger( "click" );
}
and then before updating my data and drawing my chart I used:
然后在更新我的数据和绘制图表之前，我使用了：
json = JSON.parse(localStorage.getItem('json'))
Here's an extract of the jquery I used for that:
这是我用于此目的的 jquery 的摘录：
var load = $('#cvs')
$('#clear').click(function () {
load.replaceWith(load = load.clone(true))
})
And the CSS:
和 CSS：
#cvs, #clear {
display: none;
}
#forcvs {
-webkit-appearance: button;
-moz-appearance: button;
appearance: button;
line-height: 16px;
padding: .2em .4em;
margin: .2em;
}
Hope it can help some one and thanks for the replies :)
希望它可以帮助某人并感谢您的回复:)
回答by agershun
You can try AlasqlJavaScript library to load XLSX data into JavaScript array without any complex programming. Alasql uses XLSX.js library to work with Excel files.
您可以尝试使用AlasqlJavaScript 库将 XLSX 数据加载到 JavaScript 数组中，而无需任何复杂的编程。Alasql 使用 XLSX.js 库来处理 Excel 文件。
This is a sample:
这是一个示例：
<script src="alasql.min.js"></script>
<script src='xlsx.core.min.js'></script>
<script>
alasql('select * from xlsx("cities.xlsx",{headers:true, sheetid:"Sheet1", range:"A1:B6"})',
[],function(data) {
console.log(data);
});
</script>
You can run this sampleat alasql.org site.
您可以在 alasql.org 站点上运行此示例。
By default headers option is false, sheetid is "Sheet1", range - all cell on the sheet.
默认情况下，标题选项为 false，sheetid 为“Sheet1”，范围 - 工作表上的所有单元格。
Alasql also supports TXT, TAB, CSV, JSON, and XLS formats. You also can produce XLSX file back from JavaScript array:
Alasql 还支持 TXT、TAB、CSV、JSON 和 XLS 格式。您还可以从 JavaScript 数组生成 XLSX 文件：
var stars = [{star:"Aldebaran"},{star:"Algol"},{star:"Vega"}];
alasql('SELECT * INTO XSLX("stars.xlsx",{headers:true}) FROM ?',[stars]);
回答by Amit Kumar
You can read system excel file without uploading using sheet.js
使用sheet.js无需上传即可读取系统excel文件
$(document).ready( function() {
var data = SheetName = '';
var fileUrl = "data.xlsx";
var oReq;
if(window.XMLHttpRequest) oReq = new XMLHttpRequest();
else if(window.ActiveXObject) oReq = new ActiveXObject('MSXML2.XMLHTTP.3.0');
else throw "XHR unavailable for your browser";
oReq.open("GET", fileUrl, true);
if(typeof Uint8Array !== 'undefined') {
oReq.responseType = "arraybuffer"; // Set response type
oReq.onload = function(e) {
if(typeof console !== 'undefined')
var arraybuffer = oReq.response;
var data = new Uint8Array(arraybuffer);
var wb = XLSX.read(data, {type:"array"}); // Read file data
toJson(wb);
};
}
else {
oReq.setRequestHeader("Accept-Charset", "x-user-defined");
oReq.onreadystatechange = function() {
if(oReq.readyState == 4 && oReq.status == 200) {
var ff = convertResponseBodyToText(oReq.responseBody);
if(typeof console !== 'undefined')
var wb = XLSX.read(ff, {type:"binary"});
toJson(wb);
}
};
}
oReq.send(); // sends request
function toJson(excel) {
SheetName = excel.SheetNames[0];
excel.SheetNames.forEach(function(sheetName) {
var json = XLSX.utils.sheet_to_json(excel.Sheets[sheetName]); // convert sheet into json format
if( !json.length ) return false;
data = json;
});
}
});
});
More help you may get here:
您可以在此处获得更多帮助：
http://onegibi.com/programming/javascript-jquery-read-system-excel-file-without-uploading/
http://onegibi.com/programming/javascript-jquery-read-system-excel-file-without-uploading/
