C#读取Excel文件

之前读取Excel用的NPOI,返回List<dynamic>，操作起来很方便。但是数据量一大，就很慢，还出现内存不足的异常。

        public List<dynamic> Read(string path, string sheetName = "", bool isFirstRowColumn = true)        {            var list = new List<dynamic>();            string[] aryName = null;            IWorkbook workbook = null;            try            {                using (var fs = new FileStream(path, FileMode.Open, FileAccess.Read))                {                    if (path.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本                        workbook = new XSSFWorkbook(fs);                    else if (path.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本                        workbook = new HSSFWorkbook(fs);                    ISheet sheet = null;                    if (sheetName != null)                    {                        if (workbook != null) sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);                    }                    else                    {                        if (workbook != null) sheet = workbook.GetSheetAt(0);                    }                    if (sheet == null) return null;                    var firstRow = sheet.GetRow(0);                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数                    int startRow;                    if (isFirstRowColumn)                    {                        aryName = new string[cellCount];                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                        {                            var cell = firstRow.GetCell(i);                            if (cell == null) continue;                            var cellValue = cell.StringCellValue;                            if (cellValue == null) continue;                            aryName[i] = cellValue;                        }                        startRow = sheet.FirstRowNum + 1;                    }                    else                    {                        startRow = sheet.FirstRowNum;                    }                    //最后一列的标号                    var rowCount = sheet.LastRowNum;                    for (var i = startRow; i <= rowCount; ++i)                    {                        var row = sheet.GetRow(i);                        if (row == null) continue; //没有数据的行默认是null　　　　　　　                        dynamic o = new DynamicObjectExt();                        var verifyRow = false;                        for (int j = row.FirstCellNum; j < cellCount; ++j)                        {                            var name = aryName == null ? string.Format("Item{0}", j) : aryName[j];                            var value = row.GetCell(j) != null ? row.GetCell(j).ToString() : null;                            if (!string.IsNullOrEmpty(value))                            {                                value = value.Trim().Replace("\r", "").Replace("\n", "");                            }                            o.Set(name, value);                            if (!string.IsNullOrEmpty(value))                            {                                verifyRow = true;                            }                        }                        //过滤空行                        if (!verifyRow)                        {                            continue;                        }                        list.Add(o);                    }                }                return list;            }            catch (Exception ex)            {                FileHelper.WriteLog(ex.ToString(), string.Format(@"{0}\log.txt", AppConst.Single.LogName));                return null;            }        }

CategoryName = zj.类目名称.ToString();

使用的时候全是动态解析，动态类型很慢。读取了几万数据直接就崩溃了，改成了直接用OleDb读取，直接转为DataTable。

        DataTable dt = new DataTable();        //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ofd.FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //07版本        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";         //13版本        using (var oledbConn = new OleDbConnection(strConn))        {            oledbConn.Open();            var sheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });            var sheet = new string[sheetName.Rows.Count];            for (int i = 0, j = sheetName.Rows.Count; i < j; i++)            {                sheet[i] = sheetName.Rows[i]["TABLE_NAME"].ToString();            }            var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetNames + "$"), oledbConn);            adapter.Fill(dt);        }        return dt;

操作DataTable也很方便了。