Geek asks and answers

Range of copying NPOI to another worksheet

I am using NPOI to work with Excel in C #. But there is no complete documentation on how to work with it. I need to copy some range to another worksheet. Does anyone know how to do this? Perhaps you are using another dll (not interop) to provide this functionality. If yes, please tell me.

In excel, everything is very simple:

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

Thanks for your reply!

+3
source share
1 answer

NPOI does not support this out of the box, but the implementation is simple. Two functions are of interest here: CopyColumn()and CopyRange().

CopyRangeExample() opens a workbook, creates a new output sheet and copies cells (data and style) from one sheet to another.

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

And the rest of the code:

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

Just remember to include NPOI and System.IO in your project:

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

More articles:


All Articles