如何通过<em>asp</em>将excel表导入access数据库中-asp excel导入,

你数据库必须有这几张表，或者在导入的时候根据excel的表头去创建拿几张表，读取excel的数据insert到表中。/// <summary>
/// 将Excel中的数据通过OLE连接导入DataSet
/// </summary>
/// <param name="filePath"></param>
/// <param name="ds"></param>
/// <returns></returns>
public void ImportExcelToDataSet(string filePath, DataSet ds)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";//HDR标识是否有表头，IMEX标识格式是文本
DataTable dt = null;
OleDbConnection conn = new OleDbConnection(strConn);//建立OLE连接
conn.Open();
OleDbDataAdapter odda = null;
try
{
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获得工作表名
int num = dt.Rows.Count;
if (dt != null)
{
string[] sheetName = new string[num];//用来存储工作表名
int i = 0;
foreach (DataRow row in dt.Rows)//循环读取工7a64e78988e69d83336作表名
{
sheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
for (int j = 0; j < num; j++)
{
string sql="select * from [" + sheetName[j] + "]";
odda = new OleDbDataAdapter(sql, conn);
odda.Fill(ds, sheetName[j]);//获取工作表中的数据
}
}
AddDatasetToSQL(ds,6);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}

private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{

//// <summary>
/// 从Excel提取数据--》Dataset
/// </summary>
/// <param name="filename">Excel文件路径名</param>

try
{
DataSet ds=new DataSet();
if (this.openFileDialog1.SafeFileName.Split('.')[1] == "xls")
{
ImportExcelToDataSet(this.openFileDialog1.FileName, ds);
}

//if (this.openFileDialog1.FileName == string.Empty)
//{
// throw new ArgumentNullException("Excel文件上传失败！");
//}

//string oleDBConnString = String.Empty;
//oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
//oleDBConnString += "Data Source=";
//oleDBConnString += this.openFileDialog1.FileName;
//oleDBConnString += ";Extended Properties=Excel 8.0;";
//OleDbConnection oleDBConn = null;
//OleDbDataAdapter oleAdMaster = null;
//DataTable m_tableName=new DataTable();
//DataSet ds=new DataSet();

//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 = this.openFileDialog1.SafeFileName.Split('.')[0];

//}
//string sqlMaster;
//sqlMaster = "select * from Sheet1";
//oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
//oleAdMaster.Fill(ds,"m_tableName");
//oleAdMaster.Dispose();
//oleDBConn.Close();
//oleDBConn.Dispose();

//AddDatasetToSQL(ds,6);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将Dataset的数据导入数据库
/// </summary>
/// <param name="pds">数据集</param>
/// <param name="Cols">数据集列数</param>
/// <returns></returns>
private bool AddDatasetToSQL(DataSet pds, int Cols)
{
int ic, ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns.Count < Cols)
{
throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{

for (int i = 0; i < pds.Tables[i].Rows.Count; i++)
{
Add(
pds.Tables[0].Rows[i][1].ToString(), pds.Tables[0].Rows[i][2].ToString(),
pds.Tables[0].Rows[i][3].ToString(), pds.Tables[0].Rows[i][4].ToString(),
pds.Tables[0].Rows[i][5].ToString()
);

}

}
else
{
throw new Exception("导入数据为空！");
}
return true;
}
至于怎么插入数据 - - 你自己写吧

Excel数据导入到Access、Sql Server中示例代码
将Excel中的数据导入到Access中，前提是在Access中的表已经建好。

dim conn
dim conn2
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:\book1.mdb"

set conn2=CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"

sql = "SELECT * FROM [Sheet1$]"
set rs = conn2.execute(sql)
while not rs.eof
sql = "insert into xxx([a],[b],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"
conn.execute(sql)
rs.movenext
wend

conn.close
set conn = nothing
conn2.close
set conn2 = nothing

function fixsql(str)
dim newstr
newstr = str
if isnull(newstr) then
newstr = ""
else
newstr = replace(newstr,"'","''")
end if
fixsql = newstr
end function

　　导入到Sql Server数据库中时，如果Excel文件和数据库不在同一台服务器上时，请参考上面的代码。在同一机器上可以参考下面代码（不需要先把表建表，程序会自己动建表，用Excel中的第e69da5e6ba90e799bee5baa6e997aee7ad94364一行数据做为表的字段名）：

dim conn
set conn=CreateObject("ADODB.Connection")
conn.Open ("driver={SQL Server};server=localhost;uid=sa;pwd=sa;database=hwtemp;")
sql = "SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""c:\book1.xls"";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] "
conn.execute(sql)

conn.close
set conn = nothing