How to use DynamicJasper to create an excel spreadsheet with formulas for subtotals of columns?

Question

I would like to like to produce a Excel spreadsheet using DynamicJasper that computes subtotals of columns using formulas. For example:

Employee   Department        Expenses
----------------------------------------
Alice      Sales             $600.00
Bob        IT                $400.00
Charlie    IT                $450.00

           Sales subtotal    $600.00 
           IT subtotal       $850.00

I want the bottom two rows to be produced in such a way that the numbers are the result of Excel formulas, so that if the hours were edited, the subtotals would change.

I'm encountering various difficulties currently. If I set the Expenses column to have type double, then DynamicJasper inserts $ and makes it a string. (Thus addition becomes impossible.) But a more basic problem is how to define a cell to be a subtotal of cells above it.

I would be pleased if anyone could point me towards an example that simply totaled a column, so long as it used excel formulas to accomplish it.

score 2 · Answer 1 · edited Oct 10 '11 at 23:24

it would do something like this, and this is not tested or anything:

GroupBuilder gb1 = new GroupBuilder("column_name");

gb1.addFooterVariable(frb.getColumn("column_name_with_amounts"),CALCULATION.EMPTY,style_lala,getBigDecimalFormatter());

getBigDecimalFormatter is:

private DJValueFormatter getBigDecimalFormatter(f) {
    return new DJValueFormatter(){

        public Object evaluate(Object value, Map fields, Map variables, Map parameters) {

            return "here is excel code for calculating like sum=SUM(A1:A15)";
        }

        public String getClassName() {
            return String.class.getName();
        }};
}

thats about it, but beware: you have to have full control over the data you are creating report from. At getBigDecimalFormatter for instance you have to know which rows in column you are calculating. Generally i have 2d Object array for data and i can from Map fields determine everything i need.

In my opinion is quite a lot of work, but i am not that familiar with dynamicJasper, using it for a little while now...Maybe for xls format you don't even need getBigDecimalFormatter, just b1.addFooterVariable().

But on other hand if you just create plain table of data and then user in Excel with one button creates sums, which is not that much work anyway:)

Take care,Nb

How to use DynamicJasper to create an excel spreadsheet with formulas for subtotals of columns?

1 Answers1