c# - NPOI 将范围复制到另一个工作表

Question

我正在使用 NPOI 在 C# 中处理 Excel。但是没有完整的文档说明如何使用它。我需要将一些范围复制到另一个工作表。有人知道怎么做这个吗？也许您正在使用另一个 dll(不是互操作)来提供此类功能。如果是这样，请告诉我。

在 excel 中一切都非常简单:

Worksheets(2).rows(2).copy newsheet.Range("A1")

感谢您的回答!

Answer 1

NPOI 不支持开箱即用，但实现起来很简单。这里有两个函数很有趣:CopyColumn() 和 CopyRange()。

CopyRangeExample() 打开一个工作簿，创建一个新的输出表并将单元格(数据和样式)从一个表复制到另一个表。

void CopyRangeExample()
{
  var workbook = OpenWorkbook("test.xlsx");

  var destinationSheetName = "destination" + (workbook.NumberOfSheets + 1).ToString();
  workbook.CreateSheet(destinationSheetName);

  ISheet sourceSheet = workbook.GetSheet("source");
  ISheet destinationSheet = workbook.GetSheet(destinationSheetName);

  CopyColumn("I", sourceSheet, destinationSheet);
  CopyRange(CellRangeAddress.ValueOf("C6:E15"), sourceSheet, destinationSheet);

  SaveWorkbook(workbook, "test.xlsx");
}

其余代码:

void CopyRange(CellRangeAddress range, ISheet sourceSheet, ISheet destinationSheet)
{
  for (var rowNum = range.FirstRow; rowNum <= range.LastRow; rowNum++)
  {
    IRow sourceRow = sourceSheet.GetRow(rowNum);

    if (destinationSheet.GetRow(rowNum)==null)
      destinationSheet.CreateRow(rowNum);

    if (sourceRow != null)
    {
      IRow destinationRow = destinationSheet.GetRow(rowNum);

      for (var col = range.FirstColumn; col < sourceRow.LastCellNum && col<=range.LastColumn; col++)
      {
        destinationRow.CreateCell(col);
        CopyCell(sourceRow.GetCell(col), destinationRow.GetCell(col));
      }
    }
  }
}

void CopyColumn(string column, ISheet sourceSheet, ISheet destinationSheet)
{
  int columnNum = CellReference.ConvertColStringToIndex(column);
  var range = new CellRangeAddress(0, sourceSheet.LastRowNum, columnNum, columnNum);
  CopyRange(range, sourceSheet, destinationSheet);
}

void CopyCell(ICell source, ICell destination)
{
  if (destination != null && source != null)
  {
    //you can comment these out if you don't want to copy the style ...
    destination.CellComment = source.CellComment;
    destination.CellStyle = source.CellStyle;
    destination.Hyperlink = source.Hyperlink;

    switch (source.CellType)
    {
        case CellType.Formula:
            destination.CellFormula = source.CellFormula; break;
        case CellType.Numeric:
            destination.SetCellValue(source.NumericCellValue); break;
        case CellType.String:
            destination.SetCellValue(source.StringCellValue); break;
    }
  }
}

IWorkbook OpenWorkbook(string path)
{
  IWorkbook workbook;
  using (FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
  {
    workbook = WorkbookFactory.Create(fileStream);
  }
  return workbook;
}

void SaveWorkbook(IWorkbook workbook, string path)
{
  using (var fileStream = new FileStream(path, FileMode.Create, FileAccess.Write))
  {
    workbook.Write(fileStream);
  }
}

请记住在您的项目中包含 NPOI 和 System.IO:

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;