/**//// <summary>
/// 导出考生信息到XML文件
/// </summary>
/// <param name="toPath"></param>
private void ExportToXml(string toPath)
{
this.ds.WriteXml(toPath,XmlWriteMode.WriteSchema);
}
/**//// <summary>
/// 导入考生信息
/// </summary>
/// <param name="fromPath">导入的XML文件</param>
private void ImportFromXml(string fromPath)
{
using(FileStream fsReadXml=new FileStream(fromPath,FileMode.Open))
{
XmlTextReader myXmlReader=new XmlTextReader(fsReadXml);
ds_xml.ReadXml(myXmlReader);
myXmlReader.Close();
int pbValue=1;
int count=ds_xml.Tables[0].Rows.Count;
//写入数据库
foreach(DataRow dr in ds_xml.Tables[0].Rows)
{
try
{
this.pbProgress.Value=0;
this.panProgress.Visible=true;
this.lblMsg.Text="正在导入
"+dr["xjh"].ToString().Trim();
DB db=new DB();
SqlParameter[] paras=
{
new SqlParameter("@xjh",dr["xjh"].ToString().Trim()),
new SqlParameter("@xxdm",dr["xxdm"].ToString().Trim()),
new SqlParameter("@xm",dr["xm"].ToString().Trim()),
new SqlParameter("@xb",dr["xb"].ToString().Trim()),
new SqlParameter("@bh",dr["bh"].ToString().Trim()),
new SqlParameter("@zp",dr["zp"]),
};
if(ds.Tables["xs_ksxx"].Select("xjh='"+dr["xjh"]+"'").Length==0)
{
string strInsertJBXX="insert into xs_jbxx(xjh,xxdm,xm,xb,bh,zp) values(@xjh,@xxdm,@xm,@xb,@bh,@zp)";
db.ExecCommand(strInsertJBXX,paras);
}
else if(ds.Tables["xs_ksxx"].Select("xjh='"+dr["xjh"]+"'").Length==1)
{
string strUpdateJBXX="update xs_jbxx set xxdm=@xxdm,xm=@xm,xb=@xb,bh=@bh,zp=@zp where xjh=@xjh";
db.ExecCommand(strUpdateJBXX,paras);
}
System.Threading.Thread.Sleep(10);
this.pbProgress.Value=100;
this.pbProgressAll.Value=(int)((pbValue/count)*100);
Application.DoEvents();
pbValue++;
ds.Clear();
DataBind(rowfilter);
}
catch(Exception ex)
{
this.panProgress.Visible=false;
MessageBox.Show(ex.ToString());
}
}
this.panProgress.Visible=false;
}
}
Excel导入SQLSERVER数据库,请问怎样杜绝导入表头那一行
net_lover(【孟子E章】) ( ) 信誉：140
string strCon ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + @FileSource + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/"";
public void LoadData(string FileSource,string StyleSheet)
{
string strCon ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + @FileSource + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open(); //打开数据链接，得到一个数据集
DataSet myDataSet = new DataSet(); //得到自己的DataSet对象
string StrSql="select * from ["+StyleSheet+"$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter (StrSql,myConn);
myCommand.Fill(myDataSet,"["+StyleSheet+"$]");
myCommand.Dispose();
DataTable DT=myDataSet.Tables["["+StyleSheet+"$]"];
myConn.Close();
myCommand.Dispose();
conn.Close();
conn.Open();
string sql="delete from TEST";
SqlCommand comm=new SqlCommand(sql,conn);
comm.ExecuteNonQuery();
comm.Dispose();
for(int j=0;j<DT.Rows.Count;j++)
{
string ID=DT.Rows[j][0].ToString();string AUTHOR=DT.Rows[j][1].ToString();string NAME=DT.Rows[j][2].ToString();
string PRICE=DT.Rows[j][3].ToString();string strSql="insert into TEST(ID,AUTHOR,NAME,PRICE) ";
strSql=strSql + "values ('"+ID+"','"+AUTHOR+"','"+NAME+"','"+PRICE+"')";
comm=new SqlCommand(strSql,conn);
comm.ExecuteNonQuery();
if (j==DT.Rows.Count-1)
{
Label1.Visible=true;
}
}
conn.Close();
}
}
实际例子与说明如下：
/**//*如果要将表整个导出至Excel的话*/
EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:/Book1.xls -c -q -S"(local)" -U"sa" -P""'
--注意句中的northwind.dbo.orders，为数据库名＋拥有者＋表名
--直接导出用“out”关健字
-------------------------------------------
/**//*如果要利用查询来导出部分字段至Excel的话*/
EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C:/ Book2.xls -c -S"(local)" -U"sa" -P""'
--这里在bcp后面加了一个查询语句，并用双引号括起来
--利用查询要用“queryout”关键字
/**//*1、插入Excel中的资料到现存的sql数据库表中（假设C盘有excel表book2.xls，book2.xls中有个工作表sheet1，sheet1中有两列id和FName；而同时sql数据库中也有一个表test）：*/
insert into test SELECT id,FName
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')
[sheet1$]
--如果用select * ，则列的次序会乱，资料内容也会乱，无法插入成功，所以指定列名
－－－－－－－－－－－－－－－－－－－－－－－
/**//*2、插入excel表中资料到sql数据库并新建一个sql表（excel的定义和内容同上）：*/
select convert(int,id)as id,FName into test7
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')
[sheet1$]
--在select 列中最好用convert进行显示类型转换，否则资料类型会不如预期。
从excel导入数据到数据库
建立Connection对象的数据源连接字符串：
"Provider=Microsoft.Jet.Oledb.4.0;Data Source=Excel 文件物理路径 + ";Extended Properties=Excel 8.0";
DataAdapter对象中的SQL语句应为："Select 字段列表 From [工作表名$]"
具体示例：
<%@ Import NameSpace="System.Data"%>
<%@ Import NameSpace="System.Data.OleDb"%>
<script Language="C#" runat="server">
void Page_Load(Object sender,EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Server.MapPath("Book1.xls") + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]",conn);
DataSet ds = new DataSet();
adp.Fill(ds,"Book1");
ExlDataGrid.DataSource = ds.Tables["Book1"].DefaultView;
ExlDataGrid.DataBind();
}
</script>
<html>
<body>
<h4 align="center">读取Excel文件</h4>
<asp:DataGrid id="ExlDataGrid" width="100%" HeaderStyle-BackColor="#999999" ForeColor="#333333" runat="server"></asp:DataGrid>
</body>
</html>
给你一个我昨天改别人的一个导excel入sql数据库的我正用的程序,是button1按钮一点就开始导入,我导的是excel是第二三colum..如下:
private void Button1_Click(object sender, System.EventArgs e)
{
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);
if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;
for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";
try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();
}
}
}
4929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
