2.3.7 use npoi to operate the Excel-lookup Function

Today, let's take a look at the lookup function in Excel. The basic syntax is Lookup (lookup_value, lookup_vector, result_vector ). It is easier to understand with examples:

Code
Hssfsheet sheet1 = Hssfworkbook. createsheet ( " Sheet1 " );
Hssfrow row1 = Sheet1.createrow ( 0 );
Row1.createcell ( 0 ). Setcellvalue ( " Lowest income " );
Row1.createcell ( 1 ). Setcellvalue ( " Highest income " );
Row1.createcell ( 2 ). Setcellvalue ( " Tax Rate " );

Hssfrow row2=Sheet1.createrow (1);
Row2.createcell (0). Setcellvalue (0);
Row2.createcell (1). Setcellvalue (3000);
Row2.createcell (2). Setcellvalue (0.1);

hssfrow row3 = sheet1.createrow ( 2 );
row3.createcell ( 0 ). setcellvalue ( 3001 );
row3.createcell ( 1 ). setcellvalue ( 10000 );
row3.createcell ( 2 ). setcellvalue ( 0.2 );

hssfrow row4 = sheet1.createrow ( 3 );
row4.createcell ( 0 ). setcellvalue ( 10001 );
row4.createcell ( 1 ). setcellvalue ( 20000 );
row4.createcell ( 2 ). setcellvalue ( 0.3 );

hssfrow row5 = sheet1.createrow ( 4 );
row5.createcell ( 0 ). setcellvalue ( 20001 );
row5.createcell ( 1 ). setcellvalue ( 50000 );
row5.createcell ( 2 ). setcellvalue ( 0.4 );

hssfrow row6 = sheet1.createrow ( 5 );
row6.createcell ( 0 ). setcellvalue ( 50001 );
row6.createcell ( 2 ). setcellvalue ( 0.5 );

hssfrow row8 = sheet1.createrow ( 7 );
row8.createcell ( 0 ). setcellvalue ( " revenue " );
row8.createcell ( 1 ). setcellvalue ( " tax rate " );

hssfrow row9 = sheet1.createrow ( 8 );
row9.createcell ( 0 ). setcellvalue ( 7800 );
row9.createcell ( 1 ). setcellformula ( " Lookup (A9, $ A $2: $ A $6, $ C $2: $ C $6) " );

This is an example of querying the corresponding tax rate based on wages. We first create a dictionary of tax rates corresponding to different wage ranges, and then find the corresponding tax rates in the dictionary based on the specific wage. The Excel file generated after execution is as follows:
each parameter is described below:
the first parameter: the content to be searched. In this example, it points to the A9 cell, that is, 7800;
the second parameter: Compare the object region. In this example, the salary needs to be compared with the values in each cell in $ A $2: $ A $6; the third parameter: search result area. If it matches, the corresponding data in this area will be returned. In this example, the value corresponding to $ C $2: $ C $6 is returned.
someone may ask, if the dictionary does not have a 7800 tax rate, how can it be matched in Excel? The answer is fuzzy match, and The lookup function only supports fuzzy match. In Excel, the maximum value less than 7800 is found in $ A $2: $ A $6, that is, the 3001 of A3, and then the corresponding $ C $2: the value in C3 in the $ C $6 region is returned, which is the origin of the final result 0.2. Now you understand:

Vlookup
In addition, the lookup function has a big brother, Vlookup. The two brothers have many similarities, but they have more skills. Vlookup compares the number of comparisons with a "table", instead of one column or one row of the lookup function. Vlookup can choose exact or fuzzy query, lookup only supports fuzzy queries.
SetCodeReplace: Row9.createcell (1). Setcellformula ("Vlookup (A9, $ A $2: $ C $6, 3, true)");

The Excel Style generated after execution is as follows:

The first parameter: the content to be searched. Here is the A9 cell;
The second parameter: The table to be compared. Here is $ A $2: $ C $6. Note that Vlookup matches only the first column in the table.
Third parameter: the column number corresponding to the matching result. The tax rate column corresponds to 3.
The fourth parameter indicates whether to perform fuzzy match. In this example, "true" indicates fuzzy match, which is the same as that in the previous example. The third line is matched. If you change this parameter to false, the calculation error "# N/A" is reported because 1st cannot be found in column 7800 of the table.

In addition, there are hlookup similar to vlokup. The difference is that Vlookup is used to find the specified value in the first column of the table or value array, and then return the value at the specified column in the current row of the table or array. However, hlookup is used to search for the specified value in the first row of a table or numeric array, and returns the value at the specified row in the current column of the table or array. You can try it yourself.

 

Returned directory