/*************************************************/
以下是cs代码
/*************************************************/
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
/// <summary>
/// 查询数据并显示出来
/// </summary>
private void BindData()
{
try
{
SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["connectString"]);
cn.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from hhaspx_gz", cn);
SqlDataAdapter da = new SqlDataAdapter("select * from hhaspxinfo",cn);
DataSet ds = new DataSet();
DataSet dss = new DataSet();
sda.Fill(ds, "hhaspx_gz");
da.Fill(dss, "hhaspxinfo");
this.GridView1.DataSource = ds.Tables["hhaspx_gz"];
this.GridView1.DataKeyNames = new string[] { "hhaspx_id" };
this.GridView1.DataBind();
this.GridView2.DataSource = dss.Tables["hhaspxinfo"];
this.GridView2.DataKeyNames = new string[] { "id"};
this.GridView2.DataBind();
cn.Dispose();
}
catch (Exception err)
{
throw(err);
}
}
/// <summary>
/// 查询EXCEL电子表格添加到DATASET
/// </summary>
/// <param name="filenameurl">服务器路径</param>
/// <param name="table">表名</param>
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
}
/// <summary>
/// excel导入到数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["connectString"]);
cn.Open();
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string hhaspx_rq = dr[i]["日期"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string hhaspx_bh = dr[i]["编号"].ToString();//编号 列名 以下类似
string hhaspx_xm = dr[i]["姓名"].ToString();
string hhaspx_xb = dr[i]["性别"].ToString();
string hhaspx_dx = dr[i]["底薪"].ToString();
string hhaspx_kh = dr[i]["考核"].ToString();
string hhaspx_jl = dr[i]["奖励"].ToString();
string hhaspx_jt = dr[i]["津贴"].ToString();
string hhaspx_jb = dr[i]["加班"].ToString();
string hhaspx_zb = dr[i]["值班"].ToString();
string hhaspx_jx = dr[i]["绩效"].ToString();
string hhaspx_hj = dr[i]["合计"].ToString();
string sqlcheck = "select count(*) from hhaspx_gz where hhaspx_rq='" + hhaspx_rq + "'And hhaspx_xm='" + hhaspx_xm + "'"; //检查用户是否存在
SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
if (count < 1)
{
string insertstr = "insert into hhaspx_gz (hhaspx_rq,hhaspx_bh,hhaspx_xm,hhaspx_xb," +
"hhaspx_dx,hhaspx_kh,hhaspx_jl,hhaspx_jt,hhaspx_jb,hhaspx_zb,hhaspx_jx,hhaspx_hj) "+
"values('" + hhaspx_rq + "','" + hhaspx_bh + "','" + hhaspx_xm + "','" + hhaspx_xb + "',"+
"'" + hhaspx_dx + "','" + hhaspx_kh + "','" + hhaspx_jl + "','" + hhaspx_jt + "',"+
"'" + hhaspx_jb + "','" + hhaspx_zb + "','" + hhaspx_jx + "','" + hhaspx_hj + "')";
SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
else
{
Response.Write("<script>alert('内容重复!禁止导入');location='default.aspx'</script></script> ");
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
}
cn.Close();
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="gd">选择导出是哪个GridView1列表</param>
private void gridViewExport(GridView gd)
{
if (gd.Rows.Count != 0)
{
Response.Clear();
Response.BufferOutput = true;
//设定输出的字符集
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//设置导出文件的格式
Response.ContentType = "application/ms-excel";
//关闭ViewState
EnableViewState = false;
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
gd.RenderControl(textWriter);
//把HTML写回浏览器
Response.Write(stringWriter.ToString());
Response.End();
}
else
{
//此方式可以避免关闭提示后页面变形
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "aler", "<script language=javascript>alert('没有要导出的数据');</script>");
}
}
/// <summary>
/// 导出数据到excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Export_Click(object sender, EventArgs e)
{
#region
//Response.Clear();
//Response.Buffer = true;
//Response.Charset = "GB2312"; //设置了类型为中文防止乱码的出现
//Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToString("yyMMdd.ss") + ".xls"); //定义输出文件和文件名
//Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
//Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
//this.EnableViewState = false;
//System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
//System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
//System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
//this.GridView1.RenderControl(oHtmlTextWriter);
//Response.Write(oStringWriter.ToString());
#endregion
gridViewExport(this.GridView1);
gridViewExport(this.GridView2);
}
/// <summary>
/// 导出用到的方法
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(Control control)
{
//注释掉下面的代码,否则在asp.net2.0下会报错(注:GridView是asp.net 2.0下的控件,1.1下一些控件也可以导出成Excel或者Word)
//base.VerifyRenderingInServerForm(control);
}
}
======================================================================
/*************************************************/
以下是aspx代码
/*************************************************/
<%@ 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>Excel导入导出SQL数据库</title>
</head>
<body style="text-align: center">
<br /><br /><br /><br />
<form id="form1" runat="server">
<div>
<table style="width: 576px; border-collapse: separate; text-align: center">
<tr>
<td colspan="3">
Excel导入导出SQL数据库</td>
</tr>
<tr>
<td colspan="3" style="text-align: left">
<asp:FileUpload ID="FileUpload1" runat="server" Width="305px" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导入SQL" />
<asp:Button ID = "Export" runat="server" Text = "导出Excel" OnClick="Export_Click" />
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="GridView1" runat="server" Height="133px" Width="100%">
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="GridView2" runat="server" Height="133px" Width="100%">
</asp:GridView>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
<td style="width: 189px">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>