ExcelToDataSet

来源：互联网发布：积分系统数据库编辑：程序博客网时间：2024/06/18 10:43

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.IO;

/// <summary>
/// ExcelToDataSet 的摘要描述
/// </summary>
public class ExcelToDataSet
{
private string _FilePath;

public ExcelToDataSet(string FilePath)
{
_FilePath = FilePath;
}

    /// <summary>
    /// Excel文件路径，在类构造函数初始化的时候确定此属性值。
    /// </summary>
    public string FilePath
    {
        get { return _FilePath; }
        set { _FilePath = value; }
    }

    public DataSet GetXMLDataSet()
    {
        //讀取Excel
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties='Excel 8.0; HDR=Yes;MEX=1';";
        OleDbConnection conn = new OleDbConnection(connectionString);

DataSet ds = new DataSet();

        try
        {
            conn.Open();
            string xlsSheetNames = GetXlsSheetNames(conn);

            foreach (string sheetName in xlsSheetNames.Split(','))
            {
                string str = "SELECT * FROM [" + sheetName + "$]";
                OleDbCommand cmd = new OleDbCommand(str, conn);

                OleDbDataAdapter da = new OleDbDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, sheetName);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }

    private string GetXlsSheetNames(OleDbConnection connection)
    {
        string sheetNames = string.Empty;
        DataTable schemeTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        string tableName = schemeTable.TableName;
        int rowCount = schemeTable.Rows.Count;
        string sheetName = string.Empty;
        int itemIndex = 0;

        try
        {
            while (itemIndex < rowCount)
            {
                DataRow row = schemeTable.Rows[itemIndex];
                //sheetName = row[2].ToString().Substring(1, row[2].ToString().Length - 2);
                sheetName = row[2].ToString();
                sheetName = sheetName.Replace("'", "").Replace("'", "").Trim();
                if (sheetName.Substring(sheetName.Length - 1, 1) == "$")
                {
                    System.Math.Min(System.Threading.Interlocked.Increment(ref itemIndex), itemIndex - 1);
                    sheetNames += sheetName;
                }
                else
                {
                    itemIndex = itemIndex + 1;
                }
            }

            if (sheetNames == null)
            {
                connection.Close();
            }

sheetNames = sheetNames.Replace("$", ",");

            if (sheetNames.EndsWith(","))
            {
                sheetNames = sheetNames.Substring(0, sheetNames.Length - 1);
            }
        }
        catch (Exception ex)
        {
            connection.Close();
            throw ex;
        }
        return sheetNames;
    }

}