C# 基于NPOI操作Excel

更新時間：2021年06月17日 09:07:43 作者：DisonTangor

最近遇到一個數(shù)據(jù)導(dǎo)入的需求，語言是.net framework 4.7的C#。但是，這次主要探討NPOI的體驗，原則就是向前兼容。所以采用.xls的支持。下面就來看看實現(xiàn)步驟

1 單元格下拉框

在開發(fā)中我們會遇到為單元格設(shè)置下拉框。一般可以編寫如下：

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

但是，如果字符串?dāng)?shù)組存在長度限制，如NPOI導(dǎo)出Excel時下拉列表值超過255的問題（String literals in formulas can't be bigger than 255 characters ASCII）
解決方案
通過額外新建Excel的Sheet頁保存下拉內(nèi)容，并轉(zhuǎn)換為下拉框數(shù)據(jù)。

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();
// 這個代碼參數(shù)不要寫成固定的，它用來定位你的批注的位置和大小。
HSSFComment comment = 
    (HSSFComment)patriarch.CreateCellComment(new HSSFFClientAnchor(0, 0, 255,255, col1, 0, col1 + 2, 5));
comment.Author = "Dison";
comment.String = new HSSFRichTextString($"內(nèi)容");
cell.CellComment = comment;

3 讀取數(shù)據(jù)

如何解析公式的結(jié)果
代碼如下：

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

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

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

但是需要注意結(jié)果沒有等號“=”, 這里我是演示，所以寫了局部變量。

日期格式 MM-dd-yy 轉(zhuǎn) yyyy-MM-dd
由于Excel的數(shù)字和日期都是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;
    }
}

結(jié)語

NPOI還是一個相對成熟的Excel操作庫。網(wǎng)上的資料確實寫的比較潦草。但是作為程序員，必須學(xué)會耐心，尤其是debug。

常見問題解決

NPOI 導(dǎo)出添加批注功能

//添加批注
HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFComment comment12 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3));//批注顯示定位
comment12.String = new HSSFRichTextString("請?zhí)顚懲暾块T名稱！");
HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);//將批注給予單元格
cell12.CellComment = comment12;

但是有個比較重要的地方需要澄清下，就是批注的位置和大小，這是由HSSFClientAnchor八個參數(shù)控制的，千萬不能簡單的寫HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3)，

因為每個單元格的批注的位置都是不一樣的（編輯批注時的位置）。那么怎么辦呢，當(dāng)然是需要了解參數(shù)的意思：

簡單說來：

關(guān)于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的參數(shù)，有必要在這里說明一下：

dx1：起始單元格的x偏移量；
dy1：起始單元格的y偏移量；
dx2：終止單元格的x偏移量；
dy2：終止單元格的y偏移量；
col1：起始單元格列序號；
row1：起始單元格行序號；
col2：終止單元格列序號；
row2：終止單元格行序號；

其實主要是前四個是偏移量，后四個關(guān)系到批注的位置和大小。

以我自己做的一個例子來說：

HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, colindex + 1, rowIndex - 1,  colindex + 2, rowIndex + 4))；

rowIndex 是當(dāng)前單元格是第幾行，colindex 是當(dāng)前單元格是第幾列。通過行列是可以定位到當(dāng)前的單元格的。
colindex + 1 對應(yīng)上面的參數(shù)是col1 表示批注起始的位置是當(dāng)前單元格的列數(shù)的下一列，即原來是第5列，則批注起在第6列。
rowIndex - 1 對應(yīng)上面的參數(shù)是row1 表示皮質(zhì)起始的位置是當(dāng)前單元格行數(shù)的上一行，即原來是第2行，則批注起在第1行。
colindex + 2, rowIndex + 4 這兩個參數(shù)則是單元格終止的位置 +2 +4 則是決定了批注的大小，道理同colindex + 1，rowIndex - 1 。

但是NPOI導(dǎo)出有個坑就是批注大小會隨著所在位置的單元格大小變動這個影響不大如果想解決這個問題只能換導(dǎo)出方法了。。。

千萬別按照網(wǎng)上人家寫的(0, 0, 0, 0, 1, 2, 2, 3)，這會坑死的，批注位置一直不變任何單元格的批注都在同一個位置，坑死。

POI導(dǎo)出Excel時下拉列表值超過255的問題

//創(chuàng)建Excel工作薄對象

Workbook workbook = new HSSFWorkbook();

//生成一個表格 設(shè)置：頁簽

Sheet sheet = workbook.createSheet("sheet1");

 

//去數(shù)據(jù)庫中查詢我們想要的數(shù)據(jù)

List<Product> productList = Ebean.getServer(GlobalDBControl.getDB()) .createQuery(Product.class, "find product where 1 = 1 and status = 0 and producttype is not null ") .findList();

//創(chuàng)建一個數(shù)組 用來存放 我們?nèi)〕鰜淼臄?shù)據(jù)

String[] productNameArray = new String[productList.size()];

