NPOI读取模板文件生成Excel

前不久实现了用NPOI组件替代Microsoft.Office.Interop.Excel 原生组件实现导出数据到Excel的需求，其中踩了几个坑，这里记录一下。

不能使用wps创建模板文件
不能使用一个文件流，对已存在Excel文件进行修改
NPOI中sheet、row、cell都是以0作为起始序号，Office原生组件是以1作为起始序号。

使用WPS创建的模板文件可以导出数据到Excel文件，但如果使用Office打开则可能出现部分内容出错，询问是否修复的提示。用WPS打开时正常的。

使用一个文件流，对已存在Excel文件进行修改在保存时会出现异常，可以使用两个六对象，一个负责读取和修改，另外一个只负责写入，如下方代码所示。

public void ExportData(IList<IList<string>> items, string fullPathFileName, int startRowNumber, string title = "")
{
    Debug.Assert(items != null && items.Count > 0 && !string.IsNullOrWhiteSpace(fullPathFileName));
 
    IWorkbook workbook = null;
    try
    {
        using (FileStream fileStream = new FileStream(fullPathFileName, FileMode.Open, FileAccess.Read))
        {
            if (Path.GetExtension(fullPathFileName) == ".xlsx")
            {
                workbook = new XSSFWorkbook(fileStream);
            }
            else
            {
                workbook = new HSSFWorkbook(fileStream);
            }
            ISheet worksheet = workbook.GetSheetAt(0);
 
            worksheet.DefaultRowHeightInPoints = 17f;// 设置默认行高
 
            if (!string.IsNullOrWhiteSpace(title))
            {
                worksheet.Header.Center = title;
            }
 
            int rowIndex = 0;
            int columnIndex = 0;
            var firstCellStyle = worksheet.GetRow(rowIndex).GetCell(columnIndex).CellStyle;
 
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;// 设置字体大小
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.SetFont(font);
            cellStyle.WrapText = true;// 自动换行
            cellStyle.BorderTop = firstCellStyle.BorderTop;
            cellStyle.BorderBottom = firstCellStyle.BorderBottom;
            cellStyle.BorderLeft = firstCellStyle.BorderLeft;
            cellStyle.BorderRight = firstCellStyle.BorderRight;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
 
            int rowsCount = items.Count;
            IRow currentRow;
            for (int dataIndex = 0; dataIndex < rowsCount; dataIndex++)
            {
                rowIndex = dataIndex + startRowNumber - 1;
                currentRow = worksheet.GetRow(rowIndex, true);
                currentRow.RowStyle = cellStyle;
                ICell currentCell;
                for (int j = 0; j < items[dataIndex].Count; j++)
                {
                    currentCell = currentRow.GetCell(j, true);
                    currentCell.SetCellType(CellType.String);// 设置单元格类型为字符串
                    currentCell.SetCellValue(items[dataIndex][j]);
                }
            }
        }
 
        // 保存
        using (FileStream outFileStream = new FileStream(fullPathFileName, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(outFileStream);
        }
    }
    catch (OutOfMemoryException ex)
    {
        // LoggerFactory.CreateLog().LogError("Export data failed : {0}. Process.WorkingSet64: {1} MB.", ex.Message, Process.GetCurrentProcess().WorkingSet64 / 1024 / 1024);
        throw;
    }
    catch (Exception ex)
    {
        // LoggerFactory.CreateLog().LogError("Export data failed:" + ex.Message);
        throw;
    }
    finally
    {
        workbook.Close();
    }
}