关于C# NPOI处理Excel之后close不保存修改的问题

最近需要处理Excel文件，选用了NPOI

发现两个问题：

（1）对IWorkbook调用close之后对excel做的修改不会保存到对应的excel文件

（2）针对第一个问题，网上说需要将IWorkbook调用write写入到源文件中，我试过了，如果IWorkbook调用write写入到同一个文件，可能会出现文件损坏的问题

针对以上问题，利用装饰者模式进行了解决：

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.Util;
using NPOI.SS;
using NPOI.SS.Formula.UDF;

public class MyXSSFWorkbook : IWorkbook, ICloseable
    {
        public MyXSSFWorkbook(string strFilePath)
        {
            fs = File.Open(strFilePath, FileMode.OpenOrCreate);
            FilePath = strFilePath;
            if (strFilePath.IndexOf(".xlsx") > 0)
            { iWb = new XSSFWorkbook(fs); }
            else if (strFilePath.IndexOf(".xls") > 0)
            { iWb = new HSSFWorkbook(fs); }
            else
                throw new ArgumentException();
        }

        public int ActiveSheetIndex => iWb.ActiveSheetIndex;

        public int FirstVisibleTab { get => iWb.FirstVisibleTab; set => iWb.FirstVisibleTab = value; }

        public int NumberOfSheets => iWb.NumberOfSheets;

        public short NumberOfFonts => iWb.NumberOfFonts;

        public int NumCellStyles => iWb.NumCellStyles;

        public int NumberOfNames => iWb.NumberOfNames;

        public MissingCellPolicy MissingCellPolicy { get => iWb.MissingCellPolicy; set => iWb.MissingCellPolicy = value; }
        public bool IsHidden { get => iWb.IsHidden; set => iWb.IsHidden = value; }

        public SpreadsheetVersion SpreadsheetVersion => iWb.SpreadsheetVersion;

        public int AddPicture(byte[] pictureData, PictureType format)
        {
            return iWb.AddPicture(pictureData, format);
        }

        public void AddToolPack(UDFFinder toopack)
        {
            iWb.AddToolPack(toopack);
        }

        public ISheet CloneSheet(int sheetNum)
        {
            return iWb.CloneSheet(sheetNum);
        }

        public void Close()
        {
            string dirPath = Path.GetDirectoryName(FilePath);
            string tmpFileName = Guid.NewGuid() + Path.GetExtension(FilePath);
            string tmpFilePath = Path.Combine(dirPath, tmpFileName);

            using (var fsTmp = File.Open(tmpFilePath, FileMode.OpenOrCreate))
            {
                Write(fsTmp);
            }

            iWb.Close();
            fs.Close();

            File.Delete(FilePath);
            File.Move(tmpFilePath, FilePath);
        }

        public ICellStyle CreateCellStyle()
        {
            return iWb.CreateCellStyle();
        }

        public IDataFormat CreateDataFormat()
        {
            return iWb.CreateDataFormat();
        }

        public IFont CreateFont()
        {
            return iWb.CreateFont();
        }

        public IName CreateName()
        {
            return iWb.CreateName();
        }

        public ISheet CreateSheet()
        {
            return iWb.CreateSheet();
        }

        public ISheet CreateSheet(string sheetname)
        {
            return iWb.CreateSheet(sheetname);
        }

        public IFont FindFont(short boldWeight, short color, short fontHeight, string name, bool italic, bool strikeout, FontSuperScript typeOffset, FontUnderlineType underline)
        {
            return iWb.FindFont(boldWeight, color, fontHeight, name, italic, strikeout, typeOffset, underline);
        }

        public IFont FindFont(bool bold, short color, short fontHeight, string name, bool italic, bool strikeout, FontSuperScript typeOffset, FontUnderlineType underline)
        {
            return iWb.FindFont(bold, color, fontHeight, name, italic, strikeout, typeOffset, underline);
        }

        public IList<IName> GetAllNames()
        {
            return iWb.GetAllNames();
        }

        public IList GetAllPictures()
        {
            return iWb.GetAllPictures();
        }

        public ICellStyle GetCellStyleAt(int idx)
        {
            return iWb.GetCellStyleAt(idx);
        }

        public ICreationHelper GetCreationHelper()
        {
            return iWb.GetCreationHelper();
        }

        public IEnumerator<ISheet> GetEnumerator()
        {
            return iWb.GetEnumerator();
        }

        public IFont GetFontAt(short idx)
        {
            return iWb.GetFontAt(idx);
        }

        public IName GetName(string name)
        {
            return iWb.GetName(name);
        }

        public IName GetNameAt(int nameIndex)
        {
            return iWb.GetNameAt(nameIndex);
        }

        public int GetNameIndex(string name)
        {
            return iWb.GetNameIndex(name);
        }

        public IList<IName> GetNames(string name)
        {
            return iWb.GetNames(name);
        }

        public string GetPrintArea(int sheetIndex)
        {
            return iWb.GetPrintArea(sheetIndex);
        }

        public ISheet GetSheet(string name)
        {
            return iWb.GetSheet(name);
        }

        public ISheet GetSheetAt(int index)
        {
            return iWb.GetSheetAt(index);
        }

        public int GetSheetIndex(string name)
        {
            return iWb.GetSheetIndex(name);
        }

        public int GetSheetIndex(ISheet sheet)
        {
            return iWb.GetSheetIndex(sheet);
        }

        public string GetSheetName(int sheet)
        {
            return iWb.GetSheetName(sheet);
        }

        public bool IsDate1904()
        {
            return iWb.IsDate1904();
        }

        public bool IsSheetHidden(int sheetIx)
        {
            return iWb.IsSheetHidden(sheetIx);
        }

        public bool IsSheetVeryHidden(int sheetIx)
        {
            return iWb.IsSheetVeryHidden(sheetIx);
        }

        public int LinkExternalWorkbook(string name, IWorkbook workbook)
        {
            return iWb.LinkExternalWorkbook(name, workbook);
        }

        public void RemoveName(int index)
        {
            iWb.RemoveName(index);
        }

        public void RemoveName(string name)
        {
            iWb.RemoveName(name);
        }

        public void RemoveName(IName name)
        {
            iWb.RemoveName(name);
        }

        public void RemovePrintArea(int sheetIndex)
        {
            iWb.RemovePrintArea(sheetIndex);
        }

        public void RemoveSheetAt(int index)
        {
            iWb.RemoveSheetAt(index);
        }

        public void SetActiveSheet(int sheetIndex)
        {
            iWb.SetActiveSheet(sheetIndex);
        }

        public void SetPrintArea(int sheetIndex, string reference)
        {
            iWb.SetPrintArea(sheetIndex, reference);
        }

        public void SetPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
        {
            iWb.SetPrintArea(sheetIndex, startColumn, endColumn, startRow, endRow);
        }

        public void SetSelectedTab(int index)
        {
            iWb.SetSelectedTab(index);
        }

        public void SetSheetHidden(int sheetIx, SheetState hidden)
        {
            iWb.SetSheetHidden(sheetIx, hidden);
        }

        public void SetSheetHidden(int sheetIx, int hidden)
        {
            iWb.SetSheetHidden(sheetIx, hidden);
        }

        public void SetSheetName(int sheet, string name)
        {
            iWb.SetSheetName(sheet, name);
        }

        public void SetSheetOrder(string sheetname, int pos)
        {
            iWb.SetSheetOrder(sheetname, pos);
        }

        public void Write(Stream stream)
        {
            iWb.Write(stream);
        }

        private IWorkbook iWb { get; set; }
        private FileStream fs { get; set; }
        private string FilePath { get; set; }
    }

这里面对close方法做了装饰处理，首先创建一个临时文件，然后将IWorkbook的内容写入到临时文件中，（不能直接写入到源文件中，否则会出现文件损坏），然后关闭IWorkbook和FileStream,在之后将源文件删掉，再用File的move将临时文件名字改为源文件即可。 

此外这个类同时既能处理.xls和也能处理.xlsx文件，对使用者而言更加方便。

internal class Program
    {
        static void Main(string[] args)
        {
            string strFilePath = "C:\\Users\\ll\\Desktop\\A.xlsx";

            IWorkbook x = new MyXSSFWorkbook(strFilePath);

            ISheet sheet = x.GetSheetAt(0);
            if (sheet == null)
            { sheet = x.CreateSheet(); }

            IRow irow = sheet.GetRow(0);
            if (irow == null)
            { irow = sheet.CreateRow(0); }

            ICell icell = irow.GetCell(0);
            if (icell == null)
            { icell = irow.CreateCell(0); }

            icell.SetCellValue("AAA");

            x.Close();
        }
    }