界面效果
Excls 设计
数据库设计
该程序运行在vs2008上面
数据库 SqlServer2005
本实例中 数据库为 SDZY_raffle、表名为 JiFen
代码直接考上就可以运行
提示:要在后台代码中添加一个Configuration的引用
前台布局代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button2" runat="server" οnclick="Button2_Click" Text="上传"
style="width: 40px" />
<br />
<br />
exls 里的表名
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
<asp:Button ID="Button3" runat="server" Height="26px" οnclick="Button3_Click"
Text="读取Excle里面的数据" />
<br />
<br />
<asp:Button ID="Button5" runat="server" οnclick="Button5_Click" Text="更新全部" />
<br />
<br />
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click"
Text="将excls数据导入数据库" />
<br />
<br />
<br />
更新
<asp:TextBox ID="txtstar" runat="server"></asp:TextBox>
到
<asp:TextBox ID="txtend" runat="server"></asp:TextBox>
<asp:Button ID="Button6" runat="server" οnclick="Button6_Click" Text="更新" />
<br />
<br />
<asp:Button ID="Button7" runat="server" οnclick="Button7_Click"
Text="显示出所有要更新的数据" />
<br />
<asp:GridView ID="GridView2" runat="server">
</asp:GridView>
<br />
<br />
<asp:Button ID="Button4" runat="server" οnclick="Button4_Click" Text="显示的条数 " />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
后台代码
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string path = Server.MapPath("~/exls/jifen.xls");
getTableName(path);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
send();
}
/// <summary>
/// 将excls上传到服务器
/// </summary>
private void send()
{
Boolean bo = false;
string message="";
string path = Server.MapPath("~/exls/").Trim();//获取上传到服务器的路径
string name = FileUpload1.FileName.Trim();//获取上传文件的路径
string extension= Path.GetExtension(FileUpload1.PostedFile.FileName.Trim());//获取上传文件的扩展名
if (FileUpload1.HasFile)
{
if (extension == ".xls")
{
if (FileUpload1.PostedFile.ContentLength < 3048576)
{
message = "上传成功";
bo = true;
}
else
{
message = "上传文件太大";
bo = false;
}
}
else
{
message = "上传格式不正确";
bo = false;
}
}
else
{
message = "选择上传的文件";
}
if (bo)
{
FileUpload1.SaveAs(path + name);
string pa=path + name;
Session["path"] = pa;
getTableName(pa);
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('" + message + "')", true);
}
else
{
Page.ClientScript.RegisterStartupScript (this.GetType(),Guid.NewGuid().ToString(),"alert('"+message+"')",true);
}
}
/// <summary>
/// 读取
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button3_Click(object sender, EventArgs e)
{
if (DropDownList1.SelectedValue == "")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请上传')", true);
}
else
{
Read();
}
}
/// <summary>
/// 将这个dataset数据加载到Grview控件里
/// </summary>
private void Read()
{
string path = "";
if (Session["path"] == null)
{
path = Server.MapPath("~/exls/jifen.xls");
}
else
{
path= Session["path"].ToString();
}
string tableName = DropDownList1.SelectedValue.ToString().Trim();
DataTable table = new DataTable();
table = InputExcls(path, tableName);//将Excls里面数据保存到缓存DataTable中
if (Session["table"] != null)
{
Session.Remove("table");
}
else
{
Session.Add("table", table);
}
if (table.Rows.Count > 0)
{
GridView1.DataSource = table;
GridView1.DataBind();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请选择导入的Excls')", true);
}
}
/// <summary>
/// 将Excls里面的数据读取到程序里面,这里将返回以一个DataTable
/// </summary>
/// <param name="path">Excls所在的路劲</param>
/// <param name="tableName">Excls里面的表名</param>
/// <returns> 返回一个dataset</returns>
private DataTable InputExcls(string path, string tableName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
string sql = "select * from [" + tableName + "$];";
System.Data.OleDb.OleDbDataAdapter ap = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
DataTable ds = new DataTable();
ap.Fill(ds);
conn.Close();
return ds;
}
/// <summary>
/// 获取Excls表名
/// </summary>
/// <param name="path"></param>
private void getTableName(string path)
{
DropDownList1.Items.Clear();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);//获取Excls表名
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
string tableNames = schemaTable.Rows[i][2].ToString().Trim();
tableNames = tableNames.Replace("$", "");
ListItem item = new ListItem();
item.Text = tableNames;
item.Value = tableNames;
DropDownList1.Items.Add(item);
}
}
/// <summary>
/// 将数据放进数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
if (Session["table"] == null)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请先将数据读取出来')", true);
}
else
{
Inser();
}
}
/// <summary>
/// 将数据放进数据库
/// </summary>
private void Inser()
{
try
{
int num = 0;
string strCon = ConfigurationManager.ConnectionStrings["con"].ToString();
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
DataTable ds = (DataTable)Session["table"];
string sql = "";
string message = "";
for (int i = 0; i < ds.Rows.Count; i++)
{
sql = "insert into JiFen values (" + ds.Rows[i][0] + "," + ds.Rows[i][1] + "); ";
message = message + sql;
num++;
}
SqlCommand command = new SqlCommand(message, conn);
command.ExecuteNonQuery();
conn.Close();
if (num > 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('增加了" + num + "条')", true);
}
}
catch (Exception ee)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('查看数据连接,表是否存在')", true);
}
}
/// <summary>
/// 更新数据库所有信息
/// </summary>
private void updata()
{
int num = 0;
string strCon = ConfigurationManager.ConnectionStrings["con"].ToString();
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
DataTable ds = (DataTable)Session["table"];
string sql = "";
string message = "";
string [] Id=selectId();//查询要更新的用户Id
for (int i = 0; i < ds.Rows.Count; i++)
{
sql = "update JiFen Set [count]=" + ds.Rows[i][1]+" where Id='"+Id[i]+"';";
message = message + sql;
num++;
}
SqlCommand command = new SqlCommand(message, conn);
int number=command.ExecuteNonQuery();
conn.Close();
if (number > 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('更新了" + num + "条')", true);
}
}
/// <summary>
/// 查询数据库中所有用户Id将其保存到字符串数组中去
/// </summary>
private string[] selectId()
{
string strCon = ConfigurationManager.ConnectionStrings["con"].ToString();
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
string sql2 = "select Id from JiFen";
SqlDataAdapter ap =new SqlDataAdapter (sql2, conn);
DataSet ds = new DataSet();
ap.Fill(ds);
conn.Close();
string[] Id = new string[ds.Tables[0].Rows.Count];
for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
{
Id[i] =Convert.ToString ( ds.Tables[0].Rows[i][0]);
}
return Id;
}
/// <summary>
/// 要查看的条数
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
private DataTable select(int count)
{
DataTable ds1 = new DataTable();
DataTable ds = (DataTable)Session["table"];
if(count<ds.Rows.Count)
{
ds1 = ds.Copy();
ds1.Rows.Clear();
for (int i = 0; i < count; i++)
{
ds1.ImportRow(ds.Rows[i]);
}
}
return ds1;
}
/// <summary>
/// 查看的条数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button4_Click(object sender, EventArgs e)
{
if (TextBox3.Text != "")
{
DataTable ds = select(Convert.ToInt32(TextBox3.Text));
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('输入显示的条数')", true);
}
}
protected void Button5_Click(object sender, EventArgs e)
{
if (Session["table"] != null)
{
updata();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('现将数据读出来')", true);
}
}
protected void Button6_Click(object sender, EventArgs e)
{
if (txtstar.Text != "" && txtend.Text != "")
{
int starNumber = Convert.ToInt32(txtstar.Text);
int endNumber = Convert.ToInt32(txtend.Text);
starupdate(starNumber, endNumber);
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请输入要更新的条数')", true);
}
}
/// <summary>
/// 更新n到m条数据
/// 思路是先将要更新的数据从内存中取出来将它复制到一个新的datatable里面,然后在更新这个表里面的数据到数据库
/// </summary>
/// <param name="star">重几条开始</param>
/// <param name="end">到几条结束</param>
private void starupdate(int star,int end)
{
DataTable ds = (DataTable)Session["table"];
int num = 0;//记录更新了几条数据
if(end<ds.Rows.Count)
{
int lenght = end - star;
string[] Id = new string[lenght];//用来存放要更新的用户Id
DataTable newtable = new DataTable();//用来存放要更新的数据
newtable= ds.Copy();//将原来的datatable表的数据结构复制给新创建的表
newtable.Clear();
for (int j = star-1; j < end-1; j++)
{
newtable.ImportRow(ds.Rows[j]);//将ds的行里面的值一一复制给newtable表
}
//存放要更新用户的Id号到Id数据里面
for (int i = 0; i < newtable.Rows.Count; i++)
{
Id[i] = newtable.Rows[i][0].ToString();
}
string strCon = ConfigurationManager.ConnectionStrings["con"].ToString();
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
string sql = "";
string message = "";
for (int n = 0; n < newtable.Rows.Count; n++)
{
sql = "update JiFen set [count]=" + newtable.Rows[n][1] + "where Id='" + Id[n] + "';";
message = message + sql;
num++;
}
if (message != "")
{
SqlCommand command = new SqlCommand(message, conn);
int number = command.ExecuteNonQuery();
conn.Close();
if (number > 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('更新了" + num + "条')", true);
}
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('更新了0条')", true);
}
}
}
/// <summary>
/// 显示出要更新的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button7_Click(object sender, EventArgs e)
{
if (txtstar.Text != "" && txtend.Text != "")
{
int starNumber = Convert.ToInt32(txtstar.Text);
starNumber = starNumber - 1;
int endNumber = Convert.ToInt32(txtend.Text);
endNumber = endNumber - 1;
DataTable ds = (DataTable)Session["table"];
DataTable newtable = new DataTable();//用来存放要更新的数据
newtable = ds.Copy();//将原来的datatable表的数据结构复制给新创建的表
newtable.Clear();
for (int j = starNumber; j < endNumber; j++)
{
newtable.ImportRow(ds.Rows[j]);//将ds的行里面的值一一复制给newtable表
}
GridView2.DataSource = newtable;
GridView2.DataBind();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请输入要更新的条数')", true);
}
}
}