Home > Features, Implementation, Microsoft Excel > Microsoft Excel Alternative

Microsoft Excel Alternative

April 21, 2012

If you add a table control to your report, you can have the table work similar to a spreadsheet like Excel. You can add rows and columns. Each cell can be formatted independently. You can put text and numbers in each cell. You can link a cell to a field in a data source.

The harder part is dealing with formulas. But there is a simple method for that. Each cell can be named and you can change the default name to anything you want. So you can give them meaningful names. Then the formulas are placed in the scripts on the report before print event. The formulas would be written in vb.net, C#, or J-script. And with the programming constructs like looping, conditional if then else, case statements, etc., plus all the functions available.  You can have the calculations even more powerful than your typical spreadsheet software.

To view the results of the calculations, just run or preview the report.

Here is a simple example. Assume there is a table with two cells named A and B and they contain numeric data. If you want the sum of A and B put in a third cell named C, the script would look like this…

Private Sub Detail_BeforePrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs)
C.text = csng(A.text) + csng(B.text)
End Sub

The cells contain a text property and it needs to be converted to numeric in code so you can do calculations. The csng function does this and converts the text to a single precision value. You need to refer to each cell with the “.text” property.