NPIO在指定位置插入新列（附案例和代码）

背景：

I could be mistaken as I am not that familiar with NPOI, however, after a minor search, it appears that NPOI does NOT support an “Insert Column” type feature.

才发现NPIO居然不支持操作列，只能操作行和单元格。那么我们该如何实现在在指定行插入列的效果呢？

案例：

现有一个excel表如左图，B列是隐藏的，需要在B列前面添加一列数据，达到右图的效果。

 

 

 代码实现：

void Main()
{
    var file=@"C:\Users\Administrator\Desktop\demo.xlsx";
    FileStream fsRead = new FileStream(file, FileMode.Open);
    IWorkbook workBook = new XSSFWorkbook(fsRead);
    ISheet sheet = workBook.GetSheetAt(0);
    InsertColumn(sheet,1);
    MemoryStream stream = new MemoryStream();
    workBook.Write(stream);
    byte[] buffer = stream.ToArray();

    File.WriteAllBytes(file, buffer);
}

void InsertColumn(ISheet sheet, int colIndex)
{
    sheet.SetColumnHidden(colIndex, false);
    
    for (int i = 0; i < sheet.LastRowNum + 1; i++)
    {
        var row = sheet.GetRow(i);
        var cell = row.GetCell(colIndex);
        
        var copyCell = row.CreateCell(colIndex + 1);
        copyCell.SetCellValue(cell.StringCellValue);
        cell.SetCellValue("111");
    }
    sheet.SetColumnHidden(colIndex+1, true);