c#读取excel文件内容

1. 使用System.Data方式读取

这种方式读取只适合在windows中使用，且依赖于office的组件

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace DataCollect
{
    /// <summary>
    /// 读取excel信息的类
    /// </summary>
    internal class ExcelUtils
    {

        /// <summary>
        /// 读取excel及csv表格的内容
        /// </summary>
        /// <param name="pathName">文件路径</param>
        /// <param name="sheetName">sheet的name，如果只有一个或不指定，则传空</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataTable ExcelToDataTable(string pathName, string sheetName)
        {
            int symbol = 0;
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty; //创建一个空的 string（字段）对象 strConn；
            if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) {
                LogUtils.Error("文件不存在");
                throw new Exception("文件不存在"); }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".CSV":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetName + ";Extended Properties='Text;FMT=Delimited;HDR=YES'";
                    symbol = 1;
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            //链接Excel
            OleDbConnection cnnxls = new OleDbConnection(strConn);

            if (symbol == 0)
            {
                OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
                //将Excel里面有表内容装载到内存表中！
                oda.Fill(tbContainer);
            }
            else
            {
                OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from " + pathName, strConn);
                myCommand.Fill(tbContainer);
            }
            // 判断行的数据是否为null，如果是，则进行删除该行
            List<DataRow> removeRowList = new List<DataRow>();
            foreach (DataRow r  in tbContainer.Rows) {
                if (r.ItemArray[0] == null || string.IsNullOrWhiteSpace(r.ItemArray[0].ToString())) {
                    removeRowList.Add(r);
                }
            }
            if (removeRowList.Count > 0) {
                foreach (DataRow r in removeRowList) {
                    tbContainer.Rows.Remove(r);
                }
            }
            return tbContainer;
        }
    }
}

2. 使用NPOI

首先需要引用NPOI的依赖，我使用的是NPOI 2.5.6。具体代码如下：

using System.Collections.Generic;
using System.Configuration;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace DataCollect
{
    internal class ExcelUtil
    {
        /// <summary>
        /// 加载excel信息
        /// <param name="filePath">文件路径</param>
        /// </summary>
        public static List<Dictionary<string, string>>ReadExcel(string filePath)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new XSSFWorkbook(fs);
                ISheet worksheet = workbook.GetSheetAt(0);
                Dictionary<int, string> colums = new Dictionary<int, string>();
                // 读取每一行每一列的数据
                for (int row = 0; row <= worksheet.LastRowNum; row++)
                {
                    IRow currentRow = worksheet.GetRow(row);
                    if (row == 0)
                    {
                        for (int col = 0; col < currentRow.LastCellNum; col++)
                        {
                            ICell currentCell = currentRow.GetCell(col);
                            if (currentCell != null)
                            {
                                string cellValue = currentCell.ToString();
                                colums.Add(col, cellValue);
                            }
                        }
                    }
                    else
                    {
                    Dictionary<string, string> resultMap = new Dictionary<string, string>();
                        for (int col = 0; col < currentRow.LastCellNum; col++)
                        {
                            ICell currentCell = currentRow.GetCell(col);
                            if (currentCell != null)
                            {
                                string data = currentCell.ToString();
                                string columName = colums[col];
                                resultMap.put(columName, data);
                            }
                        }
                        result.add(resultMap);
                    }
                }
            }
            // 使用完删除解密后的文件
            File.Delete(filePath);
            return result;
        }