js 读取excel 导入mysql_可以读取EXCEL文件的js代码

首页给个有中文说明的例子，下面的例子很多大家可以多测试。

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;

}

}

// -->

如果是在网页上打开EXCEL 文件，那么在关闭的时候，进程里还有EXCEL.EXE，所以必须关闭后，刷新本页面！

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();

}

二、

js读取excel文件

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;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){

//alert(e);

document.all.txtArea.value = tempStr;

}

document.all.txtArea.value = tempStr; oXL.Quit();

CollectGarbage();

}

三、

我在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 <=oWB.worksheets.count;x++)

{

oWB.worksheets(x).select();

var oSheet =oWB.ActiveSheet;

\\按指定开始行和开始列读取excel文件的数据

for(var i=parseInt(r_c_num[6]);i <=parseInt(r_c_num[7]);i++)

{

for(var j=parseInt(r_c_num[8]);j <=parseInt(r_c_num[9]);j++)

{

if(typeof(oSheet.Cells(i,j).value)== "undefined ")

{

ExcelNum[j-parseInt(r_c_num[8])+6]= " ";

}

else

{

switch_letter(j);

ExcelNum[j-parseInt(r_c_num[8])+6]=oSheet.Cells(i,j).value；

}

}

//将读取的一行数据传到后台插入到数据库

WebServiceExcel.insert_From_Excel(ExcelNum);

}

}

}

}

catch(err)

{

alert( "出错了, "+err.message);

}

}

这只是前台的关键代码。

现在的问题是，如果excel文件数据太多的话，导入过程要等好长时间，性能太差了，不知道该怎么改进？？？如果导几千行数据就不行了，时间让我无法忍受。请高手赐教，很着急用，谢谢了！！！

一个用JavaScript结合Excel.Application读取本地excel文件并以表格呈现的简单例子

New Document

用JS讀取excel的例子

''

'*********************************************************

' 目的：讀取excel資料後插入到數據庫中同時紀錄成功和失敗的數目

' 傳入：

' 返回：

'*********************************************************

Function GetExcel()

Dim conn

Dim StrConn

Dim rs

Dim Sql

file=""

Set conn=Server.CreateObject("ADODB.Connection")

StrConn="Driver={Microsoft Excel Driver (*.xls)};DBQ="& Server.MapPath("EXCEL_DATA.xls")

''StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dd.xls;Extended Properties=Excel 8.0"

conn.Open StrConn

Set rs = Server.CreateObject("ADODB.Recordset")

Sql="select * from [Sheet1$]"

rs.Open Sql,conn,2,2

''讀取excel中的字段名稱,並檢察字段順序是否正確

for i=0 to rs.Fields.Count-1

FILE_HEAD=FILE_HEAD&rs(i).Name

next

''response.write FILE_HEAD

IF trim(FILE_HEAD)<>"版本使用單位類綱目節類說明綱說明目說明檔名保存年限共同分類號" THEN

RESPONSE.WRITE ""

exit Function

END IF

''讀取excel中的資料

do while Not rs.EOF

''將讀取的資料INSERT到oracle數據庫

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="總案"

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& "
"

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 file<>"" then

CALL CreateFolder()

call SetFile(file)

strpath=server.mappath("EXCEL_DATA.xls")

call DeleteFolder(strpath)

file=""

end if

End Function

'*********************************************************

' 目的： 新建一個指定的文件，如果存在就不新建，並向文件追加紀錄

' 傳入： file:要追加的數據

' 返回：

'*********************************************************

Function SetFile(file)

file_path="C:\LOG\OD60err.log"

set fstemp=server.CreateObject("Scripting.FileSystemObject")

IF (fstemp.FileExists(file_path)) THEN

ELSE

set filetemp=fstemp.CreateTextFile(file_path,true)

filetemp.writeLine "紀錄匯入失敗資料"

filetemp.close

END IF

''追加失敗資料信息OpenTextFile

set filetemp=fstemp.OpenTextFile(file_path,8,true)

filetemp.writeLine file

filetemp.close

set filetemp=Nothing

set fstemp=Nothing

End Function

'*********************************************************

' 目的： 新建一個指定的文件夾，如果存在就不新建

' 傳入：

' 返回：

'*********************************************************

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

'*********************************************************

' 目的：刪除上傳的文件，

' 傳入：傳入上傳文件的虛擬路徑

' 返回：

'*********************************************************

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

%>

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;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){

//alert(e);

document.all.txtArea.value = tempStr;

}

document.all.txtArea.value = tempStr;

oXL.Quit();

CollectGarbage();

}