Excel导入导出数据库

protected string col;//数据库表名

#region 导入按钮事件
    protected void BtnUpload_Click(object sender, EventArgs e)
    {
        string filename = string.Empty;
        try
        {
            filename = UploadXml(FileExcel);
            ImportXlsToData(filename);//将XLS文件的数据导入数据库
            if (filename != string.Empty && System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);//删除上传的XLS文件
            }
            LblMessage.Text = "数据导入成功！";
            System.IO.File.Delete(filename);//删除上传的XLS文件
        }
        catch (Exception ex)
        {
            LblMessage.Text = ex.Message;
        }
    }
    #endregion

    #region 上传Excel文件
    /// <summary>
    /// 上传Excel文件
    /// </summary>
    /// <param name="inputfile">上传的控件名</param>
    protected string UploadXml(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
    {
        string orifilename = string.Empty;
        string uploadfilepath = string.Empty;
        string modifyfilename = string.Empty;
        string fileExtend = "";//文件扩展名
        int fileSize = 0;//文件大小
        try
        {
            if (inputfile.Value != string.Empty)
            {
                //得到文件的大小
                fileSize = inputfile.PostedFile.ContentLength;
                if (fileSize == 0)
                {
                    throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                }
                //得到扩展名
                fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                if (fileExtend.ToLower() != "xls")
                {
                    throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                }
                //路径
                uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
                //新文件名
                modifyfilename = System.Guid.NewGuid().ToString();
                modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                //判断是否有该目录
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                if (!dir.Exists)
                {
                    dir.Create();
                }
                orifilename = uploadfilepath + "//" + modifyfilename;
                //如果存在,删除文件
                if (File.Exists(orifilename))
                {
                    File.Delete(orifilename);
                }
                //上传文件
                inputfile.PostedFile.SaveAs(orifilename);
            }
            else
            {
                throw new Exception("请选择要导入的Excel文件!");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return orifilename;
    }
    #endregion

    #region 插入数据
    public void Add(DataSet ds)
    {
        col = this.RadioList.SelectedValue;
        int ii = ds.Tables[0].Columns.Count;
        string sql = "select top "+ii+" name from dbo.syscolumns where id = object_id(N'" + col + "')";
        DataTable dt = dbc.GetDataTableBySql(sql);
        StringBuilder strSql = new StringBuilder();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            strSql.Append("insert into " + col + "(");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                if (j == dt.Rows.Count - 1)
                {
                    strSql.Append("" + dt.Rows[j][0].ToString() + "");
                }
                else
                {
                    strSql.Append("" + dt.Rows[j][0].ToString() + ",");
                }
            }
            strSql.Append(")");
            strSql.Append("values");
            strSql.Append("(");
            for (int a = 0; a < ii; a++)
            {
                if (a == ii - 1)
                {
                    strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "'");
                }
                else
                {
                    strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "',");
                }
            }
            strSql.Append(")");
        }
        dbc.ExecuteSql(strSql.ToString());
    }
    #endregion

    #region 将Dataset的数据导入数据库
    /// <summary>
    /// 将Dataset的数据导入数据库
    /// </summary>
    /// <param name="pds">数据集</param>
    /// <param name="Cols">数据集列数</param>
    /// <returns></returns>
    private bool AddDatasetToSQL(DataSet pds, int Cols)
    {
        int ic, ir;
        ic = pds.Tables[0].Columns.Count;
        if (pds.Tables[0].Columns.Count < Cols)
        {
            throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
        }
        ir = pds.Tables[0].Rows.Count;
        if (pds != null && pds.Tables[0].Rows.Count > 0)
        {
            Add(pds);
        }
        else
        {
            throw new Exception("导入数据为空！");
        }
        return true;
    }
    #endregion

    #region 从excel中提取数据——dataset中
    /// <summary>
    /// 从excel中提取数据——dataset中
    /// </summary>
    /// <param name="fileName"></param>
    private void ImportXlsToData(string fileName)
    {
        try
        {
            col = this.RadioList.SelectedValue;
            if (fileName == string.Empty)
            {
                throw new ArgumentNullException("Excel文件上传失败！");
            }
            string oleDBConnString = string.Empty;
            oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
            oleDBConnString += "Data Source=";
            oleDBConnString += fileName;
            oleDBConnString += ";Extended Properties=Excel 8.0;";
            OleDbConnection oleDBConn = null;
            OleDbDataAdapter oleAdMaster = null;
            DataTable m_tableName = new DataTable();
            DataSet ds = new DataSet();
            oleDBConn = new OleDbConnection(oleDBConnString);
            oleDBConn.Open();
            m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (m_tableName != null && m_tableName.Rows.Count > 0)
            {

                m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();

            }
            string sqlMaster;
            sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
            oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
            oleAdMaster.Fill(ds, "m_tableName");
            oleAdMaster.Dispose();
            oleDBConn.Close();
            oleDBConn.Dispose();
            string sql = "select   count(*)   from   syscolumns   where   id   =  object_id( '"+col+"' )";
            int i = dbc.ExecuteSql(sql);
            AddDatasetToSQL(ds, i);

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    #endregion

    #region 导出按钮事件，查询条件可以添加
    protected void BtnDownload_Click(object sender, EventArgs e)
    {
        string sql = "select * from "+col+"";
        //if (this.keyword.Text.Trim() != "")
        //{
        //    this.keyword.Text = this.keyword.Text.Trim().Replace("'", "");
        //    sql += " where patindex('%" + this.keyword.Text + "%',username)>0";
        //}
        //if (pubfun.IsBlank(dgInfo.Sort))
        //{
        //    sql += " Order By addTime DESC";
        //}
        //else
        //{
        //    sql += " Order By " + dgInfo.Sort + " " + DataGridSortType;
        //}
        DataTable dt = this.dbc.GetDataTableBySql(sql);
        //this.TotalRecords = dt.Rows.Count;
        this.DataGrid1.DataSource = dt;
        this.DataGrid1.DataBind();
        dt.Dispose();

        this.DataGrid1.Visible = true;
        if (this.DataGrid1.Items.Count == 0)
        {
            pubfun.Alert("对不起,没有查询到任何记录,不能导出数据!", -1);
            return;
        }
        else
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("Temp.xls"));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文  
            // Response.ContentEncoding = System.Text.Encoding.UTF8;
            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.DataGrid1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();
        }
    }
    #endregion