C#WinForm中如何实现将Excel2003的数据导入到数据库中
C#WinForm中如何实现将Excel2003的数据导入到数据库中在网上找个个代码，谢谢大家啊
//导出数据
private void daochu_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;//保存对话框是否记忆上次打开的目录
saveFileDialog1.CreatePrompt = true;
saveFileDialog1.Title = "导出文件保存路径";
saveFileDialog1.ShowDialog();
if (saveFileDialog1.FileName == "")
return;
string fileExt = System.IO.Path.GetExtension(saveFileDialog1.FileName);
if (fileExt != ".xls")//必须是EXCEL文件
return;
string filepath = saveFileDialog1.FileName;//文件路径
DataTable dt = new DataTable();
dt = CallExcel(filepath);//返回EXCEL文件的数据
if (InsertSQLServer(dt, saveFileDialog1.FileName.Split('.')[0]))//导入数据库
{ }
}
protected DataTable CallExcel(string filepath)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;'");
con.Open();//这个部分报错，提示外部表不是预期的格式。
string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
//OleDbCommand command = new OleDbCommand(sql, con);
//OleDbDataReader reader = command.ExecuteReader();
//if (reader.Read())
//{
// reader[0].ToString();//直接读出数据
//}
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
//reader.Close();
//command.Dispose();
con.Close();
con.Dispose();
return dt;
}
protected bool InsertSQLServer(DataTable dt, string dataname)
{
string strCon = @"server=.;database=testmyIVR;uid=sa;pwd=123";
try
{
SqlConnection con = new SqlConnection(strCon);//连接数据库
con.Open();//打开
//插入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
string strSQL = " Insert into userss values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'" + dt.Rows[i][k].ToString() + "',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
}
return true;
}
catch
{
return false;
}
} --------------------编程问答-------------------- declare @sql varchar(1000)
set @sql= ' insert into '+@tablename+
' SELECT * '+
' FROM OpenDataSource( ' 'Microsoft.Jet.OLEDB.4.0 ' ', ' 'Data Source= '+@filename+';User ID=Admin;Password=;Extended properties=Excel 5.0;HDR=yes;imex=1 ' ')...[ '+@exceltablename+ '] '
exec(@sql)
读取到dataset,sqldataadapter更新
--------------------编程问答-------------------- 连接字符串没看出有什么问题。换个Excel文件试试。。。
http://blog.csdn.net/fangxinggood/archive/2005/06/18/397315.aspx --------------------编程问答-------------------- public static SqlConnection conn = new SqlConnection();
public static SqlCommand comm = new SqlCommand();
public void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
try
{
conn.ConnectionString = "server=.;database=zyqp;user id = "sa";pwd = "1234";//连接字符串
comm.Connection = conn;
conn.Open();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}//打开连接
public void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
comm.Dispose();
}//关闭连接
public DataView dataView(string sqlStr)
{
SqlDataAdapter da = new SqlDataAdapter();
DataView dv = new DataView();
DataSet ds = new DataSet();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlStr;
da.SelectCommand = comm;
da.Fill(ds);
dv = ds.Tables[0].DefaultView;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
closeConnection();
}
return dv;
}//视图方法
private void Form1_Load(object sender, EventArgs e)
{
this.dv1.DataSource = dataView("select * from 视图名称"); //
}//窗口打开时,即显示数据 dv1是datagridview的name，可自定义修改
private void button1_Click_1(object sender, EventArgs e)
{
if (dv2.RowCount > 0)
{
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();// = new Excel.Application();
excel.Application.Workbooks.Add(true);
//生成字段名称
for (int i = 0; i < dv2.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dv2.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dv2.RowCount - 1; i++)
{
for (int j = 0; j < dv2.ColumnCount; j++)
{
if (dv2[j, i].Value == typeof(string))
{
excel.Cells[i + 2, j + 1] = "" + dv2[i, j].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dv2[j, i].Value.ToString();
}
}
}
excel.Visible = true;
}
else
{
MessageBox.Show("没有数据可以导出");
}
}
注意：添加引用和命名空间 Microsoft.Office.Interop.Excel
补充：.NET技术 , C#
