C# WEB端，Excel导入

自已根据网上和公司的资料，改写的一份C# WEB端，以备后续自已用。

网上资料来源自：http://www.jb51.net/article/44743.htm

数据库：
    //创建表
    CREATE TABLE testExcel(
       ID INT primary key identity(1,1),
       NAME VARCHAR (20),   
       AGE  INT,              
       WORKUNIT  VARCHAR (200) ,
       ADDRESS  VARCHAR (200)
    );
    
前端代码：
    <table>
        <tr>
            <td >
                <asp:FileUpload ID="FileUpload1" runat="server" />
                <asp:Button ID="Button1" runat="server" Text="确定" OnClick="Button1_Click" />
            </td>
        </tr>
    </table>
    
服务端代码：
    需要引入：
        using System.IO;
        using System.Data;
        using System.Data.OleDb;
        using System.Configuration;
        using System.Data.SqlClient;
    //确定按钮单击事件
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fileName = FileUpload1.FileName;//获取文件名
        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" && IsXls != ".xlsx")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回
        }

        string savePath = Server.MapPath("~/file/");
        FileOperator(fileName, savePath);
        FileUpload1.SaveAs(savePath + fileName);
        System.Data.DataTable dt = GetExcelDatatable(savePath + fileName, "mapTable");        
        if (dt != null)
        {
            int count = InsetData(dt, savePath + fileName);
            if (count > 0)
            {
                Response.Write("<script>alert('成功导入 " + count + " 条数据')</script>");
            }
        }
    }
    
    /// <summary>  
    /// 文件操作  
    /// </summary>  
    /// <param name="fileName"></param>  
    /// <param name="savePath"></param>
    private void FileOperator(string fileName, string savePath)
    {
        if (!Directory.Exists(savePath))
        {
            Directory.CreateDirectory(savePath);
        }
        if (File.Exists(savePath + fileName))
        {
            File.Delete(savePath + fileName);
        }
    }
    
    /// <summary>
    /// Excel数据导入Datable
    /// </summary>
    /// <param name="fileUrl"></param>
    /// <param name="table"></param>
    /// <returns></returns>
    public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
    {
        //office2007之前 仅支持.xls
        //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
        //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
        const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

        System.Data.DataTable dt = null;
        //建立连接
        OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
        try
        {
            //打开连接
            if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);            

            //查询sheet中的数据
            string strSql = "select * from [Sheet1$]";
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, table);
            dt = ds.Tables[0];            
        }
        catch (Exception ex)
        {
            //throw ex;
            dt = null;
            Response.Write("<script>alert('发生异常,错误信息：" + ex.Message + "')</script> ");
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return dt;
    }
    
    /// <summary>
    /// 从System.Data.DataTable导入数据到数据库
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public int InsetData(System.Data.DataTable dt, string filePath)
    {
        int i = 0;
        string name = "";
        string age = "";
        string workUnit = "";
        string address = "";

        //获取数据库的连接配置
        string strConnection = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
        SqlConnection sqlConnection = new SqlConnection(strConnection);
        sqlConnection.Open();
        SqlTransaction str = sqlConnection.BeginTransaction();//利用事务处理 防止中断

        try
        {
            foreach (DataRow dr in dt.Rows)
            {
                name = dr["NAME"].ToString().Trim();
                age = dr["AGE"].ToString().Trim();
                workUnit = dr["WORKUNIT"].ToString().Trim();
                address = dr["ADDRESS"].ToString().Trim();
                
                /*
                若这几个字段为空的话，结束本次循环,
                原因：
                    1. name，它若为空，则说明是空数据。
                    2. age，它是INT类型，为空的会报错。                    
                */
                if (name == "" || age == "")
                {
                    continue;
                }

                age = string.IsNullOrEmpty(age) ? "null" : age;
                kr = string.IsNullOrEmpty(kr) ? "null" : kr;

                string strSql = string.Format("Insert into testExcel (NAME,AGE,WORKUNIT,ADDRESS) Values ('{0}',{1},'{2}','{3}')", name, age, workUnit, address);
                
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandText = strSql;
                sqlCmd.Connection = sqlConnection;
                sqlCmd.Transaction = str;
                i += sqlCmd.ExecuteNonQuery();
            }
            str.Commit();
        }
        catch (Exception ex)
        {
            //数据回滚
            str.Rollback();
            i = 0;
            Response.Write("<script>alert('发生异常,数据已回滚/n信息/n" + ex.Message + "')</script> ");
        }
        finally
        {
            sqlConnection.Close();
            //删除上传的文件
            File.Delete(filePath);
        }
        return i;
    }