读取Excel数据

来源：互联网发布：node addeventlistenwr 编辑：程序博客网时间：2024/04/29 19:40

Excel2007的连接字串与Excel2003的不同。

DataTable tbSheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
用来获得sheet的名字

using System;using System.IO;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;namespace ExcelDataReader{    public class ExcelDataReader    {        private static string BuildConnectionString(string filepath)        {            if (!File.Exists(filepath))            {                throw new FileNotFoundException("Cannot find file" + filepath);            }            switch (Path.GetExtension(filepath))            {                case ".xls":                    return "Provider=Microsoft.Jet.OleDb.4.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=YES'";                case ".xlsx":                    return "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" + filepath + "';Extended Properties='Excel 12.0;HDR=YES'";                default:                    throw new NotSupportedException("The file is not a valid excel file");            }        }        public static DataSet Read(string filepath)        {            DataSet dataset = new DataSet();            string connect = BuildConnectionString(filepath);            string query = string.Empty;            using (OleDbConnection connection = new OleDbConnection(connect))            {                if (connection.State == ConnectionState.Closed)                {                    connection.Open();                }                DataTable tbSheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                if (tbSheetName != null && tbSheetName.Rows.Count > 0)                {                    foreach (DataRow row in tbSheetName.Rows)                    {                        string sheetname = row["TABLE_NAME"].ToString();                        if (sheetname.EndsWith("$"))                        {                            sheetname = sheetname.TrimEnd('$');                        }                        query = string.Format("Select * from [{0}$]", sheetname);                        using (OleDbDataAdapter adp = new OleDbDataAdapter(query, connection))                        {                            DataTable table = new DataTable(sheetname);                            adp.Fill(table);                            dataset.Tables.Add(table);                        }                    }                }            }            return dataset;        }    }}