C# OleDb读取Excel数据

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace SYS_TEST.BaseClass
{
    ///OleDB方式
    ///优点：将Excel直接当做数据源处理，通过SQL直接读取内容，读取速度较快。
    ///缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个Sheet页读取出来后（结果为Datatable）再在Datatable中根据行列数来获取指定的值。
    public class OLEDBClass
    {
        /// <summary>
        /// 获取EXCEL的Sheet表名
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static object[] GetExcelSheetName(string fileName)
        {
            ArrayList sheetNames = new ArrayList();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    conn.Close();
                    for (int i = 0; i != dt.Rows.Count; i++)
                    {
                        sheetNames.Add(dt.Rows[i]["Table_Name"].ToString());
                    }
                }
            }
            catch { }
            return sheetNames.ToArray();
        }
        /// <summary>
        /// Excel转换成DataSet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileName)
        {
            DataSet ds = new DataSet();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    string comm = "select * from [sheet1$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                }
            }
            catch { }
            return ds;
        }
        /// <summary>
        /// DataSet更新到Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="oldDs"></param>
        public static void DataSetToExcel(string fileName, DataSet oldDs)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
            conn.Open();
            string comm = "select * from [sheet1$]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
            //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
            builder.QuotePrefix = "[";//获取insert语句中保留字符（起始位置）
            builder.QuoteSuffix = "]";//获取insert语句中保留字符（结束位置）

            DataSet newDs = new DataSet();
            myCommand.Fill(newDs, "table1");
            for (int i = 0; i < oldDs.Tables[0].Rows.Count; i++)
            {
                //此处不能使用ImportRow方法将一行导入到newDs中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
                //在使用ImportRow后newds内有值但不能更新到Excel中，因为所有导入行的DataRowState!=Added
                DataRow newDr = newDs.Tables["Table1"].NewRow();
                for (int j = 0; j < newDs.Tables[0].Columns.Count; j++)
                {
                    newDr[j] = oldDs.Tables[0].Rows[i][j];
                }
                newDs.Tables["Table1"].Rows.Add(newDr);
            }
            myCommand.Update(newDs, "Table1");
            conn.Close();
        }
        /// <summary>
        /// 将Excel读取到DataSet（多Sheet表）
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataTable(string fileName)
        {
            //选择源
            string strConn = "";
            strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
            if (Path.GetExtension(fileName).Trim().ToUpper() == ".XLSX")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            }
            //定义数据集
            DataSet ds = new DataSet();
            //连接数据源
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                string strSheetName = schemaTable.Rows[i][2].ToString().Trim().Trim('\'');
                if (strSheetName.Contains("$") && !strSheetName.EndsWith("$"))
                    continue;
                if (schemaTable.Rows[i][3].ToString().Trim().ToUpper().Equals("TABLE"))
                {
                    //Sql语句
                    string strExcel = string.Format("select * from [{0}]", strSheetName);
                    //适配到数据源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
            }
            conn.Close();
            //删除空的Excel表单
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (dt.Columns.Count == 1 && dt.Columns[0].ColumnName.Trim().ToUpper() == "F1")
                {
                    ds.Tables.Remove(dt);
                    i--;
                }
            }
            return ds;
        }
    }
}