//遍歷每個peoduct對象，來獲取productName屬性并添加到數(shù)組中

for (int i = 0; i < productList.size(); i++)

{ Product product = productList.get(i);

productNameArray[i] = product.getTitle(); }

//將下拉框數(shù)據(jù)放到新的sheet里，然后excle通過新的sheet數(shù)據(jù)加載下拉框數(shù)據(jù)

Sheet hidden = workbook.createSheet("hidden");

//創(chuàng)建單元格對象 Cell cell = null;

//遍歷我們上面的數(shù)組，將數(shù)據(jù)取出來放到新sheet的單元格中

for (int i = 0, length = productNameArray.length; i < length; i++)

{ //取出數(shù)組中的每個元素

String name = productNameArray[i];

//根據(jù)i創(chuàng)建相應(yīng)的行對象（說明我們將會把每個元素單獨(dú)放一行）

Row row = hidden.createRow(i);

//創(chuàng)建每一行中的第一個單元格

cell = row.createCell(0);

//然后將數(shù)組中的元素賦值給這個單元格

cell.setCellValue(name); }

 

// 創(chuàng)建名稱，可被其他單元格引用

Name namedCell = workbook.createName(); namedCell.setNameName("hidden");

// 設(shè)置名稱引用的公式

namedCell.setRefersToFormula("hidden!$A$1:$A$" + productNameArray.length);

//加載數(shù)據(jù),將名稱為hidden的sheet中的數(shù)據(jù)轉(zhuǎn)換為List形式

DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

// 設(shè)置第一列的3-65534行為下拉列表

// (3, 65534, 0, 0) ====> (起始行,結(jié)束行,起始列,結(jié)束列)

CellRangeAddressList regions = new CellRangeAddressList(3, 65534, 0, 0);

// 將設(shè)置下拉選的位置和數(shù)據(jù)的對應(yīng)關(guān)系 綁定到一起

DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

//將第二個sheet設(shè)置為隱藏 workbook.setSheetHidden(1, true);

//將數(shù)據(jù)賦給下拉列表 sheet.addValidationData(dataValidation);

//最后將文件導(dǎo)出就可以了，后面的代碼就不寫了，我只寫一些這個問題相關(guān)的代碼

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   如果出現(xiàn)多列情況，可復(fù)用下面方法

private void ExcelTo255(Workbook workbook,String sheetName,int sheetNameIndex,String[] sheetData,int firstRow,int lastRow,int firstCol,int lastCol){
    //將下拉框數(shù)據(jù)放到新的sheet里，然后excle通過新的sheet數(shù)據(jù)加載下拉框數(shù)據(jù)
    Sheet hidden = workbook.createSheet(sheetName);

    //創(chuàng)建單元格對象
    Cell cell =null;
    //遍歷我們上面的數(shù)組，將數(shù)據(jù)取出來放到新sheet的單元格中
    for (int i = 0, length = sheetData.length; i < length; i++){
        //取出數(shù)組中的每個元素
        String name = sheetData[i];
        //根據(jù)i創(chuàng)建相應(yīng)的行對象（說明我們將會把每個元素單獨(dú)放一行）
        Row row = hidden.createRow(i);
        //創(chuàng)建每一行中的第一個單元格
        cell = row.createCell(0);
        //然后將數(shù)組中的元素賦值給這個單元格
        cell.setCellValue(name);
    }
    // 創(chuàng)建名稱，可被其他單元格引用
    Name namedCell = workbook.createName();
    namedCell.setNameName(sheetName);
    // 設(shè)置名稱引用的公式
    namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + sheetData.length);
    //加載數(shù)據(jù),將名稱為hidden的sheet中的數(shù)據(jù)轉(zhuǎn)換為List形式
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

    // 設(shè)置第一列的3-65534行為下拉列表
    // (3, 65534, 2, 2) ====> (起始行,結(jié)束行,起始列,結(jié)束列)
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    // 將設(shè)置下拉選的位置和數(shù)據(jù)的對應(yīng)關(guān)系 綁定到一起
    DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

    //將第二個sheet設(shè)置為隱藏
    workbook.setSheetHidden(sheetNameIndex, true);
    //將數(shù)據(jù)賦給下拉列表
    workbook.getSheetAt(0).addValidationData(dataValidation);
}

日期格式導(dǎo)入混亂

原因

大概是NPOI導(dǎo)入時會大概判斷一下Excel文檔里面的單元格是什么格式的內(nèi)容，

有Blank,Boolean,Numeric,String,Error,Formula 等幾種，

但是就是沒有日期的，日期的單元格會被判斷成Numeric(數(shù)字)類型，

所以日期格式的單元格就按數(shù)字類型來取其中的值，

所以單元格被判斷成數(shù)字的之后還要再判斷一下是否為日期格式。

/// <summary>
        /// 獲取單元格類型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    short format = cell.CellStyle.DataFormat;
                    if (format != 0) { return cell.DateCellValue; } else { return cell.NumericCellValue; }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }