C#開發之基於NPOI的操作Excel開發體驗

DisonTangor發表於2021-06-16
C#Excel
最近遇到一個資料匯入的需求，語言是.net framework 4.7的C#。但是，這次主要探討NPOI的體驗，原則就是向前相容。所以採用.xls的支援。網上的資料，我稍微整合了一些。

#1 單元格下拉框

在開發中我們會遇到為單元格設定下拉框。一般可以編寫如下：

var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(stringArray);
HSSFDataValidation validate = new HSSFDataValidation(cellRanges, constraint);
validate.ShowProptBox = true;
sheet.AddValidationData(validate);

但是，如果字串陣列存在長度限制，如NPOI匯出Excel時下拉選單值超過255的問題（String literals in formulas can't be bigger than 255 characters ASCII）
解決方案

通過額外新建Excel的Sheet頁儲存下拉內容，並轉換為下拉框資料。

ISheet hidden = workbook.CreateSheet(columnName);
IRow row = null;
ICell cell = null;
for (int i = 0; i < stringArray.Length; i++)
{
    row = hidden.CreateRow(i);
    cell = row.CreateCell(0);
    cell.SetCellValue(stringArray[i]);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = column.ColumnName;
// 注意下面的語法是Excel的公式，建議不要落掉一個`$`，很多文件都要所遺漏。
namedCell.RefersToFormula = $"{columnName}!$A$1:$A${stringArray.Length}";
DVConstraint constraint =  DVConstraint.CreateFormulaListConstraint(columnName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
HSSFDataValidation validate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);

#2 新增批註

程式碼如下：

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// 這個程式碼引數不要寫成固定的，它用來定位你的批註的位置和大小。
HSSFComment comment = 
    (HSSFComment)patriarch.CreateCellComment(new HSSFFClientAnchor(0, 0, 255,255, col1, 0, col1 + 2, 5));
comment.Author = "Dison";
comment.String = new HSSFRichTextString($"內容");
cell.CellComment = comment;

#3 讀取資料

如何解析公式的結果

程式碼如下：

if (row.GetCell(i).CellType.Equals(CellType.Formula))
{
    var data = row.GetCell(i).RichStringCellValue;
}

如果希望讀取公式也可以如下：

var data = row.GetCell(i).ToString();

但是需要注意結果沒有等號“=”, 這裡我是演示，所以寫了區域性變數。

日期格式 MM-dd-yy 轉 yyyy-MM-dd

由於Excel的數字和日期都是Numeric格式，；處理如下：

if (row.GetCell(i).CellType.Equals(CellType.Numeric))
{
    ICell cell = row.GetCell(i);
    short format = cell.CellStyle.DataFormat;
    if (format != 0)
    {
        var data = cell.DateCellValue.ToString("yyyy-MM-dd");
    }
    else 
    {
        var data = cell.NumericCellValue;
    }
}

結語

NPOI還是一個相對成熟的Excel操作庫。網上的資料確實寫的比較潦草。但是作為程式設計師，必須學會耐心，尤其是debug。

參考文件

相關文章