ExcelHelp 学习笔记一： C#读取Excel中数据

1.定义一个ExcelHelp静态文件，提供系统全体调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ExcelApplication
{
    public static class ExcelHelp
    {
        //////该方式可以读取.xls以及.xlsx文件
        private const string connectionString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source={0};Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";

        //"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
　　　　　　
        private static OleDbConnection getOleDbConnection(string filePath)
        {
            return new OleDbConnection(string.Format(connectionString, filePath));
        }

        public static DataTable GetDatasByDataTable(string filePath, string sheetName)
        {
            DataTable datatable = new DataTable();

            OleDbConnection connection = getOleDbConnection(filePath);

            try
            {
                connection.Open();

                if (string.IsNullOrEmpty(sheetName))
                {
                    System.Data.DataTable SheetTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                    sheetName = SheetTable.Rows[0]["Table_Name"].ToString();

                    if (sheetName == "_xlnm#_FilterDatabase")
                    {
                        sheetName = SheetTable.Rows[1]["Table_Name"].ToString();
                    }

                    sheetName = sheetName.Remove(sheetName.Length - 1, 1);
                }

　　　　　　　　　/////在此处的SQL语句中，sheetName必须使用“[sheetName$]”进行打包起来，否则读取不了数据
                string strExcel = string.Format("select * from [{0}$]", sheetName);

                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connection);

                DataSet ds = new DataSet();
                adapter.Fill(ds, sheetName);
                datatable = ds.Tables[sheetName];

                return datatable;
            }
            catch (Exception e)
            {
                throw new Exception("读取Excel数据失败");
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

2.在需要的地方调用该方法获取Excel中的数据

           string filePath = this.textBox1.Text.Trim();

            if (string.Empty.Equals(filePath))
            {
                MessageBox.Show("请选择Excel文件");
                return;
            }

            DataTable result = ExcelHelp.GetDatasByDataTable(filePath, null);

            for (var i = 0; i < result.Rows.Count; i++)
            {
                for (var j = 0; j < result.Columns.Count; j++)
                {
                    var item = result.Rows[i][j].ToString();
　　　　　　　　　　　　////此处的item 就是获取到的Excel中的值，可以用于做业务数据处理
                }
            }