Transfer Excel To SQL By C/S

随着公司业务增多,EXCEL需要导入的次数越来越多,为了解决人工导入太繁琐,在网上找了点资料做了个页面自动将EXCEL导入SQL功能

ASPX

<% @ Page Language = " C# "  AutoEventWireup = " true "  CodeBehind = " Default.aspx.cs "  Inherits = " TransferExceltoSQL._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 > Untitled Page </ title >
</ head >
< body >
     < form  id ="form1"  runat ="server" >
     < div >
         < input  id ="File1"  type ="file"  runat ="server"   />
         < asp:GridView  ID ="GridView1"  runat ="server"  AutoGenerateColumns ="False" >
             < Columns >
                 < asp:boundfield  DataField ="fid" ></ asp:boundfield >
                 < asp:boundfield  DataField ="fname" ></ asp:boundfield >
                 < asp:boundfield  DataField ="fdatetime" ></ asp:boundfield >
             </ Columns >
         </ asp:GridView >
        
         < br  />
         < asp:Button  ID ="Button1"  runat ="server"  Text ="Button"  onclick ="Button1_Click"   />
    
     </ div >
     </ form >
</ body >
</ html >

.ASPX.CS

using  System;
using  System.Data;
using  System.Data.OleDb;
using  System.Data.SqlClient;
using  System.Configuration;
using  System.Collections;
using  System.Linq;
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.Xml.Linq;

namespace  TransferExceltoSQL
{
     public  partial  class  _Default : System.Web.UI.Page
    {
         protected   void  Page_Load( object  sender, EventArgs e)
        {
             if  ( ! IsPostBack)
            {
            }
        }

         protected   void  Button1_Click( object  sender, EventArgs e)
        {
            CreateTable();
        }
         public   void  CreateTable()
        {
             string  sConnectionString  =   " Provider=Microsoft.Jet.OLEDB.4.0; "   + " Data Source= "   +  File1.PostedFile.FileName.ToString()  +   " ; "   + " Extended Properties=Excel 8.0; " ;

            OleDbConnection objConn  =   new  OleDbConnection(sConnectionString);

            objConn.Open();

            OleDbCommand objCmdSelect  =   new  OleDbCommand( " SELECT fid, fname, fdatetime FROM [Sheet1＄] " , objConn);

            OleDbDataAdapter objAdapter1  =   new  OleDbDataAdapter();

            objAdapter1.SelectCommand  =  objCmdSelect;

            DataSet objDataset1  =   new  DataSet();

            objAdapter1.Fill(objDataset1,  " test " );

             this .GridView1.DataSource  =  objDataset1.Tables[ 0 ].DefaultView;

             this .GridView1.DataBind();

            DataTable dt  =  objDataset1.Tables[ 0 ];

            DataView myView  =   new  DataView(dt);

            SqlConnection conn;

             string  dns  =  System.Configuration.ConfigurationManager.AppSettings[ " ConnectionString " ];

            conn  =   new  SqlConnection(dns);

            SqlCommand cmd  =  conn.CreateCommand();

            cmd.CommandType  =  CommandType.StoredProcedure;

            cmd.CommandText  =   " sp_transextosql " ;

             int  count  =   0 ;

             try
            {
                 foreach  (DataRowView myDrv  in  myView)
                {
                    count ++ ;
                     if  (conn.State.ToString()  !=   " Closed " )
                        conn.Close();
                    cmd.Parameters.Clear();

                    SqlParameter paraid  =  cmd.Parameters.Add( " @fid " , SqlDbType.Int);
                    SqlParameter paraname  =  cmd.Parameters.Add( " @fname " , SqlDbType.NVarChar, ( 50 ));
                    SqlParameter paradatetime  =  cmd.Parameters.Add( " @fdatetime " , SqlDbType.DateTime);

                    paraid.Direction  =  ParameterDirection.Input;
                    paraname.Direction  =  ParameterDirection.Input;
                    paradatetime.Direction  =  ParameterDirection.Input;

                    paraid.Value  =  Convert.ToInt32(myDrv[ 0 ]);
                    paraname.Value  =  Convert.ToString(myDrv[ 1 ].ToString());
                    paradatetime.Value  =  Convert.ToDateTime(myDrv[ 2 ]);

                    conn.Open();
                    cmd.ExecuteNonQuery();

                }
            }
             catch  (Exception ex)
            {
                Page.Response.Write( " alert('第 "   +  count.ToString()  +   " 条数据出错!'); " );
                objConn.Close();
                 throw  ex;
            }
             finally
            {
                objConn.Close();
            }
        }
    }
}

SQL PROC

CREATE   proc  sp_transextosql
( @fid   int ,
@fname   nvarchar ( 50 ),
@fdatetime   datetime )
as
insert   into  t_TransExcelToSQL(f_id, f_name, f_datetime)  values ( @fid , @fname , @fdatetime )

GO

转载于:https://www.cnblogs.com/anson2020/archive/2007/08/21/864258.html