读取Excel表到DateSet,将DataSet转换成List

namespace Demo.UI

{

    //建立一个对象与stu.xls其中的Sheet1表格的字段对应

    public class Man

    {

        public string Field1 { get; set; }

        public string Field2 { get; set; }

        public string Field3{ get; set; }

        public string Field4 { get; set; }

        public string Field5 { get; set; }

        public string Field6 { get; set; }

        public string Field7 { get; set; }

        public string Field8 { get; set; }

        public string Field9 { get; set; }

        public string Field10 { get; set; }

       

       

    }

 

 

 

 

 

 

 

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

 

            DataSet dataSet = ExcelToDataSet(@"E:/stu.xls", "sheet1");

            List<Man> mans = DataSetToIList<Man>(dataSet, 0) as List<Man>;

            this.GridView1.DataSource = mans;

            this.GridView1.DataBind();

        }

 

 

        //把DataSet转换成IList<T>泛型

        public IList<T> DataSetToIList<T>(DataSet dataSet, int index)

        {

            List<T> ts = new List<T>();

            DataTable dataTable = dataSet.Tables[index];//得到DataSet下的某一张表

            List<PropertyInfo> properties = typeof(T).GetProperties().ToList<PropertyInfo>();

            //通过泛型对象T得到其属性集合

 

            for (int i = 0; i < dataTable.Rows.Count; i++)//循环数据表dataTable中的每一行

            {

                T t = Activator.CreateInstance<T>();//通过反射实例化一个T的对象

 

                for (int j = 0; j <properties.Count ; j++)//循环属性集合

                {

                    properties[j].SetValue(t, dataTable.Rows[i][j].ToString(), null);

                    //分别将取到的值给T对象的每一个字段

                }

 

                ts.Add(t);

            }

 

            return ts;

           

        }

 

 

 

        //读取Excel表并返回一个DataSet

        public DataSet ExcelToDataSet(string filePath,string sheetName)

        {

            DataSet dataSet = new DataSet();

            string connectionString = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ;

Data   Source ={0};Extended Properties=Excel 8.0"

               , filePath);//通过ODBC连接数据的字符串，并将一个具体的Excel文件路径传入

            string sqlString = string.Format("Select * from [{0}$]", sheetName);

            //读取Excel文件中的某一页,页名称根据sheetName变量传入

            OleDbConnection connection = new OleDbConnection();

            connection.ConnectionString = connectionString;

            OleDbCommand command = new OleDbCommand();

            command.Connection = connection;

            command.CommandType = CommandType.Text;

            command.CommandText = sqlString;

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

            dataAdapter.SelectCommand = command;

            dataAdapter.Fill(dataSet);

            return dataSet;

        }

  

    }

}