本文介绍了在Winform中使用NPOI(PIO项目下的.Net组件)来操作Excel文件，而无需安装Office。
要实现的效果是这样的：
1、导出Excel：根据指定datatable，弹出导出窗口，用户自定义路径、导出名，然后导出。
2、导入Excel：弹出导入窗口，用户自定义导入Excel文件，导入到datatable中。
首先，需要在NuGet程序包中搜索并下载NPOI组件，如下图所示：
再添加一个ExcelHelper操作类，网上很多例子，我简化了很多样式相关的代码，只留下主要功能，并且自测没问题，附上ExcelHelper操作类:
public class ExcelHelp{/// <summary>///将datatable流文件导出到指定路径的Excel中/// </summary>/// <param name="sourceTable"></param>public virtual void DataTableExportExcel(DataTable sourceTable){MemoryStream ms = DataTableToExcel(sourceTable);string saveFileName = "";bool fileSaved = false;SaveFileDialog saveDialog = new SaveFileDialog();//打开选择保存窗口saveDialog.DefaultExt = "xls";saveDialog.Filter = "Excel文件|*.xls";//保存窗口的文件筛选saveDialog.FileName = "MyExcel";//默认文件名称saveDialog.ShowDialog();saveFileName = saveDialog.FileName;if (saveFileName.IndexOf(":") < 0) return; //用户点了取消if (saveFileName != ""){try{FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);fs.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);ms.Close();ms.Dispose();fs.Close();fileSaved = true;}catch (Exception ex){fileSaved = false;MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);}}else{fileSaved = false;}GC.Collect();//强行销毁if (fileSaved && File.Exists(saveFileName)){MessageBox.Show("导出成功！", "通知");}else{MessageBox.Show("导出失败！", "通知");}}/// <summary>/// 根据指定流文件将Excel导入到datatable中/// </summary>public virtual void ExcelExportDataTable(){OpenFileDialog fileDialog = new OpenFileDialog();fileDialog.Filter = "Excel文件|*.xls";fileDialog.InitialDirectory = "E:\\";//设置默认打开路径if (fileDialog.ShowDialog() == DialogResult.OK){string fileName = fileDialog.FileName;//得到文件所在位置FileStream fs = new FileStream(fileDialog.FileName, FileMode.Open,FileAccess.Read);DataTable dt = ExcelToDataTable(fs,0,0);}}/// <summary>/// 将datatable中的数据放入内存流中/// </summary>/// <param name="souruceTable">来源datatable</param>/// <returns></returns>private MemoryStream DataTableToExcel(DataTable souruceTable){int rowCount = souruceTable.Rows.Count;int colCount = souruceTable.Columns.Count;HSSFWorkbook workbook = new HSSFWorkbook();//新建一个工作簿ISheet sheet = workbook.CreateSheet("mysheet");//新建一个sheet页IRow headerRow = sheet.CreateRow(0);MemoryStream ms = new MemoryStream();//构建标题行foreach (DataColumn col in souruceTable.Columns){headerRow.CreateCell(col.Ordinal,CellType.String).SetCellValue(col.Caption);}//构建数据行for (int i = 0; i < rowCount; i++){IRow dataRow = sheet.CreateRow(i + 1);for (int j = 0; j < colCount; j++){DataRow row = souruceTable.Rows[i];DataColumn col = souruceTable.Columns[j];dataRow.CreateCell(j).SetCellValue(row[col].ToString());}}workbook.Write(ms);ms.Flush();ms.Position = 0;sheet = null;headerRow = null;workbook = null;return ms;}/// <summary>/// 将excel数据流中的数据转化为datatable/// </summary>/// <param name="ExcelFileStream">指定流文件</param>/// <param name="SheetIndex">导入sheet页页号</param>/// <param name="HeaderRowIndex">行标题行号</param>/// <returns></returns>private DataTable ExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex){HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);ISheet sheet = workbook.GetSheetAt(SheetIndex);DataTable table = new DataTable();/*根据标题行索引构建datatable列名*/IRow headerRow = sheet.GetRow(HeaderRowIndex);int cellCount = headerRow.LastCellNum;for (int i = headerRow.FirstCellNum; i < cellCount; i++){DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);table.Columns.Add(column);}/*构建datatable表体*/int rowCount = sheet.LastRowNum;for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++){IRow row = sheet.GetRow(i);DataRow dataRow = table.NewRow();for (int j = row.FirstCellNum; j < cellCount; j++){if (row.GetCell(j) != null){dataRow[j] = row.GetCell(j).ToString();}}table.Rows.Add(dataRow);}ExcelFileStream.Close();workbook = null;sheet = null;return table;}}
使用方法如下所示，导出Excel：
DataTable dt = InintTable();ExcelHelp eh = new ExcelHelp();eh.DataTableExportExcel(dt);//测试数据public DataTable InintTable(){DataTable dt = new DataTable("TestTable");dt.Columns.Add("Code", typeof(string));dt.Columns.Add("Name", typeof(string));dt.Columns.Add("Age", typeof(int));dt.Columns.Add("Time", typeof(DateTime));for (int i = 0; i < 30; i++){DataRow dr = dt.NewRow();dr["Code"] = (i + 100).ToString();dr["Name"] = "人员" + i.ToString();dr["Age"] = 20;dr["Time"] = DateTime.Now.AddDays(i);dt.Rows.Add(dr);}return dt;}
导入Excel：
ExcelHelp eh = new ExcelHelp();eh.ExcelExportDataTable();
