C#NPOI 往excel里面追加一行或者一个Datatable

 /// <summary>/// 追加记录DataTable(不含表头，将表内容直接追加进去至末尾)/// </summary>/// <param name="path">路径</param>/// <param name="sheetIndex">目标表下标</param>/// <param name="dt">表格</param>/// <returns></returns>public static bool AppendDataTableToExcel(string path, int sheetIndex, DataTable dt,bool hasFieldRow = true){try{//获取文件流using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)){IWorkbook workbook;//获取excel的文件类型ExcelType type = GetExcelFileType(path).Value;//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)switch (type){case ExcelType.xlsx:workbook = new XSSFWorkbook(stream);break;default:workbook = new HSSFWorkbook(stream);break;}//ISheet sheet = null;//获取工作表,默认获取第一张if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0){sheet = workbook.GetSheetAt(sheetIndex);}else{System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");}//是否已经获取工作表，如果没有则直接返回if (sheet == null){return false;}#region//往表中追加Datatableif (hasFieldRow)//判断是否添加表头{//添加表头(当表为空时从0开始，不为空则加1)IRow headRow = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));for (int i = 0; i < dt.Columns.Count; i++){//获取单元格ICell cell = headRow.CreateCell(i);//设置单元格数据类型cell.SetCellType(CellType.String);//单元格赋值cell.SetCellValue(dt.Columns[i].ColumnName.ToString());}}//添加表头为1，不添加表头为0int starRow;if (hasFieldRow){starRow = 1;}else{starRow = 0;}//遍历行for (int i = 0; i < dt.Rows.Count; i++){//创建行IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);//遍历列for (int j = 0; j < dt.Columns.Count; j++){//创建单元格ICell cell = cells.CreateCell(j);//设置单元格属性cell.SetCellType(CellType.String);//单元格赋值cell.SetCellValue(dt.Rows[i][j].ToString());}}#endregion//保存bool success = Export(workbook, path);return success;}}catch{return false;}}/// <summary>/// 追加记录DataRow/// </summary>/// <param name="path">路径</param>/// <param name="sheetIndex">目标表下标</param>/// <param name="dr">行数据</param>/// <returns></returns>public static bool AppendDataRowToExcel(string path, int sheetIndex, DataRow dr){try{//获取文件流using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)){IWorkbook workbook;//获取excel的文件类型ExcelType type = GetExcelFileType(path).Value;//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现)switch (type){case ExcelType.xlsx:workbook = new XSSFWorkbook(stream);break;default:workbook = new HSSFWorkbook(stream);break;}//ISheet sheet = null;//获取工作表,默认获取第一张if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0){//获取工作表sheet = workbook.GetSheetAt(sheetIndex);}else{System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空！");}//是否已经获取工作表，如果没有则直接返回if (sheet == null){return false;}#region//往表中追加Datatable(没有追加表头数据)//创建行单元(当表为空时从0开始，不为空则加1)IRow cells = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));//遍历列for (int j = 0; j < dr.ItemArray.Length; j++){//创建单元格ICell cell = cells.CreateCell(j);//设置单元格属性cell.SetCellType(CellType.String);//单元格赋值cell.SetCellValue(dr.ItemArray[j].ToString());}#endregion//保存bool success = Export(workbook, path);return success;}}catch{return false;}}/// <summary>/// 将表格存储为Excel/// </summary>/// <param name="workbook"></param>/// <returns></returns>private static bool Export(IWorkbook workbook,string path){try{FileInfo file = new FileInfo(path);if (file.Exists){file.Delete();file = new FileInfo(path);}// 写入 ,创建其支持存储区为内存的流System.IO.MemoryStream ms = new System.IO.MemoryStream();//写入内存workbook.Write(ms);workbook = null;//FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);byte[] data = ms.ToArray();fs.Write(data, 0, data.Length);fs.Flush();//关闭ms.Close();//释放ms.Dispose();//fs.Close();return true;}catch (Exception ex){throw new Exception(ex.Message);}}