NPOI 实现在已存在的Excel中任意位置开始插入任意数量行，并填充数据

1 npoi版本2.1.3.1

2 需要添加的引用：

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI;
using NPOI.OpenXml4Net.OPC;

3 调用方式

OperationExcel oe = new OperationExcel(0,5);第一行开始插入5行，第三个参数是对应要添加到新添加行的每一列的数据
oe.EditorExcel(savePath, readPath,oe);

4 分装好的类：

   

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI;
using NPOI.OpenXml4Net.OPC;

namespace TransactionToString
{
   public class OperationExcel
    {
        private int insertRowIndex;
        private int insertRowCount;
        private Dictionary<int, string> insertData;
        public OperationExcel(int insertRowIndex, int insertRowCount,Dictionary<int,string> insertData=null)
        {
            if (insertData!=null)
            {
                this.insertData = insertData;
            }
            this.insertRowIndex = insertRowIndex;
            this.insertRowCount = insertRowCount;
        }
        private IWorkbook NPOIOpenExcel(string filename)
        {
            IWorkbook myworkBook;
            Stream excelStream = OpenResource(filename);
            if (POIFSFileSystem.HasPOIFSHeader(excelStream))
                return new HSSFWorkbook(excelStream);
            if (POIXMLDocument.HasOOXMLHeader(excelStream))
            {
                return new XSSFWorkbook(OPCPackage.Open(excelStream));
            }
            if (filename.EndsWith(".xlsx"))
            {
                return new XSSFWorkbook(excelStream);
            }
            if (filename.EndsWith(".xls"))
            {
                new HSSFWorkbook(excelStream);
            }
            throw new Exception("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
        }

        private Stream OpenResource(string filename)
        {
            FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
            return fs;
        }

        //插入
        private void InsertRow(ISheet sheet,int insertRowIndex,int insertRowCount,IRow formatRow)
        {
            sheet.ShiftRows(insertRowIndex, sheet.LastRowNum, insertRowCount, true, false);
            for (int i = insertRowIndex; i < insertRowIndex+insertRowCount; i++)
            {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;
                targetRow = sheet.CreateRow(i);
                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                {
                    sourceCell = formatRow.GetCell(m);
                    if (sourceCell==null)
                    {
                        continue;
                    }
                    targetCell = targetRow.CreateCell(m);
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);

                }
            }

            for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
            {
                IRow firstTargetRow = sheet.GetRow(i);
                ICell firstSourceCell = null;
                ICell firstTargetCell = null;

                for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
                {
                    firstSourceCell = formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (firstSourceCell == null)
                    {
                        continue;
                    }
                    firstTargetCell = firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                    firstTargetCell.SetCellType(firstSourceCell.CellType);
                    if (this.insertData!=null&&this.insertData.Count>0)
                    {
                        firstTargetCell.SetCellValue(insertData[m]);
                    }
                    firstTargetCell.SetCellValue("test");
                }
            }


           
        }

        public void WriteToFile(IWorkbook workbook,string filename)
        {
            if (File.Exists(filename))
            {
                File.Delete(filename);
            }
            using (FileStream fs=new FileStream(filename,FileMode.OpenOrCreate,FileAccess.Write))
            {
                workbook.Write(fs);
                fs.Close();
            }
        }

        public void OpenExcel(string filename)
        {
            System.Diagnostics.Process process = new System.Diagnostics.Process();
            process.StartInfo.FileName = filename;
            process.StartInfo.ErrorDialog = true;
            process.Start();
        }

        public void EditorExcel(string savePath, string readPath, OperationExcel oe)
        {
            try
            {
                IWorkbook workbook = oe.NPOIOpenExcel(readPath);
                if (workbook == null)
                {
                    return;
                }
                int sheetNum = workbook.NumberOfSheets;
                for (int i = 0; i < sheetNum; i++)
                {
                    ISheet mysheet = workbook.GetSheetAt(i);
                    //获取原格式行
                    IRow mySourceRow = mysheet.GetRow(insertRowIndex);
                    oe.InsertRow(mysheet, insertRowIndex, insertRowCount, mySourceRow);
                }

                oe.WriteToFile(workbook, savePath);
                oe.OpenExcel(savePath);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
           
        }
    }
}

5 不足之处欢迎留言讨论