导入Excel数据！

 

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

using System.Data;
using System.Data.Common;
using System.Data.OleDb;

using Excel = Microsoft.Office.Interop.Excel;

namespace Action.Common
{
    public class OfficeUtil
    {
        /// <summary>
        /// 使用OLEDB的方式读EXCEL文件内容，返回DataSet集合
        /// </summary>
        /// <param name="filename">EXCEL文件名，包含完整路径</param>
        /// <param name="hasTitle">EXCEL文件第一行是否是标题，true表示第一行是标题</param>
        /// <param name="version">OLEDB使用的EXCEL连接的版本，请输入"8.0"</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelRead(string filename, bool hasTitle, string tableName, string version)
        {
            string titleStr = hasTitle ? "Yes" : "No";
            if (string.IsNullOrEmpty(version))
            {
                version = "8.0";
            }
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = "[Sheet1$]";
            }
            else if (tableName.IndexOf("{1}quot;) == -1)
            {
                tableName ="["+tableName+ "{1}quot;+"]";
            }

            string connStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0.0;Data Source={0};Extended Properties='Excel {1};HDR={2};IMEX=1'", filename, version, titleStr);
            DataSet ds = null;
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {               
                try
                {
                    conn.Open();
                    string cmdStr = "select * from " + tableName;
                    OleDbDataAdapter da = new OleDbDataAdapter(cmdStr, connStr);
                    ds = new DataSet();
                    da.Fill(ds, "table1");
                }
                catch
                {
                    if (!conn.State.ToString().ToLower().Equals("open"))
                    {
                        conn.Open();
                    }
                    tableName = filename.Substring(filename.LastIndexOf("/") + 1, filename.Length - filename.LastIndexOf("/") - 1);
                    tableName = "[" + tableName.Substring(0, tableName.IndexOf(".")) + "$]";
                    string cmdStr = "select * from " + tableName;
                    OleDbDataAdapter da = new OleDbDataAdapter(cmdStr, connStr);
                    ds = new DataSet();
                    da.Fill(ds, "table1");
                }
                finally
                {
                    if (conn.State.ToString().ToLower().Equals("open"))
                    {
                        conn.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 使用OFFICE提供的方式读EXCEL文件内容，返回DataSet集合
        /// </summary>
        /// <param name="filename">EXCEL文件名，包含完整路径</param>
        /// <param name="hasTitle">EXCEL文件第一行是否是标题，true表示第一行是标题</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelRead(string filename, bool hasTitle)
        {
            Excel.Application app = new Excel.Application();
            if (null == app)
            {
                return null;
            }
            Excel.Workbook wb = null;
            try
            {
                wb = app.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch
            {
                app.Quit();
                app = null;
                return null;
            }
            Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
            if (null == ws)
            {
                wb = null;
                app.Quit();
                app = null;
                return null;
            }

            int rowCount = ws.UsedRange.Rows.Count;
            int colCount = ws.UsedRange.Columns.Count;
            if ((hasTitle && rowCount < 2) || (!hasTitle && rowCount < 1))
            {
                return null;
            }

            int rowIndexBegin = 1;
            int colIndexBegin = 1;

            string cell1 = "A1";

            string cell2Column = ((char)((colCount % 26) + 64)).ToString();
            string cell2Row = rowCount.ToString();
            string cell2 = cell2Column + cell2Row;

            Excel.Range range = ws.get_Range(cell1, cell2);
            DataTable dt = new DataTable("table1");

            for (int j = colIndexBegin; j < colCount + 1; j++)
            {
                if (hasTitle)
                {
                    Excel.Range r = range[rowIndexBegin, j] as Excel.Range;
                    dt.Columns.Add(r.Value2.ToString());
                }
                else
                {
                    dt.Columns.Add("col" + j.ToString());
                }
            }

            if (hasTitle)
            {
                rowIndexBegin++;
            }

            for (int i = rowIndexBegin; i < rowCount + 1; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = colIndexBegin; j < colCount + 1; j++)
                {
                    Excel.Range r = range[i, j] as Excel.Range;
                    dr[j - 1] = r.Value2;
                }
                dt.Rows.Add(dr);
            }

            ws = null;
            wb = null;
            app.Quit();
            app = null;

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            return ds;

        }


    }
}