使用Java给现有Excel工作表创建、刷新数据透视表

数据透视表的定义及用处

数据透视表（Pivot Table）是一种对大量数据快速汇总和建立交叉关系的交互式动态表格，能帮助用户分析和组织数据。例如，计算平均值、标准差、计算百分比、建立新的数据子集等。

本文将通过使用免费Spire.XLS for Java类库来介绍如何在Java程序中给现有Excel工作表创建和刷新数据透视表。

测试环境搭建

• Intellij Idea2019.1
• JDK 1.8.0
• Spire.Xls.jar

Jar包获取及导入：

方式 1：通过官网下载Free Spire.XLS for Java产品包，解压后将lib文件夹下的Spire.Xls.jar手动导入IDEA中。具体导入方式见下图：

方式 2：创建一个Maven应用程序，在pom.xml文件中配置Maven仓库路径及指定Spire.XLS for Java的Maven依赖。

<repositories>
<repository>
<id>com.e-iceblue</id>
<url>http://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId> e-iceblue </groupId>
<artifactId>spire.xls.free</artifactId>
<version>2.2.0</version>
</dependency>
</dependencies>

配置完成后，在IDEA中，您需点击"Import Changes"即可导入JAR包；在Eclipse中，则需要点击"Save"按钮。

示例代码

示例1：创建数据透视表

import com.spire.xls.*;
public class CreatePivotTable {
    public static void main(String[] args) {
        //加载示例文档
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);

        //为需要汇总和创建分析的数据创建缓存
        CellRange dataRange = sheet.getCellRange("A1:C17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //使用缓存创建数据透视表，并指定透视表的名称以及在工作表中的位置
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("E10"), cache);

        //添加行字段
        PivotField pf=null;
        if (pt.getPivotFields().get("季度") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("季度");
        }
        pf.setAxis(AxisTypes.Row);

        //设置行字段的标题
        pt.getOptions().setRowHeaderCaption("季度");
        //添加列字段
        PivotField pf3 = null;
        if (pt.getPivotFields().get("产品类型") instanceof PivotField){
            pf3 = (PivotField) pt.getPivotFields().get("产品类型");
        }
        pf3.setAxis(AxisTypes.Column);
        //设置列字段标题
        pt.getOptions().setColumnHeaderCaption("产品类型");
        //添加值字段
        pt.getDataFields().add(pt.getPivotFields().get("销售额（万元）"),"求和项：总销售额",SubtotalTypes.Sum);

        //设置透视表样式
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //保存文档
        workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}

创建效果：

示例2：刷新数据透视表

默认情况下，数据源的更改变动不会自动更新到透视表，需要在透视表上进行刷新才能显示最新数据变化。

import com.spire.xls.*;
public class RefreshPivotTable {
    public static void main(String[] args) {
        //创建实例，加载Excel
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Test1\\Desktop\\CreatePivotTable.xlsx");

        //获取第一个工作表
        Worksheet sheet = wb.getWorksheets().get(0);

        //更改透视表的数据源数据
        sheet.getCellRange("B5").setText("扫地机器人");
        sheet.getCellRange("B9").setText("扫地机器人");
        sheet.getCellRange("B13").setText("扫地机器人");
        sheet.getCellRange("B17").setText("扫地机器人");

        //获取透视表，刷新数据
        PivotTable pivotTable = (PivotTable) sheet.getPivotTables().get(0);
        pivotTable.getCache().isRefreshOnLoad();

        //保存文档
        wb.saveToFile("output/RefreshPivotTable.xlsx",FileFormat.Version2013);
    }
}

刷新效果：