读取Excel表格数据

一、
在工程中加入相关的Com组件
代码示例:
   ExcelObj=new Excel.Application();
    object missing=Type.Missing;
    Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
     this.textBoxExcelName.Text, missing, missing, missing,
     missing, missing, missing, missing, missing, missing, missing,
     missing,missing);
    Excel.Sheets sheets = theWorkbook.Worksheets;
    Excel.Worksheet datasheet=null;
    foreach(Excel.Worksheet sheet in sheets)
    {
     if(sheet.Name==textBoxSheetName.Text)
     {
      datasheet=sheet;
      break;
     }
    }
    if(null==datasheet)
    {
     MessageBox.Show(this,"没有名称为"+textBoxSheetName+"的Sheet.");
     return;
    }
    if(""==this.textBoxCellFrom.Text||""==this.textBoxCellTo.Text)
    {
     MessageBox.Show(this,"请输入编号起始单元格。");
     return;
    }
    Excel.Range range=datasheet.get_Range(this.textBoxCellFrom.Text,this.textBoxCellTo.Text);
    System.Array myvalues = (System.Array)(range.Cells.Value);//如果只有一个格(cellfrom==cellto)转成object即可.
    string[] codes=new string[myvalues.Length];
    int i=0;
    for (i = 1; i <= myvalues.Length; i++)
    {
     if (myvalues.GetValue(i, 1) == null)
      codes = "";
     else
      codes = (string)myvalues.GetValue(i, 1).ToString();
    }
二、经常需要在数据库与Execl之间互导数据。net时代，ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel，网上已经有很多类似的资源，最典型也是最简单的可能如下：（asp.net环境）
/// <summary>
        /// 获取Excel文件的Sheet1的名字
        /// </summary>
        /// <param name="strExcelFileName">Excel文件名（带全路径）</param>
        /// <returns>Sheet1的名字</returns>
        public static string getSheetName(string strExcelFileName)
        {
            Excel.Application app = null;
            string name;
            try
            {
                app = new ApplicationClass();
                app.Workbooks.Open(strExcelFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Sheets sheets = app.Worksheets;
                if ((sheets != null)
                    && (sheets.Count > 0))
                {
                    name = ((Excel._Worksheet)sheets.get_Item(1)).Name;
                }
                else
                {
                    name = "";
                }
            }
            catch (Exception ef)
            {
                name = "";
            }
            finally
            {
                if ((app != null)
                    && (app.Workbooks != null))
                {
                    app.Workbooks.Close();
                }
            }
            return name;
        }

        /// <summary>
        /// 读取Excel文件中Sheet1的内容
        /// </summary>
        /// <param name="fileName">Excel文件名</param>
        /// <returns>DataTable</returns>
        public static System.Data.DataTable getExcelSheetData(string fileName)
        {
            //  OLEDB字串
            string strConn = getConnStr(fileName);

            
            OleDbConnection conn=null;
            string name = "";
            try
            {
                
                //连接数据源
                conn = new OleDbConnection(strConn);

                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if ((dt != null)
                    && (dt.Rows != null)
                    && (dt.Rows.Count > 0)
                    && (dt.Columns != null)
                    && (dt.Columns.Count > 2))
                {
                    name = dt.Rows[0][2].ToString();
                    if ((name == null)
                        || (name.Equals("")))
                    {
                        throw new Exception("Sheet Name NULL!");
                    }
                }
                else
                {
                    throw new Exception("Get Sheet Name Error!");
                }
                
                string sql = string.Format("select * from [{0}]", name);  //Sql语句
                
                DataSet ds = new DataSet(); //定义存放的数据表
              
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn); //适配到数据源

                adapter.Fill(ds);

                if ((ds != null)
                    && (ds.Tables != null)
                    && (ds.Tables.Count > 0))
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ef)
            {
                throw ef;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public  static bool FileExists(string fileName, string type)
        {
            try
            {
                if ((type == null)
                    || (type.Equals("")))
                {
                    type = ".xls";
                }
                if ((fileName != null)
                    && (!fileName.Equals("")))
                {
                    if (!fileName.ToLower().EndsWith(type.ToLower()))
                    {
                        return false;
                    }
                }
                return File.Exists(fileName);
            }
            catch (Exception ef)
            {
                return false;
            }
        }

        /// <summary>
        /// 获取Excel的OLEDB字串
        /// </summary>
        /// <param name="fileName">Excel 文件名</param>
        /// <returns>OLEDB字串</returns>
        private static string getConnStr(string fileName)
        {
            string conn = "";
            try
            {
                conn = ReadConfig.getConnectionString("ExcelODBC");
            }
            catch (Exception ef)
            {
                conn="";
            }
            if ((conn == null)
                || (conn.Trim().Equals("")))
            {
                //源的定义
                conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";"
                    + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            }
            else
            {
                int t = conn.ToLower().IndexOf("data source");
                if (t > 0)
                {
                    int n = conn.ToLower().IndexOf(";", t);
                    if (n > 0)
                    {
                        string connb = conn.Substring(0, t - 1) + conn.Substring(n + 1, conn.Length - n - 1);
                        conn = connb;
                    }                    
                }
                conn = conn + "Data Source=" + fileName + ";";
            }
            return conn;
        }

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/houshen/archive/2009/10/05/4633529.aspx