C# Excel 交互類

2019-11-17 04:07:59

字體：大中小

來源：轉載

供稿：網友

view plaincopy to clipboardPRint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace MyExcel
{
    public class ExcelConnector
    {
        string connString;
        public ExcelService(string path)
        {
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
        }

        private OleDbConnection conn;
        public OleDbConnection Connection
        {
            get
            {

                if (conn == null)
                {
                    conn = new OleDbConnection(connString);
                    conn.Open();
                }
                else if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
                else if (conn.State == ConnectionState.Closed)
                {
                    conn = new OleDbConnection(connString);
                    conn.Open();
                }
                return conn;
            }

        }
        /// <summary>
        /// 獲取Excel 中的工作表
        /// </summary>
        /// <returns></returns>
        public List<string> GetDataFromExcelWithAppointSheetName()
        {
            DataTable dtSheetName = null;
            try
            {
                dtSheetName = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                List<string> strTableNames = new List<string>();
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    string s = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    Console.WriteLine(s);
                    //過濾一下沒用的表，Excel 默認生成的隱藏文件
                    if (!s.Contains("_FilterDatabase") && s.LastIndexOf('_') + 1 != s.Length)
                    {
                        strTableNames.Add(s);
                    }
                }
                return strTableNames;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Connection.Dispose();
            }
        }
        /// <summary>
        /// 通過工作表名獲取數據
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetContentBySheetName(string name)
        {
            DataTable dt = new DataTable();
            OleDbDataAdapter myCommand = null;
            string strExcel = "select * from [" + name + "]";
            try
            {
                myCommand = new OleDbDataAdapter(strExcel, Connection);
                dt = new DataTable();
                myCommand.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                myCommand.Dispose();
                Connection.Dispose();


            }

        }



    }
}

上一篇：ASP.NET中調用存儲過程帶(輸入/輸出)參數和返回值的一些常用的例子

下一篇：C#.NET連接mysql方法