c# 用OpenXmL读取.xlsx格式的Excel文件 返回DataTable

1.需要引用的dll :  DocumentFormat.OpenXml.dll  ---需要安装一下OpenXml再引用

                             WindowsBase  ---直接在项目里添加引用

2.方法：

/// <summary>  
        /// 读取.xlsx格式的Excel文件数据，读取其特定名称的工作薄  
        /// </summary>  
        /// <param name="filePath">文件路径 如 D:\\excel1.xls</param>  
        /// <param name="sheetName">工作薄名称 如 Sheet1</param>  
        /// <param name="fieldNames">要转化成的DataTable的列名数组</param>  
        /// <returns></returns>  
        public static DataTable ReadExcelToDataTable(Stream fileStream, string sheetName, string[] fieldNames)  
        {  
  
              using (var document = SpreadsheetDocument.Open(fileStream, false))  
            {  
                var sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(p => p.Name == sheetName);  
                var enumerable = sheets as Sheet[] ?? sheets.ToArray();  
                if (!enumerable.Any())  
                {  
                    return null;  
                }  
                var wsp = (WorksheetPart)document.WorkbookPart.GetPartById(enumerable.First().Id);  
                //获取Excel中共享数据  
                var stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;  
                var rows = wsp.Worksheet.Descendants<Row>();//获得Excel中得数据行  
                DataTable table = new DataTable();  
                foreach (var name in fieldNames)  
                {  
                    table.Columns.Add(name, typeof(string));  
                }  
                //因为须要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开端是行数据  
                var _count = 0;  
                var enumerable1 = rows as Row[] ?? rows.ToArray();  
                var total = enumerable1.Count() - 1;  
                foreach (var row in enumerable1)  
                {  
                    if (row.RowIndex > 1)  
                    {  
                        _count++;  
                        GetDataRow(row, stringTable, ref table, fieldNames); //Excel第二行同时为DataTable的第一行数据  
                        if (_count % 100 != 0) continue;  
                        var per = (100 * _count / total - 5) <= 0 ? 1 : (100 * _count / total - 5);  
                        var perS = per.ToString(CultureInfo.InvariantCulture) + "%";  
  
                        HttpContext.Current.Response.Write("<script>top.process('" + perS + "');</script>");  
                        HttpContext.Current.Response.Flush();  
                    }  
                }  
                return table;  
            }  
        }  
        /// 获取Excel行数据  
        private static void GetDataRow(IEnumerable<OpenXmlElement> row, OpenXmlElement stringTable, ref DataTable table, string[] fieldNames)  
        {  
            if (stringTable == null) throw new ArgumentNullException("stringTable");  
            var dic = new Dictionary<int, string>();  
            var i = 0;  
            foreach (Cell cell in row)  
            {  
                GetValue(i, cell, stringTable, ref dic);  
                i++;  
            }  
            if (dic.Count == 0)  
            {  
                return;  
            }  
            var dr = table.NewRow();  
            int index = 0;  
            foreach (var name in fieldNames)  
            {  
                dr[name] = dic[index];  
                index++;  
            }  
            table.Rows.Add(dr);  
        }  
        /// 获取Excel单元格数据         
        private static void GetValue(int i, CellType cell, OpenXmlElement stringTable, ref Dictionary<int, string> dic)  
        {  
            if (stringTable == null) throw new ArgumentNullException("stringTable");  
            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引  
            var value = string.Empty;  
            try  
            {  
                if (cell.ChildElements.Count == 0)  
                    return;  
                value = cell.CellValue.InnerText;  
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))  
                {  
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;  
                }  
                dic.Add(i, value);  
  
            }  
            catch (Exception)  
            {  
            }  
        }