【C#】采用OleDB读取Excel文件转DataTable

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace DaliyTest
{
    public class ExcelToDataTable : IWork
    {
        public void Work()
        {
            var path = AppDomain.CurrentDomain.BaseDirectory + "读取文件";

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            //文件路径
            var filePath = Directory.GetFiles(path).FirstOrDefault();
            var fileNames = filePath.IndexOf("\", StringComparison.Ordinal) != 0 ? filePath.Split('\') : new[] { filePath };

            //文件名
            var fileName = fileNames[fileNames.Length - 1];

            var ds = GetExcelData(filePath, fileName, "sheet名称");

            var newsList = new List<News>();

            foreach (DataRow item in ds.Tables[0].Rows)
            {
                var news = new News
                {
                    Name = StringUtil.NullToEmpty(item[""]),
                    Title = StringUtil.NullToEmpty(item[""]),
                    Link = StringUtil.NullToEmpty(item[""])
                };

                newsList.Add(newsNagative);
            };
        }

        /// <summary>
        /// 将Excel转为DataSet
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="filename">文件名</param>
        /// <param name="_sheetName">sheet名</param>
        /// <returns>DataSet</returns>
        public static DataSet GetExcelData(string filepath, string filename, string _sheetName)
        {
            var dtExcel = new DataSet();

            if (string.IsNullOrEmpty(filepath)) return dtExcel;

            try
            {
                string connString;
                var extension = Path.GetExtension(filepath);

                switch (extension)
                {
                    case ".xls":
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                                     ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                        break;
                    case ".xlsx":
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath +
                                     ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                        break;
                    default:
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                                     ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                        break;
                }

                using (var conn = new OleDbConnection(connString))
                {
                    conn.Open();

                    var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                        new object[] { null, null, _sheetName, null });
                    conn.Close();

                    var builder = new StringBuilder(string.Format("SELECT * FROM [{0}$] WHERE 1=1 ", _sheetName));

                    foreach (DataRow row in dt.Rows)
                    {
                        builder.AppendFormat(" AND {0}  IS NOT NULL AND {0} <> '' ", row["Column_name"]);
                    }

                    conn.Open();
                    using (var oledAdapter = new OleDbDataAdapter(builder.ToString(), conn))
                    {
                        oledAdapter.Fill(dtExcel);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Open xls file error

" + ex.Message, ex);
            }

            return dtExcel;
        }
    }
}

 public static class StringUtil
      {
        public static string NullToEmpty(object sValue)
        {
            if (sValue == DBNull.Value)
                return "--";
            return sValue == null ? "--" : ToDBC(sValue.ToString().Trim());
        }

        /// <summary>
        ///     转半角
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static String ToDBC(this String input)
        {
            var c = input.ToCharArray();
            for (int i = 0; i < c.Length; i++)
            {
                if (c[i] == 12288)
                {
                    c[i] = (char)32;
                    continue;
                }
                if (c[i] > 65280 && c[i] < 65375)
                    c[i] = (char)(c[i] - 65248);
            }
            return new String(c);
        }
    }

如果报错：未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序

本错误是由于你使用了ACCESS2007版本建立的数据库,但服务器中没有相配合使用的程序,所以出错.

解决办法1：（验证可以）

去http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe下载。然后安装就行了。

或者百度网盘下载 下载地址

解决办法2： (未验证)

选择 该应用程序的 应用程序池 ------>选择高级设置 --------->启用32位应用程序 ------->true