本文介紹了在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();

?

?

總結

以上是生活随笔為你收集整理的C#中使用NPIO实现导入导出Excel简单操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。