如何将本地Excel文件数据传递给Javascript数组(用于谷歌图表)? [英] How can I pass local Excel file data to Javascript array (for google charts)?
问题描述
我有一个Excel文件(总是相同),有几个WorkSheets, IT同事希望能够更新,以便更新Google图表(或其他图表API,您可以建议我)网页。
(顺便说一下,我只需要每个工作表的某一部分,所以如果你知道一种方法,只提取我需要的那将是伟大的:))
我想避免服务器端操作,但这可能是不可能的,因为我想要阅读本地文件。
我已经看过了转换为JSON的方法,或者使用xlsx加载为base64字符串。 js,但我发现我的同事很容易使用的简单。
非常感谢。
修改
我找到了一个方法来完成我想要的,如果它可以帮助任何人:
首先,我把所有需要的数据放在一个csv文件中。 >
然后,我使用HTML5 FileAPI来读取我用文件输入加载的csv文件。
最后我用了cvstojson脚本解析该文件,然后将其传递给谷歌图表dataTable我想要的方式。
但是,这允许加载数据只有一次,所以我使用按钮 - 文件输入的风格标签在文件加载后触发隐藏的重置按钮(实际上克隆超过重置),现在它看起来像一个单一按钮,允许在更改其中的数据后多次加载文件。
我也使用jQuery的localStorage来填充我的json。
我知道这很漂亮,所以这里是一个代码提取:
< div id =load>
< button id =clear>清除< / button>
< label id =forcvsfor =cvs>加载数据< / label>
< input type =fileid =cvsonchange =handleFiles(this.files);接受= CSV。 ><峰; br />
< / div>
handleFiles功能导致cvstojson脚本:
var json = JSON.parse(localStorage.getItem('json'))
函数handleFiles(files){
//检查各种File API支持。
if(window.FileReader){
// FileReader被支持。
getAsText(files [0]);
} else {
alert(此浏览器不支持FileReader);
}
}
函数getAsText(fileToRead){
var reader = new FileReader();
//将文件读入内存为UTF-8
reader.readAsText(fileToRead);
//处理错误加载
reader.onload = loadHandler;
reader.onerror = errorHandler;
}
函数loadHandler(event){
var csv = event.target.result;
processData(csv);
}
函数processData(csv){
object = $ .csv.toObjects(csv,{separator:;})
drawOutput(object);
}
函数errorHandler(evt){
if(evt.target.error.name ==NotReadableError){
alert(Canno't read文件!);
}
}
函数drawOutput(object){
json = object
localStorage.setItem('json',JSON.stringify(json))
drawChart()
$(#clear).trigger(click);
}
然后在更新我的数据并绘制我使用的图表之前:
json = JSON.parse(localStorage.getItem('json'))
以下是我用于jQuery的一个摘录:
var load = $('#cvs')
$('#clear')。click(function(){
load.replaceWith(load = load.clone(true))
} )
而CSS:
#cvs,#clear {
display:none;
}
#forcvs {
-webkit-appearance:button;
-moz-appearance:button;
外观:按钮;
line-height:16px;
padding:.2em .4em;
margin:.2em;
}
希望它可以帮助一些,谢谢回复:)
您可以尝试 Alasql JavaScript库将XLSX数据加载到JavaScript数组中,无需任何复杂的编程。 Alasql使用XLSX.js库来处理Excel文件。
这是一个示例:
< 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 ){
console.log(data);
});
< / script>
您可以运行此样本在alasql.org网站。
默认标题选项为false,sheetid为Sheet1,范围 - 全部单元格在表单上。
Alasql还支持TXT,TAB,CSV,JSON和XLS格式。您还可以从JavaScript数组生成
的XLSX文件:
var stars = [{star:Aldebaran} {星: 陵},{星: 维加}];
alasql('SELECT * INTO XSLX(stars.xlsx,{headers:true})FROM?',[stars]);
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.
(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.
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.
Then, I used HTML5 FileAPI to read the csv file that I loaded with a file input.
Finally I used a cvstojson script to parse the file then passed it to th google graph dataTable the way I wanted.
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:
<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:
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:
var load = $('#cvs')
$('#clear').click(function () {
load.replaceWith(load = load.clone(true))
})
And the 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 :)
You can try Alasql JavaScript library to load XLSX data into JavaScript array without any complex programming. Alasql uses XLSX.js library to work with Excel files.
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 sample at alasql.org site.
By default headers option is false, sheetid is "Sheet1", range - all cell on the sheet.
Alasql also supports TXT, TAB, CSV, JSON, and XLS formats. You also can produce XLSX file back from JavaScript array:
var stars = [{star:"Aldebaran"},{star:"Algol"},{star:"Vega"}];
alasql('SELECT * INTO XSLX("stars.xlsx",{headers:true}) FROM ?',[stars]);
这篇关于如何将本地Excel文件数据传递给Javascript数组(用于谷歌图表)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!