Javascript 如何在客户端读取excel文件内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6382572/
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 read an excel file contents on client side?
提问by Jyoti
From the JSP page, I need to browse excel file and after selecting file on system, I need to read that excel file contents and fill my form.
从 JSP 页面，我需要浏览 excel 文件，在系统上选择文件后，我需要阅读该 excel 文件内容并填写我的表格。
Currently I have tried with below code but its only working in IE with some changes in IE internet options for ActiveXObject. Its not working in rest of the browsers.
目前我已经尝试使用下面的代码，但它只能在 IE 中工作，并且对 ActiveXObject 的 IE 互联网选项进行了一些更改。它不适用于其他浏览器。
<script>
function mytest2() {
var Excel;
Excel = new ActiveXObject("Excel.Application");
Excel.Visible = false;
form1.my_textarea2.value = Excel.Workbooks.Open("C:/Documents and Settings/isadmin/Desktop/test.xlsx").ActiveSheet.Cells(1,1).Value;
Excel.Quit();
}
</script>
Please suggest some solution so that it works in all browsers.
请提出一些解决方案，使其适用于所有浏览器。
采纳答案by Nivas
It is generally not possible to read/write any file via JavaScript in a browser. So without any additional plug-ins you will not be able to read/write Excel files from the browser. The ActiveX objects of Excel let you do this, but only IE supports ActiveX objects.
通常不可能在浏览器中通过 JavaScript 读/写任何文件。因此，如果没有任何附加插件，您将无法从浏览器读取/写入 Excel 文件。Excel 的 ActiveX 对象允许您执行此操作，但只有 IE 支持 ActiveX 对象。
There may be other plugins for other browsers, but i am aware of none.
其他浏览器可能还有其他插件，但我不知道。
In the first place, why do you want to do that? Can you give a use case? Perhaps there are better options available than what you are trying.
首先，你为什么要这样做？你能给出一个用例吗？也许有比您正在尝试的更好的选择。
UPDATE
更新
You will have to pass the excel file to the server and do the reading of the excel in the server side (in a servlet for instance).
您必须将 excel 文件传递到服务器并在服务器端（例如在 servlet 中）读取 excel。
You will have to use a <input type='file'>in the JSP within a multipart form<form name="myForm" action="myServlet" enctype="multipart/form-data" method="post">
您必须<input type='file'>在多部分表单中的 JSP 中使用 a<form name="myForm" action="myServlet" enctype="multipart/form-data" method="post">
On the server side, you may want to use Apache Commons File Upload.
在服务器端，您可能需要使用Apache Commons File Upload。
Once you have the file (or a stream on it) you can parse the file using, say, Apache POI HSSF/XSSFand then update the data to a database or pass it back to a JSP
一旦你有了文件（或上面的流），你就可以使用Apache POI HSSF/XSSF解析文件，然后将数据更新到数据库或将其传递回 JSP
回答by Trevor Dixon
An xlsx spreadsheet is a zip file with a bunch of xml files in it. Using something like zip.js, you can extract the xml files and parse them in the browser. xlsx.jsdoes this. Here's my simple example. Copied here for convenience:
xlsx 电子表格是一个 zip 文件，其中包含一堆 xml 文件。使用zip.js 之类的东西，您可以提取 xml 文件并在浏览器中解析它们。xlsx.js 就是这样做的。这是我的简单示例。为了方便，复制到这里：
/*
Relies on jQuery, underscore.js, Async.js (https://github.com/caolan/async), and zip.js (http://gildas-lormeau.github.com/zip.js).
Tested only in Chrome on OS X.
Call xlsxParser.parse(file) where file is an instance of File. For example (untested):
document.ondrop = function(e) {
var file = e.dataTransfer.files[0];
excelParser.parse(file).then(function(data) {
console.log(data);
}, function(err) {
console.log('error', err);
});
}
*/
xlsxParser = (function() {
function extractFiles(file) {
var deferred = $.Deferred();
zip.createReader(new zip.BlobReader(file), function(reader) {
reader.getEntries(function(entries) {
async.reduce(entries, {}, function(memo, entry, done) {
var files = ['xl/worksheets/sheet1.xml', 'xl/sharedStrings.xml'];
if (files.indexOf(entry.filename) == -1) return done(null, memo);
entry.getData(new zip.TextWriter(), function(data) {
memo[entry.filename.split('/').pop()] = data;
done(null, memo);
});
}, function(err, files) {
if (err) deferred.reject(err);
else deferred.resolve(files);
});
});
}, function(error) { deferred.reject(error); });
return deferred.promise();
}
function extractData(files) {
var sheet = $(files['sheet1.xml']),
strings = $(files['sharedStrings.xml']),
data = [];
var colToInt = function(col) {
var letters = ["", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
var col = $.trim(col).split('');
var n = 0;
for (var i = 0; i < col.length; i++) {
n *= 26;
n += letters.indexOf(col[i]);
}
return n;
};
var Cell = function(cell) {
cell = cell.split(/([0-9]+)/);
this.row = parseInt(cell[1]);
this.column = colToInt(cell[0]);
};
var d = sheet.find('dimension').attr('ref').split(':');
d = _.map(d, function(v) { return new Cell(v); });
var cols = d[1].column - d[0].column + 1,
rows = d[1].row - d[0].row + 1;
_(rows).times(function() {
var _row = [];
_(cols).times(function() { _row.push(''); });
data.push(_row);
});
sheet.find('sheetData row c').each(function(i, c) {
var $cell = $(c),
cell = new Cell($cell.attr('r')),
type = $cell.attr('t'),
value = $cell.find('v').text();
if (type == 's') value = strings.find('si t').eq(parseInt(value)).text();
data[cell.row - d[0].row][cell.column - d[0].column] = value;
});
return data;
}
return {
parse: function(file) {
return extractFiles(file).pipe(function(files) {
return extractData(files);
});
}
}
})();
回答by Baldy
You can load and open the file client side in most modern browsersusing the HTML5 File API
您可以使用HTML5 File API在大多数现代浏览器中加载和打开文件客户端
Once you have loaded the file you can parse the contents with a library that supports certain excel output formats (such as csv / xlsx).
加载文件后，您可以使用支持某些 excel 输出格式（例如 csv / xlsx）的库来解析内容。
Here are a couple of options...
这里有几个选项......
回答by catamphetamine
This one works in all major browsers.
这个适用于所有主要浏览器。
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 jfkelley
I do this all the time - my prototypes are designed to let stakeholders modify an excel and have it populate the html prototype, often without a server.
我一直这样做 - 我的原型旨在让利益相关者修改 excel 并让它填充 html 原型，通常不需要服务器。
- Save the excel as XML
- Load the XML via AJAX and parse out the structure
- 将excel另存为XML
- 通过 AJAX 加载 XML 并解析出结构
(1) here's a handy button macro I put in my excel files, allowing painless saving as XML (and re-saving as xls):
( 1) 这是我放在我的 excel 文件中的一个方便的按钮宏，允许轻松保存为 XML（并重新保存为 xls）：
Sub SaveAndExportXML()
Dim fname As String
Dim fnamexml As String
fname = ThisWorkbook.FullName
fnamexml = Replace(fname, ".xls", ".xml", 1, 1, vbTextCompare)
If MsgBox("Will save the following (runtime XML and source XLS) files: " & vbNewLine & "XML: " & fnamexml & vbNewLine & "Src: " & fname & vbNewLine & vbNewLine & "Ok? If so, please click Yes here and on the next 3 prompts...", vbYesNo) = vbNo Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fnamexml, FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False
're-save as xls:
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
(2) The parsing JS is more complicated but the premise is simple. There will be XML objects like Worksheet, Table, Row, Cell, and Data. There's also an XML attribute called ss:Index I find handy sometimes.
( 2)解析JS比较复杂但是前提很简单。将有 XML 对象，如 Worksheet、Table、Row、Cell 和 Data。还有一个名为 ss:Index 的 XML 属性，有时我觉得很方便。
