asp.net中 Excel的导入到数据库怎么写,求 A Bid ip1 192.168.1.12 192.168.1.2 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 先把excel数据导入到dataset中,然后重dataset中把值再插入数据库。将excel中的数据导入到dataset方法如下:using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.OleDb;namespace WindowsFormsApplication1{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } private void button1_Click(object sender, EventArgs e) { System.Data.DataTable dt; OpenFileDialog openfile = new OpenFileDialog(); if (openfile.ShowDialog() == DialogResult.OK) { try { dt = GetData(openfile.FileName);//获得Excel } catch (Exception ex) { throw ex; } } else { dt = null; } } /// <summary> /// 用oledb方式读取excel到datatable /// </summary> /// <res></res> /// <param name="strPath">文件存放路径</param> /// <returns></returns> private static System.Data.DataTable GetData(string strPath) { System.Data.DataTable dt = new System.Data.DataTable(); try { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;"; string strSheetName = ""; using (OleDbConnection con = new OleDbConnection(strCon)) { con.Open(); System.Data.DataTable dtTemp = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); strSheetName = dtTemp.Rows[0][2].ToString().Trim(); } String strCmd = "select * from [" + strSheetName + "]"; OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon); cmd.Fill(dt); } catch (Exception ex) { MessageBox.Show(ex.Message); } return dt; } }}然后再写一个For循环,将dataset中的数据逐一插入数据。 SqlConnection conn = new SqlConnection("server = Jzx; database = Jzx;uid= sa;pwd = sa"); //连接excel数据源 string excelconnstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 文件名(注意要绝对路径如:c:/....) + ""; excelconnstring += @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""; System.Data.OleDb.OleDbConnection excelconn = new System.Data.OleDb.OleDbConnection(excelconnstring); string sql = "select * from [sheet1$]"; System.Data.OleDb.OleDbDataAdapter mycomm = new System.Data.OleDb.OleDbDataAdapter(sql, excelconn); DataSet myds = new DataSet(); mycomm.Fill(myds, "ss"); SqlCommand cm = new SqlCommand(); cm.Connection = conn; conn.Open(); for (int i = 0; i < myds.Tables[0].Rows.Count; i++) { string updateSql1 = "insert into xz(xzj,wsl)values('" + myds.Tables[0].Rows[i]["贾子炫"] + "','" + myds.Tables[0].Rows[i]["男"] + "')"; cm.CommandText = updateSql1; cm.ExecuteNonQuery(); } 还有如果你想用Excel做数据源的话呢,记得Excel的第一行就是字段名 对Remoting比较熟悉的大大请进 截取字符串的值 如何动态的生成控件? C#读取XML的问题?请高手帮忙 画线是怎么设置线条的粗细呀??? 如何设置一个子form, 坐标位置自动跟着父form移动 ***试了好久都不行,客户端一用代理就无法访问Remoting Server 急求,,,如何用C#编写一个石头剪刀布游戏程序 有关StreamReader的Position问题 VS2005如何設置一個Project的輸出類型為ClassLibrary. c# 实现两窗体视频播放同步,画中画效果,控件共享,传递控件引用! 数据库文件怎么定位
将excel中的数据导入到dataset方法如下:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{ } private void button1_Click(object sender, EventArgs e)
{
System.Data.DataTable dt;
OpenFileDialog openfile = new OpenFileDialog();
if (openfile.ShowDialog() == DialogResult.OK)
{
try
{
dt = GetData(openfile.FileName);//获得Excel
}
catch (Exception ex)
{
throw ex;
}
}
else
{
dt = null;
}
} /// <summary>
/// 用oledb方式读取excel到datatable
/// </summary>
/// <res></res>
/// <param name="strPath">文件存放路径</param>
/// <returns></returns>
private static System.Data.DataTable GetData(string strPath)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;";
string strSheetName = "";
using (OleDbConnection con = new OleDbConnection(strCon))
{
con.Open();
System.Data.DataTable dtTemp = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
strSheetName = dtTemp.Rows[0][2].ToString().Trim();
}
String strCmd = "select * from [" + strSheetName + "]";
OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon);
cmd.Fill(dt);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
return dt;
}
}
}然后再写一个For循环,将dataset中的数据逐一插入数据。
//连接excel数据源
string excelconnstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 文件名(注意要绝对路径如:c:/....) + "";
excelconnstring += @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
System.Data.OleDb.OleDbConnection excelconn = new System.Data.OleDb.OleDbConnection(excelconnstring);
string sql = "select * from [sheet1$]";
System.Data.OleDb.OleDbDataAdapter mycomm = new System.Data.OleDb.OleDbDataAdapter(sql, excelconn);
DataSet myds = new DataSet();
mycomm.Fill(myds, "ss");
SqlCommand cm = new SqlCommand();
cm.Connection = conn;
conn.Open();
for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
{
string updateSql1 = "insert into xz(xzj,wsl)values('" + myds.Tables[0].Rows[i]["贾子炫"] + "','" + myds.Tables[0].Rows[i]["男"] + "')";
cm.CommandText = updateSql1;
cm.ExecuteNonQuery();
}