关于Npoi+excel文件读取，修改文件内容的处理方式

因最近有需求场景，实现对文件的读写操作，又不单独生成新的文件，对于源文件的修改，做了一个简单实现，如下↓

---

// 要操作的excel文件路径
            string fileName = Server.MapPath("~/Content/test.xlsx");
            if (!System.IO.File.Exists(fileName)) return Content("文件不存在！");
            IWorkbook workbook;

            //把文件内容导入到工作薄当中，然后关闭文件
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);

            workbook = WorkbookFactory.Create(fs);
            fs.Close();//编辑工作薄当中内容            
            ISheet sheet = workbook.GetSheetAt(0);


            //workbook.GetSheetAt(0).GetRow(0).Cells[0].SetCellValue("TestCell");
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                foreach (ICell cell in sheet.GetRow(i).Cells)
                {
                    cell.SetCellType(cell.CellType);
                    if (cell.CellType == CellType.Numeric)
                    {
                        if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                        {
                            cell.SetCellValue(cell.DateCellValue.ToString());
                        }
                        else//其他数字类型
                        {
                            cell.SetCellValue(cell.NumericCellValue.ToString());
                        }
                    }
                    else
                    {
                        cell.SetCellValue(cell.StringCellValue.ToString());
                    }
                }
                if (i != 0)
                {
                    ICell firstCell = sheet.GetRow(i).Cells[0];
                    firstCell.SetCellType(firstCell.CellType);
                    var cellValue = firstCell.StringCellValue.ToDecimal().ToString();
                    firstCell.SetCellValue(cellValue);
                }
            }

            //把编辑过后的工作薄重新保存为excel文件
            FileStream fs2 = System.IO.File.Create(fileName);
            workbook.Write(fs2);
            fs2.Close();
            return Content("操作成功！！" + DateTime.Now);

读取文件内容更新文件状态

// 要操作的excel文件路径
            string fileName = Server.MapPath("~/Content/test.xlsx");
            if (!System.IO.File.Exists(fileName)) return Content("文件不存在,请检查路径及文件名称是否正确！");
            //IWorkbook workbook;
            //把文件内容导入到工作薄当中，然后关闭文件
            //FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
            //workbook = WorkbookFactory.Create(fs);
            //fs.Close();//编辑工作薄当中内容     
            //如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
            XSSFWorkbook xssFWorkbook;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
            {
                xssFWorkbook = new XSSFWorkbook(file);
            }

            ISheet sheet = xssFWorkbook.GetSheetAt(0);
            var userList = GetUserLists(sheet);
            var userNames = userList.Select(u =>
            {
                return u.UserName.Replace(u.UserName, $"'{u.UserName}'");
            });
            var companyNames = userList.Select(u =>
            {
                return u.CompanyName.Replace(u.CompanyName, $"'{u.CompanyName}'");
            });

            var userIdCards = userList.Select((u, i) =>
             {
                 return $" SELECT '{u.UserName}' AS UserName,'{u.CompanyName}' AS CompanyName,'{u.LegalPersonIdNumber}' AS LegalPersonIdNumber ";
             });
            var dataList = AsyncHelper.RunSync(() => _userIdFileAppService.RunSearchAndUpdateData(
                new UserIdentityCardInput
                {
                    UserNameString = string.Join(",", userNames),
                    CompanyNameString = string.Join(",", companyNames),
                    UserIdCardString = string.Format(@"SELECT * INTO #tbUserIdCardTable FROM ( {0} ) a;", string.Join("  UNION  ", userIdCards))
                }
                ));
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                if (i != 0)
                {
                    var userName = sheet.GetRow(i).Cells[0].ToString();
                    var companyName = sheet.GetRow(i).Cells[1].ToString();
                    //添加列
                    ICell lastCell = sheet.GetRow(i).CreateCell(3);
                    lastCell.SetCellType(lastCell.CellType);
                    ICellStyle newStyle = xssFWorkbook.CreateCellStyle();
                    //XSSFCellStyle styleTemp = ((XSSFCellStyle)newStyle);
                    //HSSFPalette palette = xssFWorkbook.GetCustomPalette();

                    if (dataList.Find(a => a.UserName == userName && a.CompanyName == companyName) != null)
                    {
                        lastCell.SetCellValue("已更新");
                        //Color c = Color.FromArgb(98, 185, 106);
                        //palette.SetColorAtIndex((short)9, c.R, c.G, c.B);
                        //var color = palette.FindColor(c.R, c.G, c.B);
                        //newStyle.FillPattern = FillPattern.SolidForeground;
                        //newStyle.FillForegroundColor = color.Indexed;

                        XSSFColor color = new XSSFColor();
                        color.SetRgb(new byte[] { 255, 255, 0 });
                        newStyle.FillPattern = FillPattern.SolidForeground;
                        newStyle.FillForegroundColor = 13;
                    }
                    else
                    {
                        lastCell.SetCellValue("未更新");
                        //创建字体
                        XSSFFont ffont = (XSSFFont)xssFWorkbook.CreateFont();
                        XSSFColor color = new XSSFColor();
                        color.SetRgb(new byte[] { 255, 51, 0 });
                        //给字体设置颜色
                        ffont.Color = color.Indexed;
                        //给样式添加字体
                        newStyle.SetFont(ffont);

                        newStyle.FillPattern = FillPattern.SolidForeground;
                        newStyle.FillForegroundColor = 10;

                    }
                    lastCell.CellStyle = newStyle;
                }
            }

            //把编辑过后的工作薄重新保存为excel文件
            FileStream fs2 = System.IO.File.Create(fileName);
            xssFWorkbook.Write(fs2);
            fs2.Close();
            return Content("操作成功！！" + DateTime.Now);