2.3.9 using npoi for Excel -- using npoi to obtain the return value of the formula

As we learned how to set a formula in Excel through npoi, some readers may ask: "Can npoi obtain the return value of the formula ?", The answer is yes!
1. Obtain the return value of the formula in the template file
For example, if an Excel file named text.xls is in the dashboard, its content is as follows:

Note that the formula "$ A1 * $ B1" is set in cell C1, rather than the value "12 ". With npoi, you only need to write a few simple statements. Code The return value of this formula can be obtained: Hssfworkbook WB =   New Hssfworkbook ( New Filestream ( " D:/test.xls " , Filemode. Open ));
Hssfcell Cell = WB. getsheet ( " Sheet1 " ). Getrow ( 0 ). Getcell ( 2 );
System. Console. writeline (cell. numericcellvalue );

Output result:

When npoisucceeds, the formula in the .xls file is used. Note that the numericcellvalue attribute is automatically processed based on the cell type. If it is null, 0 is returned. If it is a numerical value, a numerical value is returned. If it is a formula, the calculated result is returned. The cell type can be obtained through the celltype attribute.

2. Obtain the return value of the formula in the Excel file generated by npoi
In the above example, the return value of the formula is obtained from an existing Excel file. If the Excel file is created using npoi, you can use the above method to obtain the expected result. For example: 1 Hssfworkbook =   New Hssfworkbook ();
2 Hssfsheet sheet1 = Hssfworkbook. createsheet ( " Sheet1 " );
3 Hssfrow row = Sheet1.createrow ( 0 );
4 Row. createcell ( 0 ). Setcellvalue ( 3 );
5 Row. createcell ( 1 ). Setcellvalue ( 4 );
6 Hssfcell Cell = Row. createcell ( 2 );
7
8 Cell. setcellformula ( " $ A1 + $ B1 " );
9 System. Console. writeline (cell. numericcellvalue );

Execute the above Code and output the result "0" instead of the expected result "7 ". How can this problem be solved? The hssfformulaevaluator class is used. You can add the following two sentences after the first row: Hssfformulaevaluator E= NewHssfformulaevaluator (hssfworkbook );
Cell=E. evaluateincell (cell );

The running result is as follows:

 

Returned directory