JXLS模板开发图表报表「终于解决」

云烟 • 2023年 8月 21日下午8:00 • 未分类 • 阅读 183

欢迎大家来到IT世界,在知识的湖畔探索吧!

背景：因业务需求，需要开发带有统计图表的excel报表。做了一番调研，发现市面上做excel报表的开源库还是比较多的，其中比较优秀的有JXLS、EasyExcel。EasyExcel属于阿里系，据说生成速度比较快，也省内存，但模板填充功能比较简单，比较复杂的一些excel报表难以用模板实现，要书写较多的额外代码，无法满足日后要利用模板开发大量报表需求的目的。而JXLS模板填充功能在所有开源库中算是比较全面的，也支持使用SXSSF（Streaming Usermodel API，这是一种专门用于处理大型Excel文件的API，可以在内存中处理大量数据并生成Excel文件，避免OutOfMemoryError），本文主要讲述利用JXLS开发带图表的excel报表。

一、JXLS

先说一下什么是JXLS。JXLS是一个流行的Excel报表模板库，可以将数据填充到Excel模板中，生成丰富的Excel报表。JXLS支持多种数据源，包括Java集合、JavaBeans、SQL查询等。它还提供了多种模板标记，可以方便地控制报表的样式和布局。

JXLS的官网 http://jxls.sourceforge.net，上面有模板相关标签的使用方法及sample。相对还算丰富，包含循环(纵向、横向)、条件判断、动态表格、自定义函数、公式等，基本满足我们现在excel报表的实现需求。JXLS有1跟2版本，有较大区别，2版本改用批注方式写命令标签，功能也更丰富，我们采用jxls2最新版。

使用jxls需引入依赖：

<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls</artifactId>
	<version>2.11.0</version>
</dependency>
<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls-poi</artifactId>
	<version>2.11.0</version>
</dependency>

欢迎大家来到IT世界,在知识的湖畔探索吧!

一个简单的例子

以下模板使用jx:each输出一个列表。

支持的命令

除了jx:each，jxls还支持很多命令，以下是一些常用的。

jx:each: 遍历集合，生成多行数据。
jx:if: 根据条件判断是否生成数据。
jx:area: 定义单元格范围。
jx:grid: 将数据导出到指定的单元格范围内
jx:eachCommand: 在循环中执行自定义命令。
jx:formula: 计算公式并将结果输出到单元格中。
jx:image: 在单元格中插入图片。
jx:mergeCells: 合并单元格。
jx:dateCell: 输出日期并指定格式。

用法详见官网示例: https://jxls.sourceforge.net/samples/object_collection.html

二、在excel中加入图表

如何在excel报表中加入图表呢？先看以下两种方式的生成效果。

方式一：使用Excel自带的图表工具

此方式的好处是如果数据发生改变，图表也会随之改变。

创建一个包含图表的 Excel 模板文件。模板中分别插入了一个柱状图、一个折线图。

生成效果。

方式二：使用Image作为图表

此方式使用图片生成图表，所以当数据发生变化时，图表无法联动。而且由于图片的比较大，会使生成的报表比使用excel自带的图表工具要大很多。

创建一个 Excel 模板文件。模板中通过jx:chart批注插入了一个柱状图、一个折线图（src、imageType属性）。

生成效果。

Image方式产生的图表使用的是d3.js库所产生的svg转换成图片而来。

JXLS扩展

从上面例子可以看到，无论方式一还是方式二，模板中都有一个jx:chart的批注，此命令并非JXLS自带的command，是为了展示图表而扩展的一个command。因为模板中插入的图表在生成最终报表时，图表的位置及大小不能随数据的部局变化而自行移动到合适的位置，并且图表xy轴也不能随动态填充的数据而改变选择数据的范围，所以扩展一个jx:chart command来实现这个功能。

jx:chart命令的作用是确定图表在生成的excel中报表中放置的位置、宽度高度及选择数据的单元格区域，它具有以下属性：

(注：粗体为必填项，方式一时必填seriesAxis，方式二时必填imageBytes)

