如何使用 DynamicJasper 创建带有列小计公式的 Excel 电子表格？

发布于 2024-09-19 23:39:34 字数 631 浏览 5 评论 0原文

我想使用 DynamicJasper 生成一个 Excel 电子表格，使用公式计算列的小计。例如：

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

           Sales subtotal    $600.00 
           IT subtotal       $850.00

我希望以这样的方式生成底部两行，即数字是 Excel 公式的结果，这样如果编辑小时数，小计就会发生变化。

目前我遇到了各种困难。如果我将 Expenses 列设置为 double 类型，则 DynamicJasper 会插入 $ 并将其设为字符串。（因此加法变得不可能。）但是一个更基本的问题是如何将一个单元格定义为它上面的单元格的小计。

如果有人能给我指出一个简单地汇总一列的示例，我会很高兴，只要它使用 Excel 公式来完成即可。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱她像谁 2024-09-26 23:39:34

它会做类似这样的事情，并且这没有经过测试或任何东西：

GroupBuilder gb1 = new GroupBuilder("column_name");

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

getBigDecimalFormatter 是：

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();
        }};
}

就是这样，但要注意：您必须完全控制要从中创建报告的数据。例如，在 getBigDecimalFormatter 中，您必须知道要计算列中的哪些行。一般来说，我有数据的二维对象数组，我可以从地图字段确定我需要的一切。

在我看来，这是相当多的工作，但我对dynamicJasper不太熟悉，现在使用它有一段时间了......也许对于xls格式，你甚至不需要getBigDecimalFormatter，只需<代码>b1.addFooterVariable()。

但另一方面，如果您只是创建普通的数据表，然后 Excel 中的用户通过一个按钮创建总和，无论如何这也不是那么多工作:)

小心，Nb

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

回复收藏 0 原文

~没有更多了~