使用NPOI读取Excel数据到DataTable
如今XML文件的存储格式大行其道,可是也不是适用于全部情况,非常多单位的数据交换还是使用Excel的形式。这就使得我们须要读取Excel内的数据。载入到程序中进行处理。可是如何有效率的读取,如何使程序保持健壮,这须要非常大的努力。
我们假设要写一个动态链接库会非常花费时间和精力。这就使得开源项目是个非常有效率的选择。
在各类关于Excel的开源项目中NPOI是中国的程序猿发起的。他的一大优点是直接处理Ole文件,用户不必安装Office。如今发展到2.0还能够自己主动推断Excel文件版本号,我们自己推断文件后缀名的方式不是非常准确(用户可能自己更改)。我认为对于读取文件这个是非常方便的一个功能!
不足之处:对于大文件的读取速度还是有些慢,做了下測试,载入20W数据。大约10+M的Excel文件大约须要16S。转化数据须要6S,这个略微有些慢。读取文件流的地方还应该能够优化。
好了,废话少说,一下是我自己写的一个使用NPOI读取Excel数据的帮助类
using System; using System.Collections.Generic; using System.Data; using System.Globalization; using System.IO; using System.Linq; using System.Text; using System.Threading; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; namespace Vevisoft.Excel.Core { /// <summary> /// 使用NOPI读取Excel数据 /// </summary> public class ExcelImportCore { private IWorkbook _workbook; private string _filePath; public List<string> SheetNames { get; set; } public ExcelImportCore() { SheetNames = new List<string>(); //LoadFile(_filePath); } #region Excel信息 /// <summary> /// 获取Excel信息 /// </summary> /// <param name="filePath"></param> public List<string> LoadFile(string filePath) { var prevCulture = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; var stopwatch = new System.Diagnostics.Stopwatch(); stopwatch.Start(); _filePath = filePath; SheetNames = new List<string>(); using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { _workbook = WorkbookFactory.Create(fs); } stopwatch.Stop(); Console.WriteLine("ReadFile:" + stopwatch.ElapsedMilliseconds/1000 + "s"); return GetSheetNames(); } /// <summary> /// 获取SHeet名称 /// </summary> /// <returns></returns> private List<string> GetSheetNames() { var count = _workbook.NumberOfSheets; for (int i = 0; i < count; i++) { SheetNames.Add(_workbook.GetSheetName(i)); } return SheetNames; } #endregion #region 获取数据源 /// <summary> /// 获取全部数据,全部sheet的数据转化为datatable。 /// </summary> /// <param name="isFirstRowCoumn">是否将第一行作为列标题</param> /// <returns></returns> public DataSet GetAllTables(bool isFirstRowCoumn) { var stopTime = new System.Diagnostics.Stopwatch(); stopTime.Start(); var ds = new DataSet(); foreach (var sheetName in SheetNames) { ds.Tables.Add(ExcelToDataTable(sheetName, isFirstRowCoumn)); } stopTime.Stop(); Console.WriteLine("GetData:" + stopTime.ElapsedMilliseconds/1000 + "S"); return ds; } /// <summary> /// 获取第<paramref name="idx"/>的sheet的数据 /// </summary> /// <param name="idx">Excel文件的第几个sheet表</param> /// <param name="isFirstRowCoumn">是否将第一行作为列标题</param> /// <returns></returns> public DataTable GetTable(int idx, bool isFirstRowCoumn) { if (idx >= SheetNames.Count || idx < 0) throw new Exception("Do not Get This Sheet"); return ExcelToDataTable(SheetNames[idx], isFirstRowCoumn); } /// <summary> /// 获取sheet名称为<paramref name="sheetName"/>的数据 /// </summary> /// <param name="sheetName">Sheet名称</param> /// <param name="isFirstRowColumn">是否将第一行作为列标题</param> /// <returns></returns> public DataTable GetTable(string sheetName, bool isFirstRowColumn) { return ExcelToDataTable(sheetName, isFirstRowColumn); } #endregion #region 方法 /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; var data = new DataTable(); data.TableName = sheetName; int startRow = 0; try { sheet = sheetName != null ? _workbook.GetSheet(sheetName) : _workbook.GetSheetAt(0); if (sheet != null) { var firstRow = sheet.GetRow(0); if (firstRow == null) return data; int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { //.StringCellValue; var column = new DataColumn(Convert.ToChar(((int) 'A') + i).ToString()); if (isFirstRowColumn) { var columnName = firstRow.GetCell(i).StringCellValue; column = new DataColumn(columnName); } data.Columns.Add(column); } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null dataRow[j] = row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString(); } data.Rows.Add(dataRow); } } else throw new Exception("Don not have This Sheet"); return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } #endregion } }调用的方法例如以下:
private void toolStripButton1_Click(object sender, EventArgs e) { var importCore = new ExcelImportCore(); var opdiag=new OpenFileDialog(); tabControl1.TabPages.Clear(); if (opdiag.ShowDialog() == DialogResult.OK) { importCore.LoadFile(opdiag.FileName); var ds = importCore.GetAllTables(false); // for (int i = 0; i < importCore.SheetNames.Count; i++) { var tp=new TabPage {Text= Name = importCore.SheetNames[i]}; tabControl1.TabPages.Add(tp); //加入数据源 var dgv=new DataGridView { //AutoGenerateColumns = false, DataSource = ds.Tables[i], Dock = DockStyle.Fill }; tp.Controls.Add(dgv); } } }