DataTable. Compute usage

I did not expect DataTable. Compute to have so many features
1: Aggregate Function "Sum ()"

2: free computing expression "20*30 + 1"

3: bool expression "1 = 2"

4: IFF logical expression "IIF (20> 1000, 0, 1)" // It also supports IsNull, Trim, SubString, etc.

A small application: Let the DataTable simulate the Formula function of Excel.

The Formula function in Excel is powerful. It would be nice if DataTable has similar functions. Most event DataTable is only used as a data carrier and is rarely used for computing. The Compute function provided by DataTable provides powerful computing functions.

The Compute function has two parameters: Expression and Filter.

Expresstion is a calculated Expression. For details about Expression, see "http://msdn2.microsoft.com/zh-cn/library/system.data.datacolumn.expression (VS.80). aspx" here ". Filter is a condition Filter, similar to the Where condition of SQL.

1: Expresstion is described in detail here.

First look at the simplest usage, Using Aggregate functions. This aggregate function is a commonly used function in reports or Excel, such as Sum and Avg. For data columns, Datatable can be easily calculated, such as DataTable. cumpute ("Sum (column 1)", "column 1> 0"); for simple statistical functions, these functions are enough. (Other functions provided include min, max, count, variance, standard deviation, and so on ). This function is common and not surprising.

Let's look at the "free expression" calculation. Write the mathematical expression as a string and then directly input the Compute function to obtain the calculation result. In this way, the dynamically generated computing expression can be computed. For example, you need to calculate "column 1 × 30 + 20 + 2" or purely mathematical calculations.

The Compute function can also perform logical computing. For example, this expression can return false: Compute ("1 = 2", "true"); this usage may be similar to the above situation, results can be obtained when some conditions are freely assembled.

LogicTest is also frequently used in Excel. You can specify some logical expressions, such as "IF (20> 1000, 0, 1 )". How can this function be implemented if it is placed in the DataTable? Compute is also used to write the statement: "IIF (20> 1000, 0, 1 )".

IF "IF (C102 = 0, 0, C105/C102 * 30)" is implemented in the DataTable, use the above technical points.

2: The second Filter parameter is a simple query condition. For example, "true", "Id> 4", "Name like '% nd' and sex = 'male '".

The conditions cannot be complex. These conditions are sufficient.

The previously used Marge works well and can also be used for internal connections. These functions are useful when the data volume is small and requires simple computation.

Of course, IF the logic is extremely complex, this function of DT is not enough. You have to write the logic yourself (even IF you want to implement "IF (C102 =, C105/C102 * 30 )", I also need to write a bit of processing logic on my own, but it is more common. It is enough to write it once)