之前读取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也很方便了。