网页资讯视频图片知道文库贴吧地图采购
进入贴吧全吧搜索
林雨其蒙吧 关注：4贴子：141
  • 4回复贴，共1页

Excel导入Access

只看楼主收藏回复

using System;
using System.Collections;
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.Data.OleDb;
public partial class ACCESS : System.Web.UI.Page
{
     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\db.mdb";
     
     protected void Page_Load(object sender, EventArgs e)
     {
         OleDbConnection cn = new OleDbConnection(strConn);
         cn.Open();
         OleDbDataAdapter sda = new OleDbDataAdapter("select * from test", cn);
         DataSet ds = new DataSet();
         sda.Fill(ds, "test");
         this.GridView1.DataSource = ds.Tables["test"];
         this.GridView1.DataKeyNames = new string[] { "id" };
         this.GridView1.DataBind();
     }
  
     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;
     }
     protected void Button1_Click(object sender, EventArgs e)
     {
         if (FileUpload1.HasFile == false)
         {
             Response.Write("<script>alert('请您选择Excel文件')</script> ");
             return;
         }
         string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
         if (IsXls != ".xls")

IP属地:广西1楼2010-07-21 22:14回复
             {
                 Response.Write("<script>alert('只可以选择Excel文件')</script>");
                 return;
             }
             OleDbConnection cn = new OleDbConnection(strConn);
             cn.Open();
             string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;             
             string savePath = Server.MapPath(("~\\temp\\") + filename);
             FileUpload1.SaveAs(savePath);                       
             DataSet ds = ExecleDs(savePath, filename);          
             DataRow[] dr = ds.Tables[0].Select();          
             int rowsnum = ds.Tables[0].Rows.Count;
             if (rowsnum == 0)
             {
                 Response.Write("<script>alert('Excel表为空表,无数据!')</script>");  
             }
             else
             {
                 for (int i = 0; i < dr.Length; i++)
                 {
                     string id = dr[i]["ID"].ToString();
                     string Name = dr[i]["Name"].ToString();
                     string sqlcheck = "select count(*) from test where   Name='" + Name+"'";  
                     OleDbCommand sqlcmd = new OleDbCommand(sqlcheck, cn);
                     int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
                     if (count<=0)
                     {
    
    IP属地:广西2楼2010-07-21 22:14
    回复
                           string insertstr = "insert into test(Name) values('" + Name + "')";
                           OleDbCommand cmd = new OleDbCommand(insertstr, cn);
                           try
                           {
                               cmd.ExecuteNonQuery();
                           }
                           catch (MembershipCreateUserException ex)
                           {
                               Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                           }
                       }
                   }
                   Response.Write("<script>alert('Excle表导入成功!');location='ACCESS.aspx'</script>");
              
               }
               cn.Close();
           }
      }
      
      IP属地:广西3楼2010-07-21 22:14
      回复
        <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ACCESS.aspx.cs" Inherits="ACCESS" %>
        <!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 id="Head1" runat="server">
             <title>Excel导入SQL数据库</title>
        </head>
        <body style="text-align: center">
             <form id="form1" runat="server">
             <div>
                 <table style="width: 576px; border-collapse: separate; text-align: center">
                     <tr>
                         <td colspan="3">
                             &nbsp;Excel导入ACCESS数据库</td>
                     </tr>
                     <tr>
                         <td colspan="3" style="text-align: left">
                             <asp:FileUpload ID="FileUpload1" runat="server" Width="305px" />
                             &nbsp; &nbsp;
                             <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导入ACCESS" /></td>
                     </tr>
                     <tr>
                         <td colspan="3">
                             <asp:GridView ID="GridView1" runat="server" Height="133px" Width="100%">
                             </asp:GridView>
                         </td>
                     </tr>
                     <tr>
                         <td>
                         </td>
                         <td>
                         </td>
                         <td style="width: 189px">
                        
                         <a href="ACCESS.aspx">back</a>
                         </td>
                     </tr>
                 </table>
            
             </div>
             </form>
        </body>
        </html>
        
        IP属地:广西4楼2010-07-21 22:16
        回复
          #region 方法 ToExcel(string strSQL, string FileName) 创建EXCEL
               /// <summary>
               ///   创建EXCEL
               /// </summary>
               /// <param name="strSQL"></param>
               /// <param name="FileName"></param>
               public static void ToExcel(string[] arr, string FileName)
               {
                   string strItemCode = "";
                   HttpContext.Current.Response.Charset = "GB2312";
                   HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
                   HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                   HttpContext.Current.Response.ContentType = "application/ms-excel";
                   HttpContext.Current.Response.Write(strItemCode);
                   for (int i = 0; i < arr.Length; i++)
                   {
                       strItemCode += arr[i].ToString().Trim() + "\n";
                   }
                   HttpContext.Current.Response.Write(strItemCode);
                   HttpContext.Current.Response.End();
               }
               #endregion
          
          IP属地:广西5楼2010-07-24 11:43
          回复

            扫二维码下载贴吧客户端

            下载贴吧APP
            看高清直播、视频！
            • 4回复贴，共1页
            发表回复
            发贴请遵守贴吧协议及“七条底线”贴吧投诉
            内  容:
              查看全部
            发 表
            保存至快速回贴

            发布成功
            去贴吧APP订阅抽奖结果通知
            扫码进入贴吧APP
            ···········
            开奖后第一时间站内信通知
            ···········
            中奖结果全员公示