欢迎大家来到IT世界,在知识的湖畔探索吧!chartName;//图片名称，需与模板里插入的图表名称匹配。 String
startCol;//起始列位置(index从1算起)。 int
startRow;//起始行位置(index从1算起)。 int
cols;//长度占多少列单元格。 int
rows;//高度占多少行单元格。 int
seriesAxis;//选择数据，当使用excel的图表工具插入图表时，需选择依赖的单元格数据。String
           //此属性是一个数组串，一个数组元素代表一个数据选择(x、y轴依赖的数据域)，
           //一个数组元素中包含的属性有：
           //        catAx=图表右键选择数据里的水平(分类)轴坐标
           //        catStartCol=x轴数据区域起始列位置(index从1算起)
           //        catStartRow=x轴数据区域起始行位置(index从1算起)
           //        catCols=x轴数据区域长度占多少列单元格
           //        catRows=x轴数据区域长度占多少行单元格
           //
           //        valAx=图表右键选择数据里的Y值
           //        valStartCol=y轴数据区域起始列位置(index从1算起)
           //        valStartRow=y轴数据区域起始行位置(index从1算起)
           //        valCols=y轴数据区域长度占多少列单元格
           //        valRows=y轴数据区域长度占多少行单元格
imageBytes;//图片数据，当使用图片方式生成图表时需填写的属性。 byte[]
imageType;//图片格式，JPEG、PNG。 String

还是以上面方式一的例子，以柱图为例，它的批注如下：

jx:chart(chartName="chart1" cols="4" rows="14" lastCell="C7" seriesAxis="[{catAx=Template!$A$5, catStartCol=1, catStartRow=5, catCols=1, catRows=items1.size(), valAx=Template!$B$5, valStartCol=2, valStartRow=5, valCols=1, valRows=items1.size()}, {catAx=Template!$A$5, catStartCol=1, catStartRow=5, catCols=1, catRows=items1.size(), valAx=Template!$C$5, valStartCol=3, valStartRow=5, valCols=1, valRows=items1.size()}]")
//注释：
//1. chartName="chart1" 与柱图的名称对应（图中蓝色部分）
//2. cols="4" 图表的宽度为4
//3. rows="14" 图表的高度为14
//4. seriesAxis中有两个元素：
//  (1)    catAx=Template!$A$5  x轴数据区域
//         catStartCol=1  x轴数据区域起始列
//         catStartRow=5  x轴数据区域起始行
//         catCols=1  x轴数据区域宽度为1
//         catRows=items1.size()  x轴数据区域高度
//         valAx=Template!$B$5  y轴数据区域
//         catStartCol=2  y轴数据区域起始列
//         catStartRow=5  y轴数据区域起始行
//         valCols=1  y轴数据区域宽度为1
//         valRows=items1.size()  y轴数据区域高度
//  (2)    ...

右键data1图表，选择数据…

三、示例代码

欢迎大家来到IT世界,在知识的湖畔探索吧!package com.test.report.generation.generator.chart;

@Slf4j
public class JxlsChartSample {

