Home >Web Front-end >JS Tutorial >js code that can read EXCEL files_javascript skills
js code that can read EXCEL files_javascript skills
- PHP中文网Original
- 2016-05-16 18:47:391406browse
js读取 EXCEL 文件 的实现代码，比较全了大家可以自行测试了。
首页给个有中文说明的例子，下面的例子很多大家可以多测试。
<script language="javascript" type="text/javascript"><!--
function readExcel() {
var excelApp;
var excelWorkBook;
var excelSheet;
try{
excelApp = new ActiveXObject("Excel.Application");
excelWorkBook = excelApp.Workbooks.open("C:\\XXX.xls");
excelSheet = oWB.ActiveSheet; //WorkSheets("sheet1")
excelSheet.Cells(6,2).value;//cell的值
excelSheet.usedrange.rows.count;//使用的行数
excelWorkBook.Worksheets.count;//得到sheet的个数
excelSheet=null;
excelWorkBook.close();
excelApp.Application.Quit();
excelApp=null;
}catch(e){
if(excelSheet !=null || excelSheet!=undefined){
excelSheet =nul;
}
if(excelWorkBook != null || excelWorkBook!=undefined){
excelWorkBook.close();
}
if(excelApp != null || excelApp!=undefined){
excelApp.Application.Quit();
excelApp=null;
}
}
// --></script>如果是在网页上打开EXCEL 文件，那么在关闭的时候，进程里还有EXCEL.EXE，所以必须关闭后，刷新本页面！
<script>
function ReadExcel()
{
var tempStr = "";
var filePath= document.all.upfile.value;
var oXL = new ActiveXObject("Excel.application");
var oWB = oXL.Workbooks.open(filePath);
oWB.worksheets(1).select();
var oSheet = oWB.ActiveSheet;
try{
for(var i=2;i<46;i++)
{
if(oSheet.Cells(i,2).value =="null" || oSheet.Cells(i,3).value =="null" )
break;
var a = oSheet.Cells(i,2).value.toString()=="undefined"?"":oSheet.Cells(i,2).value;
tempStr+=(" "+oSheet.Cells(i,2).value+
" "+oSheet.Cells(i,3).value+
" "+oSheet.Cells(i,4).value+
" "+oSheet.Cells(i,5).value+
" "+oSheet.Cells(i,6).value+"\n");
}
}catch(e)
{
document.all.txtArea.value = tempStr;
}
document.all.txtArea.value = tempStr;
oXL.Quit();
CollectGarbage();
}
</script>
<html>
<input type="file" id="upfile" /><input type="button" onclick="ReadExcel();" value="read">
<br>
<textarea id="txtArea" cols=50 rows=10></textarea>
</html>二、
js读取excel文件
3f1c4e4b6b16bbbd69b2ee476dc4f83a
function readThis(){
var tempStr = "";
var filePath= document.all.upfile.value;
var oXL = new ActiveXObject("Excel.application");
var oWB = oXL.Workbooks.open(filePath);
oWB.worksheets(1).select();
var oSheet = oWB.ActiveSheet;
try{
for(var i=2;i8a850a13a7d4f6304fd432c6bbfe3440
100db36a723c770d327fc0aef2ce13b1
385593bf7209582d73c2d3d650334bb8
a7e1e84ae9ae90c7575fa0f1aa364ee80c6dc11e160d3b678d68754cc175188a
17753354e4c2f2d0ae75f3fe89c62dc240587128eee8df8f03d0b607fe983014
73a6ac4ed44ffec12cee46588e518a5e
三、
我在vs2005平台上要实现这么一个功能，点击一个按钮一次将大量的excel文件数据导入到sqlserver2005中
我用的是ajax技术，在前台用javascript操做excel文件，循环读取所有的excel文件，每读取一行就放进一个数组里通过web服务传到后台用c#语言将一行数据插入到数据库。思路大概就是这样。
现在功能已经实现了，具体代码如下
用javascript定义一个函数，循环读取excel文件数据
function readExcel()
{
try
{
var ExcelNum=new Array();
//重复导入之前，删除上次导入的同期数据
WebServiceExcel.deleteOldNumber();
var oXL = new ActiveXObject( "Excel.Application ");
\\r_c_num[5]的值为excel文件的名字
var path=document.all.excelpath.value+ "\\ "+r_c_num[5]
var oWB = oXL.Workbooks.open(path);
\\如果excel文件有多个sheet的话从第一个sheet循环读取
for(var x=1;x 21912cbd467a7c4460603aead679be18
6a74014ee44f5deb5894267f99b68016
ef1cac8df639bc9110cb13b8b5ab3dd7
08eb756f81b28a01d33212cce3fbe742 New Document < ;/TITLE>
662ac72aed5b9336e652bfbc791d1873
1af68bab93ad8c9a16d78302082dec0c");
for (i = 1; i 9cb79e33020c8c3e0ed27f0219987550");
for (j = 1; j a28b7a2209f5086bff07189fdd857135a4b561c25d9afb9ac8dc4d70affff419" : "b6c5a531a458a2e790c1fd6421739d1c");
document.write(value );
document.write(i == 1 ? "0d36329ec37a2cc24d42c7229b69747a01c3ce868d2b3d9bce8da5c1b7e41e5b" : "b90dd5946f0946207856a8a37f441edf");
}
document.write("5194ba175fd9c71e9a9f6e68244d73a8");
}
document.write("f16b1740fad44fb09bfe928bcc527e08");
oSheet = null;
}
//-->
< ;/SCRIPT>
34934661d7147ca926b095899343bad0
dba39307b985180c8e6db1f7ba0ea126
f2475bc0d02a524246eacdb398ec424e
6eac519e8537e4205ddd37e30a7b548a
Example of reading excel using JS
ebd717f27c42a8ccbe27e9412e8a96f9"Version unit category outline program description outline description file name storage period common classification number" THEN
RESPONSE.WRITE "ba506bbeca4521b7c9bda6832be34aa7alert('EXCEL file field order is wrong or The number of fields is wrong!!')db271416853c42fd247a57c1a2c29eb6"
exit Function
END IF
''Read data in excel
do while Not rs.EOF
'' will read INSERT the retrieved data into the oracle database
for i=0 to rs.Fields.Count-1
EDITION=rs(0)
FILE_CODE=rs(2) rs(3) rs(4) rs( 5)
FILE_NAME=rs(9)
KIND1_DESC=rs(6)
KIND2_DESC=rs(7)
KIND3_DESC=rs(8)
KIND4_DESC=rs(9)
SAVE_YEAR=rs(10)
FILE_UNIT=rs(1)
COM_FILE_CODE=rs(11)
''====================== =========================
CHECED_SQL="Select nvl(FILE_CASE,'') FILE_CASE FROM ODM67 where EDITION='"&TRIM(EDITION )&"' and FILE_CODE='"&TRIM(FILE_CODE)&"' "
If mobjDB.OpenSQL(CHECED_SQL) Then
If mobjDB.IsEmpty Then
FILE_CASE="0001"
CASE_DESC=" General case"
INS_SQL=""
INS_SQL=INS_SQL & " INSERT INTO ODM67(" & VBCRLF
INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_CASE," & VBCRLF
INS_SQL=INS_SQL & " CASE_DESC,CRT_USER,CRT_DATE," & VBCRLF
INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF
INS_SQL=INS_SQL & " VALUES(" & VBCRLF
INS_SQL=INS_SQL & " ' "&TRIM(EDITION)&"','"&TRIM(FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_CASE)&"','"&TRIM(CASE_DESC)&"', " & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM (NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"','"&TRIM(NOWTIME)& "')"
CALL mobjDB.ExecSQL(INS_SQL)
End If
End If
''====================== =========================
INS_SQL=""
INS_SQL=INS_SQL & " INSERT INTO ODM61( " & VBCRLF
INS_SQL=INS_SQL & " EDITION,FILE_CODE,FILE_NAME,KIND1_DESC," & VBCRLF
INS_SQL=INS_SQL & " KIND2_DESC,KIND3_DESC,KIND4_DESC,SAVE_YEAR," & VBCRLF
INS_SQL=INS_SQL & " FILE_UNIT,COM_FILE_CODE,CRT_USER, CRT_DATE ," & VBCRLF
INS_SQL=INS_SQL & " CRT_TIME,MDF_USER,MDF_DATE,MDF_TIME)" & VBCRLF
INS_SQL=INS_SQL & " VALUES(" & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(EDITION) &"','"&TRIM(FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_NAME)&"','"&TRIM(KIND1_DESC)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(KIND2_DESC)&"','"&TRIM(KIND3_DESC)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(KIND4_DESC)&"','"&TRIM (SAVE_YEAR)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(FILE_UNIT)&"','"&TRIM(COM_FILE_CODE)&"'," & VBCRLF
INS_SQL=INS_SQL & " ' "&TRIM(SESSION("USER_ID"))&"','"&TRIM(TODAY)&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(NOWTIME)&"','"&TRIM(SESSION("USER_ID"))&"'," & VBCRLF
INS_SQL=INS_SQL & " '"&TRIM(TODAY)&"' ,'"&TRIM(NOWTIME)&"')"
''RESPONSE.WRITE INS_SQL& "82c2f54f4cf245029ffb326befd49b3f"
IF mobjDB.ExecSQL(INS_SQL) THEN
InCount=InCount 1
ELSE
NoCount=NoCount 1
file=file&TODAY&" "&NOWTIME&" "&EDITION&" "&FILE_CODE & VBCRLF
END IF
exit for
next
rs.MoveNext
Loop
rs.close
set rs=nothing
Conn.close
set StrConn=nothing
if filea8093152e673feb7aba1828c43532094"" then
CALL CreateFolder()
call SetFile(file)
strpath=server.mappath("EXCEL_DATA.xls")
call DeleteFolder(strpath)
file=""
end if
End Function
'******* ***************************************************
' Purpose: Create a new specified file. If it exists, do not create a new file and append records to the file
' Pass in: file: data to be appended
' Return:
'**** *************************************************** ***
Function SetFile(file)
file_path="C:LOGOD60err.log"
set fstemp=server.CreateObject("Scripting.FileSystemObject")
IF (fstemp.FileExists(file_path) ) THEN
ELSE
set filetemp=fstemp.CreateTextFile(file_path,true)
filetemp.writeLine "Record failed import data"
filetemp.close
END IF
''Append Failure information OpenTextFile
set filetemp=fstemp.OpenTextFile(file_path,8,true)
filetemp.writeLine file
filetemp.close
set filetemp=Nothing
set fstemp=Nothing
End Function
'************************************************ *************
' Purpose: Create a new specified folder, if it exists, do not create it
' Pass in:
' Return:
'** *************************************************** *****
Function CreateFolder()
Dim fso, f
folder="c:LOG"
Set fso = CreateObject("Scripting.FileSystemObject")
IF fso.FolderExists (folder) THEN
ELSE
Set f = fso.CreateFolder(folder)
CreateFolderDemo = f.Path
END IF
End Function
'******** *************************************************
' Purpose: Delete the uploaded file,
' Pass in: Pass in the virtual path of the uploaded file
' Return:
'****************** *****************************************
Function DeleteFolder(filepath)
Dim fso, f
folder="EXCEL_DATA.xls"
Set fso = CreateObject("Scripting.FileSystemObject")
''response.write fso.FileExists(filepath)
IF fso. FileExists(filepath) THEN
fso.DeleteFile filepath
END IF
End Function
%>
Copy Code The code is as follows:
3f1c4e4b6b16bbbd69b2ee476dc4f83a
function readThis(){
var tempStr = "";
var filePath= document.all.upfile.value ;
var oXL = new ActiveXObject("Excel.application");
var oWB = oXL.Workbooks.open(filePath);
oWB.worksheets(1).select();
var oSheet = oWB.ActiveSheet;
try{
for(var i=2;i8d414e835f60d0d2bb3e674e6533e118
100db36a723c770d327fc0aef2ce13b1
207aba4856d62bc13b9723fe93a91967
0c6dc11e160d3b678d68754cc175188a
2dfdda4630711f08a22f09c4ddfd6677 40587128eee8df8f03d0b607fe983014
73a6ac4ed44ffec12cee46588e518a5e
Related articles
See more- An in-depth analysis of the Bootstrap list group component
- Detailed explanation of JavaScript function currying
- Complete example of JS password generation and strength detection (with demo source code download)
- Angularjs integrates WeChat UI (weui)
- How to quickly switch between Traditional Chinese and Simplified Chinese with JavaScript and the trick for websites to support switching between Simplified and Traditional Chinese_javascript skills