using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Configuration;
using System.Data.OleDb;
namespace WebApplication1
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnUpFiles;
protected System.Web.UI.WebControls.DataGrid dgBrowse;
protected System.Web.UI.HtmlControls.HtmlInputFile File1=new HtmlInputFile();
protected System.Data.OleDb.OleDbConnection conn=new OleDbConnection(ConfigurationSettings.AppSettings["JSJD"]);
protected System.Data.OleDb.OleDbCommand myCmd,cmd_bd,cmd_delNull;
protected System.Data.OleDb.OleDbDataAdapter myDa,da_bd;
protected System.Web.UI.WebControls.Label lblCount;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
btnUpFiles.Visible=true;
File1.Visible=true;
GridBind();
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnUpFiles.Click += new System.EventHandler(this.btnUpFiles_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
#region 提示
public void WarningWithScript(string strMessage)
{
System.Web.HttpResponse response=System.Web.HttpContext.Current.Response;
response.Write("<script language='javascript'>alert(/""+strMessage.Replace("/"","///"")+"/")</script>");
}
#endregion
private void btnUpFiles_Click(object sender, System.EventArgs e)
{
//提取EXCEL文件内容,保存到数据库
if(File1.PostedFile.ContentLength==0)
{
this.WarningWithScript("请选择上传数据的源文件!");
return;
}
//使用HTTP协议将Excel文件上传到服务器
Stream stream = File1.PostedFile.InputStream;
long length = File1.PostedFile.InputStream.Length;
byte[] date = new byte[length];
stream.Read(date,0,Convert.ToInt32(length));
string file_name =System.IO.Path.GetFileName(File1.PostedFile.FileName);
File1.PostedFile.SaveAs("C://Inetpub//wwwroot//WebApplication1//Temp//"+file_name);
string strPath="C://Inetpub//wwwroot//WebApplication1//Temp//"+file_name;
//从数据源读取数据
string mystring="Provider = Microsoft.Jet.OLEDB.4.0;Data Source='"+strPath+"';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$] where ID IS NOT NULL",cnnxls);
DataSet myDs=new DataSet();
try
{
myDs.Clear();
myDa.Fill(myDs);
string str1=myDs.Tables[0].Rows[1][1].ToString();
string str2=myDs.Tables[0].Rows[1][2].ToString();
}
catch
{
this.WarningWithScript("请先保存并关闭文件,再次上传!");
return;
}
if(myDs.Tables[0].Rows.Count>0)
{
string strSql=null;
if(conn.State!=ConnectionState.Open)
{
conn.Open();
}
//查询现有数据,如果已经存在数据,那么在现有数据的基础上进行追加
//取出数据库中的记录数量,在此数量的基础上进行追加
DataSet dsNowData=new DataSet();
dsNowData=this.GetDataSource();
int RowsCount=dsNowData.Tables[0].Rows.Count;
int ITemp=0;
if(RowsCount!=0)
{
ITemp=Convert.ToInt32(RowsCount);
}
else
{
ITemp=0;
}
for(int i=ITemp;i<myDs.Tables[0].Rows.Count;i++)
{
strSql="insert into JSJD.TEST20070903(ID,TBRQ,TBR) values('";
strSql+=myDs.Tables[0].Rows[i].ItemArray[0].ToString()+"','";
strSql+=myDs.Tables[0].Rows[i].ItemArray[1].ToString()+"','";
strSql+=myDs.Tables[0].Rows[i].ItemArray[2].ToString()+"')";
try
{
if(conn.State!=ConnectionState.Open)
{
conn.Open();
}
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
}
catch(Exception ex)
{
this.WarningWithScript("数据导入失败!+"+ex.Message.ToString());
return;
}
}//--------
this.WarningWithScript("数据导入成功!");
//删除临时文件
System.IO.File.Delete(strPath);
conn.Close();
}
GridBind();
}
private void GridBind()
{
DataSet ds=new DataSet();
ds=this.GetDataSource();
this.lblCount.Text="目前共有"+ds.Tables[0].Rows.Count.ToString()+"条记录!";
this.dgBrowse.DataSource=ds;
this.dgBrowse.DataBind();
this.dgBrowse.SelectedIndex=-1;
}
private DataSet GetDataSource()
{
DataSet ds=new DataSet();
ds.Clear();
if(conn.State!=ConnectionState.Open)
{
conn.Open();
}
cmd_bd=new OleDbCommand("select ID,TBRQ,TBR from JSJD.TEST20070903 ORDER BY ID ASC",conn);
da_bd=new OleDbDataAdapter(cmd_bd);
da_bd.Fill(ds);
conn.Close();
return ds;
}
}
}