C#：导入Excel通用类（Xlsx格式）

• 一、引用插件EPPlus.dll

　　　　   插件下载地址：https://pan.baidu.com/s/1mkgQajI  提取密码：5fqn

 

• 二、定义通用类XlsxExcelData.cs

 

 

public class XlsxExcelData:IExcelData
    {
        #region 属性值
        private Dictionary<string, DataTable> m_tableDic;
        public Dictionary<string, DataTable> DataTableDictionary
        {
            get { return m_tableDic; }
        }
        public List<string> SheetNames
        {
            get
            {
                if (m_tableDic == null)
                    return null;
                return m_tableDic.Keys.ToList();
            }
        }
        public List<DataTable> DataTableList
        {
            get
            {
                if (m_tableDic == null)
                    return null;
                return m_tableDic.Values.ToList();
            }
        }
        public int SheetCount
        {
            get
            {
                if (m_tableDic == null)
                    return 0;
                return SheetNames.Count;
            }
        }
        private string m_filePath;
        public string FilePath
        {
            get { return m_filePath; }
        }
        private Stream m_stream;
        public Stream ExcelStream
        {
            get { return m_stream; }
        }
        public ExcelType ExcelType
        {
            get { return Interface.ExcelType.xlsx; }
        }
        #endregion
        #region 构造
        public XlsxExcelData(string path)
        {
            m_filePath = path;
        }
        public XlsxExcelData(Stream stream)
        {
            m_stream = stream;
        }
        #endregion
        #region 方法
        public List<Dictionary<string, string>> DataTableToDictionary(DataTable dt)
        {
            List<Dictionary<String, String>> dicList = new List<Dictionary<string, string>>();
            foreach (DataRow row in dt.Rows)
            {
                Dictionary<String, String> dic = new Dictionary<String, String>();
                foreach (DataColumn col in dt.Columns)
                {
                    dic.Add(col.ColumnName, row[col].ToString());
                }
                dicList.Add(dic);
            }
            return dicList;
        }

        /// <summary>
        /// 执行方法
        /// </summary>
        public void Load()
        {
            if (m_filePath != null)
                Load(m_filePath);
            else
                Load(m_stream);
        }

        /// <summary>
        /// 执行方法
        /// </summary>
        /// <param name="path">文件路径</param>
        public void Load(string path)
        {
            m_filePath = path;
            ExcelPackage package = null;
            try
            {
                using (FileStream fs=File.Open(path,FileMode.OpenOrCreate,FileAccess.Read))
                {
                    package = new ExcelPackage(fs);
                }
            }
            catch
            {
                throw;
            }
            LoadExcel(package);
            package.Dispose();
        }

        /// <summary>
        /// 执行方法
        /// </summary>
        /// <param name="stream">文件流</param>
        private void Load(Stream stream)
        {
            m_stream = stream;
            ExcelPackage package = null;
            try
            {
                package = new ExcelPackage(stream);
            }
            catch
            {
                throw;
            }
            LoadExcel(package);
            package.Dispose();
        }

        /// <summary>
        /// 获取Excel对应字典
        /// </summary>
        /// <param name="package"></param>
        private void LoadExcel(ExcelPackage package)
        {
            m_tableDic = new Dictionary<string, DataTable>();
            var sheets = package.Workbook.Worksheets.GetEnumerator();
            while (sheets.MoveNext())
            {
                DataTable dt = new DataTable();
                var sheet = sheets.Current;
                string name = sheet.Name;
                if (sheet.Dimension!=null)
                {
                    for (int i = 1; i <=sheet.Dimension.Columns ; i++)
                    {
                        string header = ExcelHeaderGenerator.GetExcelHeader(i);
                        DataColumn col = new DataColumn(header);
                        dt.Columns.Add(col);
                    }
                    for (int i = 1; i <= sheet.Dimension.Rows ; i++)
                    {
                        try
                        {
                            ExcelRow excelRow = sheet.Row(i);
                            DataRow dRow = dt.NewRow();
                            for (int j = 1; j <=sheet.Dimension.Columns; j++)
                            {
                                ExcelRange range = sheet.Cells[i, j];
                                if (string.IsNullOrEmpty(range.Text))
                                {
                                    dRow[j - 1] = string.Empty;
                                }
                                else
                                {
                                    dRow[j - 1] = range.Text;
                                }
                            }
                            dt.Rows.Add(dRow);
                        }
                        catch (Exception e)
                        {
                            throw;
                        }
                    }
                }
                m_tableDic.Add(name, dt);
            }
        }

        /// <summary>
        /// 获取第一列
        /// </summary>
        /// <returns></returns>
        public List<Dictionary<string, string>> GetFirstRecords()
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
            DataTable dt = GetDataTable(0, 1);
            foreach (DataRow row in dt.Rows)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                foreach (DataColumn column in dt.Columns)
                {
                    dic.Add(column.ColumnName, row[column].ToString());
                }
                result.Add(dic);
            }
            return result;
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sheetIndex">工作薄索引</param>
        /// <param name="columnNum">列数</param>
        /// <returns></returns>
        public DataTable GetDataTable(int sheetIndex, int columnNum)
        {
            DataTable dt = GetDataTable(sheetIndex);
            Convert(columnNum, ref dt);
            return dt;
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sheetIndex">工作薄索引</param>
        /// <returns></returns>
        public DataTable GetDataTable(int sheetIndex)
        {
            if (m_tableDic == null)
                return null;
            if (sheetIndex >= SheetCount)
                throw new Exception("表格索引超出序列，当前索引数量为" + SheetCount);
            DataTable dt = DataTableList[sheetIndex];
            DataTable dt_copy = new DataTable();
            foreach (DataColumn col in dt.Rows)
            {
                dt_copy.Columns.Add(new DataColumn(col.ColumnName));
            }
            foreach (DataRow row in dt.Rows)
            {
                DataRow r = dt_copy.NewRow();
                r.ItemArray = row.ItemArray;
                dt_copy.Rows.Add(r);
            }
            return dt_copy;
        }

        /// <summary>
        /// 检测重复列
        /// </summary>
        /// <param name="colNum">列数</param>
        /// <param name="dt">DataTable</param>
        private void Convert(int colNum, ref DataTable dt)
        {
            if (colNum < 1)
                throw new Exception("指定作为标题的行数必须是大于0");
            if (colNum > dt.Rows.Count)
                throw new Exception("指定作为标题的行数不能大于表格的总行数" + dt.Rows.Count);
            List<string> columnArray = new List<string>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (columnArray.Contains(dt.Rows[colNum - 1].ItemArray[i].ToString()))
                    throw new Exception("指定为DataTable标题的行不能存在重复值，重复值为" + dt.Rows[colNum - 1].ItemArray[i].ToString());
                columnArray.Add(dt.Rows[colNum - 1].ItemArray[i].ToString());
            }
            int r = 0;
            for (int i = 0; i < colNum; i++)
            {
                dt.Rows.RemoveAt(i - r);
                r++;
            }
            for (int i = 0; i < columnArray.Count; i++)
            {
                dt.Columns[i].ColumnName = columnArray[i];
            }
        }
        #endregion
    }

　　

原著：清风一人醉 http://www.cnblogs.com/W--Jing/

以上方法可以个人分享研究！

不可做商业项目，违者必究！