ASP.NET如何将excel的数据导入到Access的表中
现在有(Excel)A表{ID,Name,Class},(Access)B表{ID,Name,ClassID},C表{ID,Class},B表的ClassID对应C表的C...
现在有(Excel)A表{ID,Name,Class},(Access)B表{ID,Name,ClassID},C表{ID,Class},B表的ClassID对应C表的Class; 现在我要将A表的数据导入到B,C表中..应该怎么做?求完整代码,或者方法,在线等,或者有什么小技巧,一切的方法也可以!
展开
2个回答
2013-07-26
展开全部
你数据库必须有这几张表,或者在导入的时候根据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)//循环读取工作表名
{
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中的数据通过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)//循环读取工作表名
{
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;
}
至于怎么插入数据 - - 你自己写吧
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
2013-07-26
展开全部
使用oledb 读取excel 数据
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询
广告 您可能关注的内容 |