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)            {            }        }