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
     
       insertData;        public OperationExcel(int insertRowIndex, int insertRowCount,Dictionary
      
        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 不足之处欢迎留言讨论