C# 基于NPOI操作Excel

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

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

1 單元格下拉框

在開(kāi)發(fā)中我們會(huì)遇到為單元格設(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ù)組存在長(zhǎng)度限制，如NPOI導(dǎo)出Excel時(shí)下拉列表值超過(guò)255的問(wèn)題（String literals in formulas can't be bigger than 255 characters ASCII）
解決方案
通過(guò)額外新建Excel的Sheet頁(yè)保存下拉內(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;
// 注意下面的語(yǔ)法是Excel的公式，建議不要落掉一個(gè)`$`，很多文檔都要所遺漏。
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();
// 這個(gè)代碼參數(shù)不要寫成固定的，它用來(lái)定位你的批注的位置和大小。
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é)果沒(méi)有等號(hào)“=”, 這里我是演示，所以寫了局部變量。

日期格式 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é)語(yǔ)

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

常見(jiàn)問(wèn)題解決

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("請(qǐng)?zhí)顚懲暾块T名稱！");
HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);//將批注給予單元格
cell12.CellComment = comment12;

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

因?yàn)槊總€(gè)單元格的批注的位置都是不一樣的（編輯批注時(shí)的位置）。那么怎么辦呢，當(dāng)然是需要了解參數(shù)的意思：

簡(jiǎn)單說(shuō)來(lái)：

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

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

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

以我自己做的一個(gè)例子來(lái)說(shuō)：

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

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

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

POI導(dǎo)出Excel時(shí)下拉列表值超過(guò)255的問(wèn)題

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

Workbook workbook = new HSSFWorkbook();

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

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

 

//去數(shù)據(jù)庫(kù)中查詢我們想要的數(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)建一個(gè)數(shù)組 用來(lái)存放 我們?nèi)〕鰜?lái)的數(shù)據(jù)

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

//遍歷每個(gè)peoduct對(duì)象，來(lái)獲取productName屬性并添加到數(shù)組中

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

{ Product product = productList.get(i);

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

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

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

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

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

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

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

String name = productNameArray[i];

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

Row row = hidden.createRow(i);

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

cell = row.createCell(0);

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

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)換為L(zhǎng)ist形式

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ù)的對(duì)應(yīng)關(guān)系 綁定到一起

DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

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

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

//最后將文件導(dǎo)出就可以了，后面的代碼就不寫了，我只寫一些這個(gè)問(wèn)題相關(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通過(guò)新的sheet數(shù)據(jù)加載下拉框數(shù)據(jù)
    Sheet hidden = workbook.createSheet(sheetName);

    //創(chuàng)建單元格對(duì)象
    Cell cell =null;
    //遍歷我們上面的數(shù)組，將數(shù)據(jù)取出來(lái)放到新sheet的單元格中
    for (int i = 0, length = sheetData.length; i < length; i++){
        //取出數(shù)組中的每個(gè)元素
        String name = sheetData[i];
        //根據(jù)i創(chuàng)建相應(yīng)的行對(duì)象（說(shuō)明我們將會(huì)把每個(gè)元素單獨(dú)放一行）
        Row row = hidden.createRow(i);
        //創(chuàng)建每一行中的第一個(gè)單元格
        cell = row.createCell(0);
        //然后將數(shù)組中的元素賦值給這個(gè)單元格
        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)換為L(zhǎng)ist形式
    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ù)的對(duì)應(yīng)關(guān)系 綁定到一起
    DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

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

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

原因

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

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

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

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

所以單元格被判斷成數(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;
            }
        }