asp.net中excel数据的导入与导出

一、这是一个导excel入sql数据库的程序,导的是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();
}

}
}

看看这个从excel批量导入数据库SQL

private DataSet GetCollection()
{
DataSet ds=new DataSet();
string strCon,strCmm;
//strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\irms\\tmp\\irsbd.xls;Extended Properties=Excel 8.0;"; 
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("tmp\\irsbd.xls")+";Extended Properties=Excel 8.0;"; 
strCmm="select distinct * from [Sheet1$]";
OleDbConnection oleCnn=new OleDbConnection(strCon);
OleDbCommand oleCmm=new OleDbCommand(strCmm,oleCnn);
OleDbDataAdapter oleDa=new OleDbDataAdapter(oleCmm);
oleDa.Fill(ds,"irsbd");

return ds;

}

private void PutData(DataSet ds)
{
string strCon=Application["strCon"].ToString();
string strSql="select top 1 * from irsbd";
DataSet myDs=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(strSql,strCon);
da.Fill(myDs,"irsbd");
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
if(ds.Tables[0].Rows[i]["sbdno"].ToString().Trim()!="")
{
DataRow dr=myDs.Tables[0].NewRow();
DataRow dr1=ds.Tables[0].Rows[i];
dr["sbdno"]=dr1["sbdno"];
dr["sbdnm"]=dr1["sbdnm"];
dr["sbdpd"]=dr1["sbdno"];
dr["sbdit"]=dr1["sbdit"];
dr["sbddt"]=dr1["sbddt"];
dr["sbdco"]=tbCo.Text.Trim();

dr["sbdel"]=DateTime.Today;
dr["sbdcs"]=0;
...省略

myDs.Tables[0].Rows.Add(dr);
}

SqlCommandBuilder sqlCb=new SqlCommandBuilder(da);

da.Update(myDs,"irsbd");
myDs.AcceptChanges();
}

      二、Asp.net页面输出到EXCEL 

  近来,在开发ISO文件管理系统的时候,曾经遇到过要将ASPX直接输出到EXCEL的需求,现将经验所得与大家分享。其实,利用ASP.NET输出指定内容的WORD、EXCEL、TXT、HTM等类型的文档很容易的。主要分为三步来完成。 

     (一)、定义文档类型、字符编码   

   Response.Clear(); 

   Response.Buffer= true; 

   Response.Charset="utf-8";   

   //下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开 

   //filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc    .xls    .txt   .htm   

   Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls"); 

   Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");   

   //Response.ContentType指定文件类型 可以为application/ms-excel    application/ms-word    application/ms-txt    application/ms-html    或其他浏览器可直接支持文档  

   Response.ContentType = "application/ms-excel"; 

  (二)、定义一个输入流   

   System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); 

   System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);   

  (三)、将目标数据绑定到输入流输出   

   this.RenderControl(oHtmlTextWriter);    

   //this 表示输出本页,你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件   

   Response.Write(oStringWriter.ToString()); 

   Response.End();   

Add comment

  Country flag

biuquote
微笑得意调皮害羞酷大笑惊讶发呆喜欢可怜尴尬闭嘴噘嘴皱眉伤心抓狂呕吐坏笑漫骂发怒
Loading

Calendar

<<  February 2020  >>
MonTueWedThuFriSatSun
272829303112
3456789
10111213141516
17181920212223
2425262728291
2345678

切换到大日历中浏览

Month List

声明

本博所有网友评论不代表本博立场,版权归其作者所有。

© Copyright 2011

 

Powered by BlogYi.NET ver:2.5.0.10, original powered by BlogEngine.NET.