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

这转自https://www.cnblogs.com/mibing/p/8004965.html

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);
            }
           
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Threading;
using NPOI.HSSF.UserModel;
 
public static class ExcelHelper
    {
        public static DataSet ExcelToDataSet(string fileName)
        {
            return ExcelToDataSet(fileName, true);
        }
 
        public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader)
        {
            int sheetCount = 0;
            return ExcelToDataSet(fileName, firstRowAsHeader, out sheetCount);
        }
 
        public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader, out int sheetCount)
        {
            using (DataSet ds = new DataSet())
            {
                using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = WorkbookFactory.Create(fileStream);
                    IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
 
                    sheetCount = workbook.NumberOfSheets;
 
                    for (int i = 0; i < sheetCount; ++i)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
                        ds.Tables.Add(dt);
                    }
                    return ds;
                }
            }
        }
 
        public static DataTable ExcelToDataTable(string fileName, string sheetName)
        {
            return ExcelToDataTable(fileName, sheetName, true);
        }
 
        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool firstRowAsHeader)
        {
            using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = WorkbookFactory.Create(fileStream);
 
                IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
 
                ISheet sheet = workbook.GetSheet(sheetName);
 
                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }
 
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                return ExcelToDataTable(sheet, evaluator);
            }
        }
 
        private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = GetCellCount(sheet);
 
                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }
 
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    FillDataRowByRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }
 
                dt.TableName = sheet.SheetName;
                return dt;
            }
        }
 
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);
 
                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }
 
                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }
 
                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        FillDataRowByRow(row, evaluator, ref dr);
                        dt.Rows.Add(dr);
                    }
                }
 
                dt.TableName = sheet.SheetName;
                return dt;
            }
        }
 
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="row"></param>
        /// <param name="evaluator"></param>
        /// <param name="dr"></param>
        private static void FillDataRowByRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    ICell cell = row.GetCell(j);
 
                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Blank:
                                {
                                    dr[j] = DBNull.Value;
                                    break;
                                }
                            case CellType.Boolean:
                                {
                                    dr[j] = cell.BooleanCellValue;
                                    break;
                                }
                            case CellType.Numeric:
                                {
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dr[j] = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        dr[j] = cell.NumericCellValue;
                                    }
                                    break;
                                }
                            case CellType.String:
                                {
                                    dr[j] = cell.StringCellValue;
                                    break;
                                }
                            case CellType.Error:
                                {
                                    dr[j] = cell.ErrorCellValue;
                                    break;
                                }
                            case CellType.Formula:
                                {
                                    cell = evaluator.EvaluateInCell(cell) as HSSFCell;
                                    dr[j] = cell.ToString();
                                    break;
                                }
                            default:
                                throw new NotSupportedException(string.Format("Unsupported format type:{0}", cell.CellType));
                        }
                    }
                }
            }
        }
 
        /// <summary>
        /// 获取单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static int GetCellCount(ISheet sheet)
        {
            int firstRowNum = sheet.FirstRowNum;
 
            int cellCount = 0;
 
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);
 
                if (row != null && row.LastCellNum > cellCount)
                {
                    cellCount = row.LastCellNum;
                }
            }
            return cellCount;
        }
 
          /// <summary>
        /// 数据导出
        /// </summary>
        /// <param name="data"></param>
        /// <param name="sheetName"></param>
        public static void ExportToExcel(this DataTable data)
        {
            ExportToExcel(data,"Sheet1");
        }
 
        /// <summary>
        /// 数据导出
        /// </summary>
        /// <param name="data"></param>
        /// <param name="sheetName"></param>
        public static void ExportToExcel(this DataTable data, string sheetName)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Filter = "Excel(97-2003)|*.xls|Excel(2007-2013)|*.xlsx";
            if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
 
 
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow rowHead = sheet.CreateRow(0);
 
 
            //填写表头
            for (int i = 0; i < data.Columns.Count; i++)
            {
                rowHead.CreateCell(i, CellType.String).SetCellValue(data.Columns[i].ColumnName.ToString());
            }
            //填写内容
            for (int i = 0; i < data.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    row.CreateCell(j, CellType.String).SetCellValue(data.Rows[i][j].ToString());
                }
            }
 
            for (int i = 0; i < data.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
 
            using (FileStream stream = File.OpenWrite(fileDialog.FileName))
            {
                workbook.Write(stream);
                stream.Close();
            }
            MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            GC.Collect();
        }
    }

VS2010安装NuGet

https://jingyan.baidu.com/article/642c9d34242718644a46f7b3.html

用Nuget安装NPOI