使用 Javascript（XLSX 或 XLS）读取 Excel 文件
使用 Javascript 读取 XLSX
首先添加简单的 HTML 文件输入和上传文件的按钮
<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" onclick="UploadProcess()" />
<br/>
<div id="ExcelTable"></div>
我还包含了空的 HTML div，以便从我们的 Excel 文件在其中创建表格。
现在，我们将创建函数来上传文件并处理 Excel 文件以从中获取数据并将其转换为 HTML 表格。
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
<script type="text/javascript">
function UploadProcess() {
//Reference the FileUpload element.
var fileUpload = document.getElementById("fileUpload");
//Validate whether File is valid Excel file.
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
if (regex.test(fileUpload.value.toLowerCase())) {
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
//For Browsers other than IE.
if (reader.readAsBinaryString) {
reader.onload = function (e) {
GetTableFromExcel(e.target.result);
};
reader.readAsBinaryString(fileUpload.files[0]);
} else {
//For IE Browser.
reader.onload = function (e) {
var data = "";
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
GetTableFromExcel(data);
};
reader.readAsArrayBuffer(fileUpload.files[0]);
}
} else {
alert("This browser does not support HTML5.");
}
} else {
alert("Please upload a valid Excel file.");
}
};
function GetTableFromExcel(data) {
//Read the Excel File data in binary
var workbook = XLSX.read(data, {
type: 'binary'
});
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);
//Create a HTML Table element.
var myTable = document.createElement("table");
myTable.border = "1";
//Add the header row.
var row = myTable.insertRow(-1);
//Add the header cells.
var headerCell = document.createElement("TH");
headerCell.innerHTML = "Id";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Name";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Country";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Age";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Date";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Gender";
row.appendChild(headerCell);
//Add the data rows from Excel file.
for (var i = 0; i < excelRows.length; i++) {
//Add the data row.
var row = myTable.insertRow(-1);
//Add the data cells.
var cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Id;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Name;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Country;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Age;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Date;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Gender;
}
var ExcelTable = document.getElementById("ExcelTable");
ExcelTable.innerHTML = "";
ExcelTable.appendChild(myTable);
};
</script>
在上面的Javascript代码中，我们首先添加XLSX插件文件的引用，然后添加两个函数
UploadProcess：点击按钮上传文件并将其转换为二进制数据，它还会检查浏览器是否为IE，然后进行相应处理以将文件转换为二进制数据。
ProcessExcel：此函数获取二进制数据，读取工作表名称，创建表元素并将每一行附加到其中。
我已经使用 Comment 解释了代码行。
假设我们的示例 Excel 文件如下所示
因此，如果在 HTML/Javascript 中使用上述代码，输出将如下所示
使用 Javascript 读取 XLS 文件
以类似的方式，我们也可以读取.xls（excel）文件并将其显示在HTML表格中，我将重复相同的代码，只是几行代码不同，而且我们将在其中使用不同的插件，用于. .xls
<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" onclick="UploadProcess()" />
<br/>
<div id="ExcelTable"></div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
<script type="text/javascript">
function UploadProcess() {
//Reference the FileUpload element.
var fileUpload = document.getElementById("fileUpload");
//Validate whether File is valid Excel file.
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
if (regex.test(fileUpload.value.toLowerCase())) {
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
//For Browsers other than IE.
if (reader.readAsBinaryString) {
reader.onload = function (e) {
GetTableFromExcel(e.target.result);
};
reader.readAsBinaryString(fileUpload.files[0]);
} else {
//For IE Browser.
reader.onload = function (e) {
var data = "";
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
GetTableFromExcel(data);
};
reader.readAsArrayBuffer(fileUpload.files[0]);
}
} else {
alert("This browser does not support HTML5.");
}
} else {
alert("Please upload a valid Excel file.");
}
};
function GetTableFromExcel(data) {
//Read the Excel File data in binary
var cfb = XLS.CFB.read(data, {type: 'binary'});
var workbook = XLS.parse_xlscfb(cfb);
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);
//Create a HTML Table element.
var myTable = document.createElement("table");
myTable.border = "1";
//Add the header row.
var row = myTable.insertRow(-1);
//Add the header cells.
var headerCell = document.createElement("TH");
headerCell.innerHTML = "Id";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Name";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Country";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Age";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Date";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Gender";
row.appendChild(headerCell);
//Add the data rows from Excel file.
for (var i = 0; i < excelRows.length; i++) {
//Add the data row.
var row = myTable.insertRow(-1);
//Add the data cells.
var cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Id;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Name;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Country;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Age;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Date;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Gender;
}
var ExcelTable = document.getElementById("ExcelTable");
ExcelTable.innerHTML = "";
ExcelTable.appendChild(myTable);
};
</script>XLS 文件上传代码与 .XLSX 相同，以下是更改
我们包含了差异 JS 插件文件：
<script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js"></script>
更改了函数“GetTableFromExcel”的几行代码
//Read the Excel File data in binary
var cfb = XLS.CFB.read(data, {type: 'binary'});
var workbook = XLS.parse_xlscfb(cfb);
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);?
其余代码保持不变。
使用 Javascript 将 Excel 转换为 JSON
您还可以上传 Excel，然后将其转换为 JSON 数据并继续进行。
<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 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>
document.getElementById('upload').addEventListener('change', handleFileSelect, false);
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>
在上面的代码中，我们使用 XLSX 插件读取 Excel，然后循环遍历每个工作表并获取 XL 行对象并进一步将其转换为 JSON。
THE END
0
二维码
海报
使用 Javascript（XLSX 或 XLS）读取 Excel 文件
们使用 XLSX 插件读取 Excel，然后循环遍历每个工作表并获取 XL 行对象并进一步将其转换为 JSON。
