利用Oledb读取Excel

     本文主要提供了一个利用Oledb读取一个Excel的类，如果有写得不当的地方，希望能得到大家的斧正。

 

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Common.Excel
{
    //连接字符串说明
    //HDR=Yes:将第一行作为DataTable的列名，根据该列的数据判断该列的数据类型
    //HDR=No:将所有行都作为数据，所有的数据类型都是string,空值为空字符串""
    //IMEX=0:汇出模式，这个模式开启的Excel档案只能用来做“写入”用途。
    //IMEX=1:汇入模式，这个模式开启的Excel档案只能用来做“读取”用途。
    //IMEX=2:连結模式，这个模式开启的Excel档案可同时支持“读取”与“写入”用途。

    public static class OleDbHandler
    {
        public static string[] GetSheetNames(string excelPath)
        {
            string connectionStr = GetConnectionStr(excelPath, true);
            return GetSheetNamesByOleDb(connectionStr);
        }

        public static DataSet ExcelToDataSet(string excelPath)
        {
            return ExcelToDataSet(excelPath, true);
        }

        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
        {
            string connectionStr = GetConnectionStr(excelPath, firstRowAsHeader);
            string[] sheetNames = GetSheetNamesByOleDb(connectionStr);

            using (DataSet ds = new DataSet())
            {
                foreach (string sheetName in sheetNames)
                {
                    //过滤隐藏表，Oledb读取表会在表后面加上$符号，对于一些有公式的sheet，OleDb会创建一个隐藏表，但这些表没有加上$符号
                    if (sheetName.EndsWith("$"))
                    {
                        DataTable dt = ExcelToDataTableByOleDb(connectionStr, sheetName);
                        ds.Tables.Add(dt);
                    }
                }

                return ds;
            }
        }

        public static DataTable ExcelToDataTable(string excelPath, string sheetName)
        {
            return ExcelToDataTable(excelPath, sheetName, true);
        }

        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
        {
            string connectionStr = GetConnectionStr(excelPath, firstRowAsHeader);
            return ExcelToDataTableByOleDb(connectionStr, sheetName);
        }

        private static DataTable ExcelToDataTableByOleDb(string connectionStr, string sheetName)
        {
            using (DataTable dt = new DataTable())
            {
                using (OleDbConnection conn = new OleDbConnection(connectionStr))
                {
                    OleDbDataAdapter da = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), connectionStr);

                    da.Fill(dt);
                    dt.TableName = sheetName;

                    return dt;
                }
            }
        }

        private static string[] GetSheetNamesByOleDb(string connectionStr)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionStr))
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string[] sheetNames = new string[dt.Rows.Count];

                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    sheetNames[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                }

                return sheetNames;
            }
        }

        private static string GetConnectionStr(string excelPath, bool firstRowAsHeader)
        {
            string suffix = Path.GetExtension(excelPath);

            string excelVersion;
            string provider;

            switch (suffix.ToLower())
            {
                case ".xls":
                    provider = "Microsoft.Jet.OLEDB.4.0";
                    excelVersion = "Excel 8.0";
                    break;
                case ".xlsx":
                    provider = "Microsoft.Ace.OleDb.12.0";
                    excelVersion = "Excel 12.0";
                    break;
                default:
                    throw new NotSupportedException(string.Format("The file extension[{0}] is not supported.", suffix));
            }

            if (firstRowAsHeader)
            {
                return string.Format("Provider={0};Data Source={1};Extended Properties='{2};HDR=Yes;IMEX=1'", provider, excelPath, excelVersion);
            }
            else
            {
                return string.Format("Provider={0};Data Source={1};Extended Properties='{2};HDR=No;IMEX=1'", provider, excelPath, excelVersion);
            }
        }
    }
}