    public Map<String, Object> getData() throws Exception {
        Map<String, Object> data = new HashMap<>();
        List<Item> items1 = new ArrayList<>();

        Item item1 = new Item();
        item1.setName("股票 (香港)");
        item1.setY1(new BigDecimal("9001558.50"));
        item1.setY2(new BigDecimal("3501558.50"));
        item1.setValue(item1.getY1());
        items1.add(item1);

        Item item2 = new Item();
        item2.setName("股票 (美國)");
        item2.setY1(new BigDecimal("6501558.50"));
        item2.setY2(new BigDecimal("4234266.50"));
        item2.setValue(item2.getY1());
        items1.add(item2);

        Item item3 = new Item();
        item3.setName("股票 (其他)");
        item3.setY1(new BigDecimal("20015580.43"));
        item3.setY2(new BigDecimal("7015580.50"));
        item3.setValue(item3.getY1());
        items1.add(item3);

        Item item4 = new Item();
        item4.setName("期貨");
        item4.setY1(new BigDecimal("1941558.50"));
        item4.setY2(new BigDecimal("3501558.50"));
        item4.setValue(item4.getY1());
        items1.add(item4);

        Item item5 = new Item();
        item5.setName("轉倉");
        item5.setY1(new BigDecimal("-2015580.50"));
        item5.setY2(new BigDecimal("-40015580.50"));
        item5.setValue(item5.getY1());
        items1.add(item5);

        Item item6 = new Item();
        item6.setName("利息 (香港)");
        item6.setY1(new BigDecimal("-30401558.50"));
        item6.setY2(new BigDecimal("-40201558.50"));
        item6.setValue(item6.getY1());
        items1.add(item6);

        Item item7 = new Item();
        item7.setName("利息 (美國)");
        item7.setY1(new BigDecimal("-50155890.50"));
        item7.setY2(new BigDecimal("-3015580.50"));
        item7.setValue(item7.getY1());
        items1.add(item7);

        List<Item> items2 = new ArrayList<>();
        for (Item item : items1) {
            Item itemClone = item.clone();
            itemClone.setName("华盛-" + item.getName());
            BigDecimal multiplier = new BigDecimal("3000");
            itemClone.setY1(item.getY1().multiply(multiplier));
            itemClone.setY2(item.getY2().multiply(multiplier));
            itemClone.setValue(itemClone.getY1());
            items2.add(itemClone);
        }

        //todo 方式一:ExcelSelf 使用Excel自带的图表工具生成图表（1..在Excel模板中插入图表，选择数据；2.在Excel模板中增加jx:chart批注，确定图表的位置及大小。）
        data.put("items1", items1);
        data.put("items2", items2);

        //todo 方式二:Image 使用图片格式的图表（1.ScriptEngineInvoke.generateChartImage会根据传入的数据及图表类型，调用js生成SVG串，再将SVG串转成PNG图片。2.再在Excel模板中增加jx:chart批注，确定图表的位置及大小，src为PNG的byte值。）
        //data.put("lineChartPngByte", ScriptEngineInvoke.generateChartImage(items1, ChartTypeEnum.Line.options()));
        //data.put("barChartPngByte", ScriptEngineInvoke.generateChartImage(items2, ChartTypeEnum.Bar.options()));

        return data;
    }

    @Data
    public static class Item implements Cloneable{

        private String name;
        private BigDecimal y1;
        private BigDecimal y2;
        private BigDecimal value;

        @Override
        public Item clone() {
            Item detail = null;
            try {
                detail = (Item) super.clone();
            } catch (CloneNotSupportedException e) {
                throw new RuntimeException(e);
            }
            return detail;
        }
    }

    private static String template = "/templates/charts_ExcelSelf.xlsx";
    private static String output = "report/src/main/resources/1_charts_ExcelSelf.xlsx";

    public static void main(String[] args) throws Exception {
        JxlsChartSample jxlsChartSample = new JxlsChartSample();
        Map<String, Object> data = jxlsChartSample.getData(new HashMap<>());
        log.info("Opening input stream");
        try (InputStream is = JxlsChartSample.class.getResourceAsStream(template)) {
            log.info("InputStream={}", is);
            try (OutputStream os = new FileOutputStream(output)) {
                Context context = PoiTransformer.createInitialContext();
                context.putVar("items1", data.get("items1"));
                context.putVar("items2", data.get("items2"));
                context.putVar("lineChartPngByte", data.get("lineChartPngByte"));
                context.putVar("barChartPngByte", data.get("barChartPngByte"));
                // with multi sheets it is better to use StandardFormulaProcessor by disabling the FastFormulaProcessor
                //JxlsHelper.getInstance().setUseFastFormulaProcessor(false).processTemplate(is, os, context);
                JxlsExcelGenerator jxlsExcelGenerator = new JxlsExcelGenerator();
                jxlsExcelGenerator.processTemplate(is, os, context);
            }
        }
    }
}

总结一下，开发一个Excel图表的报表步骤：

创建模板。
模板中插入图表（修改图表名称）。
为图表选择数据区域。
添加jx:chart命令批注（对应好图表名称）。

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://itzsg.com/22812.html