求一个能把EXCEL批量导入SQL数据库的ASP程序代码!!!

Question

求一个能把EXCEL批量导入SQL数据库的ASP程序代码!!! 能直接将EXCEL表的对应字段数据添加到MSSQL的表的相应字段!!! 要求完整代码!!!!

Answer 1

private void Button1_Click(object sender, System.EventArgs e)
{
//选择文件
ofdSelectExcel.Filter = "Excel Files(*.xls)|*.xls";
ofdSelectExcel.RestoreDirectory = true;
if( ofdSelectExcel.ShowDialog() == DialogResult.OK )
{
if ( ofdSelectExcel.FileName.Trim().Length == 0)
{
MessageBox.Show(this,"Please select a excel file first!");
return;
}
else
{
ImportExcelToSqlServer(ofdSelectExcel.FileName.Trim());
}

}
}

//********************************************************
//提取数据
public void ImportExcelToSqlServer(string fileName)
{
if (fileName == null)
{
throw new ArgumentNullException("filename string is null!");
}

if (fileName.Length == 0)
{
throw new ArgumentException("filename string is empty!");
}

string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";

OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName=new DataTable();;
DataSet ds=new DataSet();
try
{
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

if (m_tableName != null && m_tableName.Rows.Count > 0)
{

m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();

}
string sqlMaster;
sqlMaster=" SELECT * FROM ["+m_tableName+"]";
oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
oleAdMaster.Fill(ds,"m_tableName");

MailRebateManager manger=new MailRebateManager();
bool isSucess=manger.AddExceLGmailRebate(ds.Tables["m_tableName"],ApplicationVariable.HomeCompanyID);
if(isSucess)
{
MessageBox.Show("Manipulate Succs!");
}
else
{
MessageBox.Show("Manipulate Failed");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
SimpleLogger.Log(ex);
try
{

}
catch (OleDbException e)
{
SimpleLogger.Log(e);
MessageBox.Show("An exception of type " + e.GetType() );
}
}

}
//*****************************************
//将数据进行处理分别插入主表和从表
public bool AddExceLGmailRebate(DataTable tb,string homeCompanyID)
{
bool ret=false;
SqlConnection con=null;

DataTable table=new DataTable();
table=tb;

string sConn = PublicManager.GetDBConnectionString(homeCompanyID);
con=new SqlConnection();
con.ConnectionString=sConn;

SqlTransaction tran=null;
SqlCommand com=null;
SqlCommand comm=null;

try
{
con.Open();
tran = con.BeginTransaction();

if (table != null && table.Rows.Count > 0)
{

for(int i=1;i<table.Rows.Count;i++)
{

string m_PromoCode=Convert.ToString(table.Rows[i][0]);
if(m_PromoCode=="")
{
m_PromoCode=Convert.ToString(table.Rows[i-1][0]);
}
if(m_PromoCode.Length>50 )
{
m_PromoCode=m_PromoCode.Substring(0,50);
}

string m_ItemDescription=Convert.ToString(table.DefaultView[i][1]);
if(m_ItemDescription.IndexOf("(")>0)
{
int num=m_ItemDescription.IndexOf("(");
m_ItemDescription=m_ItemDescription.Substring(0,num);
if(m_ItemDescription.Length>50)
{
m_ItemDescription=m_ItemDescription.Substring(0,50);
}

}
if(m_ItemDescription.Length>50)
{
m_ItemDescription=m_ItemDescription.Substring(0,50);
}

string begin=Convert.ToString(table.DefaultView[i][2]);
string m_BeginPromoPeriodDate;
string m_EndPromoPeriodEndDate;
if(begin=="")
{
continue;
}
else
{
string beginTime=begin.Substring(0,8);
beginTime=beginTime.Replace("/","-");
m_BeginPromoPeriodDate=beginTime;
string endTime=begin.Substring(begin.Length-8);
endTime=endTime.Replace("/","-");
m_EndPromoPeriodEndDate=endTime;
}

string m_RebateAmountStr=Convert.ToString(table.DefaultView[i][3]);
if(m_RebateAmountStr.Length >9)
{
m_RebateAmountStr=m_RebateAmountStr.Substring(0,9);
}
decimal m_RebateAmount;
if(m_RebateAmountStr=="")
{
m_RebateAmount=0;
}
else
{
m_RebateAmount= Convert.ToDecimal(m_RebateAmountStr);
}

string m_TotalSoldStr=Convert.ToString(table.DefaultView[i][7]);
if(m_TotalSoldStr.Length >4)
{
m_TotalSoldStr=m_TotalSoldStr.Substring(0,4);
}
int m_TotalSold;
if(m_TotalSoldStr=="")
{
m_TotalSold=0;
}
else
{
m_TotalSold=Convert.ToInt32(m_TotalSoldStr);
}

string m_RebateReserveStr=Convert.ToString(table.DefaultView[i][8]);
if(m_RebateReserveStr.Length>9)
{
m_RebateReserveStr=m_RebateReserveStr.Substring(0,9);
}
decimal m_RebateReserve;
if(m_RebateReserveStr=="")
{
m_RebateReserve=0;
}
else
{
m_RebateReserve=Convert.ToDecimal(m_RebateReserveStr);

}

string m_RedeemedStr=Convert.ToString(table.DefaultView[i][17]);
if(m_RedeemedStr.Length >8)
{
m_RedeemedStr=m_RedeemedStr.Substring(0,8);
}
decimal m_Redeemed;
if(m_RedeemedStr=="")
{
m_Redeemed=0;
}
else
{
m_Redeemed=Convert.ToDecimal(m_RedeemedStr);
}
string m_PromoItem=Convert.ToString(table.DefaultView[i][23]);
if(m_PromoItem.Length >50)
{
m_PromoItem=m_PromoItem.Substring(0,50);
}

DateTime m_InDate;
m_InDate=DateTime.Now;

string m_sqlMaster="INSERT INTO act.dbo.Newegg_GMailRebate(PromoCode,PromoItem,RebateAmount,ItemDescription,BeginPromoPeriodDate,EndPromoPeriodEndDate,PostMaskDate,TotalSold,RebateReserve,Redeemed,InDate)VALUES('"+m_PromoCode+"','"+m_PromoItem+"','"+m_RebateAmount+"', '"+m_ItemDescription+"','"+m_BeginPromoPeriodDate+"','"+m_EndPromoPeriodEndDate+"', '"+m_InDate+"','"+m_TotalSold+"','"+m_RebateReserve+"','"+m_Redeemed+"','"+m_InDate+"')";
comm=new SqlCommand(m_sqlMaster,con);
comm.Transaction =tran;
comm.ExecuteNonQuery ();
comm.Dispose();

}

for(int i=1;i<table.Rows.Count;i++)
{

string m_PromoCode=Convert.ToString(table.Rows[i][0]);
if(m_PromoCode=="")
{
continue;
}
int m_PromoCodeTransactionID=GetMasterTransactionID(m_PromoCode,homeCompanyID);

string m_InvoiceNumber=Convert.ToString(table.Rows[i][4]);
if(m_InvoiceNumber.Length >30)
{
m_InvoiceNumber=m_InvoiceNumber.Substring(0,30);
}
if(m_InvoiceNumber=="")
{
m_InvoiceNumber="0";
}

string m_InvoiceDate=Convert.ToString(table.DefaultView[i][6]);
if(m_InvoiceDate=="")
{
DateTime m_InDate;
m_InDate=DateTime.Now;
m_InvoiceDate=Convert.ToString(m_InDate);
}

string m_serialNumberStr=Convert.ToString(table.Rows[i][5]);
if(m_serialNumberStr.Length >4)
{
m_serialNumberStr=m_serialNumberStr.Substring(0,4);
}
if(m_serialNumberStr=="")
{
m_serialNumberStr="0";
}
int m_serialNumber=Convert.ToInt32(m_serialNumberStr);

string m_TotalValidStr=Convert.ToString( table.DefaultView[i][9]);
if(m_TotalValidStr

.Length >4)
{
m_TotalValidStr=m_TotalValidStr.Substring(0,4);

}
if(m_TotalValidStr=="")
{
m_TotalValidStr="0";
}
int m_TotalValid=Convert.ToInt32 (m_TotalValidStr);

string m_TotalInValidStr=Convert.ToString( table.DefaultView[i][10]);
if(m_TotalInValidStr.Length >4)
{
m_TotalInValidStr=m_TotalInValidStr.Substring(0,4);
}
if(m_TotalInValidStr=="")
{
m_TotalInValidStr="0";
}
int m_TotalInValid=Convert.ToInt32(m_TotalInValidStr);

string m_ProcessFeeStr=Convert.ToString(table.DefaultView[i][13]);
if(m_ProcessFeeStr.Length >9)
{
m_ProcessFeeStr=m_ProcessFeeStr.Substring(0,9);
}
if(m_ProcessFeeStr=="")
{
m_ProcessFeeStr="0";
}
decimal m_ProcessFee=Convert.ToDecimal(m_ProcessFeeStr);

string m_sqlDetails="INSERT INTO act.dbo.Newegg_GMailExcelMaster(PromoCodeTransactionID,InvoiceNumber,InvoiceDate,SerialNumber,TotalValid,TotalInvalid,ProcessFee)VALUES('"+m_PromoCodeTransactionID+"','"+m_InvoiceNumber+"','"+m_InvoiceDate+"', '"+m_serialNumber+"','"+m_TotalValid+"','"+m_TotalInValid+"','"+m_ProcessFee+"')";
com=new SqlCommand(m_sqlDetails,con);
com.CommandTimeout=60;
com.Transaction =tran;
com.ExecuteNonQuery();
com.Dispose();
m++;
}
tran.Commit();

ret=true;

}

}
catch(Exception ex)
{

try
{
tran.Rollback();
}
catch (SqlException e)
{
if (tran.Connection != null)
{
MessageBox.Show("An exception of type " + e.GetType() );
}
}
MessageBox.Show("Error come up row number:"+m.ToString());
MessageBox.Show("Error details:"+ex.Message);
SimpleLogger.Log(ex);
ret=false;
}
finally
{
con.Close();
con.Dispose();

}
return ret;

}

============================================
这是将excel批量导入数据库的类文件,你可以自己在
asp中使用了

Answer 2

protected void Button1_Click(object sender, System.EventArgs e)
{
string XLS_Path="";
string XLS_Name = MakeFileName();
string fileExtName = "";
if(File1.PostedFile.ContentLength >0)
{
try
{
fileExtName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("."));
if(fileExtName!=".xls"||fileExtName.Length<1)
{
Response.Write( "<script language=javascript>alert('数据导入失败.可能是文件格式或路径不正确！！！');</script>");
return;
}
File1.PostedFile.SaveAs(Server.MapPath("../uploadexcel/")+XLS_Name+fileExtName);
XLS_Path =Server.MapPath("../uploadexcel/")+ XLS_Name+fileExtName;
}
catch(Exception ex)
{
Response.Write(ex.ToString());
}
}
else
{
Response.Write( "<script language=javascript>alert('请选择文件后再上传！！！');</script>");
return;
}
string ConStr=System.Configuration.ConfigurationManager.AppSettings["ConnString"];
SqlConnection Conn=new SqlConnection(ConStr);
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '"+XLS_Path+"'"+";Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
try
{
myDa.Fill(myDs);
}
catch
{
Response.Write( "<script language=javascript>alert('数据导入失败,请检查！！');</script>");
return;
}
if(myDs.Tables[0].Rows.Count<=0)
{
Response.Write( "<script language=javascript>alert('文件中可能没有数据，请检查后重新导入！');</script>");
return;
}

int []zjid=new int[myDs.Tables[0].Rows.Count];
string strSql = "";
string CnnString="Provider=SQLOLEDB;"+ConStr;
OleDbConnection conn =new OleDbConnection(CnnString);
OleDbCommand myCmd =null;

Answer 3

嗯。。如果只是偶尔导入个EXCEL文件的话，直接用SQL自带的数据导入就可以了。

Answer 4

'====================================================================='==========
''''''''''''''''''H*F*W 2009 Execl数据导入SQL SERVER''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''Execl数据导入 SQL SERVER VBS脚本过程
'''''''''''广告:-) QQ:176469428 愿能结识拥有共同兴趣的朋友
''''''实用时根据情况自己修改 主要为字段信息
'''导入数据 过程传入 数据库服务器，登陆用户，登陆密码，数据库，数据表，Execl表格文件路径
Sub HFWExlToSQL(Hserver,Huid,Hpwd,Hdb,HTab,HExlsPath)
Dim hxls '调用 Execl
Dim Hxlbook '打开 Execl
Dim Hxlsheet ' 建立 表格
Dim i '表格记录变量
Dim Cn '数据库连接
Dim Rn '数据库查询
Dim SQLcmd '数据库查询语句
On Error GoTo HFWErrHelp '如果错误则转到错误处理位置执行
'下面一句 先检查文件是否存在
'If Dir(HExlsPath) = "" Then MsgBox "文件" & HExlsPath & "不存在", vbOKOnly, "H*F*W": Exit Sub'晕死 这句在 VBS 中不能用 VB中可以
Set hxls = CreateObject("Excel.application")
Set Hxlbook = hxls.Workbooks.Open(HExlsPath)
Set Hxlsheet = Hxlbook.WorkSheets(1)
Set Cn = CreateObject("ADODB.Connection") '建立数据库连接
Set Rn = CreateObject("ADODB.Recordset") '建立查询
Cn.Open "driver=SQL Server;server=" & Hserver & ";UID=" & Huid & ";PWD=" & Hpwd & ";database=" & Hdb
If Err.Number <> 0 Then MsgBox "数据库连接错误!", vbOKOnly, "H*F*W": Exit Sub '数据库连接错误退出过程
i = 1 'Execl 中记录已 1 开始
While Hxlsheet.cells(i, 1) <> "" '这里的SQL命令 要根据 实际情况进行修改
SQLcmd = "("
'''''''''''''''''''z这下面的字段要根据 实际情况修改''''''''''''''''''''''''''''''
SQLcmd = SQLcmd & Hxlsheet.cells(i, 1) & ",'" '字段 1 ID
SQLcmd = SQLcmd & Hxlsheet.cells(i, 2) & "','" '字段 2 name
SQLcmd = SQLcmd & Hxlsheet.cells(i, 3) & "','" '字段 3 password
SQLcmd = SQLcmd & Hxlsheet.cells(i, 4) & "','" '字段 4 Sex
SQLcmd = SQLcmd & Hxlsheet.cells(i, 5) & "','" '字段 5 TEL
SQLcmd = SQLcmd & Hxlsheet.cells(i, 6) & "')" '字段 6 Cname
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SQLcmd = "insert into Husers(id,name,password,Sex,TEL,Cname)values" & SQLcmd
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Rn.Open SQLcmd, Cn, , , adCmdText
''''''''''动态跟中导入过程中的 成功 与 失败
''If Err.Number = 0 Then
'' Text1.SelText = i & " ------->OK" & vbCrLf
''Else
'' Text1.SelText = i & " ------->Fail" & vbCrLf
''End If
'''''''''''''''''''''''''''''''''''''''''''
i = i + 1
Wend
Set Hxlsheet = Nothing
Set Hxlbook = Nothing
hxls.quit '关闭Execl 否则进程不被关闭并且文件被一直占用
Cn.Close '关闭连接
MsgBox "操作完成！~OK"
Exit Sub
HFWErrHelp: ' 下面是错误处理过程
If Cn.State <= 0 Then Cn.Close: MsgBox "连接关闭！"
MsgBox "发生错误！" & vbCrLf & "错误号: " & Err.Number & vbCrLf & Err.HelpContext & vbCrLf & Err.Description
End Sub
'====================================================================='====================================

Answer 5

只会导入到oracle