Aspose.Cells Smart markers 基于模板导出Excel

缺乏、安全感 2023-06-21 12:28 95阅读 0赞

Aspose.Cells可以预先定义Excel模板，然后填充数据(官方文档:http://www.aspose.com/docs/display/cellsjava/Smart+Markers)。

设置的模板是一个标准的Excel文件,包含了可视化的格式，公式，以及标记(smart markers)，并且可以引用多个数据源。

支持的标记(Smart Marker Options)

&=DataSource.FieldName 数据元是ICellsDataTable或者是JavaBean  
&=\[Data Source\].\[Field Name\] 数据源是 ResultSet  
&=$VariableName  一个变量  
&=$VariableArray 数组变量  
&==DynamicFormula 暂时不清楚  
&=&=RepeatDynamicFormula 动态计算

下面介绍这两种标记的使用方式

1、&=$VariableName  一个变量  
2、&=$VariableArray 数组变量

![format_png][]

Java代码

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

String path = "SmartMarkerDesigner.xls";
            InputStream inputStream = ClassLoader.getSystemResourceAsStream(path);
            Workbook wb = new Workbook(inputStream);
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.setWorkbook(wb);
            designer.setDataSource("Variable", "Single Variable");
            designer.setDataSource("MultiVariable", new String[]
            {
                    "Variable 1", "Variable 2", "Variable 3"
            });
            designer.setDataSource("MultiVariable2", new String[]
            {
                    "Skip 1", "Skip 2", "Skip 3"
            });
            designer.setDataSource("Array1Dim", new String[]
            {
                    "A1", "A2", "A3"
            });
            designer.setDataSource("Array2Dim", new String[][]
            {
                    {
                            "A11", "A12", "A13"
                    },
                    {
                            "A21", "A22", "A23", "A24"
                    },
                    {
                            "A31", "A32",
                    },
            });

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

![format_png 1][]

下面介绍JavaBean的使用方式

&=DataSource.FieldName 数据元是ICellsDataTable或者是JavaBean

&=Person.name(bean) 注意这个地方需要加上bean

![format_png 2][]

其中&=&=C\{r\}\*D\{r\} 是动态计算的标记

&=&= 构面跟一个表达式 r代表当前行，C\{r\}\*D\{r\} 这样在循环的时候没一行都会计算。

上面的例子用id这列进行分组Grouping Data

normal - The group by field(s) value is not be repeated for the corresponding records in the column; instead they are printed once per data group.

分组的列不会被循环输出，只会输出一次。

merge - The same behavior as for the normal parameter, except that it merges the cells in the group by field(s) for each group set.

和normal一样，但是他会合并单元格

repeat - The group by field(s) value is repeated for the corresponding records.

分组的列会循环输出

&=subtotal9:Person.id 放在那一列下面是按照id进行分组统计。

![format_png 3][]

或者&=Employee.money(subtotal9:Employee.id) 这么写也行，按照id进行分组统计

![format_png 4][]

![format_png 5][]

subtotalN (N的值可以是1-11 平均数 总行数 最大值 最小值 总和 等等。)

Performs a summary operation for a specified field data related to a group by field. The N represents numbers between 1 and 11 which specify the function used when calculating subtotals within a list of data. (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN,...9=SUM etc.) Refer to the Subtotal reference in Microsoft Excel's help for further details.

The format actually states as:  
subtotalN:Ref where Ref refers to the group by column.

&=Products.Units(subtotal9:Products.ProductID) specifies summary function upon **Units** field with respect to the **ProductID** field in the **Products** table.

&=Tabx.Col3(subtotal9:Tabx.Col1) specifies summary function upon the **Col3** field group by **Col1** in the table **Tabx**.

&=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) specifies summary function upon **ColumnD** field group by **ColumnA** and **ColumnB** in the table **Table1**.

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMTIwNDA0ODU0MDkuanBn?x-oss-process=image/format,png
[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMTMyMDU0ODk1ODAuanBn?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMTkzNzkyMzczMDMuanBn?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMzQwODYyNjQ3MDkuanBn?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMzA0OTA3OTgzNDYuanBn?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNTMxMDcyLzIwMTQwOC8yNTEwMzMyNzg5MTQ2NjUuanBn?x-oss-process=image/format,png