excel数据读取到数据库

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using PublicProject.ENTITY;
using System.Data;
using System.IO;
using System.Data.OleDb;
using PublicProject.BLL;

public partial class 使用Excel读取数据 : System.Web.UI.Page
{
    //集合
    private List<School> schoolList = new List<School>();
    private DataSet dsall = new DataSet();
    private int count = 0;
    private SchoolBll schoolBll = new SchoolBll();
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    //public void ReaderXls()
    //{
    //    string xlsFilePath = @"F:\项目代码\公用架构\PublicProjectSolution\PublicProjectWeb\xls\DateReader.xls";
    //    Excel
    //}
    

    /// <summary>
    /// 单击导入按钮的事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnImport_Click1(object sender, EventArgs e)
    {
        string fileName = string.Empty;
        try
        {
           fileName= UpLoadXls(FileExcel);
           ImportXlsToData(fileName);//将XLS文件的数据导入数据库  
           if (fileName != string.Empty && System.IO.File.Exists(fileName))
           {
               System.IO.File.Delete(fileName);//删除上传的XLS文件
           }
           LblMessage.Text = "数据导入成功！";
           this.gvimport.DataSource = schoolList;
           this.gvimport.DataBind();
           this.lbimport.Text = schoolList.Count.ToString();
           int all = Convert.ToInt32(this.lballcount.Text.ToString());
           int import = Convert.ToInt32(this.lbimport.Text.ToString());
           int unimport = all - import;
           if (unimport > count)
           {
               this.lbunimport.Text = "," + unimport.ToString() + "条，由于传输原因未导入到数据库！";
           }
        }
        catch (Exception ex)
        {

            LblMessage.Text=ex.Message;
        }
    }


    /// <summary>
    /// 上传Excel文件
    /// </summary>
    /// <param name="inputfile">上传的控件名</param>
    /// <returns></returns>
    private string UpLoadXls(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("~/xls/XlsUpLodes");
                //新文件名
                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;
    }


    /// <summary>
    /// 将上传文件中的数据读取到数据库中
    /// </summary>
    /// <param name="fileName">上传的文件的地址</param>
    private void ImportXlsToData(string fileName)
    {
        try
        {
            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();

            AddDatasetToSQL(ds, 8);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }





    /// <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)
        {
            for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
            {
                School model = new School();
                string schooolid = pds.Tables[0].Rows[i][3].ToString();

                model.Email = pds.Tables[0].Rows[i][0].ToString();
                model.Pass= pds.Tables[0].Rows[i][1].ToString();
                model.RealName = pds.Tables[0].Rows[i][2].ToString();
                model.School1 = pds.Tables[0].Rows[i][3].ToString();
                model.ColSchool = pds.Tables[0].Rows[i][4].ToString();
                model.SubName = pds.Tables[0].Rows[i][5].ToString();
                model.GradeName = pds.Tables[0].Rows[i][6].ToString();
                model.ClassName = pds.Tables[0].Rows[i][7].ToString();
                //excel的格式：必须是英文列头
                //EMAIL 密码     姓名       学校    学院     专业     年级   班级 
                //email  PASS  REAL_NAME SCHOOL  COL_NAME SUB_NAME GRADE  CLASS
                // 0   1      2        3       4       5        6      7
                schoolBll.Add(model);
                schoolList.Add(model);
            }
            //全部信息
            this.gvgetall.DataSource = schoolList;
            this.gvgetall.DataBind();
            dsall = pds;
            this.lballcount.Text = pds.Tables[0].Rows.Count.ToString();
        }
        else
        {
            throw new Exception("导入数据为空！");
        }
        return true;
    }


    /// <summary>
    /// 插入数据到数据库
    /// </summary>
    //public void Add(ccwu.Model.T_RESUME_INFO model)
    //{
    //    string sql = "select * from T_STUDENT_INFO where email='" + model.EMAIL.ToString() + "'";//根据一个号去查询
    //    DataSet ds = ccwu.DBUtility.DbHelperSQL.Query(sql.ToString());
    //    //  int count = 0;
    //    if (ds.Tables[0].Rows.Count == 0)
    //    {
    //        //insert into PersonRecord
    //        ccwu.DAL.T_STUDENT_INFO dalstudent = new ccwu.DAL.T_STUDENT_INFO();
    //        ccwu.DAL.T_RESUME_INFO dalresume = new ccwu.DAL.T_RESUME_INFO();
    //        ccwu.Model.T_STUDENT_INFO m = new ccwu.Model.T_STUDENT_INFO();
    //        m.EMAIL = model.EMAIL.ToString();
    //        m.PASS = model.pass.ToString();
    //        m.SCHOOL_INFO_ID = Convert.ToInt32(model.SCHOOL_INFO_ID.ToString());
    //        //向学生注册表里
    //        int lastid = dalstudent.Add(m);
    //        model.STUDENT_INFO_ID = lastid;
    //        //向简历表里添加信息
    //        dalresume.AddForAll(model);
    //        list.Add(model);
    //    }
    //    if (ds.Tables[0].Rows.Count > 0)
    //    {
    //        count = count + 1;
    //    }

    //}















    protected void gvgetall_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {

    }
    protected void gvimport_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {

    }
}

转载于:https://www.cnblogs.com/wei2yi/archive/2011/07/07/2100087.html