由于使用了插件ActiveXObject,而这个插件只是IE提供的,因此以下所有代码都只能在IE中执行
方法一:
可以导入xls、xlsx文件。
固定导入文件的列数。
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title> New Document </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
<script type="text/javascript">
function ReadExcel() {
var tempStr = "";
//得到文件路径的值
var filePath = document.getElementById("upfile").value;
//创建操作EXCEL应用程序的实例
var oXL = new ActiveXObject("Excel.application");
//打开指定路径的excel文件
var oWB = oXL.Workbooks.open(filePath);
//操作第一个sheet(从一开始,而非零)
oWB.worksheets(1).select();
var oSheet = oWB.ActiveSheet;
//使用的行数
var rows = oSheet .usedrange.rows.count;
//读取每格的值,注意是从1开始
try {
for (var i = 1; i <= rows; i++) {
if (oSheet.Cells(i, 1).value == "null" || oSheet.Cells(i, 2).value == "null")
break;
var a = oSheet.Cells(i, 1).value.toString() == "undefined" ? "": oSheet.Cells(i, 1).value;
tempStr += (" " + oSheet.Cells(i, 1).value + " " + oSheet.Cells(i, 2).value + " " + oSheet.Cells(i, 3).value + "\n");
}
}
catch(e) {
document.getElementById("txtArea").value = tempStr;
}
document.getElementById("txtArea").value = tempStr;
//退出操作excel的实例对象
oXL.Application.Quit();
//手动调用垃圾收集器
CollectGarbage();
}
</script>
</head>
<body>
<input type="file" id="upfile" /><input type="button" onclick="ReadExcel();" value="read">
<br>
<textarea id="txtArea" cols=50 rows=10></textarea>
</body>
</html>
方法二:
可以导入xls、xlsx文件。
直接读取文件所有内容,还可以读取多个sheet。
<html>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<head>
<title>Untitled Page</title>
</head>
<script language="javascript" type="text/javascript">
function toWeb(fileName) {
rstArray = importXLS(fileName);
rstArrayUnique = rstUnique(rstArray);//删除重复行
}
function importXLS(fileName) {
var excelCon;
excelCon = new ActiveXObject("ADODB.Connection");
excelCon.Provider = "Microsoft.Jet.OLEDB.4.0; Persist Security Info = False;";
excelCon.ConnectionString = "Data Source=" + fileName + ";Extended Properties='Excel 8.0; HDR=NO;IMEX=2'" ;
excelCon.CursorLocation = 1;//adUseNoe
excelCon.Open;
var strQueryExcel;
//var strSheetName = "Sheet2$";
var rsTemp = new ActiveXObject("ADODB.Recordset");//hold a set of records from a database table
rsTemp = excelCon.OpenSchema(20);//return Recordset
var rst = new Array();
var strTable;
while (!rsTemp.EOF)//end of file
{
if (rsTemp("TABLE_TYPE") == "TABLE") {
rsExcel = new ActiveXObject("ADODB.Recordset");
strQueryExcel = "SELECT * FROM [" + rsTemp("TABLE_NAME") + "]";
rsExcel.ActiveConnection = excelCon;
rsExcel.Open(strQueryExcel);
strTable=rsTemp("TABLE_NAME")+"表内容<br><table id='excelTable' border='1' cellspacing='0' cellpadding='0'>";//style='VISIBILITY:hidden'
for (i = 0; i < rsExcel.Fields.Count;i++) {
strTable = strTable+"<td>" + rsExcel.Fields(i).name + "</td>";
}
do {
strTable = strTable + "<tr>";
var rst_i = new Array();
for(i = 3; i < rsExcel.Fields.Count; i++) {
strTable=strTable + "<td>" + rsExcel.Fields(i).value + "</td>";
rst_i.push(rsExcel.Fields(i).value);
}
strTable=strTable + "</tr>";
rst.push(rst_i);
rsExcel.MoveNext;
}while(!rsExcel.EOF);
}
document.write(strTable + "</table><br>")
rsExcel = null;
rsTemp.MoveNext;
}
excelCon.Close;
excelCon = null;
rsExcel = null;
return rst;
}
function rstUnique(arr) {
var result = [], hash = {};
for (var i = 0, elem; (elem = arr[i]) != null; i++) {
if (!hash[elem]) {
result.push(elem);
hash[elem] = true;
}
}
return result;
}
</script>
<body>
<input type="file" id="file_open" />
<input type="button" id="button_file_open" value="import_file" onClick="if(file_open.value=='')alert('请选择xls文件');else toWeb(file_open.value)" />
</body>
</html>
这个方法在表头会出现F1/F2/F3等,如果不想要,可以看这篇文章 javascript将EXCEL文件导入前端第一行未出现或者出现F1F2F3的解决方法……