利用Oledb读取Excel

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

View Code

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);
            }
        }
    }
}

秒客网

利用Oledb读取Excel

相关文章